For MVP: data entry from combo box line items

[Bill Morgan]

Problem: I need to ensure that data entry personnel always
enter the Customer LastName and FirstName that correspond
to that Customer's ID Number.

They pick (click on) the Customer ID from a combo box that
is three columns wide and also lists the Customer LastName
and FirstName. The combo box is based on a query that
comprises all Customer ID's, LastNames, FirstNames, and it
is bound to the Customer ID field on the form's underlying
table. When they click on the Customer in the combo box,
the Customer ID posts to the Customer ID field (text box).

What is the cleanest/simplest way to make the Customer
FirstName and LastName (columns 2 and 3 of the combo box)
also automatically post to the forms LastName FirstName
fields at the same time that the Customer ID posts?

Thanks for any help youc an provide ...

Bill Morgan
(e-mail address removed)

 

[Roger Carlson]

Well, I'm not an MVP, but...

There is no need to need to copy the FirstName and LastName into the other
table, if that's what you're thinking. Storing the ID is enough. You can
join the tables back together later in a query or report.

However, there is no problem with simply *displaying* those values. There
are a number of ways to do this. One would be to have the first and last
name text boxes (txtFirst and txtLast) remain unbound. Then in the
AfterUpdate Event of the Customer ID combo box do something like this:

Me.txtFirst = cboCustomerID.Column(1)
Me.txtLast = cboCustomerID.Column(2)

 

[John Vinson]

Problem: I need to ensure that data entry personnel always
enter the Customer LastName and FirstName that correspond
to that Customer's ID Number.

Ummm... the data entry personnel should *never need to enter this
information* if it's already in the database. Relational databases
like Access use the "Grandmother's Pantry Principle": "a place - ONE
place! - for everything, everything in its place". The customer names
should be stored in the Customer table and ONLY in the customer table.
The names can be *displayed* in conjunction with your other tables'
data using queries or combo boxes or other tools; your other tables
should contain only the CustomerID as a link.

They pick (click on) the Customer ID from a combo box that
is three columns wide and also lists the Customer LastName
and FirstName. The combo box is based on a query that
comprises all Customer ID's, LastNames, FirstNames, and it
is bound to the Customer ID field on the form's underlying
table. When they click on the Customer in the combo box,
the Customer ID posts to the Customer ID field (text box).

What is the cleanest/simplest way to make the Customer
FirstName and LastName (columns 2 and 3 of the combo box)
also automatically post to the forms LastName FirstName
fields at the same time that the Customer ID posts?

The cleanest way is *not to do so at all*. If you want to *see* the
name on the form, use unbound textboxes with control sources like

=cboCustomerID.Column(n)

wnere n is the zero based subscript of the desired field in the
combo's rowsource query.

 

[Guest]

Roger,

Both your responses are neat, simple, and much
appreciated. Don't know why I still feel paranoid about
seeing Customer ID's in a table without the associated
name, but I still do. Looks like I could also customize
your second solution to cause a post to bound text boxes
rather than only to unbound. Thanks again ...

 

[Guest]

John, thanks for your reply. As usual, I have been asking
the wrong question - that's no doubt why I have been
having a hard time finding the answer. I'm going to emply
the method you suggest (=cboCustomerID.Column(n)in unbound
text boxes). Thanks again.

b.

 

[Roger Carlson]

Well, you're free to do as you wish, but I would strongly caution you
against storing these values as second time. It violates the rules of
normalization, which exist because they work.