How do I fill multiple columns with one lookup in Access?

[Guest]

I'm trying to dust off the brain cobwebs from when I took some database
classes in college and am helping my in-laws' rep firm design a relatively
small database to track their commissions. Here's what I can't figure out:

In the Commission table, I want to look up a Company name and, once the
company has been selected, automatically fill two associated fields with the
territory that company is in and the representative they do business with.

Is this possible, and how?

 

[Larry Linson]

I'm trying to dust off the brain cobwebs from when I took some database
classes in college and am helping my in-laws' rep firm design a relatively
small database to track their commissions. Here's what I can't figure
out:

In the Commission table, I want to look up a Company name and, once the
company has been selected, automatically fill two associated fields with
the
territory that company is in and the representative they do business with.

Is this possible, and how?

Use a three-or-more Column RowSource in a ComboBox. The default will be that
AutoExpand is enabled, so you can type in the value for which you want to
search and the Combo Box will automatically scroll to the nearest match --
much easier than _just_ scrolling.

Put those "two associated fields" as additional Fields in the RowSource,
then in the AfterUpdate event of the Combo, use code something like this:

Me!txtFirst = Me!cboSearch.Column(1)
Me!txtSecond = Me!cboSearch.Column(2)

where txtFirst and txtSecond are the TextBoxes you want to set and cboSearch
is the multicolumn Combo Box.

Larry Linson
Microsoft Access MVP

 

[John Vinson]

I'm trying to dust off the brain cobwebs from when I took some database
classes in college and am helping my in-laws' rep firm design a relatively
small database to track their commissions. Here's what I can't figure out:

In the Commission table, I want to look up a Company name and, once the
company has been selected, automatically fill two associated fields with the
territory that company is in and the representative they do business with.

Is this possible, and how?

It's possible... and it's bad design.

Don't store this information redundantly. The representative might
change; the company might expand their territory.

Just store the CompanyID in the Commission table, and use a Query
linking to the Company table to find these two fields. There is no
reason to add new fields to the Commission table; the data is already
available.

John W. Vinson[MVP]

 

[Guest]

Thanks John. I came to that conclusion, too, a few hours after I posted the
question. I was battling a case of tunnel vision, I guess. I realized I
don't need all that info in one table. Thanks for confirming my thoughts
after I further considered the problem!

 

[Jeff Wampler]

Yes, but what if you want to keep track of the Territory and Rep at the time of the transaction and you don't want it to update if the customer record is changed?