Expand|Select|Wrap|Line Numbers
- 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