SubForm and Combo Box

[FlashManAB]

Can I use an unbound combo box on a form to narrow the records appearing on a subform in the form?
If I do this, does the subform have to have the field of interest, or can the combobox refer to the many side of a foreign key?

Any help would be appreciated.

 

[Tim L]

You seem to be at dual purposes, you want to use an unbound combo but if the combo box refers to 'anything' then it is no longer unbound... (I'm presuming that by 'refers' you mean in the Control Source, because you must have a Row Source, if don't you're not going to have a very useful combo...)

I'm not sure what you mean by 'does the subform have to have the field of interest'. The subform will not need to display a control that shows the same value as the combo however, one of the fields in the subform's underlying Record Source must will need to contain data that is combatible with data in the combo's Row Source.

This solution should work whether you have bound the combo or not, and it does not matter if the subform is bound to the parent form by a PK-FK relationship (if it is, however, the records that could be displayed in the subform will be restricted due to both the PK-FK relationship and this method):

Ensure that the Record Source for the subform is a query, and not a table. In the query ensure that one of the fields has the criteria of having to match the current value of the combo (ideally the Row Source for the combo will be a query listing the values in your subform's query that you wish to use to narrow the records in the subform):

With (preferably only) the form that you want to be referencing loaded. Place the cursor in the criteria box for the relevant field, click on the build icon (the one that looks like a wizard's wand with some dots to the left of it). Now, navigate down the left pane to Forms\Loaded Forms\<your form name>. In the middle pane find the combo which is going to be used. In the third pane double click on <value>; the 'path' to your combo will appear in the top pane. Click on OK and this path will be inserted into the criteria box of the query.​

Close and save the query.​

Note, if the combo has more than one column you may need to set which column is used by the query (the default is the first column). To change the column: open the combo properties box and click on the data tab, then enter the required column number in the Bound Column field. (confusingly, column references start at 1 in the properties of combos but when referred to using '.columns(<column number>)' the first column is (0). As you are using the properties dialogue you would use 1 for the first column, 2 for the second...)

The records displayed in the subform should now be filtered according to the current selection of the combo. You should use the combo's AfterUpdate event to requery the subform each time a new selection is made in the combo. Check out this link for advice on how to refer to forms and subforms.

HTH

Tim

 

[FlashManAB]

Thanks Tim, I am sorry I did not post a very detailed message but you have answered my question.
The record source for the subform is a query.
I have the combo box and its value is from a query with a value I want to narrow the subform values with.
I changed the criteria of my subform query to equal the unbound combobox value and added a requery to the after update of the combo box.
It works - Thank you for your help!
When I change the find combobox, the subform field changes to the correct value.
I am having trouble with the visual basic though when I load the subform with the main form.
Could you offer some advice? I haven't quite got the gist yet of the detail in the link you posted.
(I will rename it)

Private Sub Combo30_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Me.Requery
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Classification] = '" & Me![Combo30] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Tim L]

FlashManAB,

I'm not entirely sure what your current problem is. When you say 'load the subform with the main form' what do you mean? You say that things are working fine but still have a problem; I'm afraid that I'm going to have to ask you to spend a bit more time explaining the problem if you don't want a guess as the answer

As mentioned in my first reply, the combo code needs to Requery the subform. Currently your code is Requerying the form on which the control resides, presumably the main form. Have another look at the link I gave you, keeping in mind that the control (and hence the code) is on the Main form and refers to the Subform (note, in the link where it refers to Sub1 and Sub2, Sub2 is a subform of Sub1, and Sub1 is a subform of the Main form - you will, of course, need to change the references used in the examples to the names you have used in your form).

I note that you appear to be using the combo to 'find a record' as well as to filter the subform. There is nothing inherently wrong with this practice. I am, however, drawn to ask if the subform is bound to the parent with a PK-FK relationship. If so, then the combo should work fine, but will add it's filtering to the PK-FK 'filtering effect' that already takes place, so reducing the number of records accordingly. If not, would doing so alleviate the need to filter the subform based on the combo?

You mention that you 'changed the criteria of [your] subform query to equal the unbound combobox value', did you mean to say that you changed the criteria to INCLUDE the reference to the combo? Because if you didn't then you've lost the original criteria.

Tim

PS: When posting code it is good practice to use the code tags to make it easier to read (indent spaces are preserved). You can either type in the tags (which are [ CODE ] & [ /CODE ], without the spaces) manually, or highlight the code and click on the # button above the text edit window to have the tags put in for you.

 

[Rabbie]

Private Sub Combo30_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[Classification] = '" & Me![Combo30] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It should be me.recordsetclone

 

[FlashManAB]

Hi Tim and Rabbie.
I am suffering a bit from the terminology, I am trying to explain that I have done what you suggested, but access is not my first language
Tim, you are correct, what I did first was put a combobox on a subform. The subform is bound to the parent form (main form) with a PK-FK relationship.
When the combobox was accessed from the subform (subform only loaded), it worked fine.
The sub form query criteria is set now to refer to the value of the unbound combobox value as you suggested and includes both the form and the subform name in the criteria statement.
I am happy to announce that it is working! (Thank you immensely) The problem from my last post, was that I made a set of criteria that was set for a combobox on the subform when it was loaded alone.

I will have another go at the 'form' information from the link you posted to get a better understanding of the terminology.
(Am I correct in that this information will help me to modify the code when I move the combobox to the main form?)

And yes, I am trying to filter and find at the same time. I thought that this was what the find was for (erroneously).
I appreciate your patience; I think I am straining the friendship. I hope I will get there eventually.

 

[Tim L]

I am happy to announce that it is working! (Thank you immensely)

You're welcome and I'm pleased that you got it working.

And yes, I am trying to filter and find at the same time. I thought that this was what the find was for (erroneously).

As I said before, I see no reason why you shouldn't be able to use the combo to do both actions.

I am a little surprised that the combo is currently on the subform, and not the main form as I got the impression from the Original Post (OP) that this was the case. By placing the combo on the subform I think that you may have circumnavigated some of it's use. The 'find a record' method that is created by the wizard generally goes to the one record that matches the selection or to the first record that matches the selection. By filtering against a specific criteria the display should either go to the one record that matches the filter criteria, or the first record... See where I am coming from?

If the combo had been placed on the parent form then its use would be more beneficial. By selecting an item from the list the parent form would be moved to the one (or first) record that matched the selection AND the subform would be filtered to also match that selection. If the subform, as in your case, is linked by a PK-FK relationship then there may also be a design issue here, that of storing information in a record that is related to another record which holds the same information - duplication of data storage being a big No, No. However, if it is the case that the 'filter' is only looking for a key word within other data then this is less of an issue; I don't know enough about your database to make a proper judgement call).

I will have another go at the 'form' information from the link you posted to get a better understanding of the terminology.
(Am I correct in that this information will help me to modify the code when I move the combobox to the main form?)

Getting to grips with the syntax will certainly help you. Be careful when moving the combo from the subform to the parent as there may be references that need to be changed, both in the code and in the query; you're correct that the information in the link should help you with this task though. For practice, it might just be better, however, to start afresh with a new combo on the parent form.

Regards,

Tim

_____________________
If a post has helped you, you may add to the sender's reputation by giving feedback: click on the scales at the top right of their post.