Dynamic SQL statement to cascade comboboxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to cascade comboboxes using a dynamic SQL statement. I have
archive boxes stored in different locations on two different sites.

cboSortArchiveBoxes selects the site.

cboTestReportArchiveBoxNum selects the archive box number and storage
location.


In the AfterUpdate event of cboSortArchiveBoxes I have the following code.
However, this code displays archive boxes on all sites. Using SQL how do I
display only the selected site in the combobox?


Private Sub cboSortArchiveBoxes_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT tblArchiveBoxes.ArchiveBoxNum,
tblStorageLctns.StorageLctn, tblStorageLctns.StorageLctnSite "
strSQL = strSQL & "FROM tblStorageLctns INNER JOIN tblArchiveBoxes ON
tblStorageLctns.StorageLctnID = tblArchiveBoxes.ArchiveBoxStorageLctn "
strSQL = strSQL & "ORDER BY tblArchiveBoxes.ArchiveBoxNum DESC;"

‘***I need some code here to tell the combo box which site to dispay***

Me!cboTestReportArchiveBoxNum.RowSourceType = "Table/Query"
Me!cboTestReportArchiveBoxNum.RowSource = strSQL

End Sub


This is what cboTestReportArchiveBoxNum looks like:

ArchiveBoxNum StorageLctn StorageLctnSite
7000/RoomA/East Tamaki
6000/RoomB/East Tamaki
5000/RoomA/East Tamaki
3000/RoomA/Mosgiel
2000/RoomA/Mosgiel


I want it to look like this:

ArchiveBoxNum/StorageLctn/StorageLctnSite
7000/RoomA/East Tamaki
6000/RoomB/East Tamaki
5000RoomA/East Tamaki


Any help or links to examples on the web appreciated,
Seth
 
Seth

Take a look at the WHERE clause (WHERE x = 7) syntax of the SQL statement.

Good luck

Jeff Boyce
<Access MVP>
 
Thanks Jeff.
That did the trick.
Seth

Jeff Boyce said:
Seth

Take a look at the WHERE clause (WHERE x = 7) syntax of the SQL statement.

Good luck

Jeff Boyce
<Access MVP>
Click to expand...
 
Back
Top