Referencing a Column AND registering Data in a table (1 Viewer)

[Jotun]

Hey everyone, I need a hand with getting a certain field to do two things. I have a combo box (Employee Name) that, when selected, brings up the Employee Number in the adjacent field. HOWEVER, this number is NOT stored in the associated table! Gar! I realize that the control source can either be a reference to the table or a reference on the form, but I need it to fill in the number based on the name AND put it in the respective table.

In my control source, it references the ID properly with this expression:
=[EmpName].column(1)

I tried comming up with expressions to do both store and reference data, but I can't. I have very little VBA experience. Any suggestions guys?

PS: The employee name field is [EmpName] and the field that will be pulling data from that field based upon the name is [EmpID]. I can't get the [EmpID] to show up in the table, it just says "0".

Thanks

 

[RuralGuy]

Is your ComboBox bound to a field in the RecordSource of the form?

 

[Jotun]

Well, I'm using 2000, so Im not sure to be honest. I'll check first thing in the morning.

 

[Jotun]

Yes, but I just cant get the data to go to the table.

 

[RuralGuy]

What is the field type for the field to which the ComboBox is bound? What is the Bound Column set to? What is the SQL for the RowSource of the ComboBox?

 

[Jotun]

Rural, I actually solved my problem by writing a Macro for the expression...

=[EmpName].column(1)

...and just kept the control source as the EmpID (linked by recordsource to the table).

Thanks!

 

[RuralGuy]

It sounds like you now have the data from [EmpName].column(1) in more than one table which is a normalization violation and will give you problems later on. If your form were based on a query that joined your current RecordSource with the table that contains the field you want, then putting the FK in that table would make the other field available for display without it actually being in that table. That is what Relational Databases do best.