Dynamic form on CrossTab query (1 Viewer)

emm980

New member
Local time
Today, 12:08
Joined
Sep 10, 2020
Messages
3
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:08
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

If you download some calendar form samples, the principle is the same to what you're trying to do. I would recommend against using CreateControl though.
 

emm980

New member
Local time
Today, 12:08
Joined
Sep 10, 2020
Messages
3
Hi. Welcome to AWF!

If you download some calendar form samples, the principle is the same to what you're trying to do. I would recommend against using CreateControl though.

Thanks for reply. Could you kindly send me some example (or just one!!) to study the case?
 

Users who are viewing this thread

Top Bottom