Create Form with fixed Row Headings

[Stef2017]

Hello,
I am newbie to Access 2016 and I would like your assistance on a project.
I would like to keep a record of employee documentation. All the employees should have a specific number of documents (i.e ID, Passport, Criminal Record, etc.) and I need to keep information for each of these documents such as document Number, issue date, expiry date, and issuing authority.

I have created three tables as per below:

tbl_Employees		tbl_DocumentType		tbl_Documents
EmployeeID	PK	DocumentID	PK	DocumentID	FK
Employee Full Name		DocumentName		DocumentName
				Issue Date
				Expiry Date
				Document Number
				Issuing Authority
				EmployeeID	FK

Now I would like to create a form where I can insert for each Employee all his documents with information for these documents as per the below example.

EmployeeID:	1
Employee Full Name:	Mark Johnes
DocumentName	Document Number	Issue Date	Expiry Date	Issuing Authority
Passport	A111111	15/10/2016	15/10/2021	Police
ID	B222222	10/09/2009	-	Police
Criminal Record	C333333	12/12/2016	12/12/2017	Police
Training Certificate	D444444	14/05/2016	14/05/2017	Training Center
Medical certificate	E555555	22/06/2017	22/06/2018	Medical Center

Where the Document Name column is a predefined list of specific number of documents (in this example 5 documents), meaning that the form I need must present a table as per the above with fixed 5 Heading Rows as per the tbl_DocumentType (in this example 5 rows).
So far I have not managed to create such form as it is so difficult for me to tell Access to fix the table with rows from tbl_DocumentType.
The only I have managed so far is a form that will present only each document that can find for each employee in the table “tbl_Documents”.

I am sure there is an easy way out but I cannot find it, please help….

[pbaldy]

Typically you would use a form bound to the employee table with a subform bound to the documents table. Master/child links would keep them in sync. If desired, you could use an append query to seed the documents table with empty records for each document type for an employee.

[Stef2017]

Typically you would use a form bound to the employee table with a subform bound to the documents table. Master/child links would keep them in sync. If desired, you could use an append query to seed the documents table with empty records for each document type for an employee.

Firstly i want to thank you for the prompt response. It is much appreciated.

Well i did the bound form which is basically a form which for every employee ID form the table of employees has a sub form with the documents of each person. The problem is that the sub form displays ONLY the documents that it will find in the table tbl_Documents. So if a person does not have a Passport then the row PSP will not show in the subform.

All i want to do is, in the subform to have a predefined list of documents rows (from table tbl_DocumentType) regardless if the peron has the specific document type inside the Documents table.

I have attached a template of my project.

The other thing with the Append query i did not quite understand to be honest. How would that work? Does the Append Query can add all types of documents for each employee ID in the tbl_Documents? Can this be done??? This might work i guess but dont know how to do it, some tips plz if not asking much..

Stef.

[pbaldy]

Create an append query that appends to the documents table, selects from the document types table, and gets the employee ID from the form. Then you can fire that off from a button or the after insert event of the main form (when you add a new employee).

[Stef2017]

Create an append query that appends to the documents table, selects from the document types table, and gets the employee ID from the form. Then you can fire that off from a button or the after insert event of the main form (when you add a new employee).

Thank you again.

I already tried that append query and it works quite good. the problem is that i cannot associate the Employee ID from the form, only from the table i can. Could you plz be more specific if this is not too much to ask?
maybe a screenshot of the append query or maybe the code for the button.
Thank you in advance.

[pbaldy]

In design view of the query, in the field argument you'd have:

EmployeeID: [Forms]![FormName]![TextboxName]

You can use this behind the button:

DoCmd.OpenQuery "QueryName"

[Stef2017]

In design view of the query, in the field argument you'd have:

EmployeeID: [Forms]![FormName]![TextboxName]

You can use this behind the button:

DoCmd.OpenQuery "QueryName"

I made the button work with macro, so thank you very much. The after insert event of the main form also works perfect!
The Append query (if i got it right) should look something like the

[pbaldy]

That probably works, but it wasn't my suggestion, which was this in the field argument:

EmployeeID: [Forms]![FormName]![TextboxName]

and you wouldn't have the employee table in the grid.

[pbaldy]

Welcome to the site by the way!

[Stef2017]

Welcome to the site by the way!

Thanks a lot!
Although i did not understand what you mean with "field argument" i tried to put the EmployeeID: [Forms]![FormName]![TextboxName] (which by the way i wrote it as EmployeeID: [Forms]![frm Employee Details]![EmployeeID] in my case) in the field, and criteria box also, but with no success.

with the Like function seems to work. Now the challenge is to instruct the append query not to copy duplicates!!
Any suggestion would be more than welcome.

By the way i am covered in most of this thread,if you do not wish to help with the above "duplicates" challenge i will understand.
Lastly, you are my dude!!!!

You are very helpful, thank you.

[pbaldy]

It should only have been in the field argument, not the criteria. Not a big deal, your way works.

If you execute it in the insert event there couldn't be duplicates, could there? You have the two fields as key fields, so I'm surprised it allows duplicates. Do they say no duplicates in the Indexed property in the table?

I'm happy to be your dude, but don't tell my wife!!

[davegri]

Take a look at this simplified approach:
Stef2017-v01.zip
The documents table is actually a many-to-many linking structure between the document type and the employee.
When you add a new employee, it will automatically add the document type rows to the subform.