Run SQL Update Query in MS Access VBA

Question

I am trying to run this code to fire an update query from VBA. Access is giving me a syntax error. I suspect this has to do with the fact that I'm trying to run an update query using an INNER JOIN with a form. Is what I'm trying to do at all possible?

Private Sub Btn_Edit_Data_Click()

Dim db As DAO.Database
Dim UpdateQdf As DAO.QueryDef
Dim UpdateSQL As String

Set db = CurrentDb()
Set UpdateQdf = db.QueryDefs("Qry_Update_Counterparty_Data")

UpdateSQL = "UPDATE Repository_Redux INNER JOIN [Forms]![Frm_Reject_Button] ON Repository_Redux.[Counterparty ID] = [Forms]![Frm_Reject_Button]![Txt_CP_ID] " & _
            "SET Repository_Redux.[Counterparty Name] = [Forms]![Frm_Reject_Button]![Txt_CP_Name_Edit]"

UpdateQdf.SQL = UpdateSQL

DoCmd.OpenQuery "Qry_Update_Counterparty_Data"

Set db = Nothing
Set qdf = Nothing

End Sub

I solved it this way, thanks everyone:

 UpdateSQL = "UPDATE Repository_Redux SET Repository_Redux.[Counterparty Name] = [Forms]![Frm_Reject_Button]![Txt_CP_Name_Edit] WHERE Repository_Redux.[Counterparty ID] = [Forms]![Frm_Reject_Button]![Txt_CP_ID]"

Well done. Very interesting! So one can refer to the form directly in the SQL, well done (again). One caveat, I think that would mean the form must remain open at the time of the operation. — S Meaden
– S Meaden, Commented Jan 4, 2018 at 21:00
Yes, it is open as a dialog and the user can get out of it only by canceling the edit action or run the above query. Thanks for the kind words, I wish I could give credit to my skills but I'm just trial an error ;) — OneLineAtTheTime
– OneLineAtTheTime, Commented Jan 4, 2018 at 21:15
I'm trying to run an update query using an INNER JOIN with a form...a form is a user application GUI object. It contains no data! So you are updating what? Likely you want to join to data behind form or use values in a form's controls like textboxes. — Parfait
– Parfait, Commented Jan 4, 2018 at 21:26

S Meaden · Accepted Answer · 2018-01-04 20:44:42Z

1

Just a suggestion, I've not tried this...

On your form you have have an event handler that stores in a global variable (yes, I know that's dodgy) the values from your form that you intend to use in the query. Then you can define a function that reads the global variable. Then you can use the function in the SQL query.

Let us know how you get on.

Googling suggests other have tried this

Anybody else got a better "global state machine" to bridge the form to the SQL?

answered Jan 4, 2018 at 20:44

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erik A · Accepted Answer · 2018-01-04 20:33:57Z

0

No, you can never join a form in a query (or SELECT FROM a form, for that matter). You can only join in tables or other queries.

You can, however, try to join in a forms record source.

answered Jan 4, 2018 at 20:33

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

5 Comments

OneLineAtTheTime Over a year ago

I'm trying to join a textbox within the form as shown by what follows the ON statement. The textbox itself has a recordsource but not sure what you mean about the recordsource of the form itself? Would it be possible to run the update query without the inner join and using the WHERE clause for the join?

Erik A Over a year ago

Eh... text boxes don't have record sources, they have control sources. Combo boxes and list boxes have both. I don't know exactly what you expect should happen.

OneLineAtTheTime Over a year ago

I figured it out, this way works just fine. Thanks for all your help.

OneLineAtTheTime Over a year ago

Sorry I didn't mean to delete your answer. Feel free to put it back.

mkinson Over a year ago

You can, so long as you use VBA.

Collectives™ on Stack Overflow

Run SQL Update Query in MS Access VBA

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related