Multiple Subforms linked to multiple combo box (1 Viewer)

Hello Everyone,

I am currently working on a form with multiple sub forms. Each sub form represent past forecasts with detail level, such as product type (ie box/bulk,etc).

I was wondering if there is any way to link all of the sub forms to the combo boxes

When a specific type of product is selected, I would like to be able to drill down to the customer level. Currently I am able to do so, only with the current month forecast, but no with the sales. Same happens if I tried to go to the Customer level (under Sales), I have to manually filter the info, as I can't seem to find a way to link the combo boxes to two or more sub-forms...

Below is my code related to my dilema:

Private Sub CmbAbbv_AfterUpdate()
Call SearchCriteria
Me.CboDescription.Requery
Me.cboplant.Requery
Me.CboShiptoName.Requery
End Sub

Private Sub CboDescription_AfterUpdate()
Call SearchCriteria
Me.cmbAbbv.Requery
Me.cboplant.Requery
Me.CboShiptoName.Requery
End Sub

Private Sub cboplant_AfterUpdate()
Call SearchCriteria
Me.cmbAbbv.Requery
Me.CboDescription.Requery
Me.CboShiptoName.Requery
End Sub

Private Sub CboShiptoName_AfterUpdate()
Call SearchCriteria
Me.cmbAbbv.Requery
Me.CboDescription.Requery
Me.cboplant.Requery
End Sub

Function SearchCriteria()
Dim Abbv, strDescription, strPlant, strShiptoName As String
Dim task, strCriteria As String

If IsNull(Me.cmbAbbv) Then
Abbv = "[Abbv] like '*'"
Else
Abbv = "[Abbv]='" & Me.cmbAbbv & "'"
End If

If IsNull(Me.CboDescription) Then
strDescription = "[Description] like '*'"
Else

strDescription = "[Description] = '" & Me.CboDescription & "'"
End If

If IsNull(Me.cboplant) Then
strPlant = "[Plant] like '*'"
Else
strPlant = "[Plant]= '" & Me.cboplant & "'"
End If

If IsNull(Me.CboShiptoName) Then
strShiptoName = "[SHIP_TO NAME] like '*'"
Else
strShiptoName = "[SHIP_TO NAME] = '" & Me.CboShiptoName & "'"
End If
strCriteria = Abbv & "And" & strDescription & "And" & strPlant & "And" & strShiptoName
task = "Select * from QryMarchFcst where " & strCriteria
Me.QryMarchFcst.Form.RecordSource = task
Me.QryMarchFcst.Form.Requery

End Function
Private Sub CboMaterial_AfterUpdate()
Me.cmbAbbv.Requery
Me.CboDescription.Requery
Me.cboplant.Requery
End Sub

Private Function ClearCtrl(Ctrl As Control)
Ctrl = Null
Call SearchCriteria
End Function


Private Sub CmdClearAll_Click()
Dim task As String
Me.cmbAbbv = Null
Me.CboDescription = Null
Me.cboplant = Null
Me.CboShiptoName = Null
task = "select * QryMarchFcst,QryDailySales where [Material] is null"

End Sub

Thanks a lot for your help!!!

I'm not really clear on the problem. You can set the record source of any number of subforms in your function. Not sure how they differ, so can't be more specific.

Hello,

Let me explain again:

I have a form with multiple combo boxes. Each boxes filter the information from the previous box. So I can go to a granular level if needed to.

My form also have a sub form. The sub form have many tabs. Each tab represent a monthly forecast. One tab, also represent the monthly sales

I would like that the combo boxes to filter all the tabs at the same time. Currently, I can filter only one tab (The present month)

All the tabs have the same type of field. Material ID, Description, Qty, etc.

I hope this clarifies things up. I look forward your input

Thanks in advance,

Anthony

I'm only guessing, but based on what you have would this work?

task = "Select * from QryFebruaryFcst where " & strCriteria
Me.QryFebruaryFcst.Form.RecordSource = task

Hello,

Thanks for your input.

I have 5 tabs on my suboform (4 months of the current year including April + Current month Sales). So this is what I wrote based on your previous response:

task = "Select * from QryMarchFcst, and QryDailysales subform, and QryFebFcst subform, and QryJantesting subform, and QryAprilFcst subform where " & strCriteria
Me.QryMarchFcst.Form.RecordSource, QryDailysales Subform.RecordSource, QryFebFcst subform.Form.RecordSource, QryJantesting subform.Form.RecordSource, QryAprilFcst subform.Form.RecordSource = task
Me.QryMarchFcst.Form.Requery


I have two questions:

1) Access is now giving me a syntax error, so I am missing something here:
Any ideas what that might be and how to fix it?
2) for the Me.QryMarchFcst.Form.Requery Should I type all the subforms on a separate line or should I do comma and , until all the subforms are included on the requery command

Thanks again for your help,

Anthony

No, that structure incorrect. Do they all have the same source? I was assuming each needed it's own source, so more like:

task = "Select ... As Appropriate"
Me.FirstSubform.Form.RecordSource = task

task = "Select ... As Appropriate"
Me.SecondSubform.Form.RecordSource = task

task = "Select ... As Appropriate"
Me.ThirdSubform.Form.RecordSource = task

Hi,

It worked out,

Thank you so much for your help!

Excellent, happy to help!

Hello again,

After successfully worked for almost 9 months, My db is giving me error messages again.

for Every Month that I have added, I add the same formula and worked fine. However, when I add the Month of November, I am getting the following error:

Run Time error '2580'
The record Source 'Select' From QryNovemberFcst where [Abbv]= '778T BK33753' And *[Plant] like *And [Ship to Name] like '*' specified on this form or report does not exist.

I uploaded the info for this Table (November) as I have done it for previous months, and nothing has changed, so not sure what is causing the error here or how to fix it...

Below is the full code:

Function SearchCriteria()
Dim Abbv, strDescription, strPlant, strShiptoName As String
Dim task, strCriteria As String

If IsNull(Me.cmbAbbv) Then
Abbv = "[Abbv] like '*'"
Else
Abbv = "[Abbv]='" & Me.cmbAbbv & "'"
End If

If IsNull(Me.CboDescription) Then
strDescription = "[Description] like '*'"
Else

strDescription = "[Description] = '" & Me.CboDescription & "'"
End If

If IsNull(Me.cboplant) Then
strPlant = "[Plant] like '*'"
Else
strPlant = "[Plant]= '" & Me.cboplant & "'"
End If

If IsNull(Me.CboShiptoName) Then
strShiptoName = "[SHIP_TO NAME] like '*'"
Else
strShiptoName = "[SHIP_TO NAME] = '" & Me.CboShiptoName & "'"
End If
strCriteria = Abbv & "And" & strDescription & "And" & strPlant & "And" & strShiptoName
task = "Select * from QryMarchFcst where " & strCriteria
Me.QryMarchFcst.Form.RecordSource = task
Me.QryMarchFcst.Form.Requery

task = "Select * from QryDailysales where " & strCriteria
Me.QryDailysales.Form.RecordSource = task
Me.QryDailysales.Form.Requery

task = "Select * from QryFebFcst where " & strCriteria
Me.QryFebFcst.Form.RecordSource = task
Me.QryFebFcst.Form.Requery

task = "Select * from QryJantesting where " & strCriteria
Me.QryJantesting.Form.RecordSource = task
Me.QryJantesting.Form.Requery

task = "Select * from QryAprilFcst where " & strCriteria
Me.QryAprilFcst.Form.RecordSource = task
Me.QryAprilFcst.Form.Requery

task = "Select * from QryMayFcst where " & strCriteria
Me.QryMayFcst.Form.RecordSource = task
Me.QryMayFcst.Form.Requery

task = "Select * from QryJuneFcst where " & strCriteria
Me.QryJuneFcst.Form.RecordSource = task
Me.QryJuneFcst.Form.Requery

task = "Select * from QryJulyFcst where " & strCriteria
Me.QryJulyFcst.Form.RecordSource = task
Me.QryJulyFcst.Form.Requery


task = "Select * from QryAugustFcst where " & strCriteria
Me.QryAugustFcst.Form.RecordSource = task
Me.QryAugustFcst.Form.Requery

task = "Select * from QrySeptemberFcst where " & strCriteria
Me.QrySeptemberFcst.Form.RecordSource = task
Me.QrySeptemberFcst.Form.Requery

task = "Select * from QryOctoberFcst where " & strCriteria
Me.QryOctoberFcst.Form.RecordSource = task
Me.QryOctoberFcst.Form.Requery

task = "Select * from QryNovemberFcst where " & strCriteria
Me.QryNovemberFcst.Form.RecordSource = task
Me.QryNovemberFcst.Form.Requery


Any ideas or suggestions are welcome...

May i ask why are you making query for each month? Why not make one query and just filter that wuery fir a specific month and year.

Something is different about November. Can you attach the db here?

Hello,

I figure out what was happening: When I created the Qry for November, I abbreviated to QryNovFcst instead of Qry NovemberFcst, so when the code was running it was looking for QryNovember instead of the QryNovFcst.

All good now.

I do have a question that perhaps some of you might be able to assist.

For 2017 I want to be able to summarize the Year to Date Sales per Grade and compare it to the Forecast for Each Month.

It seems (at least with my little knowledge of access) that the only way to do it is creating two subforms One for the Foreast (the summary of each Month) and another one for the Year To Date Sales..

Does anyone know a better way to this. I would also like to be able to call this query when I select the Drop Down, so I can see/compare the forecast vs Sales..

Any suggestions are appreciated...

Hello,

Just a follow up on my previous message. Any ideas on my last question. I would like to put together the forecast for each month with their correspondent demand. However, it appears that the only way to do so, it's creating 2 sub forms. I was thinking that there was another way to do this with only one form.
The information comes from two tables 1 is the forecast that summarize the numbers and the other one is the table that has the monthly sales...

Any suggestions are greatly appreciated....

You haven't described your structure, but in general terms you can do it with a subquery or two queries. It would look like

SELECT ForecastFields, ActualFields
FROM ForecastData
LEFT JOIN
(SELECT ActualFields FROM ActualData)
ON Whatever = Whatever

Both would likely have criteria, and you'd need a field or fields to join on.

Hello,

Thanks for the input,

Let me describe this situation, perhaps this can describe the structure that you are referring to:

Every Month, I receive a new forecast (comes in excel) . this sheet has the forecast for the next month and rolls over 12 months forward.
I upload this info into access. I do also receive the monthly sales which is what I want to compare to the forecast of that particular month.
As an example I have received November Forecast already, which has not only November but 12 months forward.
At the End of November, I want to be able to compare November figures with the actual sales.
Here is the caveat: I would like to do it at a top level overview (the sum of customer forecasts, per Material) vs the actual Sales.
Then I would like to drill down if needed to Compare the actual demand of X customer vs their forecast.

Both tables (Forecast and the Sales) have the following fields in common: The abbv which is the abbreviation of the grade, the Material Number, Description and the Plant were the product is sourcing from as well as the State.

So far, I am combining the last 10 months of forecast in excel, but I am sure there is a better way to do this in access.

Do I need to make joins between the forecast tables to do this? Or this is not relevant for what I want to do?

Again, thanks for your comments, suggestions

Can you attach a sample db with data to play with?

I tried to upload the file, but I am getting a message that says: "Your submission could not be processed because a security token was missing"

Not really sure what that is..Any ideas?

I've seen that resolved by using a different browser, or a different version of the one you have.

I tried with explorer, google and Firefox and I am getting the same message. Any other browser that you can think of to upload this file?

No I'd expect any of those to work. Is the file under the size limit? If you want you can email it to me and I'll attach it.

Pbaldy
Gmail
Com