|
-
May 29th, 2009, 11:42 AM
#1
Thread Starter
New Member
How to manage the "No current record" situation after a Form.Requery method
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 as 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:
Code:
Private Sub lstPersons _AfterUpdate()
Me.Recordset.FindFirst "IDPerson = " & lstPersons.Value
End Sub
The code of the btnAdd() is this one:
Code:
Private Sub btnAdd_Click()
Me.AllowAdditions = True
Me.Recordset.AddNew
End Sub
The code of the btnSave_Click() event handler is this one:
Code:
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:
Code:
Private Sub Form_Current()
UpdateMaskControls
End Sub
The goal of the "UpdateMaskControls" is to show/hide the CommandButtons (if the record is new or not) and to select the correct value of "lstPersons" (in the case the user has added a new record):
Code:
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:
Code:
.lstPersons.Value = .Recordset.Fields("IDPerson")
is executed, an error occurs (the reason is: 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:
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):
Code:
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.
Last edited by polocar; Jun 2nd, 2009 at 12:18 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|