View Single Post
  #9  
Old June 1st, 2010, 02:43 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default PLEASE HELP: Refresh columns displayed in a report from a quer

sam wrote:
I have a couple questions:
1: I have put the code in form_load procedure, Is this a better place to
execute the code than form_open procedure?


Whoa! We had better be talking about reports here, not a
form. A report opened in preview or normal view (only
useful ways) does not have/use a load event so you must use
the REPORT's OPEN event.

2: I have basically put about 10 extra columns in the report, so now the
column display is driven by user selection on the form, BUT is there a way to
hide the columns(column header) that are not selected by the user?


See below.

3: Also, we are hard coding the column locations to the fields in the form,
What if the user selects the 6th column value to be displayed and not the 5th
or the 4th?
The location of the columns is fixed here, so there would be a huge gap
between 3rd column and 6th column. Is there a fix for this?


You should make the user selectable text boxes and their
associated header labels in the report invisible. Then use
code in the open event to set the label captions and the
text box's control source and make them visible at the same
time.

Because you so not know a priori which field will be bound
to which report text box, the textboxes should be named in a
uniform fashion (eg. txtSelect1, txtSelect2, ... and
lblSelect1, lblSelect2, ...). With this approach, the code
in the report's Open event could be vaguely like:

Dim k As Integer
With Forms!theform
If Not IsNull(.thistextbox) Then
k = k + 1
Me("txtSelect" & k).ControlSource = .thistextbox
Me("txtSelect" & k).Visible = True
Me("lblSelect" & k).Caption = .thistextbox
Me("lblSelect" & k).Visible = True
End If
If Not IsNull(.thattextbox) Then
k = k + 1
Me("txtSelect" & k).ControlSource = .thattextbox
Me("txtSelect" & k).Visible = True
Me("lblSelect" & k).Caption = .thattextbox
Me("lblSelect" & k).Visible = True
End If