Dynamic Controls on a Report

[Brian]

Hello all,


Here is the code I have.. I am looking for a way from a dynamics table
to have dynamics columns

Private Sub Report_Open(Cancel As Integer)
Dim Rst As Recordset, f As Field, i As Integer, x As Control
Set Rst = CurrentDb.OpenRecordset("tblStand")
For Each f In Rst.Fields
If f.Name <> "DBH" And f.Name <> "Flag" Then
i = 1
Do While i < 16

Me.Controls("txtSpc" & (i)).Value = f.Name
Loop

Rst.MoveNext

End If
Next
Rst.Close

'txtSpc(i).Value = txtSpc(i) & " " & f.Name
End Sub


Any help would be great...

 

[Allen Browne]

Report_Open is too early to assign a value to a bound control. If you are
assign a literal value from the table, you might be better to use labels,
and assign the value to the Caption of the control:
Me.Controls("lblSpc" & i).Caption = rst(i).Name

Or perhaps you were trying to to assign the ControlSource of the text box:
Me.Controls("txtSpc" & i).ControlSource = rst(i).Name

If you are using Access 2007, you need to be aware that assigning the
Control Source like that will crash Access (shut down by Windows.)

 

[Brian]

Ok here is what is happening now

I changed my code to read as such

Dim Rst As Recordset, f As Field, i As Integer, x As Control
Set Rst = CurrentDb.OpenRecordset("tblStand")
For Each f In Rst.Fields
If f.Name <> "DBH" And f.Name <> "Flag" Then
i = 1
Do While i < 16
i = i + 1
Me.Controls("txtSpc" & i).ControlSource = f.Name
Loop
Rst.MoveNext
End If
Next
Rst.Close

in the Report open but after it runs all that happens is a input box
comes up then it assigns whatever I put in there to all the columns
Here is the tblStand which is created dynamically in code the only
constants are the DBH and FLAG other than that 4 and 12 can be any
numbers and also more that what is shown as in next time it could be
4 12 16 21 up to about 45 different number combinations, and they want
to see as many as 16 columns in a report but less of course if not that
many apply.. I think I am close but not quite there yet...

DBH 4 12 Flag
6 0
8 0
10 0
12 0
14 0
16 0

 

[Larry Linson]

"Brian" <you> responded:

in the Report open but after it runs all that happens is a input box comes
up then it assigns whatever I put in there to all the columns
to see

After Allen's caution, you are still putting your code in the Open event?

What is the prompt in the Input Box? The existence of the Input Box
indicates that Access cannot unambiguously resolve some name... variable,
etc.... that you used in your procedure.

And what, exactly, do you mean by "assigns whatever I put in there to all
the columns to see"?

If you'll clarify what data you have, how it is organized, and what you are
trying to accomplish.

Larry Linson
Microsoft Access MVP

 

[Brian]

Ok here is what's happening

Here is my new code

Private Sub Report_Open(Cancel As Integer)
Dim Rst As Recordset, f As Field, i As Integer, x As Control
Set Rst = CurrentDb.OpenRecordset("tblStand")
i = 1
For Each f In Rst.Fields
If f.Name <> "DBH" And f.Name <> "Flag" Then
Me.Controls.Item("txtSpc" & CStr(i)).ControlSource = f.Name
i = i + 1
End If
Next
Rst.Close
End Sub


**** Here is what the table looks like *****
DBH 4 12 Flag
6 0 0
8 0 0
10 0 0
12 0 0
14 0 0
16 0 0


and it runs through everything fine except at the end then it brings up
as many input boxes as I have f.name's so if there are 6 columns then 6
input boxes come up that have a title of the f.name. an example column
would be like 12 and then at the end of the code 12 would pop up but
when I actually type something in the input box it puts that value in
the textbox.. the table is all created with code and has to be dynamic

I guess I am having trouble figuring out why the Report_Open event does
not work cause when i type them in it puts the value in which of course
I don't want and all my textboxs are unbound which is why I thought it
would work... by the way I am using Access 97 I tried the Caption value
of a label and must be misunderstanding cause that just crashed on me..
Again Many thanx

 

[Brian]

I guess I don't understand how if I can put numbers in the input box
after the code is done running and they should up on the report then why
can't I pass the values to them... all on report open.. hope you
guys/gals did not give up on me...remember the only thing in my table
that is constant is the DBH and Flag the others change in name and how many