Set list in one combobox based on the selection in another.

[Albion]

I have three tables

tblCompany = Company Information
tblContacts = Contacts within each company linked to tblCompany.
tblCallInfo = Information on calls to contacts linked to both tblCompany and tblContact.

I want to click a button and have a form appear requesting new CallInfo. On that form I want to have two combo boxes. In the first the user will select a company. That combo box will get it's list from tblCompany. After the company is selected in the first combobox I want the second combobox to update listing only contacts from tblContacts which are linked to that company in tblCompany. After all is said and done I just want the key numbers from tblContact and tblCompany to be added to the new record in tblCallInfo.

Does anyone know how I can do this?

-al

 

[WayneRyan]

Hi Albion,

On the After_Update event on Combo1 put
the following:

Me.Combo2.RecordSource =
"Select Contact " & _
"From tblContacts " & _
"Where Contact = '" & _
Me.Combo1 & "'"

Do a similar thing for Combo2.

Just a rough stab.

HTH,
Wayne

 

[WayneRyan]

I think your link should be where Company = Combo1.

Late for a meeting.

Wayne

 

[ds2728]

Hello,

I had problems doing about ayear ago and here is the solution that was given to me that works great.

The following goes into the second combo box for contacts, in the row source. You build a qry that will contain this in the criteria for the corresponding field from the 1st combo box.

[forms]![form name].[form].[1st combo box]

in my example I have two combo boxes the first list bore size for bearings, and the second combo is to select the series for the corresponding bore selected in the first combo box. The second combo box only list the series available for the selected bore size in the first combo box.

I know it took me awhile to figure it out, and it is hard for me to explain. I hope it makes sense because it works like a champ for me.

don't forget to refresh the row source on the second combo box if you go back to change your value in the first combo box or otherwise or selection in the second combo box will not update automatically.

Hope this helps,

dave

 

[Albion]

After a while of d<beep>king around I did this.

I add this SQL statement to the &quot;Row Source&quot; Property of cboCompany:

SELECT [tblCompany].[Item_NUM], [tblCompany].[Company_Name] FROM tblCompany;

And this one to the Row Source Property of cboContact.

SELECT DISTINCTROW [tblContact].[Item_NUM], [tblContact].[Contact_Name] FROM tblContact WHERE ((([tblContact].[Company_NUM])=[forms]![frmContactInfo]!cboCompany));

And then added a Requery to the After_Update function of cboCompany.

Thanks for all the help.