How to manage the "No current record" situation after the Form.Requery

[polocar]

Hello,
I'm preparing a simple mask in Access 2003.
The RecordSource of this mask is a table named Persons, whose fields are "IDPerson" (counter field and primary key), "Name", "SecondName" and "Age".
In the mask there are some textboxes linked to the fields above, and there are the classical CommandButtons "btnAdd", "btnDelete" and, when I’m inserting a new record (by clicking on "btnAdd"), "btnSave" and "btnCancel".
At the end there is a "lstPersons" ListBox to select the desired record. It has RowSource:
RowSource = "SELECT IDPerson, Name FROM Persons ORDER BY Name"
so it has 2 columns, the first one (that is also the one that gives the value to the ListBox) linked to "IDPerson" field (0 cm width, so invisible to the user), the second one linked to "Name" field.

The code of the lstPersons_AfterUpdate() event handler is very simple:

Private Sub lstPersons _AfterUpdate()

    Me.Recordset.FindFirst "IDPerson = " & lstPersons.Value

End Sub

The code of the btnAdd() is this one:

Private Sub btnAdd_Click()

    Me.AllowAdditions = True
    Me.Recordset.AddNew

End Sub

The code of the btnSave_Click() event handler is this one:

Private Sub btnSave_Click()

    Dim IDPerson As Long
    
    With Me
        IDPerson = Me.Recordset.Fields("IDPerson")
        DoCmd.RunCommand acCmdSaveRecord
        .Requery
        
        .Recordset.FindFirst "IDPerson = " & IDPerson
    End With
    
    Me.Refresh

End Sub

At the end I have prepared an "UpdateMaskControls" procedure that is called in the Form_Current() event handler, so, when the current record changes (because the user has selected another record in "lstPersons" or because he has clicked on "btnAdd" and then on "btnSave"), "UpdateMaskControls" code is executed:

Private Sub Form_Current()

    UpdateMaskControls
    
End Sub

The goal of the "UpdateMaskControls" is to show/hide the CommandButtons and to select the correct value of "lstPersons" (in the case the user has added a new record):

Public Sub UpdateMaskControls ()

    With Me
        
        If .NewRecord Then
            .btnSave.Visible = True
            .btnCancel.Visible = True

            .btnAdd.Visible = False
            .btnDelete.Visible = False
        Else
            .lstPersons.Enabled = True
            .lstPersons.Value = .Recordset.Fields("IDPerson")

            .btnAdd.Visible = True
            .btnDelete.Visible = True

            .btnSave.Visible = False
            .btnCancel.Visible = False
        End If
        
    End With
    
End Sub

The problem is this one: when the user adds a new record and then clicks on “btnSave”, the Me.Requery statement (in btnSave_Click event handler) calls Form_Current event handler that calls UpdateMaskControls procedure, and when the statement:

.lstPersons.Value = .Recordset.Fields("IDPerson")

is executed, an error occurs (no current record).
At the beginning I have thought that perhaps, after the Form Requery, the state of the Recordset is BOF or EOF, so at the top of UpdateMaskControls I have added the code:

If Me.Recordset.BOF Or Me.Recordset.EOF Then
	Exit Sub
End If

But I have realized that the Form Recordset is not BOF and not EOF.
I also tried to use the Me.CurrentRecord method (always at the top of UpdateMaskControls):

Dim l As Long
l = Me.Recordset.Fields("IDPerson")

to understand if its value could help me, but I have realized that after the Form Requery
l = 1 (as the current record had been the first one)

How can I catch the “No current record” exception before executing the entire UpdateMaskControls code?

Thank you very much. Sorry for the length of the thread, but I have tried to be as clear as possible.

 

[SkipVought]

Hi,

This post might be better addressed in one of the many MS Access forums, like forum702 or forum705.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd test the Me.NewRecord property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[polocar]

Hi PHV,
where would you test the Me.NewRecord property?
Thanks