Cody Vance
asked on
Need Help with Update Query
Hello again Experts,
I am trying to create an update query (see code below) that take information of the open form and updates my table. I believe the code is self explanatory. It takes the MMS# and and finds it in the table then it should take the OnHand value and minus the Qty1 and update the OnHand value to the new value. Whenever i run this query it keeps asking me for the values (cant find Qty1, etc).
Thanks for the help,
I am trying to create an update query (see code below) that take information of the open form and updates my table. I believe the code is self explanatory. It takes the MMS# and and finds it in the table then it should take the OnHand value and minus the Qty1 and update the OnHand value to the new value. Whenever i run this query it keeps asking me for the values (cant find Qty1, etc).
Thanks for the help,
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-[Forms]![WHReq].[Form]![Qty1]
WHERE (((tblPaperCost.MMSNo)=[Forms]![WHReq].[Form]![MMS1]));
ASKER
Nope still prompts for parameter value. :(
Is that Form open when the query runs ?
mx
mx
ASKER
Yeah it is
OK ...for the Text box that is holding Qty ... give the text a Name of txtQty1 ... then try this:
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-[Forms]![WHReq ]![txtQty1 ]
WHERE (((tblPaperCost.MMSNo)=[Fo rms]![WHRe q]![MMS1]) );
mx
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-[Forms]![WHReq
WHERE (((tblPaperCost.MMSNo)=[Fo
mx
ASKER
I have tried that already as well, just prompts for txtQty1 instead.. Is there maybe VBA code that does the same thing I could try?
Can you:
1) Compact & Repair (*** to shrink the size),
2) Zip up the MDB (*** to further shrink the size)
3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.
4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.
NOTE: If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
1) Compact & Repair (*** to shrink the size),
2) Zip up the MDB (*** to further shrink the size)
3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.
4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.
NOTE: If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
ASKER
I wont be able to send it to you, on top of having user-level security the tables are linked :(
As a Test ... what happens if you do this:
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-1
WHERE (((tblPaperCost.MMSNo)=[Fo rms]![WHRe q]![MMS1]) );
?
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-1
WHERE (((tblPaperCost.MMSNo)=[Fo
?
ASKER
Prompts for MMS1
ASKER
Wow ok i just figured it out, I didn't put the frm naming convention in front of WHReq in my code :::Beating head on wall:::
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank for your help once again MX
You are welcome ....
mx
mx
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-[Forms]![WHReq
WHERE (((tblPaperCost.MMSNo)=[Fo
mx