VBA to change listbox.rowsource not working

[billpennock]

I have a form that someone else wrote that takes 3 input fields and uses them to query either a name field or a tag field in a table. It calls a different result form depending on which query but the forms are exactly the same except for the rowsource. I am loath to live with paste code reuse so I'm trying to make it one form and change the rowsource property of the listbox. It doesn't work, I just get no results with any of the following attempts. this seems like it should be easy but either I'm making a stupid mistake or missing something. There are lot's of comments in my code below about what I tried, I'm only trying to get the one query to work now because if that works the other will be trivial


Except that I've seen other posts on other forms saying this should work I am about at the point of making a permanent querydef then deleting it each time, recreating it with the SQL I want and attaching that as the rowsource.

Private Sub Form_Open(Cancel As Integer)
     Dim searchSQL As String
    
' the following commented out versions of setting searchSQL show what I
' have tried and what works vs what doesn't work.  I can't find a version
' of setting searchSQL = that works.  forms!frmSearch.txt1 evaluates to
' the string chem in my testing
    
'    this displays the whole table of rows in the listbox of this form
'    searchSQL = "select sopid, sopname, soplink from tblSOP"

'    this works also just to show it is not only adding a where that kills it
'    searchSQL = "select sopid, sopname, soplink from tblSOP where 1=1"
    
'    the next two display empty listbox with no columns
'    searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like ""*" & Forms!frmsearch.txt1.Value & "*"""
'    debug.print searchSQL = select sopid, sopname, soplink from tblSOP where sopName like "*chem*"
'    searchSQL = """select sopid, sopname, soplink from tblSOP where sopName like ""*" & Forms!frmsearch.txt1.Value & "*"""""
'    debug.print searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like "*chem*""


    resultsList.RowSource = searchSQL

' I have tried resultsList.requery here and also several variations of
' resultslist.recordsourcetype to no avail.  A test of the last two searchSQL
' variations using a testSQL(searchSQL) routine works fine showing two records
' in the immediate window.  somehow programatically setting rowsource evaluates quotes
' differently than sending it with openrecordset() (used in testsql)


End Sub

 

[MajP]

searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like ""*" & Forms!frmsearch.txt1.Value & "*"""
'    debug.print searchSQL = select sopid, sopname, soplink from tblSOP where sopName like "*chem*"

That debug.print does not make any sense. You should simply be doing
debug.print SearchSql
So we can see how your string is resolving. The way you have it set up only returns true or false. And the syntax is wrong anyways to even do that.
Try with single quotes

SearchSql = "select sopid, sopname, soplink from tblSOP where sopName like '*" & Forms!frmsearch.txt1.Value & "*'"
debug.print SearchSql

Then post back how it resolves

 

[billpennock]

Sorry the debug.print searchsql = was just my way of showing how debug.print resolved the sql statement. it was not a statement to be run.

In other words the first searchSQL = statement resolves to

select sopid, sopname, soplink from tblSOP where sopName like "*chem*"

and the second one resolves to the same thing with double quotes around the entire statement

 

[billpennock]

Since I posted this I tried making the querydef. Here is the code. The deleteQDef and createQDef are just very small sub/function that do what you'd expect by the names.

Private Sub Form_Open(Cancel As Integer)
    Dim oArgs As String
    Dim searchSQL As String
    Dim qDef As QueryDef
    

' attempt with single quotes. listbox comes back empty but double click on searchSOP query and it works perfectly

    searchSQL = "select sopid, sopname, soplink " & _
            "from tblSOP where sopName like '*" _
            & Forms!frmsearch.txt1.Value & "*'"

' attempt with double quotes same result

'    searchSQL = "select sopid, sopname, soplink " & _
            "from tblSOP where sopName like ""*" _
            & Forms!frmsearch.txt1.Value & "*"""

'  everything exactly the same but no where clause and the listbox shows all table rows 

'    searchSQL = "select sopid, sopname, soplink from tblsop"
    If Not IsNull(Forms!frmsearchresults.OpenArgs) Then
        oArgs = Forms!frmsearchresults.OpenArgs
    End If
    Form.Caption = oArgs
    lblTitle.Caption = "SOP Search Results by " & oArgs

' delete the querydef if it exists

    DeleteQDef ("sopSearch") ' deletes it if it exists
' create the queryDef with the new search - this is working because I can change the field txt1 and the query in the query designer shows the change and it works with the new criteria

    Set qDef = CreateQDef("sopSearch", searchSQL)
    ' don't have anything really to do with the reference to qdef so discard
    ' since the query is  not permanent 

    Set qDef = Nothing
    resultsList.RowSource = "sopSearch"
    resultsList.Requery

    

End Sub

 

[MajP]

I do not know what you are doing wrong, but you are doing something wrong. This definitely works. There is no reason to requery, etc.,etc.

Dim searchSql As String
   searchSql = "select sopid, sopname from tblSOP where sopName like '*" & Me.txt1.Value & "*'"
   MsgBox searchSql
   resultsList.RowSource = searchSql

resolves to

select sopid, sopname from tblSOP where sopName like '*Chem*'

 

[Galaxiom]

Do you have RowSourceType as Table/Query or List?

What is the ColumnWidths Property?

 

[billpennock]

rowsourcetype is "table/query"
the column widths are 0in,4in,4in because I don't want to show the first column
and it works fine as long as there is no where clause. that is what makes no sense. Do i need to create a temporary table of my results then do a query of all the records in that table (stupid I know there has got to be some dumb thing that is causing this but it's got me beat for 3 days now) :banghead:

 

[MajP]

As I said and demonstrated you do not have to do anything. There is something else going on that we are not seeing. If you get the SQL string correct then it will work by simply changing the rowsource. Can you post some stripped down version that shows the problem?

 

[Galaxiom]

Got to be something silly.

Are you absolutely sure you have values in sopName field that do contain "chem".

 

[MajP]

Got to be something silly

That is exactly what I am thinking. It is something simple like that and they just are not seeing it. Fresh eyes probably could see it in a minute.

 

[billpennock]

@majp and @galaxiom thanks for your help, Majp thanks for the sample that worked. I had used a form that the former programmer (a bioengineer not a programmer) created and I was modifying it to work for all cases rather than, as he had, have a separate but almost identical form for each case. I don't know what was wrong with his form even now but I finally threw it out and started fresh with a form I built and it works just fine. I remember running into something like this a couple of times in my career where I tried modifying something someone else did and it simply didn't work correctly. I should have thrown it out two days ago and started over but every test I did said it should be working.

There are hidden dependencies all over this thing and I'm guessing there is either a corruption or dependency that I am just not seeing that caused the problem. Bottom line is, I can't spend more time on figuring it out when I can build my own screen in a few minutes that works for all cases.

You guys were very helpful in validating that I was not wrong in my idea, even if I never figured out why it didn't work. It's been a long time since I've done much Access programming so that was a big help.

 

[Mark_]

As a follow up, I would highly recommend creating a fresh database and import all objects into it. Look to see if this changes the size much. IF there is corruption, best to clean things up NOW than after you find you can't use it any more.

 

[billpennock]

Ok, I'm still having the problem and now I've simplified this down to an example. Why doesn't the Search Name button work. There are lot's more buttons and stuff on the real form but if I can't get the list all of that is moot


I have a little "testsql" routine in a utility database that I copied into here to prove the sql works.



This has got to be something simple that I just don't get about access

 

[moke123]

not sure if it was the problem but I replaced your listbox with a new one and my code worked, so your listbox may be corrupt. I imported everything into a new db also.

I also would take a different approach in building the sql string.

Private Sub btnSearchName_Click()

    Dim sqlSearch As String
    Dim strWhere As String
    Dim strSep As String

    Select Case Me.Frame41

    Case 1
        strSep = " and "

    Case 2
        strSep = " or "

    End Select

    If Nz(Me.txtCriteria1, "") <> "" Then
        strWhere = strWhere & " SOPName Like ""*" & Me.txtCriteria1 & "*""" & strSep
    End If

    If Nz(Me.txtCriteria2, "") <> "" Then
        strWhere = strWhere & " SOPName Like ""*" & Me.txtCriteria2 & "*""" & strSep
    End If

    If Nz(Me.txtCriteria3, "") <> "" Then
        strWhere = strWhere & " SOPName Like ""*" & Me.txtCriteria3 & "*""" & strSep
    End If



    If Nz(strWhere, "") <> "" Then
        strWhere = " Where " & Left(strWhere, Len(strWhere) - Len(strSep))
    End If

    sqlSearch = "select SOPID, SOPName, SOPLink from tblSOP "


    Me.lstResults.RowSource = sqlSearch & strWhere

End Sub

I also added an option group to choose whether to use "And" or "OR" between criteria. Heres example...

You should also have Option Explicit at the top of all your modules.

 

[billpennock]

Solved: I am uploading a new sample that has instructions on how to see the problem AND why it was so damn hard to diagnose. I do SQL92 in SQLServer all the time so it was weird for me to try to use * but all sorts of microsoft documentation says that's correct....except....see example


I hope this helps someone stumbling across this thread.