Automatically populate other fields

stu_c · Jan 8, 2018

Hi all
I have a form with a drop down list of car registrations that pulls the information from a table called "Vehicles" and other fields such as make of car etc.

when I select a registration from the drop down list I want it to automatically populate the other fields with car make, colour etc how do I do this

Table information on:
>TblVehicles

Table to pull info to
>TblVehicleBooking

Drop down list name:
> Registration

Other Field Names:
> Make
> Colour
>Misc

plog · Jan 8, 2018

Table to pull info to
>TblVehicleBooking

Just to be clear, the fields [Make], [Colour] and [Misc] should only exist in TblVehicles, they should not be fields in TblVehicleBooking. You don't store duplicate data all over the place. You can however show those fields on the form that is based on tblVehicleBooking.

You would do that with a Dlookup (https://www.techonthenet.com/access/functions/domain/dlookup.php). You would use the Dlookup as the control source of those inputs to find the right data based on the vehicle the user has chosen.

arnelgp · Jan 8, 2018

you include all other fields in your combobox.
Join tblVehicles and tblVehicleBooking on Registration Number or
common field they have:

select T1.[Registration Number], T2.[Field1], T2.[Field2] from tblVehicles As T1
Left Join tblVehicleBooking As T2 On T1.[Registration Number]=T2.[Registration Number]

Set the Column Count of combo to the number of fields you have in the SQL Query.
Hide the other column by setting their Column Widths to 0 (eg):
To show column 1 and hide column 2:
Column Widths: 1;0

do this with the rest of the column.

now on the AfterUpdate Event of the combo, you just pull the corresponding
column from your combo (0 based, meaning Column(0) is actually Column 1).

Private Sub Combo_AfterUpdate()
Me.[Registration Number]=Me.Combo.Column(0) 'column 1
Me.[OtherInfo]=Me.Combo.Column(which column, zero based)
End Sub

stu_c · Jan 8, 2018

I think I am even more confused than I was before

stu_c · Jan 8, 2018

arnelgp said:
you include all other fields in your combobox.
Join tblVehicles and tblVehicleBooking on Registration Number or
common field they have:

select T1.[Registration Number], T2.[Field1], T2.[Field2] from tblVehicles As T1
Left Join tblVehicleBooking As T2 On T1.[Registration Number]=T2.[Registration Number]

Set the Column Count of combo to the number of fields you have in the SQL Query.
Hide the other column by setting their Column Widths to 0 (eg):
To show column 1 and hide column 2:
Column Widths: 1;0

do this with the rest of the column.

now on the AfterUpdate Event of the combo, you just pull the corresponding
column from your combo (0 based, meaning Column(0) is actually Column 1).

Private Sub Combo_AfterUpdate()
Me.[Registration Number]=Me.Combo.Column(0) 'column 1
Me.[OtherInfo]=Me.Combo.Column(which column, zero based)
End Sub

So in the lookup on the table select all columns on the Registration, then go into the form AfterUpdate then type this?

Private Sub Combo_AfterUpdate()
Me.[Registration Number]=Me.Combo.Column(0) 'column 1
Me.[OtherInfo]=Me.Combo.Column(which column, zero based)
End Sub[/QUOTE]

Mark_ · Jan 8, 2018

Effectively yes...

For myself, I would have said fields as unbound (i.e. not based off of a field in your underlying query/table) as they are only being displayed and not saved. Make sure you use the matching names of the display fields on your form though.

If you really want to make sure that the end user knows these are display only, I would use labels and set their .caption to the values as needed. Hence it would read as

Code:

Private Sub Combo_AfterUpdate()
   Me.[Registration Number].caption = Me.Combo.Column(0) 'column 1
   Me.[OtherInfo].caption = Me.Combo.Column(which column, zero based)
End Sub

In your specific case this means you would add the following labels to your form;
lbl_Make
lbl_Colour
lbl_Misc

In your query you would make sure that when you look up the registration you also return [Make], [Colour], and [Misc].

Your select becomes

Code:

select T1.[Registration Number], T2.Make, T2.Colour, T2.Misc from tblVehicles As T1 Left Join tblVehicleBooking As T2 On T1.[Registration Number]=T2.[Registration Number]

and your actual after update would be

Code:

Private Sub Combo_AfterUpdate()
   Me.Make.Caption = Me.Combo.Column(1) 'column 1
   Me.Colour.Caption = Me.Combo.Column(2)
   Me.Misc.Caption = Me.Combo.Column(3)
End Sub

I hope this helps explain.

P.S. Thanks to arnelgp for the initial example.

Pat Hartman · Jan 8, 2018

My preference is to simply join to the "lookup" table. That way the RecordSource query for the form includes ALL the fields you need. Normally you would use a left join if the lookup value is optional. Also, I set the controls bound to the lookup table (except for the combo itself) to be locked so the will not accidentally be changed. This solution requires NO CODE. Just some property settings. I've attached a database sample that shows the three methods people have suggested in this thread. You will see in the example that the join is my preferred choice due to its simplicity and the fact that it does not duplicate any data.

Automatically populate other fields

stu_c

Registered User.

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

stu_c

Registered User.

stu_c

Registered User.

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Attachments

Similar threads

Users who are viewing this thread