Hello, I'm trying to develop a simple form for a food ordering database at a cafe (as a learning project). I am fairly new to this so if I am posting in the wrong section, my apologies.
In a form I made to add/modify an order, I have the following fields:
EmployeeID, Customer First Name, Customer Last Name, BNumber (optional student ID number), OrderDate (defaulted to today's date), and the Order Details, which is a subform that contains ProductID, Quantity, ProductPrice, TotalPrice, and Complete (as a checkbox).
Now, when I try to select an employee to assign to the order, it gives me this error:
"Cannot add record(s); join key of table Orders not in record set"
The employeeID field is a combobox that contains the ID (hidden with the column widths), and Last and First names.
Also, entering anything into the Customer First, Last, or BNumber results in the following error: "Field cannot be updated."
I have referred to the a thread that had a well-written solution for a similar problem but unfortunately I couldn't apply it here. I know there are many variables that may be causing this so please let me know if you would like the database to take a look (there is only sample data, if any at all). I don't know how I would post it here though.
- Edited by J. Tacktheritrix Thursday, May 02, 2013 5:33 PM
Okay, here you go.
The main form's record source should not have the table that the subform uses and the subform's record source should not have the same table that is in the main form's record source. The subform should only have the table that the subform is storing data into.
Both forms should not have any tables in their record sources that are just lookup tables for a control The value can be displayed using a combo box or list box and their row source would have those lookup tables, but not the form itself.
Also, you need to make sure that the PK field of the applicable table is part of the applicable form's record source.
Bob Larson, Access MVP 2008, 2009, 2011
As Bob Mentioned,
only the Orders Table should be in the RecordSource of the Orders Form. The ComboBox ControlSource can be a lookup field created on the Order Table to the Emplyee Table and the RowSource of the Combo box on the Form would be based on the Tables settings. To setup the Table if you haven't already do the following.
In the Order Table, add a new field (EmployeeLookup) set the type as Lookup. The wizard will guide you to the Employees Table where you would select the ID, First and Last names and other fields you feel are appropriate. If you already have this setup then what you need to make sure of is when adding the fields, you are only adding from the Orders Table list and not the Employee Table.
Ah, thank you very much to the both of you! I had a second table (customers) in the RecordSource and it seemed to have been causing all the issues.
If you or anyone else doesn't mind helping me further, here is how my form is set up now:
What I would like to make possible is, to add a customer as opposed to select one from the dropdown list. Would I have to link it to a new form that creates a customer?
Also, I tried adding in a code I found from another thread that would take the ProductPrice from the Product table and update it automatically as I selected the Product in the order detail subform. Unfortunately it didn't work. The directions I followed were:
"The subform would also contain text box controls bound to the UnitPrice and Quantity columns. To insert the current unit price of the selected product put the following in the AfterUpdate event procedure of the ProductID combo box:
Dim strCriteria As String
strCriteria = "ProductID = " & Me.ProductID
If Not IsNull(Me.ProductID) Then
Me.UnitPrice = DLookup("UnitPrice","Products",strCriteria)
Me.UnitPrice = 0
Of course I made the necessary changes to adapt it to my tables but it gave me an error saying that the object "Dim strCriteria As String
strCriteria = "ProductID = " & Me.ProductID" could not be found.
- Edited by J. Tacktheritrix Thursday, May 02, 2013 8:14 PM
So it looks like before you can get really good results fro any Forms or programming you need to work on normalizing your data. This Table you mention should be Four Tables from the Looks of it.
Then you can have one or more Tables to create Many to Many relationships to relate all the data.
To answer your other question, There should be a separate Form for Each Tables update. Having said that, you can have a Mainform and several subForms to enter data in one screen.
I can't post images cause my account is not verified but, I have the following tables
Products (1:M) Order Details
Orders (1:M) Order Details
Customers (1:M) Orders
Employees (1:M) Orders
Payment (1:M) Orders
Hope that makes sense. Order Details is the subform and Orders is the main form. My initial problem was that the Customer fields were linked to the Customer table as opposed to the Customer ID table under Orders. I got rid of the three fields from Customers and replaced it with just CustomerID in the Orders table, and now I can input data just fine. Only problem is that now I have to select customers from a drop down list when I wanted the ability to add them as I added a new order record.
Would I have to create another form, "Add Customer," for example, and then link to it in the main form?
- Edited by J. Tacktheritrix Thursday, May 02, 2013 8:36 PM
Thank you. Sorry to keep on bothering but on my second question again, I would like the "ProductPrice" in the subform "Order Details" to pull the product price from the Products table so that as soon as I pick a product, it will also display its price. Is this possible?
Actually, I just discovered the following code (adapted to my tables):
and it seems to work.
Thank you all again for the solutions!
- Edited by J. Tacktheritrix Friday, May 03, 2013 2:07 AM
Sorry to revive this thread again, but I"m running into another issue now.
On the same Orders form with the Order Details subform, I'm trying to add a another subform, "Payment" which holds the fields of PaymentMethod, CardholderName, CardNumber, AmountPaid, and Paid (checkbox). However, when I try to select a PaymentMethod from the dropdown list, it gives me this error:
"You tried to assign the Null value to a variable that is not a Variant type."
Can I get some aid on this issue?
- Proposed as answer by KCDW Sunday, May 05, 2013 10:50 PM