Creating a new record using a form

[Andrew33]

Hi All,

I’m very new to Access and any help is greatly appreciated. I’m using Access 2003 and I am creating a database to track medical locums in a placement program. The program enables doctors to go on leave and be temporarily replaced by another doctor.

I have 4 tables; a table with details regarding the locum (a doctor who fills a position in a hospital when another goes on leave), a table with details regarding the doctor who is going on leave, a table with details regarding the hospital and finally a placement table. Details on the tables include common attributes such as names, addresses etc.

The placement table has the ID numbers (primary keys) of the locum, doctor on leave and the hospital. It also has a date in which the placement occurs. The primary key for the placement table is a combination of the locum, doctor on leave, hospital and date fields. This is to ensure that every record is unique. The table below is the Placement table.

LocumID|HospitalID|DocOnLeaveID|PlacementStart|PlacementEnd
10334 9 2 12/04/2009 13/04/2009
10334 9 3 19/04/2009 20/04/2009
10334 9 3 20/04/2009 22/04/2009
10334 11 1 26/03/2009 27/03/2009
12345 9 1 5/04/2009 6/04/2009
12345 11 3 12/05/2009 13/05/2009

What I am having trouble with is creating a form that I can use to create a new record in the placement table (above). I can do this through a query but only by using the ID NUMBERS of the locum, doctor on leave and hospital.

How do I create a dropdown box that I can select the NAMES of the locum, doctor on leave and hospital that already have records in their respective tables and create a new record in the placement table? I need to use the names as a user will not know the ID numbers of the 3 entities.

Thank you for any help provided.

Cheers,
Andrew

 

[Remou]

You can add a combobox control to your form. Let us take locum as an example:

[tt]Data
Control Source: LocumID 'This is the field to be filled in
Row Source Type: Table / Query
Row Source: SELECT LocumID, LastName & ", " & FirstName FROM LocumTable
Bound Column: 1

Format:
Column Count: 2
Column Widths: 0cm; 2cm[/tt]

This will allow the user to see the name and select it and the form to fill in the relevant ID.

There is an added point if you are using a form / subform set-up. Let us say that you base your for on the doctors table and the subform on the Placement table, you can set the Link Master Fields to, say, DocID, and the Link Child Field to DocOnLeaveID and Access will automatically fill in the link child field with the appropriate DocID (you can have more than one linked field).

http://lessthandot.com

 

[Andrew33]

Thanks Remou. After many hours I have have it working.

Cheers.