ms access vba listbox.rowsource formating no results when adding where clause

Question

I am trying to get a filtered list in a listbox in msAccess by setting the listbox.rowsource using VBA. The following code sample has comments that explain everything I've tried. To run the code create a small table "tblsop" with two fields matching the SQL columns, both fields are text. then uncomment the various attempts at setting searchSQL

    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*""

    '    this one I got from a web answer to another question so I tried the # as delimiters
    '      this one displayed 2 columns but they were empty
    '    searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like #*" & Forms!frmsearch.txt1.Value & "*#"
    '    debug.print = 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

#HansUp 2. as i said in the notes the query in the immediate window comes back with the correct results when done anywhere but in rowsourxce — billpennock
– billpennock, Commented Aug 20, 2018 at 13:48
I have since redone this to create a queryDef with the sql statement. I delete the permenant querydef searchSOP and then recreate it with the SQL using the field value. I have. verified that when I change the value of the field the queryDef changes. I have opened the queryDef after running the program through the search and the new queryDef has the expected new value and works as expected but when I run the program, which sets rowsource = "searchSOP" it works if my sql does not have a where clause and it doesn't show any values if I add the where clause though the query works stand alone — billpennock
– billpennock, Commented Aug 21, 2018 at 3:20

billpennock · Accepted Answer · 2018-08-27 13:28:21Z

0

I found the answer to my particular problem. See options/object designer/sqlserver compatible ANSI92 was set to yes. This, in itself, was not the reason I had such a hard time it was the fact that this option had no meaning in debug mode. It only has meaning in actually running the form. A NASTY NASTY design bug in my opinion. I'm uploading a sample database that clearly shows the issue with instructions on the steps to take to show the problem. This cost me over a week delay in delivering to a client. optionSQL92ConmpatProblems.zip

answered Aug 27, 2018 at 13:28

billpennock

4601 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ms access vba listbox.rowsource formating no results when adding where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related