Combo box controlled query ?

[Trees]

Hi all im new to this and im trying to make a from that will display a sub from containg infomation that can be changed by altering a combo box.

im not sure if its possible but i want to have the combo box containing names of people and then the sub form to display records that relate just to the selected person. and when the combobox's selection changes so does the displayed data.

i don't know if its possible to make a dynamic query that will take the text value in the combo box and use it in a query to query the data i have. and then produce just the data corisponding to one person.

ANY HELP on this would be greatly appreaceated. i have a feeling thatis simple but i have been pulling my hair out trying to find out how to do it.

 

[Jibbadiah]

In a nutshell without crazy code or anything you could try the wizard.
Create a basic form.
Add a combo box.
When the wizard appears select "Find a record on my form based on a value I select in my combo box."

 

[Smart]

1/ Try having a form (form1) with a combo box (contains people names)
(combo box based on a query that selects peoples names)

2/ Create another query that selects your records and in the criteria section of the column peoplesnames type this
forms![form1].[comboboxname]

4/ Create a new form (form2) based on the above query
Add the required fields to the form


5/ On the combo box that contains people names (1/ above)
in the properties,event,afterupdate type this

DoCmd.OpenForm "form2"

Hope this puts you on the right track

 

[Trees]

thanks guys ill try that out

 

[rhino213]

I'm trying to do the exact same thing but with multiple combo boxes. I've tried to use the method above but it just does not make sense. Could somebody translate it into noob for me please!

Thanks!

 

[Jibbadiah]

Smart has described it pretty well below... changes as follows:

1/ Try having a form (form1) with 2 combo boxes
(each combo box will be based on a separate query)

2/ Create another query that selects your records. This query must include the 2 fields that are stored in your combo boxes. Under each of these respective columns type:
forms![form1].[combobox1]
forms![form1].[combobox2]

... where combobox1 is the name that you have given to your first combobox, etc.

4/ Create a new form (form2) based on the above query

5/ On the combobox2 (1/ above) in the properties,event,afterupdate type this

DoCmd.OpenForm "form2"

6/ Place instructions on your first form to select a value from combo box1 and then combo box 2 in order.

 

[rhino213]

Smart probably has described it pretty well. Pretty well for people that actually have any idea what he is talking about. Remember, I'm a complete Access noob. I can just about muddle through creating tables and forms but when people start talking about creating queries to do all these wonderful things, I get completely lost.

Sorry to be a pian but I'll need a bit of a step by step guide to get through this whole querie's saga. thats if its not too much trouble folks!

Thanks for your help so far! I think I'm pointing in the right direction now!

 

[Jibbadiah]

Try looking at this sample db... should be fairly straight forward.

J

 

[ScottGem]

Smart probably has described it pretty well. Pretty well for people that actually have any idea what he is talking about. Remember, I'm a complete Access noob. I can just about muddle through creating tables and forms but when people start talking about creating queries to do all these wonderful things, I get completely lost.

Sorry to be a pian but I'll need a bit of a step by step guide to get through this whole querie's saga. thats if its not too much trouble folks!

Thanks for your help so far! I think I'm pointing in the right direction now!

Creating a filtered query is easy. Just go into Query Design mode, adding the table you want to filter. Next, double click on each field you want to include from the table. Now you have a query with specific fields.

To filter the query you just add criteria for the column(s) you want to filter on. For example, If you have a date field and you only want to show records for the current year (2005) you could set the criteria to: > #12/31/04#. Or lets say you want to show records for only a specific company. You would probably have a CompanyID column and put the specific ID in the criteria.

Now, for your specific question, you want to filter the records in the form based on values chosen in comboboxes. You really wouldn't use a mainform/subform here since your object is not to show records related to a parent record but to show records that meet certain criteria.

So I would add UNBOUND comboboxes that select the criteria. I would put these in the header of your form. The next thing you need to know is that you can reference a value in a control on a form using the syntax:

=Forms!formname!controlname

As long as the form is open, that expression (using the correct form and control names) will return the value in that control. So what you want to do is set the criteria in your query to the control on the form. So when you select a company in the combobox, the query will filter on that.

Just one more thing to do is put:

Me.Requery

in the After Update event (using the code builder) of each combobox you are using to create your filter.

If you have any problems with these instructions or SPECIFIC questions about them, please feel free to ask.

 

[rhino213]

I think I understand what your saying but I do have 1 quick question though.

I understand how to create a query now using the builder. The control side of things is a bit strange to me though.

I have 3 combo boxes in the header of my new form. These are Programme, Primary Product/Service & Organisation Name. I want to filter my list by each of these in turn starting with the Programme then Primary Product/Service and finally Organisation Name.

I guess I'm asking....

How many querie's would i need and what would the control(s) be?

1 quick assumption I've just made....I'm guessing its best not to have spaces in the form or table names?

Thanks again!

 

[ScottGem]

Are you saying you want each combobox filtered by the selection in the other combos? If so, this is a standard technique called cascading or syncronized combos. You should be able to find info about them by searching this forum.

If you go into the properties dialog of any combo, on the Data tab is the RowSource property. If you click on the [...] next to it you go into query design mode. So you would design each query behind each combo to set the criteria to the value of another control as I instructed previously. you also need to Requery the next combo when you select the previous one. So, in the combo where you select Programme, you add a line of code:

Me!cboProduct.Requery

And yes, spaces are not recommended for object names. Also its a good idea to use naming conventions to name objects.

 

[rhino213]

Cheers mate! I think I've got that sorted now. Any idea how i could clear the filtering from the form/list? I'm thinking of a little button somewhere on the form that will reset all the combo boxes to display all the records.

Thanks again!

 

[rhino213]

Scratch that last post! I've cracked that one myself.

I've written a macro that closes and then re-opens the form. That seems to clear the filters beautifully.

 

[ScottGem]

Scratch that last post! I've cracked that one myself.

I've written a macro that closes and then re-opens the form. That seems to clear the filters beautifully.

An easier way is with a Clear filters button that contains one line of code:

Me.Filter=""

No need to close the form.

 

[rhino213]

Even better! Thanks!

 

[ScottGem]

glad to assist

 

[rhino213]

Ok, so this is the code for the form with all the combo boxes on it. Can someone take a look and sanity check it for me please.

I know its messy and I've broken some golden rules!
-----------------------------------------------------------------------
Private Sub Command9_Click()
Me.Filter = ""

End Sub


Private Sub Organisation_Name_DblClick(Cancel As Integer)
On Error GoTo Err_Organisation_Name_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Main Supplier Details"

stLinkCriteria = "[OrganisationName]=" & "'" & Me![OrganisationName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Organisation_Name_DblClick:
Exit Sub

Err_Organisation_Name_DblClick:
MsgBox Err.Description
Resume Exit_Organisation_Name_DblClick

End Sub

Private Sub Combo25_AfterUpdate()
Me.Combo27.Requery ' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[OrgID] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo27_AfterUpdate()
Me.Combo29.Requery ' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[OrgID] = " & Str(Nz(Me![Combo27], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[OrgID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

--------------------------------------------------------------
and here's the SQL for my combo boxes
--------------------------------------------------------------
Programme Combo Box SQL
---------------------------
SELECT SPMProgramme.ProgID, SPMProgramme.Programme
FROM SPMProgramme
ORDER BY SPMProgramme.Programme;

-----------------------------------
Primary Product/service Combo Box
----------------------------------
SELECT SPMPPS.PPSID, SPMPPS.[Primary Product/Service] AS Expr1
FROM SPMPPS
WHERE (((SPMPPS.PPSID)=[Forms]![MainSupplierFilter]![Combo27]))
ORDER BY SPMPPS.[Primary Product/Service];

--------------------------------------
Organisation Name Combo Box
--------------------------------------
SELECT SPMOrganisation.OrgID, SPMOrganisation.OrganisationName
FROM SPMOrganisation
WHERE (((SPMOrganisation.OrgID)=[Forms]![MainSupplierFilter]![Combo27]))
ORDER BY SPMOrganisation.OrganisationName;

---------------------------------------
Any ideas or edits greatly accepted! I'm so close to cracking this now!