Hello all. I am not sure what I am doing wrong, but in my attached example db, I am unable to save the data selections and entries from the 'MainMenu' form to the 'Main' table. I am new to databases and have tried various control sources with no success.
Two things I noticed are that although field3 and field4 do get stored in the 'Main' table, the new data overwrites the old data. None of the other fields gets saved.
Additionally, it looks like a subform was automatically created within the first record of the Main table, but I am not sure what it is for or how to populate it. If it is to track all data that is related to a certain field, I want it to track by the 'Unit' field since it is the Unit that orders and receives the products.
I just don't have enough experience to logically look for possible causes of the issue. Can some of you database gurus please look at my example and explain what I need to do to fix it.
When you look at the form in design view, only field3 and field4 are bound to the table. All the other textboxes say "Unbound", which means they are not connected to the table. It sounds like you want to set the control sources of those to the appropriate fields in the table.
When the form opens, it is displaying the first record in the table, so when you enter data and click on your save button, all you're doing is modifying that record. To enter a new record, you'd need to click on the >* navigation button. You could also set the data entry property of the form to yes, which would cause it to open to a new record.
I suspect this is just for testing, but want to mention that having "Product1", "Product2" is not a good design. Search on normalization, starting here:
Thanks pbaldy. I will look at how field3 and field4 are bound and try to duplicate them for my other fields. I will also set the data entry property of the form to yes. I thought that this would have been done as a default when I created the form.
Our current database started with about 15 items so it was easy for them, (I am new to this project), to list them individually as headers on the data table with the quantity entry on the form being saved to the applicable headers. We are adding over 60 new products so I created the combo boxes to select the products from a table and then save the selections to the data table. The sample I provided exactly mimics the live database, and one of the sticking points is that the quantity is a text box that stores the user input and I do not know how to tie it to the associated product combo box. From here I do not know how to transfer this to the applicable product selection on the data table. I will look at the links you provided an hopefully they will help.
Made some modifications and tried to normalize it. Have a couple things I need help on.
First is when a product is selected, the form displays the actual product but when it is saved to the 'Main' table, it does not save the actual product to the Product column. It saves the 'Product ID' from the 'Products' table. How do I get the product selected ('E' for example) to save instead of '5' which is the Product ID?
Another problem I see is that I have 10 combo boxes on my live database to select up to 10 items for a single order to a 'Unit'. If I tie the 'ProductSelect' combo boxes to the 'Product' control source on the 'Main' table, both combo boxes display the same product and they will not save as multiple rows when the save button is clicked. Basically, if the Unit orders 10 different items, I need to add 10 records all at the same time from the form when the save button is clicked. I don't mind using VB code if someone can provide it and it does the job.
Generally that's exactly what you want. You save the ID of the product, not the name or other related data. You use a query to join the 2 tables together to get the related info.
You don't want 10 combos for 10 items. The typical setup for a product sales application is 2 tables to hold the orders. The "main" table holds the order number, customer, date, etc, but nothing about the products ordered. The "details" table holds the order number, product, quantity, price. If I order 5 items, I have 5 records in that table. You would use a form/subform to present that to the user, with the form showing the main data and the subform showing the details. In design view of that subform, you would see 1 product combo. In normal view, you'd see 1 for each line.