Combo Boxes and storing both columns

[jdbit2byte]

My bound input form sends entries to table Time.

I have a combo box that looks up two columns from table Jobs:
JobNum / JobName

When the user selects a Job Name and Number from the combo box I want two
results.

The Number is stored in the Job Number field in the form.

The Name is stored in the Job Name field in the form.

I have tried to get the combo box to send both but can only get one to work
at a time.

I am pretty sure I can only bind one column to send data but I don't know of
anyway around this issue. I have heard some talk of using a listbox but a
listbox is not user friendly because there are hundreds of job listings to
choose from.

thx

 

[Jeff Boyce]

Why? Access is a relational database. You don't NEED to store both.
Instead, just store the JobNum.

When you need to see the JobName (say, for a report), use a query to join
the tblTime back to tblJob and retrieve the JobName.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jdbit2byte]

Good point I could have done it that way also, but I want the technicians who
are filling out the form to see both at time of entry and in an updating
subform... I have seen the question a lot and most posts say you can't do it.

But I found a way to send both:

The bound field recieves the bound column from the combo box.
format combo box to display the unbound column
and after update send the text to the other field

Example: me.JobNum = me.cboJobLookup.text

 

[Jeff Boyce]

What you display on the form doesn't necessarily have to get stored in the
underlying table.

Regards

Jeff Boyce
Microsoft Office/Access MVP