How to prevent showing of the records in a subform when opens up.

[Guest]

Hello pros, I have a form with a sub-form pointing to a query called search.
I am basically using the form to search for specific records in my database.
The main form has 2 fields – “street number†and “street nameâ€. The use can
specify what address to look for and the sub-form shows the result after
pressing a search button (action button, re-query and refresh). The search
works as planned (the query looks up the form and filter data matching the
string/number in these two fields). What I am not happy with is that when I
open the form, the sub-form shows at the records in my table at first, then
when I press the search button (re-query) only the record that match my
string show up in the sub-form. How can I prevent this from happen and show
records only after pressing the search button on my form when I open it up?

 

[manningfan]

Try making the field unbound. It sounds like you're using a bound
field, which means if you type over it your data is going to get hosed.

 

[Guest]

Silvio,

One way is to manipulate the RecordSource property of the subform in the
OnClick event. First, set the RecordSource of the subform in Design View to
the same value as the SQL of your query except add a WHERE clause that will
find no records:

SELECT YourTable.Field1, YourTable.Field2 FROM YourTable WHERE
YourTable.YourField1 = "$$$$$$" ' or any other meaningless string that
won't match an existing record

Then your command button can change the RecordSource to point to your query:

Me![YourSubform].Form.RecordSource = "YourQuery"
Me.Requery
....other actions

Hope that helps.
Sprinks