Can't Get Line Total to Work in Subform

[henryihunter1954]

Hello. I really need help with this problem.
I have 4 tables:
1. OrderProduct Details:
OrderProduct Details ID
Order ID
Product ID
Quantity

2. Product Info:
Product ID
Name
Description

3. Orders:
Orders ID

4. Product Cost:
Product Cost ID
Product ID
Price
Level (This states the price level - Low, Mid, High)

In my Subform, (Datasheet view), I have OrderProduct Details ID, Order ID, Product ID, Quantity, Name, Description, and Price....
Product ID - Dropdown so I can choose the Product
I want the Price to be Dropdown so I can see the 3 prices related to that Product.
I multiply the Quantity and Price so it will calculate the Line Total.

It is not working correctly. The entire line does not display at all when I try to run a test, especially when I add the Product Cost table to the query.

Please help me with this. I do not understand why it does not work.

 

[June7]

Don't include Product Cost table in form record source. Or don't use INNER JOIN.

Sounds like you need a cascading (dependent) combobox - price combobox shows only records associated with selected product.

 

[henryihunter1954]

June7, thank you very much for your suggestion.
You are correct about not using the Product Cost Table in the query.
Seems to work fine without it.
I did not have a way to get the 3 prices for each product.

Using the cascading (depending on the Product combo box), works great when I had the 2 combo boxes on the form and not within a query.
(I think you were the one that gave me the suggestion as to how I could setup the structure within the tables. Thanks again for that).

I am going to try your suggestion and not do an inner join within the query.

Again, thank you so much. I am going to give it a try. Will come back and let you know how it goes.

 

[henryihunter1954]

June7, it worked.
But there are still some issues.

I did the query without the Product Cost Table included.
Created a Combo Box that was based on the Product Cost Table.
Made the Product ID Combo Box requery the Product Cost after Update.
Enter my Quantity amount. Calculation goes into Line Total text box.

Here is the problem.
After the Line Total text box reveals the results, I tab for the next row or entering the next line entry. The result in the Line Total text box disappears.
The Line Total text box has to be Current (Mouse has to be in the box in order to show the calculation. If not in the box, the result disappears).

Next Problem.
The empty row in front of the line entry total shows the Amount of the Price that has been chosen. That is really weird.:banghead::banghead:

Do you or anyone have any suggestions?
I really thank you or any one for assistance with this matter.

 

[June7]

Can you provide db for analysis? Follow instructions at bottom of my post.

Be aware cascading combobox using lookup alias will not work nice on continuous or datasheet form.

 

[henryihunter1954]

June7:
Thanking you again for your great assistance.
Hope that I followed your instructions correctly.

 

[June7]

Now, which form has the issue?

Why are there records in OrderDetails without OrderID? LineTotal should not be saved into table, this value should be calculated when needed. What you should save is either the Product Price ID or Price.

Advise not to use spaces in naming convention.

What is a 'member' and why is this table related to both Customers and Orders? I think should be removed from Orders.

Each TypeOfEvent can have only one product? Why is the ProductID field not filled in? Perhaps you need another table to associate event types with products.

 

[henryihunter1954]

The issue that I have is in Form Edit or View Orders.
I know about the Line Total being in the Table. I only placed in there because once the calculation was done, and I tapped past the calculation onto the next line, the Line Total would disappear. (that was crazy) Never seen that ever happen before. So, was trying to find a method to keep it showing...but it did not work.

On the Main Menu, click button Edit or View Order
On this form, you will see that the Price and Line Total are empty.
Selecting the Price Drop down, the correct Price Options appear - for the correct Product...Good so far.
After selecting one of the Price Options and going to the Quantity, entering a number, tab to Line Total, the calculation is performed. From here, you will see where all goes wrong.

Not making excuses for storing the Line Total in the table, but had never seen what you will see happen in a database before.

 

[henryihunter1954]

I did try placing the Product Price ID or the Price in the query but the query, when ran, would just result in the heading showing up.

 

[henryihunter1954]

The Member is the person making the Order.
I should rename Customer to Recipient. I think I do understand your question about that issue.

 

[June7]

Normally in a relational database, info like customer name would not be saved into Orders table, CustomerID would be saved. Names make very poor unique identifiers. Still don't understand why you have Customer and Member tables. Why not 1 table of People?

Need a field in Tbl_Order Product Details for Price.
Set Price combobox ControlSource to that Price field.
This way, if product prices change in the future, the order data will not change.

Advise not to have a form RecordSource that is a query of a query. Change the subform RecordSource to Tbl_Order Product Details.

Format Product combobox with:
Name: cbxProduct
RowSource: SELECT [Product ID], [Product Name], [Product Description] FROM [Tbl_Product Info] ORDER BY [Product Name];
ColumnCount: 3
ColumnWidths: 0";1";0"

Set ProductName textbox ControlSource: = cbxProduct.Column(1)

Set ProductDescription textbox ControlSource: = cbxProduct.Column(2)

Suggest naming Price combobox to cbxPrice. Add WHERE clause to Price combobox RowSource SQL: WHERE [Product ID]=[cbxProduct]
Need VBA procedure to requery the combobox so the WHERE criteria will be applied. Use its GotFocus event.

Private Sub cbxPrice_GotFocus()
Me.cbxPrice.Requery
End Sub

OrderID in the subform should not be a combobox. This field should not even be viewed by users. This field value is set by the Master/Child links of the subform container control. Users also do not need to see OrderProductID.

 

[henryihunter1954]

June7:

Thank you for your suggestions.
I am going to give it a try and from what I've seen in the past from the suggestions that I've gotten, things work out. I am hoping that this works also.

Thanks also for being understanding.
Will get back with you to let you know my results.

 

[henryihunter1954]

Followed all instructions.
Thank you June7.

Only issue now:
In the datasheet on the form, the results show each level, low, mid, high price as if I selected each one per line. I've only tried to select 1 level but all 3 are showing up.

Do you have suggestions on how to correct this problem?
Thanking you again for all your help.

 

[June7]

Not sure I understand. The combobox should list all 3 as choices for user. But only 1 price should be saved to record.

Maybe post revised db?

 

[henryihunter1954]

Each Product shows the 3 options:
Low, Mid, and High

Example:
Roses - Dozen Roses - $50(Low) - 1 Quantity - Line Total $50
Roses - Dozen Roses - $60(Mid) - 1 Quantity - Line Total $60
Roses - Dozen Roses - $70(High) - 1 Quantity - Line Total $70

and this did the same thing for the 2 Product also.

Performed as if I did select all 3 but did not....

Don't understand why it did all of the options of the Price
and for each Product Item.

6 lines are showing. Should only be 2 line items.

 

[June7]

Did you create the VBA code as instructed? All the edits I described work for me.

 

[henryihunter1954]

June7:
Are you speaking of Requery after Update of Product ID to the Price Combo Box
and making the Product Id in Tbl_Product Price being equal in the Subform?
Yes, I did that also.
I have to be missing something.

 

[henryihunter1954]

June7:

I think I've stumbled onto part of my problem.
I've taken out the Tbl_Product Price from the Inner Join now.
You suggested that. I've now taken it out and gotten rid of all of the 3 triple entries.

 

[henryihunter1954]

June7:

With your help, the problem has been resolved. The Join was causing all of the problems after the re-structure of the data.
I really want to thank you for all of your patience and excellent help.

One last thing. Please tell me how to Post a Like or what ever it's called.
I've only Posted 1 like. It was so long ago. I've forgotten how to do it.
I want to give you the credit because it is due to you.

As to all the others that have do so.
Thanks again.

 

[henryihunter1954]

It's called a "Thank"