Filter Combo box by a field in the same form

[Guest]

In my database I want to simplify the entry of data on a form by limiting the
data in a combo box to only display the relevant data for a prevoius
selection in the same form.

i.e In the 1st field in the form I select the Company from a Combo box, the
second combo box is to select a contact for that comapny of which there may
be several for the one comapny.

Rather than scan the whole list for the right contact I want to restict the
contact list to only those conatcts of the comapny chosen in the 1st Combo
box.

I am relatively new to Access and self taught. Any help would be appreciated.

 

[Guest]

Hi Mannie

Check this link

http://www.mvps.org/access/forms/frm0028.htm

 

[Guest]

Hi Ofer,

Thanks for the link. I have been trying to get the combo boxes to work, no
luck so far.

The form I am using is to enter quotation details. The 1st como box is used
to select the company that the quotation is being sent to. The second combo
box is to select the person within the company. The current link between the
tables is that the 'Comany' table has a Field 'CompAutoNum' This is a unique
number for each entry in the table. This is linked in a 'one to many'
relationship to the table named 'Contacts'.

On the quotation form I want to select the company in the 1st combo box and
then select the person from the second combo box; this should be limited to
only those people linked to the company in the 1st combo box.

When i entered the code in your link and set the RowSourceType to Field list
it only gave me the filed names in the table, not the list of comany details.
When I tried to use the second part of the instructions Access closes every
time the query is activated.

Can you help with any other suggestions?

Thanks

Mannie G

 

[Guest]

Hi Mannie
Can you post the SQL in the Row source of the second combo?

Also, you need to refresh the second combo after a selection is made in the
first combo, using

Me.[Enter here Second combo name].Requery

On the after update event of the first combo

 

[Guest]

Hi Ofer

Thanks for your help with the second reply, pointed me in the rright
direction, a couple of errors in the code. All working well now.

Thanks again

Mannie
--
Thanks

Mannie G

Hi Mannie
Can you post the SQL in the Row source of the second combo?

Also, you need to refresh the second combo after a selection is made in the
first combo, using

Me.[Enter here Second combo name].Requery

On the after update event of the first combo

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

Hi Ofer,

Thanks for the link. I have been trying to get the combo boxes to work, no
luck so far.

The form I am using is to enter quotation details. The 1st como box is used
to select the company that the quotation is being sent to. The second combo
box is to select the person within the company. The current link between the
tables is that the 'Comany' table has a Field 'CompAutoNum' This is a unique
number for each entry in the table. This is linked in a 'one to many'
relationship to the table named 'Contacts'.

On the quotation form I want to select the company in the 1st combo box and
then select the person from the second combo box; this should be limited to
only those people linked to the company in the 1st combo box.

When i entered the code in your link and set the RowSourceType to Field list
it only gave me the filed names in the table, not the list of comany details.
When I tried to use the second part of the instructions Access closes every
time the query is activated.

Can you help with any other suggestions?

Thanks

Mannie G