Cannot add Record

[KristenD]

I changed my tables around in my db so they make a bit more sense then created a form for entry.

I have added the following code to the form as one of the tables only needs to be filled out if certain criteria are met.

Private Sub Form_AfterUpdate()
Call SetCntrlProperties
End Sub
Private Sub Form_Current()
Call SetCntrlProperties
End Sub
Private Sub SetCntrlProperties()
    Select Case Me.cboEmploymentStatus
    Case "Eligible", "No Rehire", "Disability"
        Me!OSHATraining.Form!JobNumber.Enabled = False
        Me!OSHATraining.Form!JobNumber.Locked = True
        Me!OSHATraining.Form!EmployeeType.Enabled = False
        Me!OSHATraining.Form!EmployeeType.Locked = True
        Me!OSHATraining.Form!CraftCode.Enabled = False
        Me!OSHATraining.Form!CraftCode.Locked = True
    Case Else
        Me!OSHATraining.Form!JobNumber.Enabled = True
        Me!OSHATraining.Form!JobNumber.Locked = False
        Me!OSHATraining.Form!EmployeeType.Enabled = True
        Me!OSHATraining.Form!EmployeeType.Locked = False
        Me!OSHATraining.Form!CraftCode.Enabled = True
        Me!OSHATraining.Form!CraftCode.Locked = False
End Sub

Now when I go to enter in the form it tells me on the bottom: Cannot add records; join key of table tblOSHA not in recordset.

The table is the sub form and is required to be filled out but only in the other table is it reliant on being filled out. I hope I'm making sense.

I have attached the relationship report as well so you can see the tables.

Thank you looking at it and thank you again for help with my previous questions regarding this db!

 

[John Big Booty]

Which table forms the Record Source of your Main Form?

Have I understood correctly that table tblOSHA forms the Record Source of your Sub Form?

 

[KristenD]

It is actually a combination of 2 tables and a look up table. So tblOSHA must be filled out and then tblEmployeeInfo should be filled out if on the Main Form the EmploymentStatus is "Active"

 

[KristenD]

When I went in to take a look at the code it is highlighting this :

Private Sub SetCntrlProperties()

and telling me there is a compile error.

 

[KristenD]

I have been working on this and I finally got it to add a record by adding :

Option Compare Database
Option Explicit

But once I do that it doesn't run the SetCntrlProperties so all the fields are enabled and therefore setting up the error that it can't find a record in the tblJobs with a key matching field(s) JobNumber.

If I add an autonumber to tblJobs so it looks like :

tblJobs
*JobNumID
*JobNumber
*JobSite

Then for the look up field it will just correspond to the tblOSHA like it is currently with the JobNumber and it won't be using the primary key.

Is that where the error is occurring because I'm using a lookup field in the table and form with a primary key?

I know I didn't have this error on the previous form because all the look up field primary key is used in every record.

 

[KristenD]

SELECT [tblOSHA].[OSHAID], [tblOSHA].[EmployeeID], [tblOSHA].[Osha10Hour], [tblOSHA].[TrainingDate], [tblOSHA].[Document], [tblOSHA].[Osha30Hour], [tblOSHA].[TrainingDate1], [tblOSHA].[Document1], [tblEmpInfo].[EmployeeInfoID], [tblEmpInfo].[EmployeeType], [tblEmpInfo].[CraftCode], [tblEmpInfo].[JobNumber], [tblJobs].[JobSite], tblOSHA.EmployeeInfoID AS EmployeeInfoID_tblOSHA FROM (tblJobs INNER JOIN tblEmpInfo ON tblJobs.JobNum=tblEmpInfo.JobNumber) INNER JOIN tblOSHA ON tblEmpInfo.EmployeeInfoID=tblOSHA.EmployeeInfoID;

This is the record source for the OSHA Training form.