Hi,
Here is my problem:
I have a report in which I need to dynamically update some labels and control sources.
When I run the report, all labels are correct, all data are correct. But Access ask me for the value of the field I update the source.
He is asking me :
"Enter the parameter value
Det_Month_2"
I updated the controlsource of it through VBA (see code below).
You'll see also that I dynamically assign sums. Originally the sum was made on the name of the TextBox (=field of the query) in order to avoid to change everything.
Any help will be greatly appreciated.
Here is my code:
Private Sub Report_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim j As Integer
Dim StrSQL As String
Set db = CurrentDb
StrSQL = "SELECT [...], Month, [...] FROM [...] WHERE ID = '13' ORDER BY VAL(ID)"
Set rst = db.OpenRecordset(StrSQL, dbOpenDynaset)
rst.MoveFirst
Me.LastYrDec.Caption = rst.Fields("Month")
StrSQL = "SELECT TOP 4 [...], Month, [...] FROM [...] WHERE [...] <= " & [Forms]![Myform]![Period] & " ORDER BY VAL(ID) "
Set rst = db.OpenRecordset(StrSQL, dbOpenDynaset)
rst.MoveFirst
[...]
rst.MoveNext
Me.Month_2.Caption = rst.Fields("Month")
Me.Det_Month_2.ControlSource = "= " & Left(rst.Fields("Month"), 3)
Me.p2_l3.ControlSource = "=IIf([level3] Is Null,"" "",Sum([" & Left(rst.Fields("Month"), 3) & "]))"
Me.p2_l2.ControlSource = "=IIf([level2] Is Null,"" "",Sum([" & Left(rst.Fields("Month"), 3) & "]))"
Me.p2_l1.ControlSource = "=IIf([level1] Is Null,"" "",Sum([" & Left(rst.Fields("Month"), 3) & "]))"
Me.p2.ControlSource = "=Sum(IIf([Acct]>xxxxxx,[" & Left(rst.Fields("Month"), 3) & "],0))"
rst.Close
Set rst = Nothing
Here is my problem:
I have a report in which I need to dynamically update some labels and control sources.
When I run the report, all labels are correct, all data are correct. But Access ask me for the value of the field I update the source.
He is asking me :
"Enter the parameter value
Det_Month_2"
I updated the controlsource of it through VBA (see code below).
You'll see also that I dynamically assign sums. Originally the sum was made on the name of the TextBox (=field of the query) in order to avoid to change everything.
Any help will be greatly appreciated.
Here is my code:
Private Sub Report_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim j As Integer
Dim StrSQL As String
Set db = CurrentDb
StrSQL = "SELECT [...], Month, [...] FROM [...] WHERE ID = '13' ORDER BY VAL(ID)"
Set rst = db.OpenRecordset(StrSQL, dbOpenDynaset)
rst.MoveFirst
Me.LastYrDec.Caption = rst.Fields("Month")
StrSQL = "SELECT TOP 4 [...], Month, [...] FROM [...] WHERE [...] <= " & [Forms]![Myform]![Period] & " ORDER BY VAL(ID) "
Set rst = db.OpenRecordset(StrSQL, dbOpenDynaset)
rst.MoveFirst
[...]
rst.MoveNext
Me.Month_2.Caption = rst.Fields("Month")
Me.Det_Month_2.ControlSource = "= " & Left(rst.Fields("Month"), 3)
Me.p2_l3.ControlSource = "=IIf([level3] Is Null,"" "",Sum([" & Left(rst.Fields("Month"), 3) & "]))"
Me.p2_l2.ControlSource = "=IIf([level2] Is Null,"" "",Sum([" & Left(rst.Fields("Month"), 3) & "]))"
Me.p2_l1.ControlSource = "=IIf([level1] Is Null,"" "",Sum([" & Left(rst.Fields("Month"), 3) & "]))"
Me.p2.ControlSource = "=Sum(IIf([Acct]>xxxxxx,[" & Left(rst.Fields("Month"), 3) & "],0))"
rst.Close
Set rst = Nothing