Executing Update query thru code (passing parameters)

[AT76]

Hi,

I'm trying to execute an update query thru code. When I do it manually it works OK, but when it does it thru code I get an error which says the query is missing 2 parameters. I am taking to values from a form. They are instantiated in the query but for some reason the 2 parameters do not get passed when running the query using code.

Here's my code:

 Set mydb = DBEngine.Workspaces(0).Databases(0)
    Set myQuery = mydb.QueryDefs("Q_WHSEDIST REQUEST 10")
    mydb.Execute (myQuery.Name)
    myQuery.Close

Here's is my SQL string generated by ACCESS:

SELECT [Q_RETURN REQUEST LETTER].VNDRCODE, [Q_RETURN REQUEST LETTER].SF, [Q_RETURN REQUEST LETTER].SUPPLIER, [Q_RETURN REQUEST LETTER].[SURPLUS ADDRESS], [Q_RETURN REQUEST LETTER].ADDRESS4, [Q_RETURN REQUEST LETTER].PHONE, [Q_RETURN REQUEST LETTER].FAX, [Q_RETURN REQUEST LETTER].ATTN, [Q_RETURN REQUEST LETTER].DEAR INTO T_WHSE2
FROM [Q_RETURN REQUEST LETTER]
WHERE ((([Q_RETURN REQUEST LETTER].VNDRCODE)=[Forms]![F_VENDOR RETURN REQUESTS]![txtVendorID]) AND (([Q_RETURN REQUEST LETTER].SF)=IIf([Forms]![F_VENDOR RETURN REQUESTS]![txtVendorSF]="0","      ",[Forms]![F_VENDOR RETURN REQUESTS]![txtVendorSF])));

I also tried doing the following but wasn't successful:

 Set mydb = DBEngine.Workspaces(0).Databases(0)
    Set myQuery = mydb.QueryDefs("Q_WHSEDIST REQUEST 10")
    [b]myQuery.Parameters(0).Value = CStr([Forms]![F_VENDOR RETURN REQUESTS]![txtVendorID])
    myQuery.Parameters(1).Value = CStr([Forms]![F_VENDOR RETURN REQUESTS]![txtVendorSF])[/b]
    mydb.Execute (myQuery.Name)
    myQuery.Close

I appreciate any help provided.

 

[ecompa]

I never worked using this way, but what you could do also is to define a variable for each of your form inputs and run the query inside the code, as a string where the two parameters are the variable.

Now, I had already that kind of message, when something was wrong in the resulting query: a variable not set, for instance.

 

[mp9]

I don't see an update query at all, I just see a select query.

http://www22.brinkster.com/accessory/

 

[AT76]

You are correct mp9. This is a creary to create Table: T_WHSE2. When I do it manually it creates records, but thru code it doesn't.

 

[PHV]

Have you tried to replace this:
mydb.Execute (myQuery.Name)
with this ?
myQuery.Execute

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

Thanks PHV. I tried what you suggested but I'm still getting the error message.