:How to make a parameterized action query... 
Author Message
 :How to make a parameterized action query...

I've been trying to use an insert query with parameters.  For some
reason, after I set the parameters in my code and try to perform the
query, I get the error: 3 Parameters Were Expected, but 0 Were Supplied.
Well, I just supplied them on the previous 3 lines, so something's not
kosher.  I've just about concluded that you can't do this, but I'm hoping
someone can tell me that I can.  Also, I realize that I can do this with
an AddNew method, but I've been trying to stay away from those since I
heard that it used up memory and didn't free it. Anyway, I've included the
query and how I execute it in my program incase that helps.  Also, I'm
using VB 3.0 Professional and Access 2.0.

Here's the query:

PARAMETERS [theSSN:] Long, [theJob#:] Long, [New Weekend:] DateTime;
INSERT INTO Turnaround ([Last Name], [First Name], SSN, [Job Order #],
Company, [Pay Rate], [Bill Rate], [Reg Hours], Overtime, Weekend)
SELECT DISTINCT Turnaround.[Last Name], Turnaround.[First Name],
Turnaround.SSN, Turnaround.[Job Order #], Turnaround.Company,
Turnaround.[Pay Rate], Turnaround.[Bill Rate], Turnaround.[Reg Hours],
Turnaround.Overtime, [New Weekend:] AS Weekend
FROM Turnaround
WHERE ((Turnaround.SSN=[theSSN:]) AND (Turnaround.[Job Order #]=

(All it does is find a record in the Turnaround table that has a certain
Social Security # and Job Order # matching the supplied parameters, and then
adds another record to that table exactly the same except that the
Weekend field is changed to the value supplied as a parameter. )

My code to exectue the query is this:

Set qryTurnaround = dbPayroll("AddAnOldTimecard")
qryTurnaround![theSSN:] = frmTurnaround!Data1.Recordset("SSN")
qryTurnaround![theJob#:] = frmTurnaround!Data1.Recordset("Job Order #")
qryTurnaround![New Weekend:] = mskWeekending.Text
dbPayroll.Execute qryTurnaround

Obviously, I've supplied the parameters, but VB doesn't think so.  Also,
I've checked their values as the query is executed, and they're there all

Now, this query will work if you make it just a select query by removing
the insert into line and use it to create a dynaset instead of executing
it. ie: Set dsTurnaround = qryTurnaround.CreateDynaset(), but not as an
action query.

So what is the verdict?  Can I make an parameterized action query or not,
and if so, what do I need to change to make it work?  I admit, I've never
seen an example of a parameterized action query, but then, I can't remember
ever being told I couldn't make one either.  Well, maybe until now.

Thanks for any advice.

Sun, 28 Dec 1997 03:00:00 GMT  
 :How to make a parameterized action query...

Subject: Re: [Q]:How to make a parameterized
action query...

>I've been trying to use an insert query with parameters.  For some
>reason, after I set the parameters in my code and try to perform the
>query, I get the error: 3 Parameters Were Expected, but 0 Were Supplied.

     Access has a bug, it ALWAYS says 0 supplied, even if some were.
Translation, you are missing some, but not necessarily all.

"640K ought to be enough for anybody." (Bill Gates, 1981)
QWicKeSST - The ultimate database QWK reader, and NO limits. #BLYGJOMK
Special Compile: 1.008A (Alpha)

'[1;35;40m-=> Delphi Internet Jet SST v3.013A (Alpha) - (C) PBE

Fri, 02 Jan 1998 03:00:00 GMT  
 [ 2 post ] 

 Relevant Pages 

1. Parameterizing an update query???

2. Parameterized Update query not working correctly in Access 97

3. Parameterized Queries through DAO to Access

4. parameterized Query/Report

5. recordset from a parameterized query

6. recordsets in parameterized queries

7. parameterized query

8. Function whose agrument is the Parameter to Parameterized Query

9. Parameterized query on multiple fields

10. Parameterized Queries

11. Using a Parameterized Query

12. Using a Parameterized Query


Powered by phpBB® Forum Software