Senna_Rettop wrote:
Hello,
I'm new at Access and ran into a problem. I have a table with a
field for customer's names. I want to make a lookup field out of the
names by linking it to a table that holds all the customer's names,
adresses, phone numbers, ect. I made a form with a name field and
would like to be able to lookup the name, click it and the have that
person's info come up intantly in a subform. I have the layout all set
up, but I've run into a problem if the customers have the same name.
Even if I have the lookup field account for the name and a personal ID
#, when I select it, the corresponding info. for both customers of the
same name appears as two separate records in the subform and I cannot
choose between them. Can someone tell me how to link tables/feilds so
that the names in my lookup field corresponds to only one set of data
even if the names happen to be identical. Thank you!
-Senna
Rule #1: don't use lookups. They may look pretty now, but they'll
screw you over later.
Create an autonumber field on your Customer table, and make the
combination of fields that uniquely identifies a record as the primary
key. (So the autonumber acts as a surrogate). Then use that
autonumber in your relationships.
The combobox. ... If you want, you can set the rowsource for the
combobox to a query..
SELECT CompanyID, CompanyName, ...
FROM MyTable...
WHERE...
and then in your combobox, you can hide all the columns you want access
to but don't need the user to see. Then you can set the controlsource
for your text fields to something like
=Me.controls("cboCompany").Columns(1)
=Me.controls("cboCompany").Columns(2)
etc.
The first column is the zeroeth column... just to confuse things. so
for example,
Me.controls("cboCompany").Columns(0) would be the CompanyID,
Columns(1) would be CompanyName...
What's up with the subform? Can one company or whatever have multiple
contacts?
Maybe you should back up a step and figure out the tables first... once
you have that down, the rest is easy. Could you describe how the
people and the company(?) information is related? I know I'm being a
pain, but honestly, the first step in desiging a solid database is
getting out a pen and paper and describing to yourself what it's going
to do, and what you need in it. Then you can build tables etc from
that.
If you don't start right, there's little chance of ending right. so do
the hard part first, then the rest will come a LOT easier.
HTH... and post with further questions if you need to.
Pieter