Hi im trying to repopulate a combo box based on the selection of a value of another combo box. This is where i am so far. Am i going about it at all in the right way? The error i am receiving is "syntax error. in query expression"


Private Sub cmbProvider_AfterUpdate()

intvalue = Me.cmbProvider

strS = "SELECT distinct(ProvLoc.LocationId) FROM ProvLoc Where ProvLoc.ProviderId = " & intvalue & ";"

strSql = "SELECT distinct(Location.Address1), Location.LocationId FROM Location INNER JOIN (Provider INNER JOIN ProvLoc ON Provider.ProviderId = ProvLoc.ProviderId) ON Location.LocationId = ProvLoc.LocationId WHERE Location.LocationId = " & strS & ";"

DoCmd.Requery

cmbLoc.RowSource = strSql
end sub

You first SQl will bring back more than one record, correct? You need to use the IN Clause instead of an = sign in the criteria

Location.LocationId = " & strS

Location.LocationId In (" & strS & ")"

Sorry.. Over complicating the matter. Sorted it.
Private Sub cmbProvider_AfterUpdate()

intvalue = Me.cmbProvider

strSql = "SELECT distinct(Location.Address1), Location.LocationId FROM Location INNER JOIN (Provider INNER JOIN ProvLoc ON Provider.ProviderId = ProvLoc.ProviderId) ON Location.LocationId = ProvLoc.LocationId WHERE ProvLoc.ProviderId = " & intvalue & ";"

DoCmd.Requery

cmbLoc.RowSource = strSql
end sub