error when entering quantity value into data entry form for customer orders

[alexandriafee]

Hi! I'm very new to access and was wondering if I could get some help with quite a simple question. I'm not really sure how to explain this, but I have a data entry form for a query that generates a list of customer orders. I've attached the file, hopefully you can see the issue more clearly when viewing the file. When attempting to enter data into this form, I am unable to enter the QTY value as a text box for some reason. I tried using a combo box, but this is not ideal for me and I would like to have a text box so that any numeric value would be possible.

Furthermore, I am not able to make all my data from my form successfully enter into the query. I am not sure of the cause of this, and I can hope somebody can help me out.

The error can be seen in the form New Order / Order List (whatever it is currently named.)

P.S This is an assignment, not an actual database. As you can probably tell I am very new to access.

[June7]

First, advise not to use spaces in naming convention nor reserved words as names - Date is a reserved word. Also, would be less confusing if did not use exact same field name in multiple tables.

Then, RecordSource for New Products form should not include Suppliers table. Similarly for Orders List form - don't include Suppliers and Customers tables. In some situations this may be helpful but in yours it just confuses things. Just use tables and everything will be smoother. Use queries if you want calculated fields or dynamic filter. But for starters just use tables.

Now fix comboboxes. They are bound to wrong fields.

Customer combobox properties:
ControlSource: CustomerID (from Orders table)
RowSource: SELECT CustomerID, LastName & "," & FirstName AS FullName FROM Customers ORDER BY LastName, FirstName;
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1

Product combobox properties:
ControlSource: ProductID (from Orders table)
RowSource: SELECT ProductID, ProductName FROM Products ORDER BY ProductName;
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1

Supplier combobox properties:
ControlSource: SupplierID (from Products table)
RowSource: SELECT SupplierID, [Company Name] FROM Suppliers ORDER BY [Company Name];
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1

[alexandriafee]

Hi, thank you for the reply!

The advice about the naming of tables is duly noted. I'm wasn't really sure when I was supposed to differentiate between different field names, but I will make an effort to make them unique in the future.

With the Combo boxes, particularly the first one- if I remove the Customer table won't I not be able to draw the data of the customer names from it? I tried to change the record source but I'm pretty sure I didn't do it correctly, or I'm doing it wrong, as I get an error saying #Name?. I think I understand my mistake but I'm not sure how to rectify it without ruining the current relationships I have.

Let me know if I'm misunderstanding something, but I can't seem to get it to function the way I want it to.

Furthermore, my combo boxes don't seem to update the tables either. It seems I can't select an option from the drop-down menu, but this was already a problem in my previous version.

[June7]

What you had did not correctly utilize the relationships you established. Follow my instructions and the forms work just fine. Oh rats, need to fix the TabOrder property of the data entry controls and set the Total textbox TabStop No.

Also, I did not notice there is a space in Company Name field. Edited my previous post to consider that. Again, spaces are a nuisance. As are punctuation and special characters, which fortunately you did not use.

Ideally, should not have an Inventory table. Product inventory balance should be calculated from raw transaction records when needed, not saved to table. Review http://allenbrowne.com/AppInventory.html. Even if you do save balance, no need to have a separate table. Besides, CANNOT LINK ON TWO AUTONUMBER FIELDS, in spite of fact the Relationships builder allowed this, it won't work.

"Merchana" is in Suppliers table twice.

[alexandriafee]

ipt assign.zip
Thank you! I think I've managed to correct the majority, but my final question is regarding the QTY entry in the Orders List form. I'm currently using a combo box, which is not ideal, however I cannot seem to make it a text box as it will not let me enter data in.

Following this, what would be the best way to calculate a total? I'm assuming a query, but I don't know how to go about making the calculation within a form and not just a query table.

I attached another file for reference to show the edits I've made.

[June7]

Select the QTY combobox, right click > Change To textbox.

Need to change TabOrder property of controls so the order follows the way they are arranged on form. TabOrder numbering starts with 0.

You already have calculation for Total in textbox. Set its TabStop property to No.

[alexandriafee]

My QTY entry is working now, thanks. I think my initial calculation for Total might be done incorrectly. It works in the query, but not the actual form.

Currently, I have =[QTY]*[Price] (from the query Orders List) but I keep getting the error #Name?

I think once I changed the record source from OrdersList to Orders the calculated field no longer works.

Tried to fix this by doing =[Orders!][QTY]*[Products]![Price] but that did not seem to work either ..

Sorry for all the questions, hope I'm not troubling you too much.

[June7]

Okay, my bad.

Change combobox RowSource to include price field.

Product combobox properties:
ControlSource: ProductID (from Orders table)
RowSource: SELECT ProductID, ProductName, Price FROM Products ORDER BY ProductName;
ColumnCount: 3
ColumnWidths: 0";1";1"
BoundColumn: 1

Now textbox can reference combobox column by its index to pull price: =[QTY]*[ProductName].[Column](2)

If this were a real db and not just a class exercise, something to consider would be how to deal with changing prices. Product prices tend to change over time but don't want the calculated cost to be altered for old records. Two ways to go:
1. when price changes create a new record and set old record as 'inactive' so it can be excluded from products combobox list for new orders
2. use code to save the price into order record

[alexandriafee]

You are a life saver. Thank you so much. Had no idea you could do the whole [Column](2) thing, literally just blew my mind. Major kudos thanks for all your help.