Cascading Combo Boxes

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 step
as it is a hidden box because the only choice needed 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
 
D

Douglas J. Steele

The error is because you've got embedded spaces in your table name (plus
you've got a superfluous Is Not Null)

Try:

MySQL = "SELECT DISTINCT [" & CboFieldList.Value
MySQL = MySQL & "] FROM [" & CboChooseSeason.Value
MySQL = MySQL & "] WHERE [" & CboFieldList.Value & "] Is Not Null"
MySQL = MySQL & " ORDER BY [" & CboFieldList.Value & "]"

Note that & is better than + for concatenation when dealing with strings.

That having been said, having table names like Art Tracking Fall 2007 makes
me suspect that your database isn't properly normalized. You're storing
important information as part of the table name, and I'll bet you've also
got tables like Art Tracking Summer 2007 and Art Tracking Winter 2008.
 
G

Guest

I had to link to the excel spreadsheets because every time I had to import
them into the database, they would change some formatting and I would then
have to fix them all over again! I was planning on changing the names to not
include spaces later, but will do it sooner. I am teaching myself how to do
VBA, but every time I looked for answers, the examples were not what I
wanted. Thanks a million.

Douglas J. Steele said:
The error is because you've got embedded spaces in your table name (plus
you've got a superfluous Is Not Null)

Try:

MySQL = "SELECT DISTINCT [" & CboFieldList.Value
MySQL = MySQL & "] FROM [" & CboChooseSeason.Value
MySQL = MySQL & "] WHERE [" & CboFieldList.Value & "] Is Not Null"
MySQL = MySQL & " ORDER BY [" & CboFieldList.Value & "]"

Note that & is better than + for concatenation when dealing with strings.

That having been said, having table names like Art Tracking Fall 2007 makes
me suspect that your database isn't properly normalized. You're storing
important information as part of the table name, and I'll bet you've also
got tables like Art Tracking Summer 2007 and Art Tracking Winter 2008.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rain said:
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
step
as it is a hidden box because the only choice needed 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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cascading Combo Boxes 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
FROM and JOIN Problems 1
Need help cascading 5 combo boxes 12
2nd Combo box gets only the first record 1
Cascading Combo Boxes 2
cascading combo box 20
Cascading Combo boxes 2

Top