June -- great recommendation about the "convert macro to VBA" button... never knew about it.
I removed the control updates for lock and enables given that I now start with actual record (vs. dummy record holding bunch of "---" across multiple fields).
After conversion to VBA, I still get the same error though. Below is the current VBA for entire form:
Code:
Option Compare Database
Private Sub Form_Load()
'Set focus on drop-down menu
'Me.cbo_StaffMember.SetFocus
End Sub
Private Sub cbo_StaffMember_AfterUpdate()
'Converted macro to VBA (i.e., on ribbon click "Convert Forms' macros to Visual Basic")
DoCmd.SearchForRecord , "", acFirst, "[StaffMemberIDpk] = " & Str(Nz(Screen.ActiveControl, 0))
End Sub
Private Sub cmd_AddRecord_Click()
'Prompt to confirm the save operation
If MsgBox("Do you want to create a new record?", vbYesNo + vbQuestion, "New Record") = vbNo Then
Me.Undo
End If
'Create new record
DoCmd.GoToRecord , , acNewRec
Me.cbo_StaffMember = Null
cbo_StaffMember.Requery
End Sub
Private Sub cmd_SaveRecord_Click()
'Save record
DoCmd.RunCommand acCmdSave
End Sub
Private Sub cmd_ArchiveRecord_Click()
'Prompt to confirm the delete current record operation
If MsgBox("Do you want to archive the current record (i.e., staff member is no longer onboard with USFFC)?", vbYesNo + vbQuestion, "Archive Record") = vbYes Then
If DCount("*", "T01_StaffMembers", "ReportsToStaffMemberIDpk=" & Me.StaffMemberIDpk) > 0 Then
MsgBox "This staff member cannot be archived until all subordinates have been re-assigned to another supervisor!", vbInformation, "Information"
Else
'Staff member records is updated and boolean value is set to false (i.e., staff member is no longer onboard)
CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.Onboard = False WHERE (((T01_StaffMembers.StaffMemberIDpk)=" & StaffMemberIDpk & "))"
cbo_StaffMember.Requery
Me.cbo_StaffMember = Me.cbo_StaffMember.ItemData(0)
End If
End If
'Set focus on drop-down menu
Me.cbo_StaffMember.SetFocus
End Sub
Private Sub cbo_Service_Change()
'Refreshes and clears combo boxes
cbo_Type.Requery
Me.cbo_Type = Me.cbo_Type.ItemData(0)
Me.cbo_Type = Null
cbo_RankTitle.Requery
Me.cbo_RankTitle = Me.cbo_RankTitle.ItemData(0)
Me.cbo_RankTitle = Null
End Sub
Private Sub cbo_Type_Change()
'Refreshes and clears combo boxes
cbo_RankTitle.Requery
Me.cbo_RankTitle = Me.cbo_RankTitle.ItemData(0)
Me.cbo_RankTitle = Null
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
'If the data has changed, the procedure prompts the user to continue with the save operation or to cancel it.
'Then the action that triggered the BeforeUpdate event is completed.
Dim ctl As Control
On Error GoTo Err_BeforeUpdate
'The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save the record?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
Me.Undo
End If
End If
'Updates datetime stamp
Me!txt_date_timestamp = Now()
Exit_BeforeUpdate:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub
So, the question remains, why can't I safe any changes on the first record vs. all other records? Let's recap the process:
Here's when I get the 2448 error:
- Forms open with 1st record as default record.
- If I do NOT switch to another record (via the combo) and immediately make changes...
- ... followed by then selecting another record from the combo, I get the 2448 error after acknowledging the "Save" Me.dirty pop-up.
However, I don't get the 2448 error when:
- Form open and I switch to another record via combo.
- If then make changes on another record and then go back to 1st record, no issues are experienced.
So, I must deduct that the "combo box" must be clicked at least once in order to not get the error. However, what if user wants to make a change of 1st record? There must be a way to enforce some action on the combo box w/o actually having to leave 1st record and then come back to 1st record IOT not get the error. Any thoughts on this peculiar scenario?
EEH