Help with form

[Traceyannabanna]

Hi I am very new to Access and am trying to set up a financial database for my company. I have 3 tables. TblClients, TblInvestments,TblLifeInsurance and will have another 5 different types of insurances. I want to open my form Investments, choose my client from a dropdown box, view their investments (all works great so far) but also to be able to add a new investment. I added a button to the form with a macro to "add new record". When i click this button, a blank form appears but the clients ID is not populated. Can anyone advise please?

[ArviLaanemets]

Hi

Create a single form fClients, where you can add new clients, Edit client info, find an existing client and display all incfo connected with him etc.

Create a continuous form fInvestments, where info about all existing investments are displayed. Hide the client ID field (set it invisible) and remove client id header.

Open fClients form in design mode, and drag the fInvestments form into it. To avoid problems later, rename the subform as sfInvestments (i.e. obects order is fClients>sfInvestments>fInvestments)

For sfInvestments set parent and child links both as [clientID]


Now, whenever you add a new investment in subform, it will be automatically have ClientID same, as was for active client in parent form.

[Traceyannabanna]

Thank you for your help. I have done this apart from setting the parent/child links. How do i do this in the form?

[ArviLaanemets]

Open client form in editing mode. Open form Properties (right-click on form) when they aren't opened by default;
Click on subform - on top of Property Sheet you must see subform name in dropdown.

On Property List:
Into field Link Master Fields enter field(s) in parent form, you want the subform to be linked to. (P.e. [ClientID], or [Field1];[Field2]);
Into field Link Child Fields enter field(s) in subform, you want to be linked to. parent form (P.e. [ClientID], or [Field1];[Field2]);
PS1. The number of fields in both properties must be equal;
PS2. MS Acces Help declares, that you must use only table fields to create the link. It is not true - you can refer to any unbound controls as Master links too.

Save the form. When you did all right:
In subform, only records linked to parent form are displayed;
Any new record added to subform will be automatically linked to parent form, i.e. has link fields filled.

[Traceyannabanna]

Ok thanks again. I checked and Link master Field and link child field are both set up to client ID. However, if I add a button/macro to add new record-the client ID is blank?

[ArviLaanemets]

Why do you need a button? Simply add new record into subform (Unless you disabled it, at bottom of form you have a navigation bar, and a button (small black arrow * an yellow something) to right of it for adding a new record (you also can move from last record to next).

I think your button adds a new record to table - then the form doesn't know anything about it, and can't link the record at all. When you want to use the button anyway, you code must create a new record for subform, and then when you leave the new entry, then the subform adds the new record to table

[Traceyannabanna]

Thank you -it works that way so thats brilliant-at least I have a way. I just wanted to make it easier for the user to add a new record