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,

In the AfterUpdate event of the Account Number put:
me!lstBeneficiary.RowSource="SELECT txtName FROM tblBeneficiaries WHERE strAccountNumber='" & me!cboAccountNumber & "'"

You don't need a query. Initially the lstBeneficiary rowsource should be blank, and a call to cboAccountNumber_AfterUpdate should be put in the OnCurrent event of the form.

- Frink

 

[pnabby]

Thanks for the quick reply. I actually have not built the form yet. I have just done it in the actual table. Where is the AfterUpdate?

I am sorry, I don't have much programming experience.

Thanks for your help Frink.

 

[Frink]

Hallo,

Name your Account number combo box cboAccountNumber and your Beneficiaries combo box cboBeneficiaries. I assume your Account Number field is text and called strAccountNumber and you have a tblBeneficiaries table with a strName field for the Beneficiary Name and a strAccountNumber field to link it to the Account Number. The list box of Beneficiaries I had called lstBeneficiary but it should probably be called lstBeneficiaries.

If you put [Event Procedure] (from the drop down list) in the AfterUpdate property of the combo box, then click the build button (...) it will open the code with a new
Sub cboAccountNumber_AfterUpdate()
End Sub

Put
me!lstBeneficiaries.RowSource="SELECT txtName FROM tblBeneficiaries WHERE strAccountNumber='" & me!cboAccountNumber & "'"
in between the Sub and End Sub lines

Create an Event Procedure for the forms OnOpen property and put a call to cboAccountNumber_AfterUpdate in it.

If you're not sure about any of this, have a go, and give us more details if it stil doesn't work,

- Frink

 

[pnabby]

Do I create the combo box in the form? Also, use both tables, tblLifeInsurance and tblBeneficiaries as the source?

 

[Frink]

Hallo,

You need to create cboAccountNumber and lstBeneficiaries on the form.

The RowSource for cboAccountNumber should be based on tblLifeInsurance.
The RowSource for lstBeneficiaries should be tblBeneficiaries while you set up your columns widths etc. Once you're happy with it you can delete the RowSource as this will be overwritten before each record is displayed.

- Frink

 

[pnabby]

This is what I have:

tblClientInfo:

chrFirstName
chrLastName
idsSSN (this is a primary key)
intAClntAcct (this is a primary key)

tblLifeInsurance:

idsInsurPolicy# (this is a primary key)
fidsAClntAcct (this is a lookup where the row source is:SELECT [tblClientInfo].[intAClntAcct], [tblClientInfo].[chrLastName] FROM [tblClientInfo] ORDER BY [chrLastName]
chrPolicyOwner
chrInsured
chrBeneficiary (this is a lookup where the row source is:
SELECT [qyBeneficiaries].[intBFSSN], [qyBeneficiaries].[chrBFLastName] FROM [qyBeneficiaries] ORDER BY [chrBFLastName]

I am stuck after that. I want one our anaylists to be able to enter client info in a form and when he has chosen a life insurance policy, to have the option to select a beneficiary from a list. This list will only contain beneficiaries that apply to the specific client.

 

[Frink]

Hallo,

Create cboAccountNumber on your form and use the same RowSource and Columns information as in the Lookup for the fidsAClntAcct field in tblLifeInsurance.

- Frink

 

[pnabby]

Okay, I created a form with 2 combo boxes: cboAccountNumber & cboBeneficiary and 1 list box, lstBeneficiaries.

The row source for cboAccountNumber is:

SELECT [tblClientInfo].[intAClntAcct], [tblClientInfo].[chrLastName] FROM [tblClientInfo] ORDER BY [chrLastName];

In the combo box cboBeneficiary, I did NOT put a row source in, put I put an event procedure as follows:

Private Sub cboBeneficiary_AfterUpdate()
Me!lstBeneficiaries.RowSource = "SELECT chrBFLastName FROM tblBeneficiaries WHERE fidsAClntAcct='" & Me!cboAccountNumber & "'"

End Sub

The list box, lstBeneficiaries, has nothing in it.

I know I am missing something simple. What do I need to change? Thanks,
Julie

 

[Frink]

Hallo,

Sorry, We seem to have got a bit confused. It's nearly time for me to go home

The code you have should go in cboAccountNumber_AfterUpdate, not cboBeneficiary_AfterUpdate, and you don't need a cboBeneficiary, just a lstBeneficiaries.

- Frink

 

[pnabby]

what should be lstBeneficiaries?

 

[pnabby]

Okay this is what I have:

cboAccountNumber combo box

The Row Source is:

SELECT tblClientInfo.intAClntAcct, tblClientInfo.chrLastName, tblClientInfo.idsSSN FROM tblClientInfo ORDER BY tblClientInfo.chrLastName;

The After_Update Event Procedure is:

Private Sub cboAccountNumber_AfterUpdate()

Me!lstBeneficiaries.RowSource = "SELECT chrBFLastName FROM tblBeneficiaries WHERE fidsAClntAcct='" & Me!cboAccountNumber & "'"


End Sub

The list box, lstBeneficiaries is empty.
What now?

 

[PHV]

If fidsAClntAcct is defined as numeric, you may try this:
Me!lstBeneficiaries.RowSource = "SELECT chrBFLastName FROM tblBeneficiaries WHERE fidsAClntAcct=" & Me!cboAccountNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Frink]

Hallo,

In tblBeneficiaries, what is the field name which indicates which Account it is a Beneficiary of?
The AfterUpdate code assumes it is called fidsAClntAcct and is a text field holding the Account Id.

I suspect this is not a text field at all so the single quotes should be removed from the code.

You will have to replace the fidsAClntAcct in the code with the correct tblBeneficiaries field name.

- Frink

 

[pnabby]

You Rock!!!! Thanks, it works. That field is numeric.

 

[pnabby]

Now what if I want to show the last name, first name, and social security number in the list box. Sometimes your beneficiaries will have the same last name.

After I select the beneficiary, I want it to update in the life insurance table, to name that person as the beneficiary for that policy#.

 

[Frink]

Hallo,

Increase the number of columns in lstBeneficiaries to 3.
Change the
...SELECT chrBFLastName FROM tblBeneficiaries...
to
...SELECT chrBFLastName, chrFirstName, idsSSN FROM tblBeneficiaries...

- Frink

 

[pnabby]

That is in the row source for cboAccountNumber? That does not work...now nothing is in the list box.

 

[Frink]

No.

...SELECT chrBFLastName FROM tblBeneficiaries... is not in the RowSource for cboAccountNumber.
The RowSource for cboAccountNumber defines what is displayed in the rows of cboAccountNumber.
If you want to change what is displayed in the rows of lstBeneficiaries, you have to change the rowsource of lstBeneficiaries.

You want to change the place where ...SELECT chrBFLastName FROM tblBeneficiaries... is, which is in the line of code we wrote yesterday.

- Frink

 

[pnabby]

Okay, this is what I have:

in the cboAccountNumber I have in the Row Source:

SELECT tblClientInfo.intAClntAcct, tblClientInfo.chrLastName, tblClientInfo.idsSSN FROM tblClientInfo ;

This displays all the information in the drop down box, however, it only returns the client number, intAClntAcct.

Also I have an Event Procedure in the cboAccountNumber which is:

Private Sub cboAccountNumber_AfterUpdate()


Me!lstBeneficiaries.RowSource = "SELECT intBFSSN FROM tblBeneficiaries WHERE fidsAClntAcct=" & Me!cboAccountNumber

End Sub

I tried to change this code to return the last name, first name, and SSN in the drop down list, lstBeneficiaries, however I can only get it to work when I choose one field.

What should I try next? Thanks, Frink

Julie