 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / November 2006table relationships
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. 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 >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 [quoted text clipped - 4 lines] > greatly > appreciated. 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? > Don't use the "lookup" data type/wizard in the table design view. It will > store one value, but display a different one. [quoted text clipped - 18 lines] > > greatly > > appreciated. 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 > Hi Jeff, > I had the same issue as Findlej. [quoted text clipped - 42 lines] >> > greatly >> > appreciated. 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 > 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. [quoted text clipped - 50 lines] > >> > greatly > >> > appreciated. 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 > Jeff, > In you piece of advice to Findlej, you suggest to keep the customerID and [quoted text clipped - 70 lines] >> >> > greatly >> >> > appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.