Contribution: Report/Form RecordSource Switching on-the-fly

[waldemar]

Another interesting one:

In my database I am using the same queries for different forms and reports - depending on what "View" the user picks out of a menu.

The function takes some user variable (in this case some control text from a control bar), looks for the regarding query and inserts it into the current form. If there is NO current form (because the user is viewing a report) it applies the query to the current report (can only be done by closing and reopening it)....

Function updateRecordset()
Dim sqlText As String, strReportName As String, rpt As Report, sqlTextForStudents
DoCmd.Hourglass True

' ----- 1ST: FIND OUT THE QUERY NAME -----

Select Case CommandBars("NAVIGATIONBAR&quot.Controls("CONTROLWITHQUERYDESCRITPION&quot.Text
Case "QUERYDESCRIPTION1"
sqlText = "QUERYNAME1"
Case "QUERYDESCRIPTION2"
sqlText = "QUERYNAME2"
Case "QUERYDESCRIPTION3"
sqlText = "QUERYNAME3"
Case "QUERYDESCRIPTION4"
sqlText = "QUERYNAME4"
Case "QUERYDESCRIPTION5"
sqlText = "QUERYNAME5"
Case "QUERYDESCRIPTION6"
sqlText = "QUERYNAME6"
End Select

' ----- 2ND: IF THE FOLLOWING CODE RETURNS AN ERROR, THIS MUST BE A REPORT -----

On Error GoTo reports
Forms(Screen.ActiveForm.Name).RecordSource = sqlText
On Error GoTo 0

GoTo doneStudents

reports:
On Error GoTo done
strReportName = Screen.ActiveReport.Name
On Error GoTo 0

For Each rpt In Reports
If rpt.Report.Name = strReportName Then DoCmd.Close acReport, Screen.ActiveReport.Name: Exit For
Next rpt
DoCmd.OpenReport strReportName, acViewPreview, sqlText

done:
DoCmd.Hourglass False
End Function