G
Guest
Help please. I am new at VBA. I am doing cascading combo boxes. The first two
boxes work fine, but I keep getting a syntax error in FROM clause. I can not
find it no matter how many times I try. Here is my full code. The first box
creates a list of table names that are needed to choose from (Art Tracking
Fall 2007). The second box creates a list of field names where the first (and
preferably only) choice is set to Vendor (I would love to eliminate this box
as it is a hidden box because the only field I need is Vendor). The third box
does not give me the distinct names listed in the field to choose from. The
row source for the box states: SELECT DISTINCT VENDOR FROM Art Tracking Fall
2007 WHERE VENDOR Is Not Null ORDER BY VENDOR – which is what I want. Thanks
'** UpdateCboChooseSeason() updates the CboChooseSeason combo box control
'with ALL Art Tracking table names.
Private Sub UpdateCboChooseSeason()
'Declare an empty string to store a value list.
Dim TblNames As String
TblNames = ""
'Loop through AllTables connection, add each table's name to
'TblNames variable, each enclosed in quotation marks and followed by a
semicolon.
Dim tbl As AccessObject
For Each tbl In CurrentData.AllTables
'Choose only Art Tracking table names from list.
If InStr(tbl.Name, "Art Tracking") Then
'Add current table name and semicolon to TblNames variable.
TblNames = TblNames + Chr(34) + tbl.Name + Chr(34) + ";"
End If
Next
'TblNames string now has all table names that have to do with the Art
Tracking tables.
'Make it the Row Source for the TblCombo control.
Me!CboChooseSeason.RowSourceType = "Value List"
Me!CboChooseSeason.RowSource = TblNames
'Show first item as selected item in control.
Me!CboChooseSeason.Value = Me!CboChooseSeason.ItemData(0)
'Make sure user can only select a valid name.
Me!CboChooseSeason.LimitToList = True
End Sub
'** UpdateCboFieldList() updates the CboFieldList combo box control
'from the Art Tracking table chosen in the CboChooseSeason combo box.
Private Sub UpdateCboFieldList()
'Set CboFieldList box Row Source properties.
Me!CboFieldList.RowSourceType = "Field List"
'Set CboFieldList box Row Source table to CboChooseSeason value.
Me!CboFieldList.RowSource = CboChooseSeason.Value
Me!CboFieldList = "VENDOR"
End Sub
'**UpdateCboChooseVendor() updates the CboChooseVendor combo box with unique
values from
'the VENDOR field chosen in the CboFieldList combo box.
Private Sub UpdateCboChooseVendor()
'Build an SQL statement to pull unique values from Vendor field
'(If CboFieldList is empty, do nothing)
If Not IsNull(Me!CboFieldList.Value) Then
Dim MySQL As String
MySQL = "SELECT DISTINCT " + CboFieldList.Value
MySQL = MySQL & " FROM " + CboChooseSeason.Value + " Is Not Null"
MySQL = MySQL & " WHERE " + CboFieldList.Value + " Is Not Null"
MySQL = MySQL & " ORDER BY " + CboFieldList.Value
'MsgBox MySQL
End If
Me!CboChooseVendor.RowSourceType = "Table/Query"
Me!CboChooseVendor.RowSource = MySQL
End Sub
boxes work fine, but I keep getting a syntax error in FROM clause. I can not
find it no matter how many times I try. Here is my full code. The first box
creates a list of table names that are needed to choose from (Art Tracking
Fall 2007). The second box creates a list of field names where the first (and
preferably only) choice is set to Vendor (I would love to eliminate this box
as it is a hidden box because the only field I need is Vendor). The third box
does not give me the distinct names listed in the field to choose from. The
row source for the box states: SELECT DISTINCT VENDOR FROM Art Tracking Fall
2007 WHERE VENDOR Is Not Null ORDER BY VENDOR – which is what I want. Thanks
'** UpdateCboChooseSeason() updates the CboChooseSeason combo box control
'with ALL Art Tracking table names.
Private Sub UpdateCboChooseSeason()
'Declare an empty string to store a value list.
Dim TblNames As String
TblNames = ""
'Loop through AllTables connection, add each table's name to
'TblNames variable, each enclosed in quotation marks and followed by a
semicolon.
Dim tbl As AccessObject
For Each tbl In CurrentData.AllTables
'Choose only Art Tracking table names from list.
If InStr(tbl.Name, "Art Tracking") Then
'Add current table name and semicolon to TblNames variable.
TblNames = TblNames + Chr(34) + tbl.Name + Chr(34) + ";"
End If
Next
'TblNames string now has all table names that have to do with the Art
Tracking tables.
'Make it the Row Source for the TblCombo control.
Me!CboChooseSeason.RowSourceType = "Value List"
Me!CboChooseSeason.RowSource = TblNames
'Show first item as selected item in control.
Me!CboChooseSeason.Value = Me!CboChooseSeason.ItemData(0)
'Make sure user can only select a valid name.
Me!CboChooseSeason.LimitToList = True
End Sub
'** UpdateCboFieldList() updates the CboFieldList combo box control
'from the Art Tracking table chosen in the CboChooseSeason combo box.
Private Sub UpdateCboFieldList()
'Set CboFieldList box Row Source properties.
Me!CboFieldList.RowSourceType = "Field List"
'Set CboFieldList box Row Source table to CboChooseSeason value.
Me!CboFieldList.RowSource = CboChooseSeason.Value
Me!CboFieldList = "VENDOR"
End Sub
'**UpdateCboChooseVendor() updates the CboChooseVendor combo box with unique
values from
'the VENDOR field chosen in the CboFieldList combo box.
Private Sub UpdateCboChooseVendor()
'Build an SQL statement to pull unique values from Vendor field
'(If CboFieldList is empty, do nothing)
If Not IsNull(Me!CboFieldList.Value) Then
Dim MySQL As String
MySQL = "SELECT DISTINCT " + CboFieldList.Value
MySQL = MySQL & " FROM " + CboChooseSeason.Value + " Is Not Null"
MySQL = MySQL & " WHERE " + CboFieldList.Value + " Is Not Null"
MySQL = MySQL & " ORDER BY " + CboFieldList.Value
'MsgBox MySQL
End If
Me!CboChooseVendor.RowSourceType = "Table/Query"
Me!CboChooseVendor.RowSource = MySQL
End Sub