Dynamically filtering a subform -- I'm just not getting it 1

[rycamor]

What is the most straightforward method to dynamically filter a subform based on data entry in the main form? I am not a VBA expert, and EVERY note I've read (extensively) on the subject has not yet cleared it up for me.<br>
<br>
Example: I have main form SearchClients, with subform ClientList displaying in datasheet mode. Clientlist queries SELECT * FROM Clients upon loading of form & subform. I have unbound fields in SearchClients from which I wish to read values and redefine the subform query UPON entry in each field, so as the user enters values, the data displayed in ClientList narrows to only those records that match.<br>
<br>
It seems that there is more than one way to do this, and I read tips about redefining the Recordset object, or using QBF, or creating a Recordset clone but I must be missing something in the syntax, because the best I can get is a form that needs to be requeried with some sort of command button, or that brings up parameter definition dialogs.<br>
<br>
Can someone give me a SIMPLE example, from start to finish? You'll be my friend for life.<br>
<br>
Thanx

 

[bitbrain]

Subforms are often used when you have a one to many table relationship. For example - Customers and Items. The main form would contain the Customer information (Name, Address...) and the subform would contain all of the Items associated with a specific Customer.<br>
<br>
In reading your question, it does not appear that this is your situation. If this is true (i.e. you do not have one to many table relationships with primary keys defined) forget subforms and keep reading. If you do have one to many tables, use the Form wizard to generate your form/subform. Inspect the Link Child / Link Master properties to figure out how it works.<br>
<br>
For something completely different, try this...<br>
<br>
1) From the Tools menu, choose Options. Click the Keyboard tab. Under Behavior Entering Field, select Go To End Of Field (you’ll see why later). <br>
<br>
2) Create a query that selects all fields from your client list - no criteria.<br>
<br>
3) Build a form (use the tabular wizard) that uses the query. Make sure all your Client fields are in the Detail section of the form. Test the form by viewing it - you should see all of your clients.<br>
<br>
4) Go back to design mode and add a text box control to the header section of your form. Give it a meaningful name such as txtSearch.<br>
<br>
5) Go back to your query and add criteria to your key field (Client name?). Use the Expression builder to reference the form's text box - the expression should look something like this: Like [Forms]![frmMyForm]![txtSearch] & "*"<br>
Save the query.<br>
<br>
6) Go back to the form and add the following code to the Change event of the text box:<br>
Private Sub txtSearch_Change()<br>
Requery<br>
txtSearch.SetFocus<br>
End Sub<br>
<br>
7) Save and test!<br>
<br>
<br>
HOW IT WORKS<br>
<br>
The underlying query uses wildcard criteria for the key field. Upon entry to the form, all rows will be displayed because the criteria is: Like "*"<br>
When a character such as "A" is typed into the search text box, the query is re-executed. this time, the criteria is: Like "A*"<br>
<br>
When "Requery" executes, the focus is set to the first field in the detail section of the form. Therefore, focus needs to be set back to the search text box. By default, all of the text box's text will be selected when it gets the focus. This is why the options ere changed in the beginning.<br>
<br>
I tried this with a small table and it worked well. You may run into problems if you have a large table because the Requery will take a while to run. Try it and see how it goes. If there are problems you could move the requery out of the change event and into the lost focus event. Or, take requery out of the text box and add a button to requery.<br>
<br>
Good Luck<br>
<br>
<br>