setting control source of report programmatically

[zerkat]

Hi,

I am working on a database for my job and having an issue. What I need to do is add a column on a report based on a date. Sounds easy but here's the sticking point since the report is displayed in acPreview Access will not allow me to programmatically set the control source for the column.

If I add the column in design mode and show or hide the column per the date passed through from the form that calls the report then the format of the report is screwed up and that's a no no here. We are sticklers for our formatting. The other options that I can think of all require me to set the control source in the report.

The one way I can think to do this is write the report so it's dynamic - i.e. create the report in the code...unless someone here knows how to somehow set the control source of a column in a report without actually setting it in the report??

 

[PHV]

Why not having 2 reports ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zerkat]

We would like it to be as dynamic as possible to accomodate for future growth. The report details the positions at our company with the number of hours a certain position worked on a certain task. The reason why we wantst it dynamic is that we don't want a lot of separate reports when they eliminate a position or some one's position changes.

I just tried to use OpenArgs to pass through a variable that contained the control source then setting the controlSource of the text box to the OpenArgs in the Report_Open function but it's passing through as null. Here's the code that I am using:

This code is from the form that calls the report:

Dim strControlSource As String

If Me.txtStartDate.Value < #11/1/2008# Then
strControlSource = "Position1"
DoCmd.OpenReport "reportName", acPreview, , , , strControlSource
Else
strControlSource = "Position2"
DoCmd.OpenReport "reportName", acPreview, , , , strControlSource
End If

In the report:

Private Sub Report_Open(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.txtBox.ControlSource = Me.OpenArgs
End if

End Sub


The control source of the text box in design vew is unbound. Access is not one of my strong points so if you notice anything that's completely off with my code that's why.

 

[TheAceMan1]

How are ya zerkat . . .

Move your code to the [blue]On Print[/blue] event of the [blue]section[/blue] where the textbox resides.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

The reason why we wantst it dynamic is that we don't want a lot of separate reports when they eliminate a position or some one's position changes.

All this information, i.e. people, positions, when a person changed a position, etc., should be in tables. Then there is no need to "dynamically" change a report based on data - your query will automatically retrieve the data according to the date.

The best way to accomodate future growth is to have a properly normalized database schema. This is the key.