Thursday, 19 September 2013

SQL for rowsource of listbox needs to change when moved to subform

SQL for rowsource of listbox needs to change when moved to subform

I have a FindClientsNavigation form which contains a listbox lstbxclients
populated with client names and IDs. lstbxclients is filtered based on
text entered by the user in a textbox txtFilterClients. lstbxclients is
populated by having its rowsource set to the following query:
SELECT c.ClientNumber, c.FullName FROM Clients AS c
WHERE (((c.FullName) Like '*' &
[Forms]![FindClientsNavigation]![txtFilterClients].[Text] & '*'))
ORDER BY c.FullName;
This works perfectly. However, a problem emerged when I created a Main
form containing a navigation control, and dragged the
FindClientsNavigation form onto one of the tabs in the navigation control
and tried to run the Main form in form view. I am pretty sure the problem
is in the SQL in the rowsource of lstbxClients, so I changed that SQL to:
SELECT c.ClientNumber, c.FullName FROM Clients AS c
WHERE (((c.FullName) Like '*' &
[Forms]![Main]![FindClientsNavigation]![txtFilterClients].[Text] & '*'))
ORDER BY c.FullName;
However, I still get the same error message, which you can see in the
following printscreen:

Can anyone show me how to fix the SQL so that it does not trigger this
dialog box? I don't know if this has to do with the fact that
FindClientsNavigation is only one of a number of possible subforms that
are within the NavigationSubForm of the Main form.

No comments:

Post a Comment