Update Query via Cmd Button - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update Query via Cmd Button

Jeffrey Hebert

3 years ago

I have an update Query that I am calling from a sub-form using a command button. The sub form is set to use the current record data. Currently the I get two popup windows. One warning that I'm about to modify data and a second telling my I'm going to update all 60 records. Two questions:
1. How do I suppress and/or revise the message in the pop up windows(s). I'd like a single window to simply confirm the user want to update the record.
2. How do I set the update query criteria to the current record? I only want to run the update query on the record open in the form.

	Kevin Robertson @Reply 3 years ago
Before you run your Update Query `DoCmd.SetWarnings False` Remember to turn the warnings back on after the Update Query has run `DoCmd.SetWarnings True` In the criteria of the Update Query `Forms!FormName!SubFormName.Form!YourID` Value From a Form Value From Subform

Jeffrey Hebert OP @Reply

3 years ago

Thank you Kevin, I assume that these are VBA commands (re: the Warnings) which is currently outside my skill set. I was using the Command Button builder to run the Update Query. So I don't know how to call the Query using VBA. If you can direct me to a lesson I am happy to dig into that.
Regarding the Criteria. When I add that to the criteria it open a parameter window when the query runs. Not sure what I'm missing here.
Jeff

Kevin Robertson @Reply

3 years ago

OK. Right click on the button, then on Build Event.
This will open the Macro Builder so you can edit the Embedded Macro created by the Button Wizard.
Make sure you select Show All Actions on the Ribbon.
Add the SetWarnings commands as shown in the screenshot below.
Use the green up arrow to move the first SetWarnings command above the OpenQuery command.

Can you post a screenshot of your Update Query so I can see the criteria.

	Kevin Robertson @Reply 3 years ago

	Jeffrey Hebert OP @Reply 3 years ago

Jeffrey Hebert OP @Reply

3 years ago

Show all action!  I never noticed that option before.  Thanks.

Image uploaded above.  The Update is a concatenation of 6 fields into a single field that is unique to the table.  The update works fine without the criteria but eventually there will be 1000+ records and I'm concerned about performance.

[Xe-100_Equipment_TableT]![Unit] & "-" & [Xe-100_Equipment_TableT]![Location] & "-" & [Xe-100_Equipment_TableT]![System_Code] & "-" & [Xe-100_Equipment_TableT]![Component_Code] & "-" & [Xe-100_Equipment_TableT]![Sequence]

I have a Navigation Form (call it A), that has a sub form (call it B).  From that sub-form I have a command button that launches a form where the user can change one or more of the 6 variables.  A command button then launches the Update Query that updates the Equipment_Number.  I assume that these form names need to be incorporated into the Criteria but everything I try ends up with the Parameter pop-up.

	Kevin Robertson @Reply 3 years ago
Your criteria needs to what ever you own object/fieldnames are. Criteria is in the wrong place. You are setting the criteria equal to an ID. So bring the appropriate ID into the Query and put your criteria under that.

	Kevin Robertson @Reply 3 years ago

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

Next Unseen

New Feature: Comment Live View