CODEWITHSUNDEEP
sqlvbams-access
CodeMed
CodeMed

Reputation: 9205

SQL in rowsource needs to change when form becomes a subform

I have a Microsoft Access 2010 database which contains two SQL problems in Row Source attributes.

The FindClientsNavigation form in the database was working properly until I dragged it into a navigation control on a Main form, so that FindClientsNavigation became a subform. Now the following two problems need to be fixed:

1.) When you load the Main form, a dialog box appears asking for a parameter for the txtFilterClients text box. txtFilterClients is used to filter client names and IDS in a listbox lstbxclients. 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]![Main]![FindClientsNavigation]![txtFilterClients].[Text] & '*'))
ORDER BY c.FullName;

You can see the error message/dialog in the following printscreen:

2.) The second error occurs when you click on the "Communication Forms" navigation tab and a dialog box appears asking for the value of ClientID, which is a property of the FindClientsNavigation form. This second problem seems to be in the Row Source property for the qryListCommunicationForms form, whose SQL is currently:

CommunicationTable.CommunicationNumber, CommunicationTable.ClientNumber, CommunicationTable.DateOfCommunication, CommunicationTable.Communication, CommunicationTable.Communication, CommunicationTable.CommunicationWithNumber  
FROM CommunicationTable  
WHERE (((CommunicationTable.ClientNumber)=[Forms]![Main]![FindClientsNavigation].[ClientNumber]));

I am looking for solutions to both problems, and I would also appreciate some explanation of the concepts underlying the code so that I can know how to fix similar problems which may emerge elsewhere.

Upvotes: 0

Views: 1617

Answers (1)

Andy G
Andy G

Reputation: 19367

I haven't been able to follow your description fully, but assuming that FindClientsNavigation is a subform then you need to also reference its Form property to be able to reference a control within a subform:

[Forms]![Main]![FindClientsNavigation].Form![txtFilterClients]

you also don't need to refer to the Text property of the TextBox.

Added: If the RowSource is being assigned as a string then it needs to be split into three parts, so that the textbox value will be substitied into the SQL:

"SELECT c.ClientNumber, c.FullName FROM Clients AS c WHERE (((c.FullName) Like '*'" &
[Forms]![FindClientsNavigation].[Form]![txtFilterClients] & "'*')) ORDER BY c.FullName;"

Added: The name of your subform control is NavigationSubform, FindClientsNavigation is the SourceObject for this control. So you need to use:

Like '*' & [Forms]![Main]![NavigationSubform].[Form]![txtFilterClients] & '*'

The exclamation (the bang operator!) references the default Collection for an object which, for a Form, is its Controls collection.

This change also needs to be made in the code-behind your form qryListCommunicationForm, and the RowSource shown in your original post.

Upvotes: 2

Related Questions