Hey everyone - am trying to get a simple (i think!) update query to work between two tables. I am aiming to do this by selecting the source and destination tables from combo boxes, and then select the fields that will have the reference and obsolete data with further combo boxes. I have generated what i believe is the correctish SQL, but i am getting a problem with the syntax. It was originally to do with the end not being correct, but i have now been (stupidly) moving bits around so much that i have got lost in the code.
I would be extremely grateful (as ever!) if someone can point out where i am going wrong with this. The code for the query is listed below:
Many thanks,
Fergcu
I would be extremely grateful (as ever!) if someone can point out where i am going wrong with this. The code for the query is listed below:
Code:
Private Sub cmdUpdateQuery_Click()
'variables
Dim MyDB As DAO.Database
Dim qDef As DAO.QueryDef
Dim strSQL As String
Dim strUPDATE As String
Dim strINNER As String
Dim strON As String
Dim strSET As String
Set MyDB = CurrentDb()
'strSQL = "UPDATE " & Me.cbotblDestination & " INNER JOIN " & Me.cbotblReference & " ON " & Me.cbotblDestinationFieldCheck & " = " & Me.cbotblReferenceFieldCheck & " SET " & Me.cbotblDestinationFieldData & " = " & Me.cbotblReferenceFieldData;
strUPDATE = "UPDATE '" & (Me.cbotblDestination) & "'"
strINNER = "INNER JOIN '" & (Me.cbotblReference) & "'"
strON = "ON '" & (Me.cbotblDestinationFieldCheck) & "' = '" & (Me.cbotblReferenceFieldCheck) & "'"
strSET = " SET '" & (Me.cbotblDestinationFieldData) & "' = '" & (Me.cbotblReferenceFieldData)"'"
strSQL = "strUPDATE & strINNER & strON & strSET"
' save query with new SQL statement
Set qDef = CurrentDb.QueryDefs("UpdateQuery")
qDef.SQL = strSQL
Set qDef = Nothing
DoCmd.OpenQuery "UpdateQuery", acViewNormal, acReadOnly
Exit_cmdUpdateQuery_Click:
Exit Sub
End Sub
Many thanks,
Fergcu