• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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