Updating separate tables from a list in a table

[Robbie44]

I have 7 tables that need to be updated based on a update query that I have. The field names are all the same and the parameter portion works fine but I can't get the query to access the tables. I was trying to use the table names as a parameter in the query and these parameters are in a table. I have used this query to update one table with multiple parameters and it worked fine but I can't seem to write the code correctly for the tables to be a parameter in the query and update the tables accordingly. Here is my code and the list of tables. Any help would be appreciated.

Private Sub Command31_Click()

Dim R As DAO.Recordset, Rsql As String
Dim SqlCmd As String, Tbl As TableDef, GlobalOpt As String
 
Rsql = "SELECT GlobalOptType,GlobalOptions FROM tblGlobalTables_OPP"
 
 
 Set R = CurrentDb.OpenRecordset(Rsql, dbReadOnly)
 
 Do Until R.EOF
 Tbl = R!GlobalOptType
 GlobalOpt = R!GlobalOptions

SqlCmd = "UPDATE tblGlobalSelections_Local_SSP LEFT JOIN "" & Tbl & "" ON (tblGlobalSelections_Local_SSP.SSOPTC = ""  Tbl  "".[SSOPTC])" & _
 " AND (tblGlobalSelections_Local_SSP.SSOPTP = "" & Tbl & "".SSOPTP) SET "" & Tbl & "".SSOPTC = [tblGlobalSelections_Local_SSP].[SSOPTC]," & _
 " " & Tbl & ".SSOPTP = [tblGlobalSelections_Local_SSP].[SSOPTP], "" & Tbl & "".PRICEBRAND = [tblGlobalSelections_Local_SSP].[PRICEBRAND]," & _
 """ & Tbl & "".SSSUFX = [tblGlobalSelections_Local_SSP].[SSSUFX], "" & Tbl & "".SSDESC = [tblGlobalSelections_Local_SSP].[SSDESC]" & _
" WHERE ((("" & Tbl & "".SSOPTC) Is Null) AND (("" & Tbl & "".SSOPTP)=""" & GlobalOpt & """)) OR ((("" & Tbl & "".SSOPTP) Is Null" & _
 " And ("" & Tbl & "".SSOPTP)=""" & GlobalOpt & """)) OR ((("" & Tbl & "".SSOPTP)=""" & GlobalOpt & """) AND (("" & Tbl & "".PRICEBRAND) Is Null))" & _
 " OR ((("" & Tbl & "".SSOPTP)=""" & GlobalOpt & """) AND (("" & Tbl & "".SSSUFX) Is Null)) OR ((("" & Tbl & "".SSOPTP)=""" & GlobalOpt & """)" & _
 " AND (("" & Tbl & "".SSDESC) Is Null));"
 
  CurrentDb.Execute SqlCmd
 
 R.MoveNext
 
 Loop
 
 R.Close
 Set R = Nothing
 

End Sub


Here is the table with the table names:

GlobalOptID	GlobalOptType	    GlobalOptions
01              tblBrand_OPP	     BRAND
02              tblConstruction_OPP  CONSTRUCTION
03              tblDoor_OPP	     DOOR STYLE
04              tblHinge_OPP	     HINGE TYPE
05              tblSQUA_OPP	     SQUA/ARCH/CATH
06              tblWood_OPP	     WOOD TYPE
07	        tblFinish_OPP	     FINISH

[MikeTheBike]

Hi

Without you telling us what the problem is it is difficult to be sure, but I think you have too many quotation marks.
You only need to double up the Quotes where you need to include the quotation mark as part of the string.

In you case this will only be needed to delimit the GlobalOpt variable (if it is a text field!?).
I think it should be something like this

Sql = "UPDATE tblGlobalSelections_Local_SSP LEFT JOIN " & Tbl & " ON (tblGlobalSelections_Local_SSP.SSOPTC = " & Tbl & ".[SSOPTC])" & _
 " AND (tblGlobalSelections_Local_SSP.SSOPTP = " & Tbl & ".SSOPTP) SET " & Tbl & ".SSOPTC = [tblGlobalSelections_Local_SSP].[SSOPTC]," & _
 " " & Tbl & ".SSOPTP = [tblGlobalSelections_Local_SSP].[SSOPTP], " & Tbl & ".PRICEBRAND = [tblGlobalSelections_Local_SSP].[PRICEBRAND]," & _
 " " & Tbl & ".SSSUFX = [tblGlobalSelections_Local_SSP].[SSSUFX], " & Tbl & ".SSDESC = [tblGlobalSelections_Local_SSP].[SSDESC]" & _
 " WHERE (((" & Tbl & ".SSOPTC) Is Null) AND ((" & Tbl & ".SSOPTP)=""" & GlobalOpt & """)) OR (((" & Tbl & ".SSOPTP) Is Null" & _
 " And (" & Tbl & ".SSOPTP)=""" & GlobalOpt & """)) OR (((" & Tbl & ".SSOPTP)=""" & GlobalOpt & """) AND ((" & Tbl & ".PRICEBRAND) Is Null))" & _
 " OR (((" & Tbl & ".SSOPTP)=""" & GlobalOpt & """) AND ((" & Tbl & ".SSSUFX) Is Null)) OR (((" & Tbl & ".SSOPTP)=""" & GlobalOpt & """)" & _
 " AND ((" & Tbl & ".SSDESC) Is Null));"

This assumes all Table and Field names passed as variable do not have spaces in them (square bracket are required if they have).

I have not checked to sql syntax (hopefully this may not be necessary).

HTH


MTB