Subform autofill

[Traceyannabanna]

Hi My database is for my insurance company offering various different types of insurance. I have a form(single) called Frmclients with a subform (continuous) called Sfincomeinsurance
Frmclients has Client ID and Surname with a combo box to select the client. This autofills the form and subform with the client ID and surname. The client is the main person that holds the insurance policy but has other family members such as spouse. One of the fields in my subform is "person insured" . I wondered if there is a way to select that person from a list associated with the client id? EG I would choose "Bloggs" from the combobox on the main form and then get a choice of Mr Bloggs family members to choose from in "person insured" Hope that makes sense. Thanks in advance

[Homegrownandy]

Yes, reference the ID on the main form in the query for your subform.

so the subforms record source has all the info you want to display. Then in the criteria for the ID field (whatever that may be called) you should have :

=forms![Frmclients]![whatever the text box is called with the ID]

obviously you just name the textbox there. haha. any questions just ask.

[Traceyannabanna]

Hi Thanks for the info. The forms dont have a query. I did them with the form wizard and works really well apart from the fact that I would like to be able to choose the person insured/family member from a list associated with that client

[Homegrownandy]

It will have a query which runs that form. Even if that query isn't listed on the left under querys.

In properties> data tab > record source. This is your query for the form. you can open it by clicking the three little dots (...)

[Homegrownandy]

All your doing is limiting the information you want to show. You can limit that information however you like, but as these are going to all be related to one person.. The ID of that person should be the "filter" if you like in the criteria for "person_ID".

Hope I'm making sense here because you can do this pretty easily (if I'm answering it cant be too hard).

[Traceyannabanna]

Great fingers crossed that I can do this then So....where do I insert the code that you put in your first message? Sorry I am very new to access.

[Homegrownandy]

[whatever the text box is called with the ID] in that piece of code you do need to change. I know I keep saying it but I want it to be clear.

this is on the criteria row for the ID of the person.

[Traceyannabanna]

Zip file attached

[Homegrownandy]

I was just looking. This is as messy as my first database! haha. You seem to have a lot of data repeated. You need to follow rules of normalisation.

EG. you have family name everywhere. This is counter productive. If all tables are linked to the client via the client ID then you only need information specific to the client in that table.

so. in your pensions table for example. that pension relates to client "x". client "x" details are in the client table and the relationship between those tables will allow you to manipulate the data how you want it.

This is the first thing I have seen on there but it does nee looking into. If you have any questions this is the place for them.

[Traceyannabanna]

OK thanks I will have another look at it