Reports

[camedeiros]

I have a report made from a table that works nicely. In case I want to change the table to a new one where everything is the same, except that the new one has two more fields and the table itself has a different name, how can I use the same report design?

Thanks,

Camedeiros

 

[Pat Hartman]

You can copy the report and change the recordsource to reference a query based on the new table. It is best to use queries as the recordsources of forms and reports rather than using the tables directly.

 

[Fornatian]

First make your report with all possible fields then programmatically hide controls/labels and set control sources to null

'when the report opens
Private Sub Report_Open(Cancel As Integer)
Dim strResp As String

'ask the user which control source to use for the report and set it
Me.RecordSource = InputBox("Which Table To Use?", "", "tblHours")


'if the table 'tblHours' doesn't have a field called AnotherField then
'remove it from report.
If strResp = "tblHours" Then
Me.AnotherField.ControlSource = ""   'unbind the control source to trick Access
Me.AnotherField.Visible = False 'hide the field
Me.AnotherField_Label.Visible = False 'hide the associated label
End If
End Sub

There are improvements to this code suchas referencing a form field instead of an inputbox, but you get the general idea.