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...
Code:
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:
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:
Code:
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.