Query by Form

[roryok]

Hi.

(Access 2002 - Windows XP Pro)

I have a select query called "Find Suppliers". One of its fields is "supplierNa me" which is derived from a linked "Suppliers" table.

I have a form called "Select Suppliers" which has a combobox which lists all the suppliers names and is derived from a small select query.

I have a second form called "Show Suppliers" which I want to use to show all the suppliers extracted from the "Find Suppliers" query based on the "Select Suppliers " form with the combobox.

I've tried various combinations of filtering in the query as well as the "Show Suppliers" form and getting in an awful muddle on who is passing what to whom.

My second form above shows all the suppliers irrespective on what I ask. The "Find Suppliers" query insists on asking for the search parameters.

Could someone help to point me in the right direction, please? Thanks

Roderick

[puppydogbuddy]

You did not post your queries, so I can't into any specifics on them. What I can tell you is that where you are passing parameters between two forms, either, both forms have to be open ..... or the parameters passed from form1 have to be captured in a global variable before form1 closed. The parameter can then be passed from the global variable to form2's combo box when form2 opens.

[sierra7]

If the combo box in your "Find Suppliers" form is called Combo1 the query behind it should include a key field such as [SupplierID]. If this is the first column in the query, your After_Update event on Combo1 should have something like:

Dim strCriteria as String

strCriteria = "[SupplierID] =" & Me.Combo1.Column(0)

DoCmd.OpenForm "Show Suppliers", , , strCriteria
  
DoCmd.Close acForm, "Find Suppliers"

This will open the "Show Suppliers" form and display only data relating to the selected supplier. This assumes that [SupplierID] is a key field (Long integer) that uniquely identifies a supplier and is also included in the select query of "Show Suppliers". You will know that in Access VBA you can have multiple colums in the query behind a combo box, so the second column (refered to as Column(1) !!) could display Supplier Name by hiding the first column by setting its width to zero.

If you must use [SupplierName] it will be a string field so the syntax for defining strCriteria must include quotes e.g.

strCriteria = "[SupplierName] = ' " & Me.Combo1.Column(0) & " ' "

Instead of bothering with two forms you could embed the combo box in the Header section of "Show Suppliers". Access seems to let a form re-open itself with a new criteria. This way you don't have to keep going back to reopen your "Find Suppliers" form to query another supplier.

Another variation with the combo in the header of "Show Suppliers" is to use the Set Filter technique. Here the after update event would look something like;

Dim strCriteria as String

strCriteria = "[SupplierID] =" & Me.Combo1.Column(0)

Me.Filter = strCriteria
Me.FilterOn =True

I hope that helps

[roryok]

Thanks to you both for the replies. They were extremely useful.

Fiddled around with Sierra7's code and got what was required. Client wanted separate forms so kept it that way.

Thanks again.

I've got a further query about filtering but will make a new post.

[sierra7]

Glad to have been of some help.

However, just for the record, the terms 'Filter by Form' and probably 'Query by Form' have a special meaning in Access where you use the little 'filter-funnel' buttons in the top menu.

These give extremely flexible filtering by any combination of fields on the form.

I admit to shying away from them in my early Access developments for a variety of reasons but am starting to look at them again as users are 'more computer literate' than say 5 years ago.