Dynamic List Box based on current record

[pnabby]

I am trying to create a form where the user will select an account number for a life insurance policy. When that is selected, I want a list box with choices for a beneficiary to be limited to a query that filters the beneficiaries table and displays only beneficiaries that are tied to that particular account number.

I have created a query to filter the list, but unfortunately it asks for the account number. This is redundant since I am already in that record.

Any ideas on how to do this?

Thanks for any help,
Julie

 

[Frink]

Hallo,
When you say you can't get it to work, do you get error messages, or is everything blank, does your computer crash?

To add more columns to a list box, add more fields in the SELECT and change the number of columns in the control to the number of fields, ie.

Private Sub cboAccountNumber_AfterUpdate()
Me!lstBeneficiaries.RowSource = "SELECT intBFSSN FROM tblBeneficiaries WHERE fidsAClntAcct=" & Me!cboAccountNumber
End Sub

becomes

Private Sub cboAccountNumber_AfterUpdate()
Me!lstBeneficiaries.RowSource = "SELECT intBFSSN, strLastName, strFirstName FROM tblBeneficiaries WHERE fidsAClntAcct=" & Me!cboAccountNumber
End Sub

Then set the lstBeneficiaries column count to 3 and column widths to whatever you want them to be.
If you're not sure about the SELECT statement, create a query which returns what you want (without any filtering), switch to SQL view and copy the SQL statement into the code, adding the WHERE as above.

- Frink