时间戳

首页 > 解决方案 > ms访问vba listbox.rowsource在添加where子句时格式化没有结果

vba - ms访问vba listbox.rowsource在添加where子句时格式化没有结果

问题描述

我试图通过使用 VBA 设置 listbox.rowsource 在 msAccess 的列表框中获取过滤列表。以下代码示例的注释解释了我尝试过的所有内容。要运行代码，请创建一个小表“tblsop”，其中包含与 SQL 列匹配的两个字段，这两个字段都是文本。然后取消注释设置 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

标签： vbams-accesslistbox

解决方案

我找到了我的特定问题的答案。请参阅 options/object Designer/sqlserver compatible ANSI92 设置为 yes。这本身并不是我遇到如此困难的原因，而是这个选项在调试模式下没有意义。它仅在实际运行表单时才有意义。在我看来，一个 NASTY NASTY 设计错误。我正在上传一个示例数据库，该数据库清楚地显示了问题，并附有显示问题的步骤说明。这使我在交付给客户方面延迟了一个多星期。 optionSQL92ConmpatProblems.zip

推荐阅读