Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

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,
UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-[Forms]![WHReq].[Form]![Qty1]
WHERE (((tblPaperCost.MMSNo)=[Forms]![WHReq].[Form]![MMS1]));

Open in new window

0
Cody Vance
Asked:
Cody Vance
  • 7
  • 7
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try This:

UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-[Forms]![WHReq]![Qty1]
WHERE (((tblPaperCost.MMSNo)=[Forms]![WHReq]![MMS1]));

mx
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Nope still prompts for parameter value. :(
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is that Form open when the query runs ?

mx
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Cody VanceSr. Analyst - ERPAuthor Commented:
Yeah it is
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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)=[Forms]![WHReq]![MMS1]));

mx
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
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?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
I wont be able to send it to you, on top of having user-level security the tables are linked :(
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
As a Test ... what happens if you do this:


UPDATE tblPaperCost SET tblPaperCost.OnHandQty = [OnHandQty]-1
WHERE (((tblPaperCost.MMSNo)=[Forms]![WHReq]![MMS1]));

?
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Prompts for MMS1
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
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:::
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Kind of what I thought :-)

Thought it would prompt for MMS1.

mx
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Thank for your help once again MX
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ....

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now