Linking Two Fields in Access

Question

I am wondering if there is a way to link two separate fields in a separate table. In my Vehicle Table I have linked "Plate" to "Vehicle" in my Maintenance Table. I was wondering if I can also link the "VehicleNumber" and create another field in Maintenance for the same thing but have them linked to the plate information. So if I type in the plate the vehicle number automatically pops in and vise versa. Is there a way to do this? I'm a beginner at this program so the more detailed response the better. Thank you.

Answer 1

In a form bound to MaintenanceT you can include two unbound combo boxes in the form's header section, set up as follows, firstly to filter the form to the maintenance records for a vehicle selected by the vehicle number (ID)

Name:  cboFindID

RowSource:  SELECT ID FROM VehicleT ORDER BY ID;

Secondly to filter the form to the maintenance records for a vehicle selected by plate:

Name:  cboFindPlate

RowSource:  SELECT ID, Plate FROM VehicleT ORDER BY Plate;

ColumnCount  2

ColumnWidths:  0

In the latter case the ColumnCount and ColumnWidths properties differ from the default in order to hide the ID BoundColumn and show the plate values.

In the AfterUpdate event procedure of cboFindID put:

    Me.cboFindPlate = Me.cboFindID

    Me.Filter = "Vehicle = " & Me.cboFindID

    Me.FilterOn = True

In the AfterUpdate event procedure of cboFindPlate put:

    Me.cboFindID = Me.cboFindPlate

    Me.Filter = "Vehicle = " & Me.cboFindID

    Me.FilterOn = True

You can either clear the filter to show all records from the built in navigation bar at the bottom of the form, or by including a 'Show All' button in the form with the following in its AfterUpdate event procedure:

    Me.FilterOn = False

NB:  You must not include a Plate column in MaintenanceT.  That would introduce redundancy and the table would not be Normalized to Third Normal Form, with the consequent risk of update anomalies.

However, a more usual interface would be to have a vehicle form, in single form view, and within it maintenance subform, in continuous forms or datasheet view, linked to the parent form by setting the LinkMasterFields property to ID and the LinkChildFields property to Vehicle.  The two combo boxes and button described above would then be placed in the parent form's header, amending the code as follows:

    Me.Filter = "ID = " & Me.cboFindID

You would then filter the parent form by either of the combo boxes and, by virtue of the linking mechanism, the subform in each case would show the maintenance records for the currently selected vehicle in the parent form.  New maintenance records for the vehicle can be added in the subform if required.

I've assumed in the above that the Vehicle column is a numeric data type.  If it is text, then the code would be amended as follows:

    Me.Filter = "Vehicle = """ & Me.cboFindID & """"

If you are unfamiliar with entering code into a form's, report's, report section's  or control's event procedures, this is how it's done in form or report design view:

1.  Select the form, report, section or control as appropriate and open its properties sheet if it's not already open.

2.  Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new line(s) between these.

Answer 2

PS:  For data entry into the MaintenanceT table via a form you can use two combo boxes designed in exactly the same way, but the ControlSource of each would be Vehicle, and there would be no need for any code in the controls' AfterUpdate event procedures.  With a conventional parent form/subform interface, however, this would be unnecessary as the Plate value would be visible in the parent form.