Trouble with building query in VBA...

[sstiebinger]

I'm not sure if this should be in the query forum or the programming forum, so I'm sorry if I chose the wrong one.

I'm having an issue trying to get a continuous subform query working in VBA...

I have a form that lists my accounts and also has a text box and a button to search the accounts. The accounts are listed in a subform in continuous form mode.
The continuous form data source is the following query, which works fine...

SELECT tblAccounts_Vendors.AccountID, tblAccounts_Vendors.RecordType, tblAccounts_Vendors.AccountName, tblAccounts_Vendors.AccountsCustom1, tblAccounts_Vendors.AccountsCustom2, tblAccounts_Vendors.ShipTo1, tblAccounts_Vendors.ShipTo3, tblAccounts_Vendors.ShipTo4, tblAccounts_Vendors.ShipTo5, tblAccounts_Vendors.Rep, tblAccounts_Vendors.ContactFName, tblAccounts_Vendors.MI, tblAccounts_Vendors.ContactLName, ([ContactLName] + ", ") & [ContactFName] & (", " + [MI]) AS cName FROM tblAccounts_Vendors WHERE (((tblAccounts_Vendors.RecordType)="Account")) ORDER BY tblAccounts_Vendors.AccountName;

This displays my entire accounts list, as expected.

In the main form is an unbound text box named txtSearchAccount and a button named cmdSearchAccounts.


In the On Click event for the button I have the following code:

On Error Resume Next    Application.Echo False
    Dim strSQL_Accounts As String
    If IsNull(Me.txtSearchAccount) Then
        strSQL_Accounts = "SELECT tblAccounts_Vendors.AccountID, tblAccounts_Vendors.RecordType, tblAccounts_Vendors.AccountName, " & _
            "tblAccounts_Vendors.AccountsCustom1, tblAccounts_Vendors.AccountsCustom2, tblAccounts_Vendors.ShipTo1, tblAccounts_Vendors.ShipTo3, " & _
            "tblAccounts_Vendors.ShipTo4, tblAccounts_Vendors.ShipTo5, tblAccounts_Vendors.Rep, tblAccounts_Vendors.ContactFName, tblAccounts_Vendors.MI, " & _
            "tblAccounts_Vendors.ContactLName, ([ContactLName] + "", "") & [ContactFName] & ("", "" + [MI]) AS cName FROM tblAccounts_Vendors WHERE " & _
            "(((tblAccounts_Vendors.RecordType)=""Account"")) ORDER BY tblAccounts_Vendors.AccountName;"
    Debug.Print strSQL_Accounts
    Else
        strSQL_Accounts = "SELECT tblAccounts_Vendors.AccountID, tblAccounts_Vendors.RecordType, tblAccounts_Vendors.AccountName, " & _
            "tblAccounts_Vendors.AccountsCustom1, tblAccounts_Vendors.AccountsCustom2, tblAccounts_Vendors.ShipTo1, tblAccounts_Vendors.ShipTo3, " & _
            "tblAccounts_Vendors.ShipTo4, tblAccounts_Vendors.ShipTo5, tblAccounts_Vendors.Rep, tblAccounts_Vendors.ContactFName, tblAccounts_Vendors.MI, " & _
            "tblAccounts_Vendors.ContactLName, ([ContactLName] + "", "") & [ContactFName] & ("", "" + [MI]) AS cName FROM tblAccounts_Vendors WHERE " & _
            "(((tblAccounts_Vendors.RecordType)=""Account"")) AND [AccountName] LIKE ""*" & Me.txtSearchAccount & "*"" OR [ShipTo1] LIKE ""*" & Me.txtSearchAccount & _
            "*"" OR [cName] LIKE ""*" & Me.txtSearchAccount & "*"" ORDER BY tblAccounts_Vendors.AccountName;"
    End If
    Debug.Print strSQL_Accounts
    Me.sfrmAccountList.Form.RecordSource = strSQL
    Me.sfrmAccountList.Requery
    Application.Echo True
End Sub

When I click the search button... the form fails... and I can't figure out what I'm doing wrong... I'm sure it's probably obvious but I'm really not that familiar with queries in VBA...

When I put some text into the search box and hit the button, this is what comes out in the immediate window for the debug.print command:

SELECT tblAccounts_Vendors.AccountID, tblAccounts_Vendors.RecordType, tblAccounts_Vendors.AccountName, tblAccounts_Vendors.AccountsCustom1, tblAccounts_Vendors.AccountsCustom2, tblAccounts_Vendors.ShipTo1, tblAccounts_Vendors.ShipTo3, tblAccounts_Vendors.ShipTo4, tblAccounts_Vendors.ShipTo5, tblAccounts_Vendors.Rep, tblAccounts_Vendors.ContactFName, tblAccounts_Vendors.MI, tblAccounts_Vendors.ContactLName, ([ContactLName] + ", ") & [ContactFName] & (", " + [MI]) AS cName FROM tblAccounts_Vendors WHERE (((tblAccounts_Vendors.RecordType)="Account")) AND [AccountName] LIKE "*seth*" OR [ShipTo1] LIKE "*seth*" OR [cName] LIKE "*seth*" ORDER BY tblAccounts_Vendors.AccountName;

Thank you in advance for taking the time to look at my problem.

[June7]

I would use apostrophes instead of doubled up quote marks.

strSQL_Accounts = "SELECT tblAccounts_Vendors.*, [ContactLName] + ', ' & [ContactFName] & ', ' + [MI] AS cName FROM tblAccounts_Vendors WHERE " & _
"[RecordType]='Account' AND [AccountName] LIKE '* & Me.txtSearchAccount & *' OR [ShipTo1] LIKE '*" & Me.txtSearchAccount & _
"*' OR [cName] LIKE '* & Me.txtSearchAccount & *' ORDER BY AccountName;"

Be careful mixing AND and OR operators. Parens are critical. Do you want:

(RecordType AND AccountName) OR ShipTo1 Or cName

or

RecordType AND (AccountName OR ShipTo1) Or cName

or

(RecordType AND (AccountName OR ShipTo1)) Or cName

or

RecordType AND (AccountName OR ShipTo1 Or cName)

Is the main form UNBOUND?

Why change the subform RecordSource? Could just build the WHERE clause and set the Filter and FilterOn properties.

[sstiebinger]

I'm new to Access so I copied this code from another project and modified it to my needs... I'm not familiar with how the Filters work, so I'll look into that if that's easier...
The main form is Unbound, yes.
I'm looking for: RecordType AND (AccountName OR ShipTo1 OR cName)

[June7]

Review: http://www.allenbrowne.com/ser-62.html

[sstiebinger]

That looks like exactly what I need. Thanks for the link!