Unanswered: Using a combo box to update access table fields
I'm new here, and relatively new to Access so please bear with me.
I am using Access 2010. I have 5 tables set up: A customer table, Employee table, Products table, Invoice table, and an Invoice details table.
I have also created Input forms for all of these: Customer Form, Employee form, Products Form, and an Invoice input form with a Invoice input sub form to update the Invoice and Invoice details tables.
The first three forms and tables are a piece of cake and work well. It is the Invoice tables and forms I have trouble with. The invoice table has the following fields in it:
Invoice Number (Primary Key) Invoice Date, Customer ID (Foreign Key) & Employee ID (Foreign Key)
The invoice details table has the following fields: Details Unique Key (Primary Key) Invoice Number (Foreign Key) Product ID (Foreign Key) & Quantity
The form I have created to populate the fields have the following fields linked from the Invoice input form:
Invoice Number (Primary Key) Invoice Date,
There is a sub form (Invoice Input sub form) that has a Quantity field linked from the Invoice details table.
These are all fine, but I also need to populate the other fields in the invoice tables - namely the Customer ID, Product ID and Employee ID fields. My idea is to create a combo box to bring up the name of each, but enter the ID into the appropriate fields. (Using IDs in the combo box makes it hard to know what product or customer you are selecting) The information in the combo boxes are sourced from the Customer, Employee and Product tables and works fine.
My problem is that when I select those combo box fields in the form (Say to create the invoice) it doesn't update the Invoice tables with the details in the combo boxes. Why? How do I make the database do this?
No. my use of the term 'bound' in this case refers to which value of the combobox's record (when that record is multi column) becomes the value of the combobox...it defaults to binding the first, most left column - but you can bind any column.
So 1 value of your multicolumn combobox is the value of the combobox itself - the other columns' values can be used/called per my first post.
This has nothing to do with whether or not the combobox itself is 'bound' to a record source of the form - - that is a totally different issue.
if there is anyone that can give me an idea of what I am doing wrong here I have been playing with this for a couple of days with no luck. I am confused if the problem is because I want the update to happen when the form is changed or closed, I am assuming this is possible or if the SQL is just incorrect.
For anyone reading in the future, the easy thing for me to do was to not use an unbound combo box. I am sure there is a way to do this, but if you are new to access like me use a bound combo box and it updated exactly as it should have. No SQL or VBA required.