Linking tables/forms

[Joel Griffith]

I have a database that hold information about Employees
It has 4 tables within it, these are as follows:

Employee_Personal_Information (Table 1, Main Table)

PKEmployNatINS
Employdob
Employsurname
Employfirstname
Employtitle
Employhousename
Employaddress
Employtown
Employcity
Employcounty
Employpostcode
Empoloytelnumber
Employmobile
Employsecondtelnumber
Employfaxnumber
Employemail
Employnotes

Employee_Emergency_Information (Table 2, Subform)

PKEmployNatINS
EmployRelationship
EmployRelsurname
EmployRelfirstname
EmployReltitle
EmployRelhousename
EmployReladdress
EmployReltown
EmployRelcity
EmployRelcounty
EmployRelpostcode
EmpoloyReltelnumber
EmployRelmobile
EmployRelCompany
EmployRelCompanynumber
EmployRelextention
EmployRelNotes

Employee_Employment_Information (Table 3, Subform)

PKEmployNatINS
EmployEmpJobtitle
EmployEmpStartdate
EmployEmpEnddate
EmployEmpNotes

Employee_Driving_Information (Table 4, Subform)

PKEmployNatINS
EmployDriveYesNo
EmployDriveLicNo
EmployDrivePassDate
EmployDrivePointsYesNo
EmployDriveNotes



Above are all my tables that I have in the database, all
the subforms should be linked back to the main form. by
buttons or the main form.

I have linked all the tables in the relationship window
from the main form to eech table with a 1 to 1
relationship.

I was told it would beasier for me to create 1 Main form
and the rest should be subforms.

When I created the subforms I have created them in a
single form view, to make it look like they are seperate
forms.

The prolem I have is that when I enter data in the Main
form, I then click on one of the buttons to complete a
subform, the forms opens blank like. I want it to open as
a relation of the main form with the Employee National
Insurance number copied over so I know they are one and
the same.

The Code I am using is below:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Employee_Emergency_Information subform"

stLinkCriteria = "[EmpNatins]=" & "'" & Me!
[EmpNatIns] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Would some ne please help with this as I am losing my
mind over it

Regards Joel

 

[John Vinson]

Would some ne please help with this as I am losing my
mind over it

Open your main form in Design view.

Drag one of your other forms from the Form window onto it.

This is a "Subform" now. What you are doing is opening a separate,
freestanding form - *NOT A SUBFORM*. A Subform is a control on a
mainform which contains another form - you don't need any buttons, you
don't need any code! It maintains the link for you, based on its
Master Link Field/Child Link Field properties.