List box does not like rowsource

I have 3 combo boxes and 1 wildcard search field that is used to build the rowsource for the Listbox, however, my current code does not like the rowsource.  What am I missing? "Invalid use of property"

    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 IsNull(Me.cboCountry) Then
            strWHERE = " WHERE (tblCity.StateRecid)= " & gStateID & " )"
        End If
        If Not IsNull(Me.cboState) Then
            strWHERE = " WHERE (tblCity.CountryRecID)= " & gCountryID & " )"
        End If
        If Not IsNull(Me.cboCity) Then
            strWHERE = " WHERE (tblCity.CityRecID)= " & gCityID & " )"
        End If
        If Not IsNull(Me.txtSearchCity) Then
            strWHERE = " WHERE (tblCity.CityName)= " & Me.txtSearchCity & "*" & " )"
        End If
        
        strOrderBy = " ORDER BY tblCity.CityName"
        strSQL = strSQL & strWHERE & OrderBy
        frm.lstCityFilter.RowSource (strSQL)
        frm.lstCityFilter.Requery

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
I think Rowsource is a property so it needs =:

frm.lstCityFilter.RowSource = strSQL
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open 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

Open 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
0
Dale FyeCommented:
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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
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,
0
Dale FyeCommented:
actually, instead of :

If Not IsNull(Me.cboCountry) Then

I generally use a syntax that looks like:

IF IsNullOrBlank(me.cboCountry) = false Then

Open 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

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
Dale FyeCommented:
Missed that.  Try:

frm.lstCityFilter.RowSource = strSQL
0
PatHartmanCommented:
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.
0
rspahitzCommented:
strWHERE = " WHERE (tblCity.CityName)= " & Me.txtSearchCity & "*" & " )"

Open 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 & "*" & "' )"

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
rspahitzCommented:
FYI this line does not look correct:
gCityID = DLookup("CityRecID", "tblcity", "(tblCity.CityName) LIKE '' & Me.cboState.value & '*' & '' )")

Open in new window


It should probably be this:
gCityID = DLookup("CityRecID", "tblcity", "(tblCity.CityName) LIKE '" & Me.cboState.value & "*'")

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
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.
0
PatHartmanCommented:
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
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
PatHartmanCommented:
Please take a look at the example I uploaded.  You may not have seen it yet.
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
PatHartmanCommented:
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.
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
rspahitzCommented:
Isn't gCityID a number field? You're putting text into it
0
Karen SchaeferBI ANALYSTAuthor Commented:
No its an integers, should need to use conversion string?
0
rspahitzCommented:
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
0
PatHartmanCommented:
The strSQL variable can actually be saved as a querydef if you prefer since it always includes all the options.   That would leave the procedure with only the statement.

I'm assuming that if multiple criteria are entered, then they should be AND'd.  The expression below includes parentheses.  They are critical since the expression includes both ANDs and ORs.

frm.lstCityFilter.RowSource.Requery

 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" & _
                " WHERE (tblCity.StateRecid = Forms!yourform!StateRecID OR Forms!yourform!StateRecID Is Null) " & _
                " AND (tblCity.CountryRecID = Forms!yourform!CountryRecID OR  Forms!yourform!CountryRecID Is Null) " & _
                " AND (tblCity.CityName  Like '*' & Forms!yourform!CityName & '*' OR Forms!yourform!CityName Is Null) "
       
        
        strOrderBy = " ORDER BY tblCity.CityName"
        strSQL = strSQL & strWHERE & OrderBy
        frm.lstCityFilter.RowSource = strSQL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rspahitzCommented:
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.
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
PatHartmanCommented:
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
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the great assist.

K
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.