How do I populate a from from another Table

By Chapel6 ·
Tags: Software
I am working Access 2016 and am a novice at visual basic. I am working on a database which has two tables, Patients and Visits. I want to be able to populate a form by using the Patients name, address, post Code to the form which will be saved in the visit Table. I do not have a common key other than the standard ID key. On the form I have fields which match the Patients Table and addition fields which will be filled in by the operator, like date of appointment, means of transport etc. The question is do I need code to transfer the information from the patient table to the visit table form and can I use a combo box to select the information to be transferred. Thanks for your help.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Create a custom key.

by databaseben In reply to How do I populate a from ...

The standard key that access provides is not always the best method between linking 2 tables.
What you will need to do is to assign a key that is more customize to the patient.
For example, lets say there are 1000 patients. Therefore, access will assign a key from number 1 to number 1000, for each patient, even if the patients name is identical.
Alternatively, a key could be assigned to each patient room. So if you have 200 patient rooms, then access will assign a number to each between 1 and 500.

So the solution is to create create 3 tables. Table one will store patient info. Table 2 will store a list of the rooms, Table 3 will have visitors

Table 2 will have 2 keys. 1 key will be the standard primary key that access assigns. This key records the physical record/line item.

The secondary key will have to be customized. For example, if patients names is John Doe, then the key could naturally be JD. But this is too common of a key. So you could create a key for John Doe that was born on 1/1/2017, which could be JD010117. But what if there is a Jane Doe that was also born on 1/1/2017. Then you could create a key based on John Doe born on 1/1/17 and lives in zip code 99001.

But the above are only examples and using birth dates is not a good idea due to security hackers. But you could also use a telephone number to create a custom key or if the hospital records management system provides their patients with an account number or patient code, then use it instead.

Once you create a custom key for the patient, i think you will see the logic and everything will fall in place for you.


Related Discussions

Related Forums