This tutorial explains difference among Record Source, Control Source, Row Source in Access Report and Form.
Difference among Record Source, Control Source, Row Source
In Access Report and Form, there are three different data sources, which can be very confusing for starter.
Record Source
Record Source is a Property of Report and Form, it is the data source that you want to appear in Report and Form. Record Source can be SQL statement, Table, Query.
Record Source can have more than one Field that actually appears in the Report or Form, therefore you can simply select the whole table instead of selecting specific Fields.
You can modify the Record Source by going to Report or Form Property > Data >Record Source, as show in the below picture
In VBA, you can change Record Source using RecordSource Property
Me.RecordSource = "Select * From TableName"
If you try to change Record Source of a closed Form or Report, you must need to open it first. The below code shows how you can use VBA in Form1 (active Form) to open Form2 (closed Form) and then change the Record Source
DoCmd.OpenForm "Form2" Forms!Form2.RecordSource = "Select * From TableName"
Alternatively, you can simply put the RecordSource statment under Load Event of Form2.
For Access Report
DoCmd.OpenReport "Report Name", acViewPreview, "Query Name", "Criteria"
Afterwards save and close the report
DoCmd.Close acReport, "Report Name", acSaveYes
Control Source
Control includes Text Box, Combo Box, etc, anything you can find under the Design tab.
Control Source is what data you want to display in the Control. After you have selected Record Source for the Form, you will be able to see all Field names of Record Source in Control Source. You can select a Field name in Control Source, or use an expression to make condition.
Another function for Control Source is to write value in the Table. If you set Allow Edits Property to Yes, you can modify the data and will save the updated value in the Table.
For example, Job Table contains 3 Fields: Employee ID, Name, Department. I want the Report to use these Fields, so I choose Job Table as a Record Source of a Report. Then I want to display a Text Box showing Employee name for each record, I need to change the Control Source of Text Box to Name.
The below picture shows the Data tab of Text Box Property.
To change the Record Source of Control in VBA, use ControlSource Property. Below is an example of how to change Control Source of Text Box called Text19.
Me.Text19.ControlSource = "Name"
The Control Source in Combo Box has a different meaning, please refer to the below section.
Row Source
Row Source is the data source for use in List Box and Combo Box. It can be SQL, Table, or Query.
Combo Box and List Box are basically the same, except that List Box shows multiple values at once but Combo Box only shows one value at one time.
The list of values that appears in Combo Box and List Box come from Row Source Property.
The Control Source of Combo Box and List Box is different from that in other Controls. In other Controls, Control Source is how you want to Control (Text Box for example) to display value and save value.
Since the display value of Combo Box come from Row Source, Control Source only serves the purpose of save value.
To change Row Source in VBA, use RowSource Property. Below code shows how you can set the RowSource Property using SQL to return Distinct value to avoid duplicates.
Me.Combo17.RowSource = “SELECT DISTINCT Job.[Empl ID] FROM Job”
Click here to learn more about Combo Box
Outbound References
https://msdn.microsoft.com/en-us/library/office/ff835046.aspx?f=255&MSPPError=-2147217396
DoCmd.OpenForm “Form2”
Forms!Form2.RecordSource = “Select * From TableName”
This saved me so much time! Thank you for teaching me something!