Run Subform Queries from ComboBox Result

[danielle.EWAIG]

Hi,

I am currently building a main form with a combo box control, two subforms and a hidden text box (optional). There are many different references to be made between tables, queries and forms to get to the result I am after, and I have tried many different codes from other people’s queries that seemed close to mine; however, had no luck getting my subforms to work as I’d like.

The names of my controls/forms/queries are as follows…

Main Form: frm_UReport
Subform 1: sbfrm_Query1 (refers to Query1, which refers to and calculates on tbl_C)
Subform 2: sbfrm_Query2 (refers to Query2, which refers to and calculates on tbl_S)
ComboBox: cmbo_ProductList (refers to tbl_ProductList)
TextBox: txt_ProductCode (refers to cmbo_ProductList, column 0)
I want the two subforms to run their respective queries after a Product Code has been chosen from the ComboBox. The relationship between the tables is one-to-many, from tbl_ProductList to each of tbl_S and tbl_C; with ProductCode as the primary key in tbl_ProductList.

Currently, I can choose a Product Code from the combo box, and it populates the text box successfully, but nothing happens in the subforms.

The codes I have worked with so far are as follows…

sbfrm_Query1 (Record Source):
SELECT Query1.ProductCode, Query1.PurchaseDate, Query1.ExpiryDate…
FROM Query1 WHERE (((Query1.ProductCode)=[Forms]![frm_UReport]![txt_ProductCode]));

sbfrm_Query2 (Record Source):
SELECT [Query2].[ProductCode], [Query2].[ProductDescription], [Query2].[PurchaseDate], [Query2].[AverageCost]…
FROM Query2;

txt_ProductCode (Control Source):
=[Combo9].[Column](0)

txt_ProductCode (After Update):
Private Sub txt_ProductCode_AfterUpdate()
Forms![sbfrm_Query1].Requery
End Sub

I have started working on integrating sbfrm_Query1 into the main form first, which is why the code looks different between the two subforms. Once I have it working, I will translate the same format to sbform_Query2.

Due to all the different levels of references that need to be made, I am having trouble identifying which (or if all) of the codes are incorrect.


Any assistance is greatly appreciated!

 

[jdraw]

It would be helpful to users if you could give the 30000 foot level overview of WHAT you are trying to do in plain English.

 

[danielle.EWAIG]

My apologies for the confusion in my original post, jdraw.


A simplified description of what I’m building:

A database that consists of three tables; Claims, Sales and ProductList. ProductList has a primary key of ‘ProductCode’, and the other two tables are raw records of the sales and claims we’ve received over many years. Every sales/claims entry has a product code against it, and the relationship between the tables has been built as a one-to-many from the ProductList table to the other two tables, using the ProductCode field in each.

I need to be able to pull Claims data and Sales data together, per Product Code, to view how the specified Product is performing. I had a one-on-one training session and was advised that the best format for this would be to create a main form, with a ComboBox control to select a ProductCode with, and then have two subforms (one for Claims and one for Sales) populate with the relevant data from each table. Some of this data also needs to be calculated (sum, average, etc) before it populates in the subform, so a query was created for each subform and linked as the RecordSource.

There is a lot of ‘reporting’ that we want to do on the data, and ultimately Excel is the best place for all the work we want to do on the data (but not the best place to keep all the years' worth of data). So the intention (as per the training session I had) was that I could then export the data from the two subforms into Excel, to work/report on the data further than Access will allow.

Let me know if I need to provide more detail on what I'm attempting to build and/or its use. Thanks!

Uncle Gizmo, I attempted to apply the code format you provided below, however the VBA builder didn't like the '= SELECT' part of the code (along with other parts after that)

I have this:
Private Sub Combo9_AfterUpdate()

Me.ClaimsQuery_subform.Form.RecordSource
SELECT tbl_Claims.ProductCode, tbl_Claims.PurchaseDate, tbl_Claims.Expiry, Sum(tbl_Claims.Qty) AS SumOfQty, Sum(tbl_Claims.TotalCost) AS SumOfTotalCost, Sum([TotalCost])/Sum([Qty]) AS LossSeverity
FROM tbl_Claims
HAVING (((tbl_Claims.ProductCode) = [Forms]![frm_UWReport]![Combo9].[AfterUpdate]))
ORDER BY tbl_Claims.ProductCode, Year([PurchaseDate]), Month([PurchaseDate]);

End Sub

Is there too much code in there? Is there something in particular that is throwing it off?

Thank you for your help so far.

 

[jdraw]

Thanks for the info. What exactly is a Claim?

 

[vbaInet]

I think it made sense from the initial post (sorry I've not read your latest post, I didn't need to).

There are two ways you can do it:

1. Have a full reference to the combo box (instead of the textbox) in the Record Source of the subform. Then requery the subform in the After Update event of the Combo Box.
2. Enter a reference (just the name of the combo box) in the Link Master Fields property of the subform and enter ProductCode into the Link Child Fields property. It must be typed in, don't use the button (...).

However, I wonder what the differences are between the tbl_C and tbl_S?

 

[danielle.EWAIG]

Thanks vbaInet.

I tried your first suggestion, however the subform didn't populate at all.
Is it possible I need to requery the query attached to the subform first, then requery the subform?

There's definately something missing between the query, subform, and combo-box. Any ideas based on the below?


Query1 (ProductCode Criteria):
[Forms]![frm_UReport]![Combo9].[AfterUpdate]

Sbfrm_Query1 (Record Source):
SELECT DISTINCTROW Query1.ProductCode, Query1.PurchaseDate, Query1.Expiry, Query1.SumOfQty, Query1.SumOfTotalCost, Query1.LossSeverity, * FROM Query1 WHERE (((Query1.ProductCode)=[Forms]![frm_UReport]![Combo9]));

Combo9 (After Update):
Private Sub Combo9_AfterUpdate()

Me.sbfrm_Query1.Form.Requery


End Sub


Uncle Gizmo, I've tried adding quotation marks into the code I showed above, however I keep getting compile errors of either "Expected: Case" or "Expected: End of Statement". Are you able to use the example code from my previous message and type out exactly where the quotation marks should go, please?


Thanks for all assistance so far.

 

[danielle.EWAIG]

I was attempting to work on this again today, and I think I corrupted the original UReport form.

After rebuilding it and redoing the references/criteria in the other subforms/queries, the new UReport form now populates the subforms as required!

I must have cleaned-up some incorrect code in the rebuild.

From what I can tell, some of the criteria fields were showing as [Forms]![frm_UReport]![Combo9] and some as [Forms]![frm_UReport]![Combo9].[AfterUpdate]. I made them all be in the same format, without the 'After Update' part, and voila, it worked!

Thank you for your assistance with this, Uncle Gizmo and vbaInet. Greatly appreciated.