Use unbound combo box on a subform to filter another combo box on sub from

Topic

New Reply

I have a main form with a sub form on that sub form an unbound control that I am trying to limit a second combo box the syntax I have tried is

[forms]![frmLightsonKeys]![frmLightsonKeysSubform].![lighttype]

[forms]![frmLightsonKeys]![frmLightsonKeysSubform].[form]![lighttype]

Neither work and when I enter the Combo box on the form it ask for the detail I have just select in the unbound combo box (light type) if I type that in the result is a list of those items. If I open the sub form in design view go to the query and switch to data sheet view it also asks for the “Lighttype” which one would expect.

The form is based on three tables two of which were the result of earl;her work and I thought I may have been getting an ID number rather than the description so I created a new table by appending the old data .

Any suggestions appreciated.

thanks

Peter

Reply | Quote

Replies

One way to get the syntax right, and be sure you use the correct names is to use the Expression Builder.

In the query, on the criteria line under the right field, right click and choose Build. It is slightly easier if the form is open in Design view at the time.

On the left double click Forms then Loaded Forms, then your Main form then the Subform. now scroll through the list of controls on the subform and double click the first combo. The expression will be displayed at the top of the Expression builder, and returned to your query when you click OK.
(If using access 2010 you need to double click the name of the database before you can double click forms.)
30300-Expressionbuilder8

Reply | Quote

Hi John

Great tip about the syntax but I tried and that did not work after making the selection in the unbound combo box and moving to the next one that has the syntax in the correct filed it drops down a blank box. I thought it may have been because I was selecting a text name in the unbound box so I changed the bound column in the second box to make sure I was getting the name not the number. any other thoughts.

thanks agin for your time

Peter

Reply | Quote

Thanks John I will try that and I am still using Access 2003.

thanks

Peter

Reply | Quote

After putting something into the first combo, you need to requery the second combo. Have you put that in?

Use the After Update event of the first combo.

me.secondcomboboxname.requery

Reply | Quote

Thanks John

Tried that and I now get one field when there should be more back to the drawing board I feel. The second Combo should show all the places that have the type of lights I selected in the unbound box but as I said only one field. Really appreciate the help.

peter

Reply | Quote

Tried that and I now get one field when there should be more back to the drawing board I feel. The second Combo should show all the places that have the type of lights I selected in the unbound box but as I said only one field.

Do you mean one record when you say one field?

Are you able to post a cut down version of the db, with just the bare minimum to illustrate what you are trying to do? It needs to be zipped to post here.

Reply | Quote

Hi John

Sorry it has taken so long to thank you for your help . I run access in parallels on a MAC and I managed to crash my machine and it has taken me a day to get it back again. I seem to have corrupted my DB in the process so I am back to the drawing board will post a copy when I fix it.
Sorry about my terminology yes I mean the drop down displays one record rather than all the records that match the unbound combo box.

Thank you

Peter

Reply | Quote

I hope you get things going again.

Clearly I can’t tell from here what the problem with your linked combo boxes is. But I thought it might be useful to talk about what I would do if I was having trouble getting linked combos to work properly.

[*]Are there any fields in your tables created with the Lookup Wizard? These cause confusion as they hide their true value. If you have any, change the column width of the first column from 0 to 1.
[*]Make a copy of the query behind the second combo and remove the criteria that pulls a value from the first combo. Instead put in a sort on that field. And double check that you put the criteria against the correct field. Look at the data returned by this query now. Are the values in the field that previously had the criteria the sorts of values you expected? Is the number of records about what you expected? Now that you have it sorted you can see how many records there are for each value in the field that normally has the criteria. Is that what you expect?
[*]What values does the first combo really take? Does it have a hidden column? If so, change the column width of the first column from 0 to 1. In the After Update Event put in a message box:

Msgbox me.lighttype (assuming that is the name of the combo)
[*]Return to the copy of your query. Hard code in the value returned by the MsgBox in the criteria line. Does that work? Does it return the number of values you expect?

Reply | Quote

Hi John

Got everything back to gather again, and just wanted too thank you for your help I was comparing apples with oranges ID number with text. however I was also asking for something my structure would not allow. I have another question regarding insuring lines conditionally in a report but I assume protocol means a new post which I will do

Thank you again

Peter

Reply | Quote