• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • 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
  • 4
  • 3
  • 2
  • +3
1 Solution
 
zadeveloperCommented:
Try this:

dim sqlString as String
sqlString = "UPDATE tblPaperCost SET tblPaperCost.[OnHandQty] = tblPaperCost.[OnHandQty]-" & Forms!WHReq & "." & Form!Qty1 & " WHERE tblPaperCost.[MMSNo] = " & Forms!WHReq & "." & Form!MMS1"

Open in new window

0
 
zadeveloperCommented:
sorry : I am assuming that you are doing this from a VB6 from ?
0
 
SharathData EngineerCommented:
Make sure that you have Qty1 field on your form. If it is unknow, Access prompts for user input.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Cody VanceSr. Analyst - ERPAuthor Commented:
zadeveloper,
im not sure where i would put this?  I am using an update query not VBA code.
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Qty1 is on the form
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you working with a Form or a Form/Subform?

You refer to a Form like this:

Forms!YourFormName.YourControlName

You refer to a subform like this:

Forms!YourFormName.YourSubformControlName.Form.YourSubformControl

0
 
BelloneCommented:
If you are not referring to a subform in form WHReq, then you don't need '.Form'.  
To refer to fields on a form the syntax is Forms!FormName!FieldName.  To refer to a field on a subform you need Form!FormName!SubformName.Form!FieldName.
0
 
Mark WillsTopic AdvisorCommented:
By the sounds of it, I would first try :

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

Where the dot comes into it is with subforms.

From the main/parent form, you would normally refer to an object on one of its subforms like :
[WHReq].Form![Qty1]

Or if running outside those constraints, and with a subform, seem to be missing the formname
[forms]![parent-form-name-goes-here]![WHReq].Form![Qty1]

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If this were an issue with your Form nameing syntax, then it would appear that my comment would have been the one that pointed you in the right direction.
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
i dont see where you said anything about the name of my form, but ok.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I didn't say anything about the specific form you're using, since I have no knowledge of the objects in your database.
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Doesnt bother me, i like giving points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now