Have subform display query results with command button.

[Suregrip]

Hi All looking for an answer to what I thought was going to be an easy task...
I have a form with a subform (FindBuffer_Form) which I built from a Query (FindBuffer_Query) within the query criteria I have the following (Like "*" & [Select the main Batch Number] & "*")in order to search a particular number.
I want to be able to run the Query from a command button on the main form and have the results appear in the subform, If I have the Source object of the subform set to Query.FindBuffer_Query I get prompted twice to answer the Criteria before the form opens, after the form opens the subform is generated as per the search criteria.


Ideally I want the form to open with no data in the subform run the query from command button and have the result in the subform.
I have not been able to find a solution trawling the web, any help greatly appreciated.
Thanks in advance.
Suregrip

[June7]

Is the main form bound? Is the subform related to main form by master/child links?

If not, why not a single form in Continuous view with the filter input control in the form header?

If you don't want data to show when the form opens, don't use LIKE and wildcard, use =.

[Suregrip]

Thanks for the reply,
No the main form is not bound and I don't have any relationships between the two, the main form has a number of command buttons which adds different data to the main database with different criteria, I want to be able to open the form and have the user select the appropriate command button to add/amend or view the data available.
The form will not be open all the time but called up from a link when required, could you explain why the search criteria opens before the form loads?

[June7]

Subform loads before main form - I know, weird, but that's the way it is.

Try single form as suggested.

[Suregrip]

Hi
As suggested have single form with view set to Continuous Forms and command buttons in header, looks great however when I run (FindBuffer_Query) a new datasheet opens with the results but does not update the form, I need to run the query and have the results update the form view not open a new sheet.

[lucychan]

Just checkout the forms connection.. It will shortout.

[June7]

What is FindBuffer_Query? Is it just a SELECT query? Don't need to open query. Change the code so it does not open the query, just requery the form. I use only VBA, no macros. VBA code would be:

Me.Requery

I don't use dynamic parameterized queries. I use VBA code that sets the form Filter and FilterOn properties. http://www.allenbrowne.com/ser-62code.html

[Suregrip]

Thanks for looking at This.. I'm quite new to Access and haven't quite got to grips with it all
FindBuffer_Query is a select query built using the wizard so all code is in SQL, so I guess it's independent that's why it opens a new sheet.
The query asks for a specific number within the (Order Field) hence (Like "*" & [Select the main Batch Number] & "*") and returns all values associated with the query.
How do I associate the Me.Requery to run the code and update the view on the form?

[Suregrip]

Resolved with [Forms("FindBuffer_Table").Requery]...
Oh so simple when you look in the right direction, thanks for all your help guys.
Suregrip.

[lucychan]

Now you problem is easily shortout and cleared.. Just a one line created such a big problem.
tomb raider casino