Combobox forms Access 2002

[acn4fun]

I have used the Denis - Sydney Geek posts to setup my combo boxes. I am on the final step to receiving results when I have discovered that I do not see anywhere in subform properties the Link Master and Child fields.

I have 2 combo boxes. I am able to make selections in both but the whole record set is in the result set.

I do not know VB and only a little SQL.

Thank you.

 

[Andrew Fergus]

Hi
I'm not sure if I understand the problem 100% but assuming you are seeking the master / child link fields on the subform, go into the design of the main form, right click anywhere on the subform -> Properties -> Data and you will see the Child and Master link fields. If this is on the wrong track please let us know.
HTH, Andrew.

 

[acn4fun]

Combobox 2002 Access

I would love to say that I see the Master and Child properties but they are most certainly not there. I have tried this in both the form box selecting the subform properties, data and than in the form that is being used as the subform itself.

Is there anywhere else I can go to find this linking ability?

 

[acn4fun]

Found master and child linking

Now the issue is receiving an error that it cannot bind the subform as the comboboxes are unbound.

 

[Andrew Fergus]

Am I reading your question correctly in that there are a number of combo boxes on a subform? And the first issue is that you can't see the master / child links between the main form and subform? While in design mode, click Edit -> Select Form, then right click the subform -> Properties etc. If you click the subform twice then you will be looking at the properties of the item you selected on the subform, not the subform itself. If you still can't see the master/child links can you explain how you created the form?
Andrew

 

[SydneyGeek]

Hi Rose, let’s see if this will help

You’ve got Combo1 reading [Sox Template], filtering data for Combo2 based on [Specific Temp] – and you want to filter the subform on [Specific Temp] from the second combo. Is that correct? If so, here’s the next steps. (If I got the order wrong, change it to protect the innocent.)
I’ve noticed that filtering a form on the result of a combo is often difficult, so I transfer the information to a hidden textbox and use that as the source of the filter. Also, if you base the subform on a query instead of just the table, you can add the textbox name in the Criteria row as the filter. Then, updating Combo2 updates the textbox, which in turn filters the subform. Here goes…

Create the filter text box.
1. In Design view on the form, which we’ll call frmMain for this exercise, drag a textbox from the Toolbox and place it on the form. It will say Unbound. Right-click the textbox and change these Properties: Name – txtFilter, Visible – False. Save the form and close it.

Set the subform to filter on this textbox.
1. Open the subform in Design view. Go to the Record Source property and click its Builder (…) button. If the subform is based on a query, it will open up without comment. If not, you will be prompted to create a SQL query (or words to that effect). If so, click OK.
2. You should now have a query grid with fields. If not, construct it as you would construct any query.
3. OK, to link to the main form, do this. Find the [Specific Temp] field (assuming that’s what you want to filter – see above). Click the Criteria row, and type Forms![frmMain].[txtFilter] ( you need to give the full address so Access knows where to go…adjust for the real name of your main form) Close and Save when prompted. Close the subform as well, saving when prompted.

Set the Combo filter
1. Re-open the main form in Design view, right-click Combo2, and go to its Properties. Click the Events tab, then select After Update and double-click the blank row to show Event Procedure. Click the Builder button to go the code module.
2. In the blank line at the cursor, type these 2 lines:
txtFilter.Value = Combo2.Value
[Whatever your sub form is called]!Form.Requery

Save and see how you go.

Denis

 

[acn4fun]

Combo Boxes in Access 2002

This worked like a charm!!!

I had to make 2 accomodations.

One was to have the caption for the subform read the subform Name.

The second was removing !Form from the Requery command. It was erroring out saying it could not find the field.

This worked perfectly!!!! I am now a very happy combo box form generator

 

[acn4fun]

2nd combobox not holding 1st combobox value

Ok, I got the combo boxes up and running like a charm. I'm on the 3rd series using the directions provided by SydneyGeek in a previous thread of 6/2004 and the message posted as a reply to my post.

I've run into an issue with the 3rd that has not occurred with the other 2. I have a series of alphanumeric text names in the other 2. This 3rd contains the alphanumerics and alphas. The issue is when the alphas are pulled in the second combo box it pulls every value from the 1st combo box regardless of the selection of the 2nd combo box.

The SQL looks something like this for the 2nd combo box -

SELECT DISTINCT Peoplesoft_Sox_1831.SpecificTemp FROM Peoplesoft_Sox_1831 WHERE (Peoplesoft_Sox_1831.[Sox Template]=[combosox]);

Where Combosox is the first combobox name.

I have tested in a regular query situation with no issues. I can't seem to nail how to hold the first value in this scenario.

 

[SydneyGeek]

SQL looks Ok to me -- you haven't linked the subform to the first combo by mistake?

Denis

 

[acn4fun]

Linking Combo Box

Private Sub ComboSpecific_AfterUpdate()
txtfilter.Value = ComboSpecific.Value
Sox1831BS.Requery
End Sub


That is the Module. ComboSpecific is the second value. I have attempted using ComboSox and it returns no records. When I changed to ComboSox I changed the Query where it selected Specific Template with the criteria established.