S
Stapes
Hi
I seem to be in a Catch 22 here. I Have a Combo box from which the
user selects a description. Then I am using a query to get the rest of
the COURSE details. The current record, from the table ATTEND is
linked to the Table COURSE by the field COURSE_NO. If I try to set Me!
COURSE to rs!COURSE_NO before doing the SaveRecord, it won't let me. I
get error 3331 To make changes to this field, first save the record.
If I try doing the SaveRecord first, as here, I get error 3101 The
Microsoft Jet database engine cannot find a record in the table
'COURSES' with key matching field(s) 'COURSE_NO'. Probably because
that key field has not been set yet. Is there any way around this?
Private Sub COURSE_DESCRIPTION_AfterUpdate()
On Error GoTo CDAU_Err
'fill in other fields from COURSE
Me![PAYROLL NUMBER] = [Forms]![PEOPLE]![PAYROLL]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim db As Database, qd As QueryDef, rs As Recordset, strsql As String
Set db = CurrentDb
Set qd = db.QueryDefs("qryCOURSEDescription")
qd.Parameters("pCOURSE").Value = Me![COURSE DESCRIPTION]
Set rs = qd.OpenRecordset
If rs.EOF And rs.BOF Then
MsgBox "Error: Record matching that COURSE DESCRIPTION not found."
GoTo CDAU_Exit
Else
Me![COURSE] = rs!COURSE_NO
Me![PROVIDER NUMBER] = rs!PROVNO
End If
Me![DATE].SetFocus
CDAU_Exit:
Exit Sub
CDAU_Err:
MsgBox Err.Number & " " & Err.Description
Resume CDAU_Exit
End Sub
I seem to be in a Catch 22 here. I Have a Combo box from which the
user selects a description. Then I am using a query to get the rest of
the COURSE details. The current record, from the table ATTEND is
linked to the Table COURSE by the field COURSE_NO. If I try to set Me!
COURSE to rs!COURSE_NO before doing the SaveRecord, it won't let me. I
get error 3331 To make changes to this field, first save the record.
If I try doing the SaveRecord first, as here, I get error 3101 The
Microsoft Jet database engine cannot find a record in the table
'COURSES' with key matching field(s) 'COURSE_NO'. Probably because
that key field has not been set yet. Is there any way around this?
Private Sub COURSE_DESCRIPTION_AfterUpdate()
On Error GoTo CDAU_Err
'fill in other fields from COURSE
Me![PAYROLL NUMBER] = [Forms]![PEOPLE]![PAYROLL]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim db As Database, qd As QueryDef, rs As Recordset, strsql As String
Set db = CurrentDb
Set qd = db.QueryDefs("qryCOURSEDescription")
qd.Parameters("pCOURSE").Value = Me![COURSE DESCRIPTION]
Set rs = qd.OpenRecordset
If rs.EOF And rs.BOF Then
MsgBox "Error: Record matching that COURSE DESCRIPTION not found."
GoTo CDAU_Exit
Else
Me![COURSE] = rs!COURSE_NO
Me![PROVIDER NUMBER] = rs!PROVNO
End If
Me![DATE].SetFocus
CDAU_Exit:
Exit Sub
CDAU_Err:
MsgBox Err.Number & " " & Err.Description
Resume CDAU_Exit
End Sub