AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question -------------------------
Followup To Scott,
Question - OK. To be honest I thought I already done that, so, to make sure I have got it correct. Please tell me how to create the Foreign key and how do I set the linking fields to SupplierID?
Thanks,
Gary
Hi Scott,
I got a couple of questions for you. I hope that¡¦s ok..................
Firstly, a bit of background. I¡¦ve got two tables where I want to pull all of the information from both tables together in one Form. The Form was created using the Wizard. I selected all of the fields from both Tables and created it as a Form with Sub-forms. I created a primary key in my Main Table and for the Foreign key in my secondary table (I did this by created a lookup column to the Primary key). I¡¦ve also set up all of the relationships.
The problem is this. When I Open up my Form to add or view data, the Sub Form shows all data entered and not the data related to the Main Form. So when I¡¦m scrolling thru or do a find I want the Sub-Form data to be the associated data with the main Form.
An alternative explanation is this. I have 5 supplier and they all supply me with different items. My first Table and the main window of my Form are the supplier address and contact details. The Sub-Form is the items details. When I scroll through the suppliers details in the main window the Sub-Form should scroll as well, showing each suppliers own item list, but currently it just stays showing the same data no matter what supplier I scroll to.
Can you explain how I get both Form details and Sub-Form details to scroll thru together so all the information in the view is relating to the same supplier?
After all that I¡¦ll leave the second question for now ƒº
Thanks in advance for any help you can give and I hope I¡¦ve been descriptive enough.
PC Details:
Pentium 4 2.5ghz
2 gig ram
WinXP
Office XP
Thanks and regards,
Gary
Answer -
You have two tables: (i.e. tblSuppliers and tblItems). The primary key for tblSuppliers is SupplierID. You then have a Foreign key in tblItems; SupplierID. From there, when you insert the subform, you should set the linking fields to SupplierID. That should only display matching Items in the subform.
HTH
Scott<>
Answer You should have a field in tblItems named SupplierID and set to Number, Long Integer. Generally you use a combobox to select the correct supplier and store their ID in that field. When you use the wizard to create a subform, it will prompt you to create the links. You can do it manaully, by looking at the properties of the subform in the data tab.
