Creating Lookup

[Guest]

Hi,

I'm thankful for the help! I'm using Access XP. I have a Main table where
I keep track of publications in our Library. I have a publishers table where
I keep track of publishers. There are probably 10 publishers and 200
publications, so many publications share the same publisher.

When I'm entering publications, I want to be able to click a combo box and
it will let me select a publisher that already lives in my publisher table.

The trick is, I need all of the Publisher fields - so when I select the
publisher name the address and rep name comes in also, and then the
publication record will have a permanent link to that publisher.

I have the publication table and publisher table created, then I created a
table that stores the main id from both tables and bla bla I can't get it to
work for anything. Thanks for any assistance.

 

[Douglas J. Steele]

Why do you need all of the fields from the Publisher table? Hopefully you're
not planning on storing them in your Publication table!

If it's strictly for display purposes on the form, base your Publisher Name
combobox on a query that contains all of the fields you want (you don't have
to display them all in the combobox, but make sure they're in the Row
Source).

In the combobox's AfterUpdate event, you can populate fields on your form
like:

Me.txtPublisherAddress = Me.cboPublisher.Column(1)
Me.txtPublisherRep = Me.cboPublisher.Column(2)

This assumes that your Publisher combobox is named cboPublisher, that you
want to display the publisher's address in a text box named
txtPublisherAddress, the rep information in a text box named
txtPublisherRep, that the Publisher's address is the 2nd field in the query
that populates the combobox and the Rep name is the 3rd field (columns start
at 0)