Combo box results in error "2448"

[skydivetom]

Experts:

On a form, I have an unbound combo box which -- after an update event -- runs an "embedded macro" which in turn brings up the matching record (based on the table's autonumber ID).

Below is the process:
- If I chose any value *except* the first record, I can make changes to any fields.
- If I then select another record from the combo box, the "Me.Dirty" kicks in and prompts me to save the changes. If I click "Yes", the changes are saved and the other selected record (via combo box selection) shows up.

This works well for me.

However, I have noticed the following unique scenario which results in a 2448 error. That is, ...
- If I select the **first* record from the source data (via the combo box) and I follow the same steps, I again get the "Save Changes" (Me.Dirty dialogue box) but when clicking "Yes", it now results in the following error:
- "2448 You can't assign a value to this object".
- If I click "ok", any changes to record #1 disappear.
- Again, if I were to attempt to change some field value AND if I don't click "save" but immediately select another record from the combo I then get yet another error: "Update or CancelUpdate without AddNew or Edit."

My questions:
1. Why do I get error "2448" and/or the "Update or CancelUpdate..." error only on the **first** record in the table?
2. How can I fix that so that I can switch from record #1 to any other record and, if needed, simply confirm "Yes" to the "Me.Dirty" dialogue box but then pull up the other record?



Thank you,
EEH

[June7]

Provide db for analysis.

I don't use macros, only VBA, but can usually figure out macro.

[skydivetom]

June7 -- thank you for your willingness to look into it. Attached is copy of database with "dummy records".

Please open the form and do the following:
1. Upon opening the form"F03_DataEntryForm...", employee "James T. Kirk" comes up as the default (first record).
2. Modify, e.g., his first or lastname.
3. W/o doing anything such as "Save", select another employee (e.g., Spock) from the drop-down menu.
4. Click on "Yes" when prompted to "Save" record. At this time, you'll see the error message popping up.

Note... given that I deleted other form objects and records, this now actually happens when making a change also on 2nd or 3rd record.

Regardless, how can I by-pass this error when clicking "Yes" to the changes?

Thank you,
EEH

[June7]

Why use embedded macro? You already have VBA for many events, continue with VBA.

I would not use OnChange event, never have used it for anything. If user types into combobox, this event triggers with each key stroke. Use AfterUpdate event.

Also never used "Screen.ActiveControl" reference, I explicitly reference control by name.

Suggest you put code to enable/disable controls in a Sub that can be called by the form events so code is not duplicated. Or use Conditional Formatting.

Why change both Enabled and Locked? A disabled control is effectively locked.

I have never used form BeforeUpdate event to confirm user wants to save record.

The error is caused by trying to set txt_date_timestamp in BeforeUpdate. Try referencing field Record_Modified_Date instead of textbox.

[skydivetom]

June7 - I don't need to use the macro. I think it came w/ the version that mike60smart provided.

If I change from macro to VBA, what's specific VBA code that will bring the record where OrgID matches?

Thank you,
EEH

[June7]

Code in Load event is setting some controls to Null. This is putting record into edit mode so the popup to validate saving record triggers when opening form, even though user has not done edit.

Can convert macro to VBA with command on ribbon "Convert Forms' macros to Visual Basic".

[skydivetom]

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:

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

[June7]

In my earlier post: "The error is caused by trying to set txt_date_timestamp in BeforeUpdate. Try referencing field Record_Modified_Date instead of textbox." Code is still referencing textbox. Actually, either of the following should work:

Me.txt_date_timestamp
Me!Record_Modified_Date

Note the use of dot (.) and bang (!).

I also recommended not using Change event.

[skydivetom]

June7 -- yes!!! I think that did the trick... awesome!!!!

Thousand thanks for the help.