Queries & Calculated Fields - Headache! Help!

[blankbandit]

I need some help. My brain just can't grasp how to do this.

I have an Access DB. I sell vinyl records. I have an order form. When a customer orders a record, each vinyl record has a unique ID#. No two vinyl records are alike. I have four tables: Customers, Orders, Inventory, & Order Details. I have an "Order Form" form where I input all the info. It's an all-in-one form where you can view and data entry in one screen. The form has two subforms in it.
Here's the problem:
If a customer orders more than one item (i.e. a vinyl record), each Inventory_ID (representing one vinyl record) is associated with one Order_ID which is, in turn, related to one Customer_ID. If I have one Order_ID that has multiple Inventory_IDs (i.e. has ordered two or more vinyl records) that belongs to one customer, how can I calculate that total cost?
Currently, I have a calculated field within my all-in-one form that calculates only the price of the Inventory_ID that one currently sees on the subform. The Inventory_ID item has a set price, for instance $5.00. If I scroll through the ordered Inventory_IDs, the calculated field will only display the price of the product that you currently see. It's not really calculating anything, I guess. It's just bound to that field, but I need to sum up all the price fields for all the Inventory_IDs that are associated with one Order_ID & Customer_ID whether it's a query or calculated field...I just need it done so that it'll display on this form.
Any help would be much appreciated. I can show you whatever you need to see. Thanks in advance.
David

 

[Scottyk537]

Hello David,
Build a query, showing the table of your inventory items. Bring in OrderID and ItemCost. In the ItemCost field, select SUM in the totals. In the criteria area for OrderID, reference the OrderID feild on your main form like this:
[forms]![MainForm]![OrderID]
Now build a new form with this query as the recordsource. Add the SumofItemCost as a textbox and place this form as another subform on your mainform, with no linking between the two.
You should trigger a requery of this subform probably everytime another inventory item is added. On the after update event of the Inventory_IDs, add VB code.
[forms]![Mainform]![NewSubFrom].requery

This should work.......
Scott

 

[boblarson]

[forms]![Mainform]![NewSubFrom].requery

won't work
The correct subform syntax is:

Forms!YourMainFormNameHere.Form.YourSubFormContainerNameHere.Requery

"YourSubFormContainerNameHere" is the actual subform container on the main form, not necessarily the subform name (if you used a wizard to create the subform on the main form, then it will be the same). It doesn't have to be though. You can rename your subform container to a different name than the subform itself.

The syntax to refer to a control on the subform is:

Forms!YourMainFormName.YourSubFormContainerName.Form.YourControlName

and to refer to the same control from code within subform events:

Me.YourControlName

to requery the subform within subform events:

Me.Requery

 

[blankbandit]

Thanks to both of you. It worked; however, when I do enter a new record, the query goes blank and just displays the field name. I believe this is what you guys meant by doing a "requery". Now, at this point, where do I add that "Me.Requery" extension so that it requery's the fields each time thereby avoiding the blank field? I had to take one step at a time at this.
One more question:
I've attached a screen shot of my order form. The field that was in question that is bound to the the query that I just made was to calculate a subtotal. I also have a couple of other fields in a different table such as "shipping_cost", "paypal_fee", and "optional_insurance" that I now want to sum up for a grand total under the field name, "Total Cost". They are in a table called "Orders". I have it as a subform, the middle column, in my main form, "Customers". Please advise and thanks again.
David

 

[Scottyk537]

David,
I would add this code to the after update event of your sold check box. It's hard to say without being able to work with your form and understanding the flow. I would add it to the after update event of the last required step when adding a inventory item.

As far as the other total,
If you just want to view the grand total, you can do this on the form level.
Build a new text box where the control source is the addition of all the text boxes/combo boxes you wish to add.

 

[blankbandit]

Thanks for your continued assistance...

The "Sold" checkbox field is in a subform, Inventory. I like your idea of putting the requery in the "sold" check box to requery the query. The query is called CalculationsTotal and I put it into a subform, Calculations_Total_Form. So, this requery needs to reference a different subform than which the check box is in.
I'm a little confused though. Am I supposed to go to the after update area in the properties of the "sold" checkbox, and choose "event procedure"?....Now, exactly what code to I type into the Visual Basic window at this point? Everything I've tried so far isn't working.

Thanks! David

 

[Scottyk537]

I'm sorry, if you want to trigger the requery from a checkbox, you should use the OnClick event.
I'm not sure what the other guy in this thread is referring to, but I've used this to requery forms in everyone I've created with no problems:
Go to the properties of your checkbox. Click the "..." next to the OnClick event and go to code builder.
Is your calculations subform in another subform? If so,

Private Sub Check0_Click()
forms!MainForm!SubForm!CalculationSubForm.requery
End Sub

Otherwise,

Private Sub Check0_Click()
forms!MainForm!CalculationSubForm.requery
End Sub

"MainForm" and "Subform" should be the names of your forms........

 

[blankbandit]

Okay, bear with me. This is what I got and it's just not working...

Private Sub Sold__Click()
forms!Order_Form!Child18!Child64.requery
End Sub

I guess Access named my subforms as child18 & child64 when I created them. "Order_Form" is my main form. "Child18" is my Orders subform."Child64" is the Calculations_Total_Form subform. Then the actual control in the "Child64" ubforms is "sumofprice".

I don't know what I'm doing wrong. I check the check box and nothing happens.

Thanks,
David

 

[boblarson]

You're close, but not quite there with the subform syntax. Try this instead:

Forms!Order_Form.Form.Child18.Form.Child64.requery

 

[Scottyk537]

I have never had this problem with any of my forms..... then again, I never let access create my forms for me, I create them from scratch and give them names that I can easily identify.....

 

[blankbandit]

Thanks for all of the assistance. It's not working though. I don't know what the problem is. It's just not requerying. That's fine. The main thing that I wanted is working. Thanks again!
David