Get Price Type

[adam]

I’m having a database with two tables and one query.

What I’m trying to get is when a user enters a number in the product code column of the table “Order Details Query”; the columns Description, Category and unit price to get filled with data corresponding to the number or code in column “Product Code”.

The price that is filled in the column Unit Price would be the price type column that is from the combo box in the userform “Create New Invoice”.

Any help on this would be kindly appreciated.

[HansV]

Should the user be able to change the description, category and unit price after they have been filled in, or should they always be the values from the Products table?

[adam]

The description , category and unit price in each row would get changed as the user changes the product code from the particular combo box in each row. The user do not have to type the description, Category & unit price.

[HansV]

In that case, you don't need the Description, Category and Unit Price fields in the Order Details table. It would be superfluous to store the information both in the Products table and in the Order Details table. Instead, add these fields in the Order Details Query.

By the way, many of the fields you use should not be text fields but number fields, or in the case of the price fields, currency fields.

And you should define relationships between the tables.

You should link the subform to the main form by order id.

See the attached version.

Price Type.zip

[adam]

Thanks for the sample workbook, Suggestion & Guidance. But the combo box, "price type" only works when the numbers either 1 or 2 is selected instead of the texts. Also the price types gets changed after clicking any column in the sub form by changing the appropriate value from the combo box.

Why is this? how could I prevent this and make the price type changed as I select either the text from the combo box.

[HansV]

Sorry, I forgot to update the price type combo box. Set its Column Count property to 2 and its Column Widths property to 0";1".
If you want the unit prices to be updated immmediately, add the following event procedure for the price type combo box:

Code: Select all

Private Sub Price_Type_AfterUpdate()
  RunCommand acCmdSaveRecord
End Sub

[adam]

Thanks for the help Hans. It works fine and I've added extra columns to the table "Orders" where I want them to be included in the Create New Invoice form. But I'm unable to include the Customers table in the relationships with orders and so on.

When I attempt to make a 1-to-many relationship between two tables, I am getting the error: "Relationship must be on the same number of fields with
the same data types".

What may be the reason for this. Is that because the the filed says "Text" and the remaining tables have number fields?

[HansV]

Yes, a relationship can be between two text fields, or between two number fields, or between two date/time fields, etc., but not between a text field and a number field, or between a text field and a date/time field, etc.

[adam]

How could I over come that. Because as mentioned before my customer ID formats are in numbers and texts. Do I have to make the primary key as a unique number field and create a new text filed as a customer ID filed. But in this way the primary key wont be the customer ID. right?

[HansV]

You can keep Customer ID as a text field. If you want a customer field in the Orders table in order to create a relationship between customers and orders, you should make it a text field too.

[adam]

If I keep the Customer ID as a text filed in customers table and a customer field in the Orders table still the primary key in customers table would be a text filed and the primary key n orders table will be a number filed.

will it work then to create a relationship?

[HansV]

Yes - you should not create a relationship between the primary key in the Customers table and the primary key in the Orders table (that would become a one-to-one relationship), but a relationship between the primary key in the Customers table and the customer field in the Orders table. That way, you create a one-to-many relationship: one customer can have multiple orders.

[adam]

I've created a relationship between the two tables. I would be happy if you could let me know which join type would be suitable between these two tables

[HansV]

The Join Type is not really important for tables, but it determines the default join in queries based on the tables. You can keep the first option, or set it to the option ton include all records from Customers.

I would tick the check boxes "Enforce Referential Integrity" and "Cascade Update Related Fields" in the "Edit Relationships" dialog. The first ensures that the user won't be able to create orders without specifying a customer; the second ensures that if you change the Customer ID in the Customers table, it will also change in the Orders table.

x270.png

[adam]

Thanks for the help Hans. I've added one more column to the table "Orders" with the name "lngEmp ID" taken from the employees tables. Do I have to make the same setup as in post 24656 between the tables "Orders" & "tblEmployees"

[HansV]

Yes, the setup should be the same, but since lngEmpID is a number field in the Employees table, it should also be a number field (long integer) in the Orders table.

[adam]

Yeah Hans. I've done the same. Thanks for the help.

[adam]

Why doesn't the product codes appear in ascending order when I select the product code from the combo box in the order details subform?

[HansV]

Because the row source of the combo box sorts the products by description.

[JohnH]

The description , category and unit price in each row would get changed as the user changes the product code from the particular combo box in each row. The user do not have to type the description, Category & unit price.

Sorry coming in late on this, but I think it is a mistake to just lookup the Unit Price from the Products table, rather than copy it into the Order Details table.
There are two problems with this, one major and one more minor.
The major one is that whenever you update your prices, all your historical sales data becomes incorrect as you have no record of what you actually sold something for.
The minor point is that you lose the capacity to adjust the sale price of an item if you want to do a 'deal' with someone, or give a discount for some special reason.