How can the field on the current form be used as a parameter for a query?

[cassi84]

Hi-

I have an Access DB with several different forms all relating to one master table.

Each record in the table can be resolved=yes or resolved=no.

Right now each user must click the resolved box to resolve the entry and then enter the date.

I have created a button that will run the update query below.

UPDATE [TBL Master] SET [TBL Master].[Resolved] = Yes, [TBL Master].[Date Completed] = Now()
WHERE ((([TBL Master].[Primary key])=[forms]![frm name]![primary key]))
WITH OWNERACCESS OPTION;

The query allows me to update the record on that form efficiently. However, I do not want to create one query for each form (30 plus) for this.

Is there a way to use the form that was used when the button was clicked without naming the form specifically?

 

[ajetrumpet]

Right now each user must click the resolved box to resolve the entry and then enter the date.

That must mean that the users are viewing these records in the forms when they update them. I mean, how else would they know that they need to be updated, right? Plus, the SQL that posted required the form to be open as well.

The query allows me to update the record on that form efficiently. However, I do not want to create one query for each form (30 plus) for this.

Understandable. Isn't there any other way that you can identify (globally) all the records that need to be updated? Or are these records just pulled randomly out of the air? Maybe you could use whatever identifying agent (data) the user is specifying to either query a list of records, or pull them up one at a time to update them.

Is there a way to use the form that was used when the button was clicked without naming the form specifically?

I don't think I understand this question!

 

[boblarson]

UPDATE [TBL Master] SET [TBL Master].[Resolved] = Yes, [TBL Master].[Date Completed] = Now()
WHERE ((([TBL Master].[Primary key])=[forms]![frm name]![primary key]))
WITH OWNERACCESS OPTION;
.....
Is there a way to use the form that was used when the button was clicked without naming the form specifically?

Yes, there is. You can put the update query and execution of it in a standard module and then you can pass the form name to the procedure. Then you can do the update. Like this:

Public Function RunUpdateQry(strFormName As String) 
   Dim strSQL = "UPDATE [TBL Master] SET [TBL Master].[Resolved] = Yes, [TBL Master].[Date Completed] = Now() " & _
                "WHERE ((([TBL Master].[Primary key])=" & Forms(strFormName)![primary key] & ")) " & _
                "WITH OWNERACCESS OPTION"
   Put your code to run the SQL here
End Function

Then call it in each form by using:

Call RunUpdateQry(Me.Name)

 

[cassi84]

I am confused.

How I am naming the form to use the primary key on?

It does not look defined in this code.

 

[boblarson]

ME is a programming shortcut which refers to the current class object and that would be the form the code is currently on. The .Name returns the name of that object. So, using Me.Name tells the code to use the name of the current class object. Therefore you don't need to specify the actual name because Me.Name does it for you. That way you can use the same, exact, code on any form and it will work.