C
Cyberwolf0000
Hi all,
I am adding records to 2 tables based on the entry in a form that is bound to
a 3rd table. Here is the code. All the tables are related as such
ReprintsData has a PK or ReprintID
ReasonCodes has a PK of ReasonCodeID and an FK or ReprintID
RPPCauseInfo has a PK of RPPCauseInfoID and a FK of ReasonCodeID
Private Sub cmbR_Number_AfterUpdate()
Dim strSQL As String
Dim rec As DAO.Recordset
Dim rec1 As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim rec3 As DAO.Recordset
Dim rec4 As DAO.Recordset
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bolInTrans As Boolean
Set ws = DBEngine(0)
' Error Handler
'On Error GoTo cmbR_Number_AfterUpdate_Error
'Get the associated PO number, Op Code, ROll Number, Net Impressions,
Operator Initials & Press for later use
strSQL = "SELECT Val(PREPRINT_ROLL_NUMBER)As RollNumber,
NET_IMPRESSION_COUNT As NetImps, OPERATOR_INITIALS As OPInit, Val
(PRODUCTION_ORDER_NUMBER) AS PONum, OPERATION_CODE AS OpCode, Val(Right
(PRESS_ID,3)) As Press " & _
"FROM WEB_DEV_PRINTEDLABELS " & _
"WHERE BATCH_NUMBER ='" & UCase(Me.cmbR_Number) & "';"
Set rec = CurrentDb().OpenRecordset(strSQL)
If rec.RecordCount > 0 Then
rec.MoveFirst
'Populate the Operators initials field
Me.RNOperatorID = rec!opinit
'Populate the roll number field
Me.RollNumber = rec!RollNumber
'Populate the Net Imps field
Me.NetImps = rec!NetImps
Else
'The must have manually typed a number that does not exist
Call MessageBox("No records were found that match the R-Number
provided. Please check the number and try again.|False|&OK|False|Invalid
Entry!")
End If
strSQL = "SELECT RC.ReasonCodeID " & _
"FROM tblProjects AS P LEFT JOIN ((ReprintsData AS RD LEFT JOIN
ReasonCodes AS RC ON RD.ReprintID = RC.ReprintID) LEFT JOIN RPPCauseInfo AS
RP ON RC.ReasonCodeID = RP.ReasonCodeID) ON P.PO_ID = RD.JobNumID " & _
"WHERE P.PONumber='" & rec!PONum & "' AND RD.R2RReq= True;"
Set rec4 = CurrentDb().OpenRecordset(strSQL)
'Check to see if there is already a related record in ReasonCodes
If rec4.RecordCount > 0 Then
rec4.MoveFirst
Me.ReasonCodeID = rec4!ReasonCodeID
Else
'If not then start a transaction and add a record to ReprintsData and
ReasonCodes
ws.BeginTrans
bolInTrans = True
Set db = ws(0)
'Get the PO_ID number for use in the ReprintsData table
strSQL = "SELECT PO_ID " & _
"FROM tblProjects " & _
"WHERE PONumber='" & rec!PONum & "';"
Set rec1 = CurrentDb().OpenRecordset(strSQL)
If rec1.RecordCount > 0 Then
'Add the record to the ReprintsData table
strSQL = "INSERT INTO ReprintsData (ReprintType, ReprintCreateDate,
JobNumID, R2ROpCode, InitialR2RJobNum, Completed, DateCompleted,
Cancel_CompleteBy, R2RReq) " & _
"VALUES ('Bump_Up', #" & Now() & "#," & rec1!PO_ID & ",'" & rec!
OpCode & "','" & rec!PONum & "', True, #" & Date & "#, 'System', True);"
db.Execute strSQL, dbFailOnError
' Get the ReprintID for use in the ReasonCodes table
strSQL = "SELECT ReprintID " & _
"FROM ReprintsData " & _
"WHERE JobnumID=" & rec1!PO_ID & " AND R2ROpCode='" & rec!OpCode
& "' AND InitialR2RJobNum='" & rec!PONum & _
"' AND R2RReq= True AND Completed= True AND Cancel_CompleteBy=
'System';"
Set rec2 = db.OpenRecordset(strSQL)
rec2.MoveFirst
'Add the record to the ReasonCodes table
strSQL = "INSERT INTO ReasonCodes (ReprintID, Reason, ReasonFreq,
ReasonImps, ReasonComments) " & _
"VALUES (" & rec2!ReprintID & ",'Rejected Preprint',1," & rec!
NetImps & ",'This roll was rejected by the Roll to Roll Department');"
db.Execute strSQL, dbFailOnError
'Get the ReasonCodeID for use in this record
strSQL = "SELECT ReasonCodeID " & _
"FROM ReasonCodes " & _
"WHERE ReprintID=" & rec2!ReprintID & " AND Reason='Rejected
Preprint' AND ReasonFreq=1 AND ReasonImps=" & rec!NetImps & " AND " & _
"ReasonComments='This roll was rejected by the Roll to Roll
Department';"
Set rec3 = db.OpenRecordset(strSQL)
Me.ReasonCodeID = rec3!ReasonCodeID
End If
'If there were no errors then commit the transaction
ws.CommitTrans
bolInTrans = False
End If
'Clean-up open objects
AddRec_End:
Set rec = Nothing
Set rec1 = Nothing
Set rec2 = Nothing
Set rec3 = Nothing
Set rec4 = Nothing
Set db = Nothing
If bolInTrans Then ws.Rollback
Set ws = Nothing
On Error GoTo 0
Exit Sub
cmbR_Number_AfterUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmbR_Number_AfterUpdate of VBA Document Form_frm_RejectedRolls"
End Sub
This all works fine. but when I attempted to move to another record I get the
Jet error
"The Microsoft Jet database engine cannot find a record in table
'ReasonCodes' with key matching field(s) 'ReasonCodeID'.
I checked the 2 other tables and there is a related record. Why isn't Access
seeing the record in question?
--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2
I am adding records to 2 tables based on the entry in a form that is bound to
a 3rd table. Here is the code. All the tables are related as such
ReprintsData has a PK or ReprintID
ReasonCodes has a PK of ReasonCodeID and an FK or ReprintID
RPPCauseInfo has a PK of RPPCauseInfoID and a FK of ReasonCodeID
Private Sub cmbR_Number_AfterUpdate()
Dim strSQL As String
Dim rec As DAO.Recordset
Dim rec1 As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim rec3 As DAO.Recordset
Dim rec4 As DAO.Recordset
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bolInTrans As Boolean
Set ws = DBEngine(0)
' Error Handler
'On Error GoTo cmbR_Number_AfterUpdate_Error
'Get the associated PO number, Op Code, ROll Number, Net Impressions,
Operator Initials & Press for later use
strSQL = "SELECT Val(PREPRINT_ROLL_NUMBER)As RollNumber,
NET_IMPRESSION_COUNT As NetImps, OPERATOR_INITIALS As OPInit, Val
(PRODUCTION_ORDER_NUMBER) AS PONum, OPERATION_CODE AS OpCode, Val(Right
(PRESS_ID,3)) As Press " & _
"FROM WEB_DEV_PRINTEDLABELS " & _
"WHERE BATCH_NUMBER ='" & UCase(Me.cmbR_Number) & "';"
Set rec = CurrentDb().OpenRecordset(strSQL)
If rec.RecordCount > 0 Then
rec.MoveFirst
'Populate the Operators initials field
Me.RNOperatorID = rec!opinit
'Populate the roll number field
Me.RollNumber = rec!RollNumber
'Populate the Net Imps field
Me.NetImps = rec!NetImps
Else
'The must have manually typed a number that does not exist
Call MessageBox("No records were found that match the R-Number
provided. Please check the number and try again.|False|&OK|False|Invalid
Entry!")
End If
strSQL = "SELECT RC.ReasonCodeID " & _
"FROM tblProjects AS P LEFT JOIN ((ReprintsData AS RD LEFT JOIN
ReasonCodes AS RC ON RD.ReprintID = RC.ReprintID) LEFT JOIN RPPCauseInfo AS
RP ON RC.ReasonCodeID = RP.ReasonCodeID) ON P.PO_ID = RD.JobNumID " & _
"WHERE P.PONumber='" & rec!PONum & "' AND RD.R2RReq= True;"
Set rec4 = CurrentDb().OpenRecordset(strSQL)
'Check to see if there is already a related record in ReasonCodes
If rec4.RecordCount > 0 Then
rec4.MoveFirst
Me.ReasonCodeID = rec4!ReasonCodeID
Else
'If not then start a transaction and add a record to ReprintsData and
ReasonCodes
ws.BeginTrans
bolInTrans = True
Set db = ws(0)
'Get the PO_ID number for use in the ReprintsData table
strSQL = "SELECT PO_ID " & _
"FROM tblProjects " & _
"WHERE PONumber='" & rec!PONum & "';"
Set rec1 = CurrentDb().OpenRecordset(strSQL)
If rec1.RecordCount > 0 Then
'Add the record to the ReprintsData table
strSQL = "INSERT INTO ReprintsData (ReprintType, ReprintCreateDate,
JobNumID, R2ROpCode, InitialR2RJobNum, Completed, DateCompleted,
Cancel_CompleteBy, R2RReq) " & _
"VALUES ('Bump_Up', #" & Now() & "#," & rec1!PO_ID & ",'" & rec!
OpCode & "','" & rec!PONum & "', True, #" & Date & "#, 'System', True);"
db.Execute strSQL, dbFailOnError
' Get the ReprintID for use in the ReasonCodes table
strSQL = "SELECT ReprintID " & _
"FROM ReprintsData " & _
"WHERE JobnumID=" & rec1!PO_ID & " AND R2ROpCode='" & rec!OpCode
& "' AND InitialR2RJobNum='" & rec!PONum & _
"' AND R2RReq= True AND Completed= True AND Cancel_CompleteBy=
'System';"
Set rec2 = db.OpenRecordset(strSQL)
rec2.MoveFirst
'Add the record to the ReasonCodes table
strSQL = "INSERT INTO ReasonCodes (ReprintID, Reason, ReasonFreq,
ReasonImps, ReasonComments) " & _
"VALUES (" & rec2!ReprintID & ",'Rejected Preprint',1," & rec!
NetImps & ",'This roll was rejected by the Roll to Roll Department');"
db.Execute strSQL, dbFailOnError
'Get the ReasonCodeID for use in this record
strSQL = "SELECT ReasonCodeID " & _
"FROM ReasonCodes " & _
"WHERE ReprintID=" & rec2!ReprintID & " AND Reason='Rejected
Preprint' AND ReasonFreq=1 AND ReasonImps=" & rec!NetImps & " AND " & _
"ReasonComments='This roll was rejected by the Roll to Roll
Department';"
Set rec3 = db.OpenRecordset(strSQL)
Me.ReasonCodeID = rec3!ReasonCodeID
End If
'If there were no errors then commit the transaction
ws.CommitTrans
bolInTrans = False
End If
'Clean-up open objects
AddRec_End:
Set rec = Nothing
Set rec1 = Nothing
Set rec2 = Nothing
Set rec3 = Nothing
Set rec4 = Nothing
Set db = Nothing
If bolInTrans Then ws.Rollback
Set ws = Nothing
On Error GoTo 0
Exit Sub
cmbR_Number_AfterUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmbR_Number_AfterUpdate of VBA Document Form_frm_RejectedRolls"
End Sub
This all works fine. but when I attempted to move to another record I get the
Jet error
"The Microsoft Jet database engine cannot find a record in table
'ReasonCodes' with key matching field(s) 'ReasonCodeID'.
I checked the 2 other tables and there is a related record. Why isn't Access
seeing the record in question?
--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2