Combo Box for two different fields

[Mojo1]

I want to create a combo box that will contain the Department and Department_Num in it. I have a table that has Department and Department_Num in it where I will draw the data from. I want to be able to have the values from the drop down go into each independent field when a new record is added.

Can some one help me with this one?

 

[Mojo1]

In the INV_Department table there is a Code field which is the same as Department_Num in the INFSVCAdd table and a Description field in the INV_Department table which is the same as Department on the INFSVCAdd table.

 

[Dugantrain]

OK, you should use whichever table is your look-up table (the table that has each Department and Department_Num listed once) as the rowsource for the combo box. When you use the combo box, even though you can see both fields in the list, the text area of the combo box will only show one of the fields after you have selected, so pick whichever field will be more appropriate to see after you have selected. Next, you'll need two fields on your form which will be bound to the two fields in the other table (the table that you're trying to actually put the data into). Make these visible or invisible, your choice. Alright, now, when you use the After_Update code for the combo box, these text boxes will update with the corresponding fields that you selected out of of the combo and thus your underlying table will update with those values.

*edit* Since you have separated your information into two tables, you can skip the query writing stage and tap this data straight from the look-up table.

 

[Mojo1]

I got the combo box to work and then modified the row source and something caused it not to work. I have copied the db and started again by adding a combo box to the form. Before this code was working perfectly but now when you make a selection it will not add the data to the table.

Here is the code:

Private Sub cboDept_AfterUpdate()
Me!DEPARTMENT = Me![cboDept].Column(1)
Me!DEPT_NUM = Me![cboDept].Column(2)
End Sub

I have column 1 and 2 since I have a column 0.

 

[Dugantrain]

Oh, right, it's probably:

column(0)
column(1)

Sorry, I sometimes forget to start counting from 0 .

 

[Mojo1]

Dugantrain:

Do you have an e-mail I can send you a screen shot of an error message?

 

[Mojo1]

I get the combo box to work and exit and open the form again and it does not do you have any suggestions?

 

[Dugantrain]

MDugan@us.ibm.com

 

[Mojo1]

Now the form works just fine with the combo box, I am at a loss as to why it works and then stops working and gives me errors.

 

[Mojo1]

How can I get the combo to just show what the departments and dept_num's are rather than show every record with department and dept_num.

I tried a GROUP BY and ORDER BY in the select statement in the row source and did not get it to work.

 

[Dugantrain]

I thought that you said that you had two tables, one of which had each Dept and Dept_Num listed once so that you could use this as the Combo Box's Row Source.