Join key of table not in recordset problem

[cclark9589]

I'mmm baaaaacccccckkkkk!

Got another one here that is driving me buggy. I've searched the forum and I'm just not finding an answer but that could because I'm not framing my search parameters right. I digress.

I have two databases. The main db holds time and billing information for jobs. The second (new) db is for the status reports I have to produce. Once a job is completed I need to record certain information specific to that job in a status report for the trustees. I'm trying to steamline the current process but I've hit a brick wall or actually in this case a run-time error wall.

On the Job form in the main db there is a button to Add to Status Reports. Clicking on this copies some of the job specific data and is supposed to paste that information into the _tbl_Status_qry form in the new db. It appears the copy procedure works fine, the second db opens but I get this run-time error:

Run-time error '3348': Cannot add record(s); join key of table 'tbl_Status' "not in recordset." Clicking the Help button provides absolutely no help whatsoever and I can't find anything specific to this error in the forum.

Here is the code for the click event on the Add to Status button:

Private Sub cmdStatusRpt_Click()

'Me.AllowEdits = True

Me.JobID.Enabled = True 'enables specific controls to allow copy
Me.FundID.Enabled = True
Me.EmpIDCombo.Enabled = True
Me.YrSelect.Enabled = True
Me.TypeCombo.Enabled = True
Me.AudID_Combo.Enabled = True
Me.LtrDate.Enabled = True
Me.FldDate.Enabled = True
Me.RptDate.Enabled = True
Me.ExamPer.Enabled = True
Me.Location.Enabled = True

DoCmd.GoToControl "JobID" 'copies job specific info for status report
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "FundID"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "EmpIDCombo"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "YrSelect"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "TypeCombo"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "AudID_Combo"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "LtrDate"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "FldDate"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "RptDate"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "ExamPer"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "Location"
DoCmd.RunCommand acCmdCopy
Me.JobID.Enabled = True 'disables specific controls to after copy
Me.FundID.Enabled = True
Me.EmpIDCombo.Enabled = True
Me.YrSelect.Enabled = True
Me.TypeCombo.Enabled = True
Me.AudID_Combo.Enabled = True
Me.LtrDate.Enabled = True
Me.FldDate.Enabled = True
Me.RptDate.Enabled = True
Me.ExamPer.Enabled = True
Me.Location.Enabled = True

Dim accapp As Access.Application 'opens up WP Status db
Set accapp = New Access.Application
accapp.OpenCurrentDatabase ("c:\Users\Charlie\Documents\Access Files\WPAS PR Program\Database\WP_Status.accdb")
accapp.Visible = True
accapp.DoCmd.RunCommand acCmdAppMaximize
accapp.UserControl = True
Set accapp = Nothing
End Sub

Here is the code in the Status db Form_Load event:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.FundID.Enabled = True 'enables specific controls before pasting info
Me.JobID.Enabled = True
Me.EmpIDCombo.Enabled = True
Me.YrSelect.Enabled = True
Me.TypeCombo.Enabled = True
Me.AudID_Combo.Enabled = True
Me.LtrDate.Enabled = True
Me.FldDate.Enabled = True
Me.RptDate.Enabled = True
Me.ExamPer.Enabled = True
Me.Location.Enabled = True

DoCmd.GoToControl "FundID" 'paste info from Jobs form
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "JobID"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "EmpIDCombo"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "YrSelect"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "TypeCombo"
oCmd.RunCommand acCmdPaste
DoCmd.GoToControl "AudID_Combo"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "LtrDate"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "FldDate"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "RptDate"
oCmd.RunCommand acCmdPaste
DoCmd.GoToControl "ExamPer"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "Location"
DoCmd.RunCommand acCmdPaste

Me.FundID.Enabled = False 'disables specific controls after pasting info
Me.JobID.Enabled = False
Me.EmpIDCombo.Enabled = False
Me.YrSelect.Enabled = False
Me.TypeCombo.Enabled = False
Me.AudID_Combo.Enabled = False
Me.LtrDate.Enabled = False
Me.FldDate.Enabled = False
Me.RptDate.Enabled = False
Me.ExamPer.Enabled = False
Me.Location.Enabled = False
End Sub

The form uses _tbl_Status_qry as the record source, here is the SQL statement:

SELECT tbl_Status.FundID, tbl_Status.JobID, tbl_Status.YrSelect, Employers.Employer, tbl_Status.EmpID, JobType.Type, Employees.Init, Employees.AudID, tbl_Status.LtrDate, tbl_Status.FldDate, tbl_Status.RptDate, tbl_Status.ExamPer, tbl_Status.AcctgFees, tbl_Status.HW, tbl_Status.DBPen, tbl_Status.Appr, tbl_Status.Vac, tbl_Status.DCAnn, tbl_Status.VarAnn, tbl_Status.LMCC_NLMCC, tbl_Status.SAP, tbl_Status.NECA, tbl_Status.AMF, tbl_Status.NEBF, tbl_Status.LD, tbl_Status.Int, tbl_Status.TotDiscr, tbl_Status.AmtsColl, tbl_Status.ContribsPD, Employers.Location
FROM ((tbl_Status INNER JOIN Employers ON tbl_Status.EmpID = Employers.EmpID) INNER JOIN Employees ON tbl_Status.AudID = Employees.AudID) INNER JOIN JobType ON tbl_Status.TypeID = JobType.TypeID
ORDER BY tbl_Status.FundID, tbl_Status.YrSelect DESC , Employers.Employer, tbl_Status.RptDate;

When I try to run the procedure and click Debug it always stops on the line DoCmd.RunCommand acCmdPaste, the second line in the past section above.

I've tried all sorts of things but being an Access dummy not knowing precisely what I'm doing, I haven't been able to solve this one on my own.

Can someone help me out with this? Please remember I know just enough to be dangerous to myself and don't know all the technical jargon so try to keep any explanation/pointer/guidance simple.

Thanks in advance.

 

[SpentGeezer]

"INNER JOIN JobType ON tbl_Status.TypeID = JobType.TypeID"

TypeID is missing?

 

[cclark9589]

TypeID was missing from my statement but that didn't turn the trick. I still get the same error and it stops at the same spot which is the first paste command.

Might it be possible that I have to do each copy and past command one at a time i.e., copy JobID from the Jobs frm in the Time and Billing db and then paste to JobID in the tbl_Status_qry form in the Status db?

If so, can I move back and forth between the two db's? How in heck do I select them?

I have even tried putting the form in my Time and Billing db, then linking to the status table in the Status db but still get the same error. If I rem out the first goto and paste action it crashes on the next paste command and so on.

I've looked at the SQL statement until I see double (doesn't take long) but sure as heck can't figure out what is causing the error because I sure as heck can't figure out which join key is being referred to.

 

[SpentGeezer]

Can you just use an append querry rather than copy and pasting everything?

 

[cclark9589]

I don't believe so. There is 10 or 11 fields that are job specific that have already been entered on the job form and that information has to be on the status reports as well. My thought, or hope, was to get all that done without having to re-enter everything. I'd rather only have to enter things once but I might be able to combine a couple of other queries but I still need some job specific data that I'd rather not have to enter twice. Make sense?