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?

 

[Dugantrain]

Mojo1,
The first part's easy; just drag and drop a combo box onto your form and then make the appropriate selections to populate the combo box with two fields from an underlying table or query. Then to populate other text boxes, transpose the following in code:

Private Sub Your_ComboBox_AfterUpdate()
Me.Your_Text1=Me.Your_ComboBox.Column(1)
me.Your_Text2=Me.Your_ComboBox.Column(2)
End Sub

 

[Mojo1]

With this code you provided me:
Private Sub Your_ComboBox_AfterUpdate()
Me.Your_Text1=Me.Your_ComboBox.Column(1)
me.Your_Text2=Me.Your_ComboBox.Column(2)
End Sub

Will the data for each respective field go to the field in the table.

Department Department_Num
Alumni 1

So when I make the selection for Alumni and 1 will Alumni go to the field in the table named Department and will 1 go to the field in the table named Department_Num?

 

[Dugantrain]

Yes, if you have two text boxes, both of which are bound to the respective fields of the underlying table, then this will work. Give it a try...

 

[Mojo1]

I do not understand. When you say two text boxes it goes to the respective fields.

I am not using a text boxes but a combo box, can I make it work with a combo box?

 

[Dugantrain]

Mojo1,
Per my understanding, this is what you're trying to accomplish:
-Have a combo box that has an underlying table's Department Num and Department as its recordsource
-Populate two fields in another table with these two items
If this is what you're trying to do, then the code I sent should work fine. I whipped up a tiny database that performs this task if you want to see it. If you're trying to do something else, then respond back.

 

[Mojo1]

Per my understanding, this is what you're trying to accomplish:
-Have a combo box that has an underlying table's Department Num and Department as its recordsource
-Populate two fields in another table with these two items
If this is what you're trying to do, then the code I sent should work fine. I whipped up a tiny database that performs this task if you want to see it. If you're trying to do something else, then respond back.


I duplicated what you wrote as you can see. The part about populating fields in another table is not correct. The table from which I am getting the department and department-num from I want to use so when I make a selction from the combo that table is directly affected. The table in which the form gets it's data from is the table I want to use NOT another table.

So if I selected Alumni with dept_num of 1, then in the field department alumni would be added and in the field department_num 1 would be added.

This is only on new records being added to the table.

Does this make sense now and can I get this to work?

 

[Mojo1]

Currently when I put a combo box on the form and want the department and department_num, only the department is being displayed once you make a selection. Is there some where in the properties to change something to display both the department and department_num? Is there also something I can change in the properties to allow the department selected to go to the department field and the department_num selected to go to the department_num field?

 

[Dugantrain]

Well, this is a little trickier, but doable. It still sounds like you would do well to have two tables, a look-up table with just your departments and then a department details table so that you can get that one-to-many working (one department, many details). However, you can still do all of this in one table:
-Write a query to capture these two fields. Change its "Unique Values" property to "Yes". That way, you'll have each Department listed only once, no matter how many times it's in the underlying table.
-You still want to have two text boxes bound to the two fields in the underlying table. If you prefer, you can make these boxes invisible, just so long as they're on the form and are being populated with what's being selected in the combo. After this, the After_Update code should get what you're looking for.

 

[Mojo1]

Okay, I have the fields of department and department_num in the table I use as a record source for the form. I also have a INV_Department Table which has the fields of depertment and department_num in it as well. Can you let me know as far as a query how I could populate the fields in the table I use as a record source from the query?

Based on this information, what would be the best way to accomplish what I am trying to do.?