CODEWITHSUNDEEP
sqlformsms-accessvba
OneLineAtTheTime
OneLineAtTheTime

Reputation: 43

Run SQL Update Query in MS Access VBA

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]"

Upvotes: 2

Views: 6440

Answers (2)

S Meaden
S Meaden

Reputation: 8260

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?

Upvotes: 1

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 0

Related Questions