One of the steps for creating a report in Microsoft Access is to define where the data is coming from.  This is done through the report’s Record Source property which can be a table or standalone query in the database or a SQL statement as shown in the screenshot below.

Usually, this is defined when the report is created and only gets changed if needed.  In some cases, however, you might want to use the same report object for many different queries.  You could have a mailing list report that you want to use for employees, customers or other groups at different times and the data would be drawn from different tables or even different databases.

In this case, the Record Source property can be set using VBA code through the On Open event when the report is first opened. This will automatically assign the query, table or SQL to the property and populate the report when it opens.
If you’re not experienced with VBA, then, in the reports design properties panel, find “On Open” under events and use the ellipsis (…) button to select the Code Builder.  This will put you into the VBA environment and create a new blank subroutine for the Report_Open event.  The following is a sample of the completed event that sets the RecordSource property to pull from a SQL statement.

Private Sub Report_Open(Cancel As Integer)

On Error GoTo errHandler

If Len(Me.RecordSource) = 0 Then
 Me.RecordSource = _
 "SELECT Contacts.ID, Contacts.first_name, Contacts.last_name, Contacts.email, " _
 & "Left([gender],1) AS GenderI, Contacts.Address, Contacts.City, Contacts.State, Contacts.ZipCode " _
 & "FROM Contacts;"
End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description

End Sub

The routine uses an IF … THEN decision to find any existing RecordSource setting before applying the new setting.  This allows the dynamic setting to be overridden by manually setting the RecordSource in the properties panel which might be useful for testing.

If there is no RecordSource set, then the property reads as a zero-length string ( “” ) so the Len() function (length) returns a 0.

In VBA, the Me keyword refers to the current object.

Another possibility for using this is if you are supporting a database with multiple users and a lot of reports.  If the record source of a report needs to be corrected, you would normally have to change the query or the SQL statement supplying which means that all users have to be out of the database so that you can change the report object.  This means inconveniencing the users or waiting until after hours.

One solution is to create a table to hold the SQL statements rather than storing them as part of the report and then to pull the statement from the table and use it to populate the Record Source when the report opens. Your table might look something like this:

Then, your IF statement would change to look something like this –

If Len(Me.RecordSource) = 0 Then
 Me.RecordSource = DFirst("[ReportSources]![RecordSourceText]", _
 "ReportSources", "[ReportName] = '" & Me.Name & "'")
End If

DFirst is a VBA function that searches a table or query for the first occurrence of a string based on criteria.  In this case, it’s searching the ReportName field of the ReportSources table for the report’s name and retrieving the RecordSourceText field for that record. Storing the report name allows the code to search on that key.

If you’re the developer for a database with a lot of reports, this could actually be a standard bit of code that you add as part of report design.  Again, it’s easily overridden so it won’t interfere with anything.  If there is no record source defined for the report and nothing for it in the table you’ve created, the DFirst function will return a NULL which will trigger an error.  You can write additional code to handle that possibility.

This method can also be used on forms but might cause some problems if there are subforms or subreports present.  These require links on corresponding fields between the parent and child objects which are not possible if there is no record source defined.

---

Ring Battery Doorbell Plus | Head-to-Toe HD+ Video, motion detection & alerts, and Two-Way Talk (2023 release)

• See more of who’s there – Battery Doorbell Plus with Head-to-Toe HD+ Video.
• Stay in the know even at night – Enjoy motion detection, privacy zones and see who stops by at night with Color Night Vision.
• Convenient power – Powered by a Quick Release Battery Pack for quick and easy recharging.
• Easy install – Easily setup by connecting your Battery Doorbell Plus to wifi through the Ring app and mounting with the included tools.
• Additional protection – With a Ring Protect Plan (subscription sold separately), record all your videos, review stored videos for up to 180 days (photos for 7 days), and share videos.

Check Price

ComeauSoftware.com uses affiliate links through which we earn commissions for sales.

---