One report layout for 3 queries

[StuckAndNoClue]

I'm pretty new to Access and VB and also know fairly little (more like nothing) about SQL.

I've got three queries that shows similar fields, the main ones being:

FIRST QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Preliminary First Aid (PFA) due in 30 days
PFA due in 60 days
PFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)

SECOND QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Senior First Aid (SFA) due in 30 days
SFA due in 60 days
SFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)

THIRD QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Advanced First Aid (AFA) due in 30 days
AFA due in 60 days
AFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)

I've got a form that lets me select which report i want (PFA, SFA or AFA) and i want one report layout with unbound text boxes that is sorted by Division.

The problem is, how do i program the unbound text boxes in the report to refer to the relevant fields as i dont know how to link the control source either through SQL or through VB.

Thanks

[MMcCarthy]

What you are trying to do is a coding nightmare commonly known as dynamic reporting. Unless you are a very experienced programmer then don't even try it.

You would not just have to set the textbox values you would also have to set the labels and toggle textboxes to invisible if no data. Then there are formatting issues.

It's a lot of code and if you are trying to save time on creating three different reports this is not the way to do it.

The only way this works is if you have the same number of fields and the same labels for each field and the same datatype for each field. In that case you would only need to change the record source of the report each time.

I'm pretty new to Access and VB and also know fairly little (more like nothing) about SQL.

I've got three queries that shows similar fields, the main ones being:

FIRST QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Preliminary First Aid (PFA) due in 30 days
PFA due in 60 days
PFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)

SECOND QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Senior First Aid (SFA) due in 30 days
SFA due in 60 days
SFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)

THIRD QUERY
Name
ID
Active member (criteria: yes)
Division in which a person belongs
Advanced First Aid (AFA) due in 30 days
AFA due in 60 days
AFA due in 90 days
(the last 3 which are calculated fields which is based on when they actually sat the course)

I've got a form that lets me select which report i want (PFA, SFA or AFA) and i want one report layout with unbound text boxes that is sorted by Division.

The problem is, how do i program the unbound text boxes in the report to refer to the relevant fields as i dont know how to link the control source either through SQL or through VB.

Thanks

[StuckAndNoClue]

Thanks for the quick response mmccarthy.

Yes, the three queries do have the same number of fields, and same datatypes for each field. And i can make it so that all of their field name is the same in each query too.

But I dont know how to change the control sources to refer to each query.

I am doing this so i dont have to amend all three report layout if i need the report layout changed in the future. i could have easily done three reports but that means that if i change the look of one report, i have to do so with other reports.

What you are trying to do is a coding nightmare commonly known as dynamic reporting. Unless you are a very experienced programmer then don't even try it.

You would not just have to set the textbox values you would also have to set the labels and toggle textboxes to invisible if no data. Then there are formatting issues.

It's a lot of code and if you are trying to save time on creating three different reports this is not the way to do it.

The only way this works is if you have the same number of fields and the same labels for each field and the same datatype for each field. In that case you would only need to change the record source of the report each time.

[MMcCarthy]

Thanks for the quick response mmccarthy.

Yes, the three queries do have the same number of fields, and same datatypes for each field. And i can make it so that all of their field name is the same in each query too.

But I dont know how to change the control sources to refer to each query.

I am doing this so i dont have to amend all three report layout if i need the report layout changed in the future. i could have easily done three reports but that means that if i change the look of one report, i have to do so with other reports.

Set up the report using one of the queries.

Then change the recordsource using the following code in the report open event.

 
Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "QueryName"
End Sub

The queryname will have to be based on a value in your form. how are you choosing the report type. Option group, dropdown list ?

[StuckAndNoClue]

THANKS, i'll try it tonight. :) I've been pulling my hair about this aspect of the the database for about two weeks.

I'm choosing the report by command button.

Set up the report using one of the queries.

Then change the recordsource using the following code in the report open event.

 
Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "QueryName"
End Sub

The queryname will have to be based on a value in your form. how are you choosing the report type. Option group, dropdown list ?

[MMcCarthy]

THANKS, i'll try it tonight. :) I've been pulling my hair about this aspect of the the database for about two weeks.

I'm choosing the report by command button.

A different command button for each report would complicate the process. With just one command button.

Create a drop down list of the three choices.

Then in the Report Open event you could use something like this:

 Private Sub Report_Open(Cancel As Integer) 
Dim rptType As String
 
  rptType = Forms![FormName]![ComboBoxName]
 
  If rptType = "XXX" Then
    Me.RecordSource = "QueryName1"
  ElseIf rptType = "YYY" Then
    Me.RecordSource = "QueryName2"
  ElseIf rptType = "ZZZ" Then
    Me.RecordSource = "QueryName3"
  End If
 
End Sub
 

[NeoPa]

Private Sub Report_Open(Cancel As Integer) 
  Select Case Forms![FormName]![ComboBoxName]
  Case "XXX"
    Me.RecordSource = "QueryName1"
  Case "YYY"
    Me.RecordSource = "QueryName2"
  Case "ZZZ"
    Me.RecordSource = "QueryName3"
  End Select
End Sub

-'Select Evangelist' ;)
(I'll convert Mary yet!)

[MMcCarthy]

Private Sub Report_Open(Cancel As Integer) 
Select Case Forms![FormName]![ComboBoxName]
Case "XXX"
Me.RecordSource = "QueryName1"
Case "YYY"
Me.RecordSource = "QueryName2"
Case "ZZZ"
Me.RecordSource = "QueryName3"
End Select
End Sub

-'Select Evangelist' ;)
(I'll convert Mary yet!)

I knew as soon as I'd posted it you'd be in here mouthing off.

Have to leave you something to do. Keep trying to convert me.

Mary

[StuckAndNoClue]

Thanks for the answer,
I tried it last night and it worked like a gem. :)

[MMcCarthy]

Thanks for the answer,
I tried it last night and it worked like a gem. :)

You're welcome.