2 Comboboxes, the values on the second one will depend on the value of the first one.

[CSCS]

Hi,

I have a problem and I need any help I can get!

I have a master form (MasterForm) and a subform (SubForm).

in the SubForm I have 2 comboboxes (Institution) and (Office).

Each Institution has a number of Offices.

What I want is:

Based of the Institution the user choose, the offices that belong to this instituion will appear in the combobox.

How can I do that?

Regards,
CS.

 

[Smart]

Your Office combo box should be based on a query that selects the info you require. In the institution column of the query (in the criteria section) type this forms![Yourformname]![yourinstitutioncomboboxname]

In the On enter event of the Officecombobox type this
Me!yourOfficecomboboxname.Requery

 

[Kempes]

As luck has it, I've just completed a similar task.

What you need to do is the following.

ALL BELOW IN THE OFFICE COMBO PROPERTIES

Step 1) For the row source in the Office combo, invoke the query buider.

Step 2) Select the relevant fields including Office and Institution.

Step 3) In the Criteria section under the Institution Field you will need the following

[Forms]![Formname]![FormField]

(adapt for your own database). The formfield will refer to the institution combo on your form.

Step 4) Close and Save the query then choose the events tab and put the following code in the "On Got Focus" event.

'To display relevant options in the drop down box

FieldName.Requery


This should do the job

 

[Bigmo2u]

Kempes - I tried your idea but changing for a text box and what you type triggers the info in a combo box. Some how I am not getting the result that I want. I have come close but with a text box there isn't a row source. Help please.

I figured it out. thanks....

 

[CSCS]

Kempes

which field name do you mean in:

FieldName.Requery

and please note that my 2 comboboxes are in a subform not a masterform.

 

[Kempes]

That's fine that it's in a subform. You just need to reflect that in the query criteria so it knows where to look.

The idea of adding [Forms]![yourformname]![yourformfield] in the criteria is so it has a value to base the resulting options on. Mine too is a subform.

You will need to change it as such.

[Forms]![YourParentFormName]![YourSubFormName]![FormField]

Best to choose this from expression builder as it will put most of this in for you.

Fieldname.requery refers to itself, so if your field is called Office for example, the code would be

Office.Requery

To explain a bit clearer on why this method works. When the user chooses an institution, the next box selected (Office Combo) using the code above will requery the data based upon the value chosen at the point of this box having focus.

If you are still having problems let me know and I will try to put together an example if I get time.

BIGMO2U: I am assuming the value you want to appear is a singular value driven by the option chosen, Is that correct?

This is reasonably simple to acheive. If your form is driven by an underlying query rather than a table, simply edit the query, add in the related field and select the correct table. Providing you have got your relationships correct, this should display what you require.

What I also do is when adding to the form, I lock the text box to ensure nobody can change it.

Again, let me know if you need it broken down further.

 

[CSCS]

IT WORKED!!!!

THANK YOU KEMPES !! YOU ARE GENIUS !!

Thanks alot man!