table relationships

[Guest]

I have an orders table that I have the customerID field with a one to many
relationship to the CustomerID (primary key) field in a customer table. I
want to be able to enter a new order by choosing the customer by name from a
drop down menu. I do not want to choose a customer ID. How do I set this
up? Do I use the lookup tab on the table design view? Any help is greatly
appreciated.

 

[Jeff Boyce]

Don't use the "lookup" data type/wizard in the table design view. It will
store one value, but display a different one.

Instead, work in forms, not in tables. Create a form that has an unbound
combo box. Make the CustomerID, CustomerName fields the source for the
combo box. Hide the CustomerID by setting the width of that (first) field
to 0 (zero). This will allow the user to select by name, but will have the
ID available for something else, like, say, pulling up the record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,
I had the same issue as Findlej.
I am creating a DB with a
Donors table with DonorID PK and
Donations table and form with DonorID FK.
In the Donation Form, for DonorID I put a combo box and in the properties, I
have
Table/Query in Row Source Type and
SELECT Donors.Name & " " & Donors.FirstName FROM Donors; in row source.
It works and I can select the donors by their name in the list.
But the DonorID number has disappeared from my Donation table and replaced
by the name.
Will that work when I make reports that for instance will require the
address of the donor ( I am not that far yet), or is it necessary as you
suggest to maintain the DonorID number in the form?

 

[Jeff Boyce]

I'm not sure how you mean "disappeared"... It may be that your field type
is "lookup" causing Access to display the text, but store the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
In you piece of advice to Findlej, you suggest to keep the customerID and
hide it.
With my formula the donorID (FK) number is replaced in the donation table by
the donors name.
So i wonder when I do some reports on the donations with information from
both tblDonations and tblDonors the connection will be done.
sorry if I am not clear enough

 

[Jeff Boyce]

I don't understand. If you are working on reports, you can create a query
that returns those fields you wish to see, then base your report(s) on that
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP