Grouping three Queries in one Macro

[thirty4d]

In my form I have one button that when clicked upon opens a macro that updates 2 fields with related records between 2 tables. If possible I'm avoiding not to have to create 3 buttons that will open 3 different macros.

Table1 is the datasource and Table2 is the recordset collections (extracted data from Table1).

This one field in Table1 is represented by 3 fields in Table2 .

What I'm trying to accomplish is combined these 3 queries into one macro.

This is my SQL statement:

UPDATE DISTINCTROW ToolsList LEFT JOIN Layout ON ToolsList.Partnumber = Layout.txtCuttingToolID
SET ToolsList.Description = [Enter New Value], Layout.txtCuttingTools = [Enter New Value]
WHERE (((Layout.txtCuttingTools)=[Enter Old Value]));

Notes:

The highlighted text in blue is only one that is different from the 3 queries.

The user is being prompted only with 2 parameters which is the old value and the new value.


Thanks,

 

[B827]

I don't know how you are running these queries but if they are actual queries in the database then with a bit of tweaking you could make a nice little form driven function.

As an example using your preferred naming convention.
Set up your form as unbound, and have two text fields named

Old_Value
New_Value

Save the form as frmUpdate_Records

then have thre queries with SQL like

UPDATE DISTINCTROW ToolsList LEFT JOIN Layout ON ToolsList.Partnumber = Layout.txtCuttingToolID
SET ToolsList.Description = Forms!frmUpdate_Records!New_Value, Layout.txtCuttingTools = [Enter New Value]
WHERE (((Layout.txtCuttingTools)=Forms!frmUpdate_Records!Old_Value));

then create a button on the form to execute the three queries.

The Microsoft method is as follows:

Dim stDocName As String

stDocName = "qry_Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qry_Query2"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qry_Query2"
DoCmd.OpenQuery stDocName, acNormal, acEdit
You could put in somechecking to make sure the user has entered data in both fields, and prevent the queries running if the data doesn't meet your specification.

 

[thirty4d]

B827

I appreciate very much your feedback. I did exactly what you suggested. I've got it to almost work, except it prompted me 3 times of the same parameter "Old Value" and "New Value".

In your code how does it know which query to run?

Again thanks for your time