Code for opening new form and taking data from another

[Davidp]

I work for a UK charity giving out free advice to the public on energy efficiency and we have an Access 2007 database split into one backend and multiple user front ends.
I’m not good at coding as I do it so infrequently so wonder if someone can help.

The db has a form called “ClientFormNew” which logs client details into a table called “Clients”.
Each client has a unique client ID in the table called “ClientID”.
Sometimes Clients call back for further information and we want to log that information into a related table called “ClientHistory”
Each record in the “ClientHistory” table has a unique ID called “HistoryID” which is the same number as the “ClientID” so these fields are related.

When a client calls back and the telephone adviser opens up the “ClientFormNew” form and discovers they have called before they need to click on a button called “Client History” which then opens up the “ClientHistoryForm”
This way they can put in details of the new call.
I am struggling with the code to put on the “Client History” button as I want the new form to display the details of the client’s first contact then allow details of the second contact to be added in a new row. Also the new form needs to show all that client past history which would be in the form of the following fields from the “ClientHistory” table.
HistoryID, ContactDate, Name, ContactMethod, ContactReason, AdviceGiven, Notes.

Many thanks for any help, David

 

[Mr Smin]

Are you allowed to modify the first form? If so, you might like to have a sub-form within it showing the history, without any button clicking.

Design a very simple form based on the ClientHistory table and add it as a sub-form to the first form. At this point you should be prompted for the field which should link the two - choose ClientID from the first form and HistoryID from the Subform.
This does depend on the process in which new clients are added to your system so it might not suit your purposes.

btw your naming is a bit confusing. I would have ClientID in the clients table and fkClientID in the History table (fk as in foreign key). I would otherwise assume that HistoryID was the primary key on the History table.

 

[Davidp]

many thanks for the fast reply Mr Smin.
The HistoryID field is not a Primary Key as it can have many records with the same HistoryID, (EG if a client calls several times). But the ClientID from which it is derived is indeed the Pk for the Clients table. It's a one to many relationship. Hope this makes sense.
Kind regards, David

PS: I did wonder about a subform but can that display data from two different tables and be updateable.