How to make an autocomplete field in Access, based on the value of another field?

Question

Visual Basic

1 table stores the full name, rank, and location of the soldier, the second table stores the numbers of weapons. The third link table (by primary keys) stores information about which weapon is assigned to whom (not everyone has a weapon, so I had to separate it into the second table). How can I make it so that when choosing a soldier's surname in the drop-down list, the fields rank, weapon number and location are filled in automatically?

Answer 1

"dropdown list" - dropdown?
"filled in automatically" - was the current binding displayed?
If the answer to both questions is "yes":
make a subform and where to display the link for all 3 tables - and use the value from the dropdown as a filter.
It may not be necessary, but if the values ​​​​in the subform will not be updated when the BC dropdown is changed - hang requery for the subform on the Dropdown Change Event.