SQL Update Query from VBA - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL Update Query from VBA

Stephen Kuperman

5 years ago

I have a database for a school district that has a tables for Users, Products and Locations. Every User (Student) has a location number which is typically a school. Every computer has a location number either in a warehouse or if assigned to a student / teacher the location would be the same school building they attend.
The location number is just a drop down box on the User and Product forms.
When I assign a computer to a student I need to change the Product (computer) tables location number.
Ideally I would like to just use a simple SQL Update query that would fire when the Student is assigned a laptop  so using an After Update Event.
I have searched all over looking for help to figure out an Update Query that I could run from VBA when the User (student) drop down box changes location, then change the location to match in the Product table.
Sounds confusing  every student / teacher has a location (school).
Every computer has a location, but is not always assigned to a student / teacher .

This is what I have tried
UPDATE Products
INNER JOIN Users ON Products.UserID = Users.UserID

SET Products.Location = [Users].[LocationID]
WHERE (((Products.P_ID) = [Users].[ProdID]));

But I dont see how to run this from VBA as the DoCmd.RunSQL does not seem to work with it.

Any help on this would be appreciated.

	Richard Rost @Reply 5 years ago
Does this work if you run it as a normal update query?

	Stephen Kuperman OP @Reply 5 years ago
No, nothing seems to work on this.

	Scott Axton @Reply 5 years ago
Steven - Try this: BACK UP YOU DATA. Try making a select query with the query builder first, making sure you are getting the records you want. After that change the select query to an update query and run it. Make sure it runs the way you want. If that worked ok, from design mode choose SQL view and copy the SQL to insert and manipulate in your code.

	Scott Axton @Reply 5 years ago
Sorry STEPHEN -

	Stephen Kuperman OP @Reply 5 years ago
It's Steve, thanks

Richard Rost @Reply

5 years ago

Steve, are you trying to change the value based on a field on a form? If so, you probably want to do something like this:

DoCmd.RunSQL "UPDATE OrderT SET CustomerT=" & CustomerCombo & " WHERE OrderID=" & OrderID

See what I did there? I didn't put the value or field name INSIDE the string. I concatenated that actual value in there from form fields. So what Access actually evaluates is this:

DoCmd.RunSQL "UPDATE OrderT SET CustomerT=333 WHERE OrderID=10001

Get it? I assume you want to do something like that above. I just don't really know what your table/field names are all about.

I cover this in my Expert classes. This video might also help.

	Stephen Kuperman OP @Reply 5 years ago
Using Scott's suggestion I used the query builder and came up with this - UPDATE Users INNER JOIN Products ON Users.UserID = Products.UserID SET Products.Location = [Users]![LocationID] WHERE (((Users.UserID)=[Users]![UserID])); it works when running it as a regular query, however I can't get the syntax right to run it in the VBA for the Users location AfterUpdate

	Richard Rost @Reply 5 years ago
Remember, AfterUpdate on a FIELD doesn't save the record to the table. You may need a Me.Refresh before your RunSQL statement.

	Stephen Kuperman OP @Reply 5 years ago
Richard, you are right I need to add the refresh to be sure it gets written to both tables. I appreciate the heads up.

	Richard Rost @Reply 5 years ago
You're very welcome.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View