sql statement with 3 combo boxes

[benjammin]

Hello!



So I have 3 combo boxes

Site
Location
Station

I have tblSite, tblLocation and tblStation

The user choses a site, some sites have Locations but ALL sites have Stations.

I'm running into a problem populating combo boxes, it's easy when the user chooses a site to populate location but if there is no location then how do i populate the station box?


There's only ONE site, one site can have MANY locations and MANY stations but a site might have NO locations and MANY stations

Could i do an if then on the row source for the station?

Here's my current script to populate StationName based on the value of LocationName...

Private Sub cboStationName_GotFocus()
If Me.cboLocationName.Value = Null Then
Me.cboStationName.RowSource = "SELECT StationID, StationName FROM tblStation " & _
"WHERE tblStation.SiteID = '" & cboSiteName.Value & "' " & _
"ORDER BY tblStation.StationName"
Else
Me.cboStationName.RowSource = "SELECT StationID, StationName FROM tblStation " & _
"WHERE tblStation.LocationID = '" & cboLocationName.Value & "' " & _
"ORDER BY tblStation.StationName"
End If
End Sub

That currently gives me a Data Type Mismatch In Criteria Expression when I try to use the combobox for Site

Hopefully this kind of makes sense?

THANKS!

[June7]

Site can have Stations when it doesn't have Locations, yet you want to restrict Stations by Location when selected?

Try this. When Site is selected populate both Locations and Stations based on the Site selected. Then if user does select a Location, repopulate Stations to further restrict choices. If you don't want Stations available until decision is made on Location, you could make both subordinate comboboxes not visible until conditions met. First, selection of Site would make Locations visible then selection of Location would make Stations visible. However, can offer an option to selecting a Location, maybe a checkbox 'Enter Station without Location' click would then make Stations available.

Are SiteID and LocationID number or text fields? If number, eliminate the apostrophe delimiters.

You could try this as the RowSource for Stations:
"SELECT StationID, StationName FROM tblStation WHERE " & _
IIf(IsNull(Me.cboLocationID), "SiteID=" & Me.cboSiteName, "LocationID=" & Me.cboLocationName);

Might need in code:
Me.cboLocationName.Requery
Me.cboStationName.Requery

[benjammin]

still having problems with this, will play with it more and see what i can figure out

[benjammin]

I keep receiving "Data type mismatch in criteria expression." when I try various things in row source, or gotfocus, sigh...

[benjammin]

Figured it out, totally made sense to me, whether or not this is the correct way to do this is beyond me but it works so i'm going with it.

Private Sub cboStationName_GotFocus()
If IsNull(Me.cboLocationName) Then
    StationNameSQL = "SELECT tblStation.StationID, " & _
    "                        tblStation.SiteID, " & _
    "                        tblStation.LocationID, " & _
    "                        tblStation.StationNAME " & _
    "                 FROM tblStation " & _
    "                 WHERE (((tblStation.[SiteID])=[cboSiteName]))" & _
    "                 ORDER BY tblStation.StationName;"
Else
End If
If Not IsNull(Me.cboLocationName) Then
    StationNameSQL = "SELECT tblStation.StationID, " & _
    "                        tblStation.SiteID, " & _
    "                        tblStation.LocationID, " & _
    "                        tblStation.StationName " & _
    "                 FROM tblStation " & _
    "                 WHERE (((tblStation.[LocationID])=[cboLocationName]))" & _
    "                 ORDER BY tblStation.StationName;"
Else
End If
Me.cboStationName.RowSource = StationNameSQL
End Sub