ControlSource in Reports updated in VBA (1 Viewer)

[dirkpitt]

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

 

[vbaInet]

If you're being prompted for a parameter that you didn't create then look in the underlying query that the report is based upon to identify the problem field.

With respect to your code, why are you needing to dynamically change the Control Source of controls? Wouldn't it be something you have to do on the query and just simply change the label captions instead?

 

[dirkpitt]

Thank you for your help

I am using a query that is a kind of 'base' for multiple reports and forms in the app. I prefer doing this in this case rather than multiplying the number of queries.

But it is something I thought of.

You were right, there was a field (in the control source) that was not existing. I changed it to an existing field and everything went fine.As I update it dynamically the report works fine now.

Bye

Dirk

 

[vbaInet]

Great!! Glad you got that working.