Hi dears, I'm new on this forum and a bit rusty in programming!!
However, I need your help, if possible, on my below question.
I've a CrossTab that shows by week the stock trend but I want to:
1_ put in a form
2_ allow dynamic week change basing user choice.
For instance user choose to see last 7 weeks the form shows data from week 30 till week 37; if 13 wks from 24 to 37 and so on.
I've wrote this VBA
Private Sub OPT_7_Click()
Dim sqla As String
Dim x, l As Integer
Dim c_text, c_lbl As Control
Me.OPT_1.VALUE = 0
Me.OPT_13.VALUE = 0
Me.OPT_26.VALUE = 0
Me.OPT_39.VALUE = 0
'Me.OPT_7.VALUE = 0
Me.OPT_F.VALUE = 0
DoCmd.DeleteObject acQuery, "QueryFGHist"
sqla = "PARAMETERS Forms![MainForm]![C_STK_PART] Text ( 255 ), Forms![MainForm]![C_STK_LINE] Text ( 255 ), Forms![MainForm]![C_STK_MP] Text ( 255 ), Forms![MainForm]![C_STK_MC] Text ( 255 ), Forms![MainForm]![C_STK_BU] Text ( 255 ); " _
& "TRANSFORM Sum([FG History].[FG Cons Quantity]) AS [SumOfFG Cons Quantity] " _
& "SELECT [FG History].[Product BU Code], Mid([Raw Line Code],6,4) AS LINE, [FG History].[Finished Good Code], [FG History].[Product Maturity Code], [FG History].[Macro Package Code], [FG History].Plant, [FG History].Store, [FG History].[FG Store Type Description], [FG History].[FG Avai Type Name] " _
& "FROM Week_7D INNER JOIN [FG History] ON Week_7D.YYYYWW = [FG History].[Hist Date] " _
& "GROUP BY [FG History].[Product BU Code], Mid([Raw Line Code],6,4), [FG History].[Finished Good Code], [FG History].[Product Maturity Code], [FG History].[Macro Package Code], [FG History].Plant, [FG History].Store, [FG History].[FG Store Type Description], [FG History].[FG Avai Type Name] " _
& "PIVOT [FG History].[Hist Date]"
Set qdf = CurrentDb.CreateQueryDef("QueryFGHist", sqla)
DoCmd.OpenForm "FormFGHist", acDesign
l = 12888
For x = 1 To 7
Set c_text = CreateControl("FormFGHist", acTextBox, acDetail, , "", l, 72, 1800, 288)
Set c_lbl = CreateControl("FormFGHist", acLabel, acDetail, , "", l, 72, 1800, 288)
c_text.ControlName = "C_WK_" & x
c_lbl.ControlName = "L_WK_" & x
DoCmd.Save acForm, "FormFGHist"
c_text.VALUE = QueryFGHist![SumOfFG Cons Quantity] NOT WORK
c_lbl.Caption = QueryFGHist![Hist Date] NOT WORK
l = l + 2016
Next x
DoCmd.Close acForm, "FormFGHist"
End Sub
And query, textbox and label creation performs but the set value and caption for textbox and label not.
It is possible a solution or not? I must see the week and not simply a numbering (from 1 to xx) but the weeknumber.
I've set FORM in Datasheet View and for fixed value (such as PRODUCT/STORE/etc) I've set the recordsource in Form properties but I don't know if also in VBA have I to update data.
Hope I'm clear in my sentences and that it is possible a solution!
Regards
Emmanuele
However, I need your help, if possible, on my below question.
I've a CrossTab that shows by week the stock trend but I want to:
1_ put in a form
2_ allow dynamic week change basing user choice.
For instance user choose to see last 7 weeks the form shows data from week 30 till week 37; if 13 wks from 24 to 37 and so on.
I've wrote this VBA
Private Sub OPT_7_Click()
Dim sqla As String
Dim x, l As Integer
Dim c_text, c_lbl As Control
Me.OPT_1.VALUE = 0
Me.OPT_13.VALUE = 0
Me.OPT_26.VALUE = 0
Me.OPT_39.VALUE = 0
'Me.OPT_7.VALUE = 0
Me.OPT_F.VALUE = 0
DoCmd.DeleteObject acQuery, "QueryFGHist"
sqla = "PARAMETERS Forms![MainForm]![C_STK_PART] Text ( 255 ), Forms![MainForm]![C_STK_LINE] Text ( 255 ), Forms![MainForm]![C_STK_MP] Text ( 255 ), Forms![MainForm]![C_STK_MC] Text ( 255 ), Forms![MainForm]![C_STK_BU] Text ( 255 ); " _
& "TRANSFORM Sum([FG History].[FG Cons Quantity]) AS [SumOfFG Cons Quantity] " _
& "SELECT [FG History].[Product BU Code], Mid([Raw Line Code],6,4) AS LINE, [FG History].[Finished Good Code], [FG History].[Product Maturity Code], [FG History].[Macro Package Code], [FG History].Plant, [FG History].Store, [FG History].[FG Store Type Description], [FG History].[FG Avai Type Name] " _
& "FROM Week_7D INNER JOIN [FG History] ON Week_7D.YYYYWW = [FG History].[Hist Date] " _
& "GROUP BY [FG History].[Product BU Code], Mid([Raw Line Code],6,4), [FG History].[Finished Good Code], [FG History].[Product Maturity Code], [FG History].[Macro Package Code], [FG History].Plant, [FG History].Store, [FG History].[FG Store Type Description], [FG History].[FG Avai Type Name] " _
& "PIVOT [FG History].[Hist Date]"
Set qdf = CurrentDb.CreateQueryDef("QueryFGHist", sqla)
DoCmd.OpenForm "FormFGHist", acDesign
l = 12888
For x = 1 To 7
Set c_text = CreateControl("FormFGHist", acTextBox, acDetail, , "", l, 72, 1800, 288)
Set c_lbl = CreateControl("FormFGHist", acLabel, acDetail, , "", l, 72, 1800, 288)
c_text.ControlName = "C_WK_" & x
c_lbl.ControlName = "L_WK_" & x
DoCmd.Save acForm, "FormFGHist"
c_text.VALUE = QueryFGHist![SumOfFG Cons Quantity] NOT WORK
c_lbl.Caption = QueryFGHist![Hist Date] NOT WORK
l = l + 2016
Next x
DoCmd.Close acForm, "FormFGHist"
End Sub
And query, textbox and label creation performs but the set value and caption for textbox and label not.
It is possible a solution or not? I must see the week and not simply a numbering (from 1 to xx) but the weeknumber.
I've set FORM in Datasheet View and for fixed value (such as PRODUCT/STORE/etc) I've set the recordsource in Form properties but I don't know if also in VBA have I to update data.
Hope I'm clear in my sentences and that it is possible a solution!
Regards
Emmanuele
