Update Query Help needed please.

[Guest]

Hello, and thanks in advance for your help.

I have a form that has a button. This button calls an update query. The
problem is that the update query runs on all records and not just the current
record. And I do not know how to tell the query to run only for the current
record.

My code looks like this

Private Sub Update_WebPrice_Click()
On Error GoTo Err_Update_WebPrice_Click

Dim stDocName As String

stDocName = "Update WebPrice Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Update_WebPrice_Click:
Exit Sub

Err_Update_WebPrice_Click:
MsgBox Err.Description
Resume Exit_Update_WebPrice_Click

End Sub

What I would like to do, is have the code check to make sure it is only
updating the current record. I can use the field ProductID to perform that
check. I just don't know how. I spend an hour in the forums looking for a
solution but could not find one. Any help is appreciated.

 

[John W. Vinson]

Hello, and thanks in advance for your help.

I have a form that has a button. This button calls an update query. The
problem is that the update query runs on all records and not just the current
record. And I do not know how to tell the query to run only for the current
record.

My code looks like this

Private Sub Update_WebPrice_Click()
On Error GoTo Err_Update_WebPrice_Click

Dim stDocName As String

stDocName = "Update WebPrice Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Update_WebPrice_Click:
Exit Sub

Err_Update_WebPrice_Click:
MsgBox Err.Description
Resume Exit_Update_WebPrice_Click

End Sub

What I would like to do, is have the code check to make sure it is only
updating the current record. I can use the field ProductID to perform that
check. I just don't know how. I spend an hour in the forums looking for a
solution but could not find one. Any help is appreciated.

Open [Update WebPrice Query] and put a criterion on the Criteria line under
ProductID refering to your form. The syntax would be

=[Forms]![YourFormName]![YourControlName]

referencing a control on the form which contains the productID.

What you're doing is... just running the query. There is absolutely nothing
about running a query from code, or from a form, which would change the
records that the query updates; and you cannot pass a parameter using the
OpenQuery method, at least not to my knowledge.

I would suggest that to run an Update query, you may want to use the Execute
method instead. See the online help in the VBA editor.

John W. Vinson [MVP]