Discussion Locked

Access 97 Records

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an Access 97 Database. I use 3 buttons. Next Record, Previous Record and New Record. When I click the new record to establish a new child record I get an error. If the parent has NO child records, the error does not show up.

Code behind Next & Previous is:

Private Sub Form_Current()

Dim recClone As Recordset
Dim intNewRecord As Integer

' Make a clone of the recordset underlying the form so
' we can move around that without affecting the form's
' recordset

Set recClone =Me.RecordsetClone

' But we need to check if there are no records. If so,
' we disable all the buttons except for the

If recClone.RecordCount = 0 Then
[CMDNextDepend].Enabled = False
[Cmd Previous].Enabled = FalseElse

'Synchonise the current pointer in the two recordsets

recClone.Bookmark = Me.Bookmark

' If there are records, see if we are on the first record
' If so, we should disable the and buttons

recClone.MovePrevious
[Cmd Previous].Enabled = Not (recClone.BOF)
recClone.MoveNext

' And then check whether we are on the last record
' If so, we should disable the and buttons

recClone.MoveNext
[CMDNextDepend].Enabled = Not (recClone.EOF)
recClone.MovePrevious

End If

' And finally close the cloned recordset
recClone.Close
End Sub
ERROR COMES UP IN THE ABOVE MODULE!!!!!

Code behind the New Record button is:

Private Sub CmdNewDepend_Click()
On Error GoTo Err_CmdNewDepend_Click DoCmd.GoToRecord , , acNewRec

Exit_CmdNewDepend_Click:
Exit Sub

Err_CmdNewDepend_Click:
MsgBox Err.Description
Resume Exit_CmdNewDepend_Click

End Sub

THANKS FOR ANY HELP YOU CAN OFFER!

Alan

3 replies

by sl-campbell

This is because the OnCurrent Event doesn't get called when there are no records WHEN you call the AddNew mode. And therefore the Bookmark doesn't get set. When the form first loads with no records, it doesn't go to the AddRecord mode and the bookmarks of the Form's Rs and its' clone are nothing.
But in the AddNew mode this isn't the case. Calling AddNew when there are records will fire the OnCurrent Event as well. Then what you are trying to do is set the Bookmark of the clone to the bookmark of the Form's rs when the form is in the Add New mode. This cannot be done. You can only set the bookmark to a current record and the AddNew isn't on a record.
Of course you could cheat here and add a On Error Resume Next here - but I wouldn't.Best would be to create a new Sub proceedure in the Form's class and MOVE all of the above code into it, removing it from the OnCurrent Event.
Then, on the Form_Activate/AfterInsert,AfterDelConfirm, CMDNextDepend_Click,[Cmd Previous]_Click - Events
call this Sub.

You will also need to change the code when the Next/Previous buttons get disabled: Check FIRST if
recClone.BOF is true, and if it is, then set focus to the Next Button (CMDNextDepend.SetFocus) and THEN dis-able the Previous button, and if not, then enable the Previous button. Do the same for the Next button checking the EOF and enable/dis-able this Button.

In the CmdNewDepend_Click you should hide the CMDNextDepend and [Cmd Previous] buttons and add a Cancel Button to cancel the add new and return to the previous record.

This discussion is closed to new replies.