Forum Discussion
info from pulldown menu and store it in another table
Hi All,
Who can help me?
I made a master table in access with 6 headers in it, like below
Another table has my customers in it, with company nr, company name, address, zipcode, place, and country, I also have a query from the customers ascending on the customer Nr.
I would like to make a form with a cell with a pull down bar, it should show the customer nr, if I select this it should store all the info from that customer in the mastertable in the same named cells.
How can I make this?
Thx in advance,
Willem
3 Replies
You might like to take a look at DatabaseBasics.zip in my public databases folder at:
https://onedrive.live.com/?id=44CC60D7FEA42912%21169&cid=44CC60D7FEA42912
This little demo file the third form in the section on 'entering data via a form/subforms' includes a bound cboCustomer combo box whose RowSource is:
SELECT Contacts.ContactID, [LastName] & ", " & [FirstName] AS Customer,
Contacts.Address, Cities.City, Regions.Region, Countries.Country
FROM Countries INNER JOIN (Regions INNER JOIN (Cities INNER JOIN Contacts
ON Cities.CityID = Contacts.CityID) ON Regions.RegionID = Cities.RegionID)
ON Countries.CountryID = Regions.CountryID
ORDER BY Contacts.LastName, Contacts.FirstName;
Below this are four unbound controls whose ControlSource properties reference the Column property of the bound combo box to show the address data from the above query for the customer selected in the combo box.
By storing the address data only in the referenced tables, and showing it in unbound controls in the form there is no redundancy in the Orders table to which the form is bound, and the table is consequently normalized to at least Third Normal Form (3NF) which requires that all non-key columns be determined solely by the whole of the table's primary key.
Note, however, that in the items ordered subform, the unit price of each selected item is not referenced in this way, but the current value in the UnitPrice column is assigned to a UnitPrice column in the OrderDetails table to which the subform is bound. This does not introduce redundancy, because the price of items will change over time, but the price associated with each order must remain static as that current at the time the order was created.
If I understand correctly, you are attempting to duplicate information from the customer table into the "mastertable". As arnel_gp already pointed out, that's not how relational databases work.
Please invest some time learning about the process of normalization for databases.
The tables in a relational database application are designed so that you can store each data point one time, in one place, in one table. The "relational" part is the method by which records in different tables can be linked so that the data is available without being duplicated.
An internet search will turn up dozens of references, but here's one to get you started.
https://database.guide/what-is-normalization/Willem1310 , that is not the normal way to do it.
you create a Form from your customer table and fill the info.
