List box does not like rowsource

I think Rowsource is a property so it needs =:

frm.lstCityFilter.RowSource = strSQL

I am still having issues with type mismatch.

Public gCountryID As Integer
Public gStateID As Integer
Public gCityID As Integer
Public strSQL As String
Public strWHERE As String
Public strOrderBy As String

Select allOpen in new window

[Private Sub cmdSearch_Click()

   On Error GoTo cmdSearch_Click_Error
    
'      If frm.CurrentRecord = True Then
'        Call LockControls(Me, False)        'unlock
'        If frm!cboCity > 8 Then
'            Me.cmdDelete.Enabled = True
'        Else
'            Me.cmdDelete.Enabled = False
'        End If
 '   Else
'        Call LockControls(Me, True)         'lock
  '  End If
    strSQL = ""
    strSQL = "SELECT tblCity.CityRecID, tblCity.CityName, tblCountry.CountryCode, tblState.StateCode, tblCity.StateRecID," & _
                " tblCity.CountryRecID" & _
            " FROM tblState RIGHT JOIN" & _
                " (tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID) ON" & _
                " tblState.StateRecID = tblCity.StateRecID"
        If Not Nz(Me.cboCountry.value) Then
            strWHERE = " WHERE (tblCity.CountryRecID= " & gCountryID & " )"
        End If
        If Not Nz(Me.cboState.value) Then
      '  If Me.cboState.value <> Null Then
            strWHERE = " WHERE (tblCity.stateRecID= " & gStateID & " )"
        End If
        If Not Nz(Me.cboCity.value) Then
        'If Me.cboCity.value <> Null Then
            strWHERE = " WHERE (tblCity.CityRecID= " & gCityID & " )"
        End If
         If Not Nz(Me.txtSearchCity.value) <> Null Then
            strWHERE = " WHERE (tblCity.CityName) Like & Chr(39) & Me.txtSearchCity & ' * ' & Chr(39) & """
        End If
        
        strOrderBy = " ORDER BY tblCity.CityName"
        strSQL = strSQL & strWHERE & OrderBy
        Debug.Print strSQL
        frm.lstCityFilter.RowSource = strSQL
        frm.lstCityFilter.Requery
   On Error GoTo 0
   Exit Sub

cmdSearch_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSearch_Click of VBA Document Form_frmHomePage"
End Sub

Select allOpen in new window

Since the values I am passing in the Where statements, except for the txtsearchcity (used for wild card search) are integers why would I get a type mismatch error message?

K

you can end up with too many instances of "WHERE " in the criteria.  You also have too many ")" in each of the strWHERE lines and the syntax for the CityName is wrong.  Try this:

        If Not IsNull(Me.cboCountry) Then
            strWHERE = "(tblCity.StateRecid= " & gStateID & " )"
        End If
        If Not IsNull(Me.cboState) Then
            if Len(strWHERE) > 0 then strWHERE = strWHERE & " AND "
            strWHERE = "(tblCity.CountryRecID= " & gCountryID & " )"
        End If
        If Not IsNull(Me.cboCity) Then
            if Len(strWHERE) > 0 then strWHERE = strWHERE & " AND "
            strWHERE = "(tblCity.CityRecID= " & gCityID & " )"
        End If
        If Not IsNull(Me.txtSearchCity) Then
            if Len(strWHERE) > 0 then strWHERE = strWHERE & " AND "
            strWHERE = "(tblCity.CityName LIKE '" & Me.txtSearchCity & "*')"
        End If

        if Len(strWHERE) > 0 then strSQL = strSQL & " WHERE " & strWHERE

Select allOpen in new window

Ok, when I change the If Statement from Not nz(... to me.cbostate <> "" :  I get Object Variable or With block not set, when I attempt to set the rowsource.

What I attempting to do is check for null or empty fields to determine if I need to grab a value to in the WHere statement, just thought of that I am not handling the Where statement correctly for multiple criterias for the where.  I need to move the Where out of the individual lines.
Help,

actually, instead of :

If Not IsNull(Me.cboCountry) Then

I generally use a syntax that looks like:

IF IsNullOrBlank(me.cboCountry) = false Then

Select allOpen in new window

This syntax has the advantage that it is extremely easy to determine what you are checking for, and it will also catch cases where the value in your control is a zero length string, not a NULL.  And the function is very simple:

Public Function IsNullOrBlank(SomeValue as Variant) as Boolean

    IsNullOrBlank = (TRIM(SomeValue & "") = "")

End Function

Select allOpen in new window

Ok changed code per your suggestion with one minor difference -

        If Me.cboCity.value <> "" Then
However, still errors on the setting of the rowsource for the list box.
I get Object Variable or With block not set, when I attempt to set the rowsource.
any ideas

Missed that.  Try:

frm.lstCityFilter.RowSource = strSQL

Any control can be null or a zls so I'm not sure why you are treating cboCity differently.  You should use the function that Dale suggested in all cases.

The easiest way to find the error is to print the sqlstring to the Immediate window.  If you can't see the error, then copy the string and paste it into the QBE and try to run it.  The query editor usually gives slightly better (although not good) error messages.

strWHERE = " WHERE (tblCity.CityName)= " & Me.txtSearchCity & "*" & " )"

Select allOpen in new window

I think you probably need single quotes around the city name if it hits this code, and if you're using wildcard, you have to replace = with LIKE:

strWHERE = " WHERE (tblCity.CityName) LIKE '" & Me.txtSearchCity & "*" & "' )"

Select allOpen in new window

ok turns out I don't need the txtsearchcity field, instead the user wants to limit the city combo to just the city name, so I need to modify the code to handle multiple cities and retrieve the appropriate recid(s).

Need help modifying the code to loop thru the list of city name and return a list of IDs to be used in the sql statement.

        If Len(strWHERE) > 0 Then strWHERE = strWHERE & " AND "
            gCityID = DLookup("CityRecID", "tblcity", "(tblCity.CityName) LIKE '' & Me.cboState.value & '*' & '' )")
            strWHERE = "(tblCity.CityRecID= " & gCityID & " )"
        End If

Select allOpen in new window

FYI this line does not look correct:

gCityID = DLookup("CityRecID", "tblcity", "(tblCity.CityName) LIKE '' & Me.cboState.value & '*' & '' )")

Select allOpen in new window

It should probably be this:

gCityID = DLookup("CityRecID", "tblcity", "(tblCity.CityName) LIKE '" & Me.cboState.value & "*'")

Select allOpen in new window

OK I need to create a value list of cityrecid(s) for each that = the city name or wildcard version of the city name in the field cbocityRecID.  So a dlookup will not work.  attempting a for loop, but can remember the proper syntax.

Here's a database I built a few years ago that has several examples.  the third one - Modify RecordSource by Multi-Select ListBox is probably the closest to what you need.
FillFormFields130127.zip

this is what I have so far.

          strSQL_1 = "Select cityRecID from tblcity" & _
                        "where tblCity.CityName) LIKE '' & Me.cbocity.value & '*' & '' )"
            
            Set rs = DBEngine(0)(0).OpenRecordset(strSQL_1)
            
            rs.MoveFirst
            Do While Not rs.EOF
                If Len(gCityID) > 0 Then
                    gCityID = rs.Fields("cityRecid")
                 rs.MoveNext
            Loop
                gCityID = gCityID & ", " & rs.Fields("cityRecid")
               End If
                rs.MoveNext

Select allOpen in new window

Please take a look at the example I uploaded.  You may not have seen it yet.

I am still unsure how to incorporate a check for each of the 3 combos that may make up the criteria for my list box rowsource.  Your example is 1 listbox to update form, however, mine can have the criteria can be 1 or more of the combos.  and the city can contain multiple records (in statement)  the user can select any of the options as 1 and only selection or they can build the combination of combos for further limitations.

this is what I have so far.

Private Sub cmdSearch_Click()

   On Error GoTo cmdSearch_Click_Error
    
    strSQL = ""
    strSQL = "SELECT tblCity.CityRecID, tblCity.CityName, tblCountry.CountryCode, tblState.StateCode, tblCity.StateRecID," & _
                " tblCity.CountryRecID" & _
            " FROM tblState RIGHT JOIN" & _
                " (tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID) ON" & _
                " tblState.StateRecID = tblCity.StateRecID"
        If IsNullOrBlank(Me.cboCountry) = False Then
            strWHERE = "(tblCity.CountryRecID= " & gCountryID & " )"
        End If
        If IsNullOrBlank(Me.cboState) = False Then
            If Len(strWHERE) > 0 Then strWHERE = strWHERE & " AND "
            strWHERE = "(tblCity.stateRecID= " & gStateID & " )"
        End If
        If IsNullOrBlank(Me.cboCity) = False Then
        
            strSQL_1 = "Select cityRecID from tblcity" & _
                        "where tblCity.CityName) LIKE '' & Me.cbocity.value & '*' & '' )"
            
            Set rs = DBEngine(0)(0).OpenRecordset(strSQL_1)
            
            rs.MoveFirst
            Do While Not rs.EOF
                If Len(gCityID) > 0 Then
                    gCityID = rs.Fields("cityRecid")
                 rs.MoveNext
                gCityID = gCityID & ", " & rs.Fields("cityRecid")
               End If
                rs.MoveNext
            
            Loop
            rs.Close
            
        If Len(strWHERE) > 0 Then strWHERE = strWHERE & " AND "
            gCityID = DLookup("CityRecID", "tblcity", "(tblCity.CityName) LIKE '' & Me.cboState.value & '*' & '' )")
            strWHERE = "(tblCity.CityRecID IN(" & gCityID & " )"
        End If

        If Len(strWHERE) > 0 Then
        strOrderBy = " ORDER BY tblCity.CityName"
        strSQL = strSQL & " WHERE " & strWHERE & strOrderBy
        End If
        'strSQL = strSQL & strWHERE ' & OrderBy
        Debug.Print strSQL
        frm.lstCityFilter.RowSource = strSQL
        frm.lstCityFilter.Requery
   On Error GoTo 0
   Exit Sub

cmdSearch_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSearch_Click of VBA Document Form_frmHomePage"
End Sub

Select allOpen in new window

There is no reason to be using DLookup() to get the text.

If you want to select multiple cities, you will need to switch from a combo to a listbox.  Once you do the listbox, the example I sent shows how to select multiple items and build an In() clause.  Then, your query  would be Where tblCityID In(.....)

Where the ellipsis is replaced by the IDs of the selected cities.

ok I found some great code on creating listbox filter by multiple combos - How would you modify the following line to handle the IN statement.  and still handle the wildcard to the right of the inputted data:

I  found this @ http://bytes.com/topic/access/answers/959363-how-do-i-filter-listbox-multiple-comboboxes


If Me.cboCity.ListIndex >= 0 Then strRS = strRS & " AND cityRecid = " & gCityID

 

Private Sub cmdSearch_Click()
    Dim strRS As String
 
    ' Filter the list box appropriately based on the combo box selection(s)
   On Error GoTo filterList_Error

    If Me.cboCountry.ListIndex >= 0 Then strRS = strRS & " AND CountryRecID = " & gCountryID
 
    If Me.cboState.ListIndex >= 0 Then strRS = strRS & " AND stateRecID = " & gStateID
 
    If Me.cboCity.ListIndex >= 0 Then strRS = strRS & " AND cityRecid = " & gCityID
 
    'IF FILTER FOUND ADD 'WHERE' AND REMOVE LEADING 'AND' (IF NOT FOUND IT WILL REMAIN A NULL STRING)
    If strRS <> "" Then strRS = " WHERE " & Mid(strRS, 6)
 
    'ADD FILTER TO QUERY
    strRS = "SELECT qryFilterListData.CityRecID, qryFilterListData.StateCode, qryFilterListData.CountryCode" & _
            " FROM qryFilterListData " & strRS
 
    'ADD ORDER BY CLAUSE
    strRS = strRS & " ORDER BY qryFilterListData.CityName;"
 
    Me.lstCityFilter.RowSource = strRS
   ' Me.lstCityFilter.Requery

   On Error GoTo 0
   Exit Sub

filterList_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure filterList of VBA Document Form_frmHomePage"
End Sub

Select allOpen in new window

this what I have so far on creating the in statement

Getting type mismatch error:

Private Sub cboCity_AfterUpdate()
Dim nName As String

nName = Me.cboCity
strSQL = "Select cityRecID from tblcity" & _
            " Where CityName Like me.cboCity) & " * ""
        Debug.Print strSQL
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
        rs.MoveFirst
        Do While Not rs.EOF
            If Len(gCityID) > 0 Then
                gCityID = rs.Fields("CityRecid")
             rs.MoveNext
            gCityID = gCityID & ", " & rs.Fields("cityRecid")
            gCityID = Left(gCityID, Len(gCityID) - 1)
            End If
        rs.MoveNext
        Loop
        Debug.Print gCityID
        rs.Close
End Sub

Select allOpen in new window

Isn't gCityID a number field? You're putting text into it

No its an integers, should need to use conversion string?

You should probably dtSrt by creating s local variable to hold the city and state. The beCh challenge I'd they u have the is rather than the city do combining oils give you something like 123,456 rather than Atlanta,Georgia
You'll need to play s big to get the text that I wZny but I'm not site of your goal with the city

Wow...my post got totally butchered by the smartphone...thanks Apple! Let me try to recreate it.

You should probably start by creating a local variable to hold the city and state. The big challenge I'd say you have is that the city combines into something like 123,456 rather than Atlanta,Georgia (which is still text, but not very useful).
You'll need to create a string variable to get the text that you want, but I'm not site of your goal with the city.

OK Pat I am attempting to modify the suggested code, however, I keep getting  the error

I am still getting  "Object Variable not found".

strSQL = "SELECT tblCity.CityRecID, tblCity.CityName, tblState.StateCode, tblState.StateRecID, tblCountry.CountryCode" & _
                " FROM tblState RIGHT JOIN (tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID) ON tblState.StateRecID = tblCity.StateRecID" & _
                " WHERE (((tblCity.CityName) Like [Forms]![frmHomePage]![cboCity] & '*')) OR (((tblCity.CityName) Is Null)" & _
                " AND ((tblState.StateRecID)=[Forms]![frmHomePage]![cboState])) OR (((tblState.StateRecID) Is Null)" & _
                " AND ((tblCountry.CountryCode)=[Forms]![frmHomePage]![cboCountry])) OR (((tblCountry.CountryCode) Is Null))" & _
                " ORDER BY tblCity.CityName"
        
        Debug.Print strSQL
        frm.lstCityFilter.RowSource = strSQL

Select allOpen in new window

Which line is highlighted?  Are you sure there is a frm object?  I copied "frm.lstCityFilter.RowSource = strSQL" from your earlier post.  Perhaps it should be:
Me.lstCityFilter.RowSource = strSQL

turns out I needed the full form name and got it to work.  Thanks for all your efforts.

Private Sub cboCity_AfterUpdate()

   On Error GoTo cboCity_AfterUpdate_Error

        strSQL = "SELECT tblCity.CityRecID, tblCity.CityName, tblState.StateCode, tblCountry.CountryCode, tblCity.StateRecID, tblCity.CountryRecID" & _
                " FROM tblState RIGHT JOIN (tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID) ON tblState.StateRecID = tblCity.StateRecID" & _
                " WHERE (((tblCity.CityName) Like [Forms]![frmHomePage]![cboCity] & '*')) OR (((tblCity.CityName) Is Null)" & _
                    " AND ((tblState.StateRecID)=[Forms]![frmHomePage]![cbostate])) OR (((tblCountry.CountryCode)=[Forms]![frmHomePage]![cboCountry])" & _
                    " AND ((tblState.StateRecID) Is Null)) OR (((tblCountry.CountryCode) Is Null))" & _
                " ORDER BY tblCity.CityName"
        
        Forms![frmHomePage]![lstCityFilter].RowSource = strSQL

   On Error GoTo 0
   Exit Sub

cboCity_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboCity_AfterUpdate of VBA Document Form_frmHomePage"
End Sub

Select allOpen in new window

Thanks for the great assist.

K