Cant find the error: how to make the listbox work in my form and populate the textboxes on click.

[behedwin]

Hey
How can i fix this problem in my new project?

I have the following.

Tables:
Address_Table = contains addresses to staff
Phone_Table = contains phonenumbers to staff
Email_Table = contain email to staff
Personal_Table = contain staff names and social sec number
Anstallning_Table = contracts for each staff member.

Forms:
Adress_Form = display adresses in continuous form
Phone_Form = display phonenumbers in continuous form
Anstallning_Form = display contracts
Email_Form = display email in continuous form
Profile_Form = my main form where everything is being displayed about a staff member, containing a tabbs for different information and each tab have some subforms.
Select_Person_Form = Starter form, this is where the user starts (atm). Select staff member and double click and go to Profile_Form.

My problem is the following:
In Profile_Form i have a tab called Kontrakt (contracts). Here i have a sub form to Anstallning_Form. In Anstallning_Form i want to have a listbox that display all the contracts for the current opened staff members. A staff member can have one or many contracts. The listbox displays some short information about the contract, but when i click on the contract i want to populate the textboxes below the listbox, bring up the data from the selected row in the listbox.

But i dont get it to work.
Please download my project file here (zip file): https://files.fm/u/gfcjaeqz
Here are some screenshots: https://imgur.com/a/IroC71i
and one more screenshot: https://imgur.com/a/H9jeuaj
Here is a video me trying to show the error: https://streamable.com/rt5a8

I would really like to find a solution to this.
If i can provide some better explanation, let me know.

 

[UncleCatwoman]

Here's the way I would do it:

Assume lstContracts is your list box, with the bound field being ContractID.
Make sure the list box is set to only allow one selected record (not multiple).

In the On Click event for the list box, you can either:
... using the ContractID, use DLookUps to find the contract data and populate the fields on your form
[txtContractDate]=DLookUp("[ContractDate]","tbl_Contracts","[ContractID]=" & me.lstContracts.Value)

... or, pull the contract record as a recordset and use it to populate the fields on your form
[pull the contract record into recordset rsC]
[txtContractDate] = rsC!ContractDate

 

[welshgasman]

If it is only a couple of fields I would pull them in with the listbox data, then populate from the row selected.
If a lot of fields, then I would go with pulling in the data as a recordset.

 

[Micron]

I don't see the need for a recordset. The subform recordsource should be a query or sql statement that returns values for the text boxes by using the bound field of the listbox as criteria. As noted, the listbox would have to be single select only. Upon selection (After Update), requery the form. IIRC, you cannot refer to a combo box column in a query, so I presume a listbox would be the same. Therefore, the sql for the recordsource would have to be handled via code.

If the list must be multi select, the subform would have to be continuous or datasheet type and the sql statement (subform recordsource) would have to be built in code due to having to loop through the multiple selected values.