Form won't let me add Subform due to Relationship between Source Tables

[RuffianRan]

The CustID should go on both. But that's supposed to be in the ComboBox, right? and it's not working

[RuffianRan]

That would be what makes them sync, right?

[RuffianRan]

What am I doing wrong? Why can't I get this dumb ComboBox to work? That is the key to the Subform, isn't it?

[orange]

See if this is closer - based on your feedback I'll explain.

[RuffianRan]

YES!! That's what's on the page. Thank you. I do want to understand.

[RuffianRan]

Well, it's in a different order. - Invoice, Invoice Date, Customer ID, Part ID, Quantity, Paid, Unit Price, Total - I'm supposed to change some captions

[orange]

A few things.
Many of the field names are also terms in Access.
(First, Last, Year, Type) and could get you into all sorts of syntax and other issues.
see(google) Allen Browne Badwords for a list of reserved words

I copied your frmTireOrders to frmTireOrders_Orange and worked with that.

All controls on forms should have meaningful names:
eg: cboCustID not combo93 ' will benefit you and others who maintain any of your code.

When you select the CustomerID in the combo at top of form, you want the Customer Info for that Customer.
So, once you make a selection, that represents the afterUpdate event of the combo. Based on your selection, you want to adjust the info to be presented from all Customers to that Customer with selected CustomerId.
To do this, you set a Filter and then activate that filter, then you Requery*** the form to show only that Customer's info.

Here's the code:

 Private Sub Combo93_AfterUpdate()
'set a filter to limit the Customer info to the one selected
    Me.Filter = "CustID = '" & Me.Combo93 & "'"
    Me.FilterOn = True
    
    'now get the total InVAmt for this Customer, and place it in your InvAmt textbox on the form.
    Me.InvAmt = Nz(DSum("InvAmt", "qryInvoiceTotal", "CustId='" & Me.Combo93 & "'"), 0)
    Me.Requery
End Sub

*** I am also calculating the InvAmt based on this afterUpdate event. That's using your
qryInvoiceTotal query. It's summing (DSUM) all invoices for this Customer, and the Nz is saying use the calculated amount, but if there are no invoices, use 0.

I created another query using Sales and Product qrySalesItem which gets info for this Sale and associated Products. Because the Sale info contains CustomerId, you can relate the mainform and subform based on CustomerID.

Sql for the qrySalesItem ---you can change the field order as needed ---

SELECT tblSales.Invoice
, tblSales.InvoiceDate
, tblSales.CustID
, tblSales.PartID
, tblSales.Quantity
, tblTires.UnitPrice
, tblTires.TireType
, tblSales.InvoicePaid
, [UnitPrice]*[quantity] AS SaleAmt
FROM tblTires INNER JOIN tblSales ON tblTires.PartID = tblSales.PartID;

The main form and sub form have linkfield CustID, so the subform only shows info for those Sales for the
CustomerID selected in the Main form (combo93).

How's that, if you have questions, post here.

[RuffianRan]

Thing is that it has to be like their instructions. I don't know why I didn't think of it before, I printed them off from a file, there's no reason why I can't attach the page here.

I'll continue studying what you sent.

[RuffianRan]

Great, I typed the code in wrong, or I should say didn't erase all of what I tried to type in, before I switched back to form view and now it won't let me do anything. "The expression Before Update you entered as the event property setting produced the following error: Member already exists in an object module in which this object module derives." I can't click on anything else.

I mean, every time I click on anything the message pops up

[RuffianRan]

I just deleted the form, I'll start over

[RuffianRan]

Flippin combobox still doesn't work! I'm following the instructions exactly. I can't add any code that's not supposed to be there. Should I just start the whole database over? It gave the 3 tables to work with and a couple queries.

[orange]

Just saw your posts. I have modified some names according to instructions.
I'm not following instruction re the combo -- I don''t see how they are using the combo selection to show required record??
I'm using the afterupdate of the combo.
I have the subform, but I'm not sure how to deal with the headings/columns in datasheet view. Every time I set the widths, if I go to design and back to form view, the widths are all screwed up.????

Too bad you hadn't posted this stuff a couple of days ago.

I'll attach what I have-- I adjusted your frmTireOrders and used my new frmInvoiceTotalSubform as the subform.
Keep you work separate ---

Good luck.

There are 2 files in the zip.
Previous accdb, and the latest revision @ 10:50 PM <---this goes with latest comments.

[RuffianRan]

Thanks! I've been juggling this with my Excel Midterm practice and Exam.

[vicsaccess]

sorry for the late post. but I've been reading it on my phone all night and could not post. RuffianRan, sometimes i have learned the hard way the answer is in the details of a database or instruction. reread your instruction "d". Make sure the form's record source property is set to tblcustomer! that's were you missed your primary key that links your subform. your form is set to a query with no primary key

[RuffianRan]

Just saw your post, vicsaccess, I did end up getting that part figured out. And I got my combo box to work. What I didn't get was the calculated field. I came back to download orange's file again to re-examine that area. I somehow deleted it. I get it to tell me the first amount in the subform, but not the total when more than one item was sold.