Navigation buttons on a subform

Topic

New Reply

I have a form (frmMaster), which has a subform (frmMasterSub). The subform displays a set of records that are related to a combo box on frmMaster. This bit works great. The default form navigation buttons work fine, including the ‘Add new record’ button.

I have imported the fsubNavigate subform from the CD provided with Getz, Litwin, and Gilbert’s Access 2000 Developer’s Handbook (Vol. 1) into my project, and dragged the form onto frmMasterSub.

The problem is that the next, last, previous and first buttons work as advertized, but the ‘Add new record’ button gives me an error message:
“The object ‘frmMasterSub’ isn’t open (2489)” (vbOkOnly). I do not understand what is going on, or how to correct this (the button seems to work OK if the frmMasterSub is opened as a stand-alone form).

Can anyone provide some guidance, please?

kiwi44

Reply | Quote

Replies

You’re going to have to post the code behind the AddNew button on your subform for anyone to diagnose the problem. If it works when you open the subform as a form, it’s undoubtedly something in the code, but we have to see it to find the error.

Reply | Quote

Sorry, assumed that someone might know the code, and be able to provide some general directions – obviously I should not have assumed .

Here is the code behind the fsubNavigation form:

Option Compare Database
Option Explicit

‘ From Access 2000 Developer’s Handbook, Volume I
‘ by Getz, Litwin, and Gilbert (Sybex)
‘ Copyright 1999. All rights reserved.

Private Const adhcErrNoCurrentRow = 3021
Private Const adhcCantDisableFocus = 2164

Private WithEvents frmMain As Form
Private mfIsSubform As Boolean

‘ Calculating the total number of
‘ recordset when you open the main form can
‘ take some time. If you don’t want that to
‘ happen, set this constant to False.
Private Const adhcCalcTotalRecs = True

Private Function IsSubForm(frm As Form) As Boolean
‘ Is the form referenced in the
‘ parameter currently loaded as a subform?
‘ Check its Parent property to find out.
‘ In:
‘ frm: a reference to the form in question
‘ Out:
‘ Return value: True if the form is a subform
‘ False if it’s a standalone form
On Error Resume Next
Dim strName As String
strName = Me.Parent.Name
IsSubForm = (Err.Number = 0)
Err.Clear
End Function

Private Sub cmdFirst_Click()
‘ Move to the first row on a form.
Call NavMove(acFirst)
End Sub

Private Sub cmdLast_Click()
‘ Move to the last row on a form.
Call NavMove(acLast)
End Sub

Private Sub cmdNew_Click()
‘ Move to the new row.
Call NavMove(acNewRec)
End Sub

Private Sub cmdNext_Click()
‘ Move to the next row on a form.
Call NavMove(acNext)
End Sub

Private Sub cmdPrev_Click()
‘ Move to the previous row on a form.
Call NavMove(acPrevious)
End Sub

Private Sub Form_Load()
mfIsSubform = IsSubForm(Me)
If mfIsSubform Then
Set frmMain = Me.Parent

‘ Code won’t run unless the words
‘ “[Event Procedure]” show up in the
‘ main form’s event properties.
frmMain.OnCurrent = “[Event Procedure]”
frmMain.OnDirty = “[Event Procedure]”

‘ Calculate the total number of records?
If adhcCalcTotalRecs Then
Dim rst As DAO.Recordset
Set rst = frmMain.RecordsetClone
rst.MoveFirst
rst.MoveLast
txtTotalRecs = rst.RecordCount
Set rst = Nothing
End If
End If

ExitHere:
Exit Sub

HandleErrors:
Select Case Err.Number
Case Else
MsgBox “Error: ” & _
Err.Description & _
” (” & Err.Number & “)”
End Select
Resume ExitHere
End Sub

Private Sub frmMain_Current()
‘ Called from the the main form’s Current event.
‘
‘ This function enables and disables buttons as
‘ necessary, depending on the current
‘ record on the main form.
‘
‘ This code will run after any code associated
‘ with the main form’s Current event.

Dim rst As DAO.Recordset
Dim fAtNew As Integer
Dim fUpdatable As Integer

If Not mfIsSubform Then
Exit Sub
End If

On Error GoTo HandleErrors
txtCurrRec = frmMain.CurrentRecord
Set rst = frmMain.RecordsetClone

‘ Sooner or later, Access will figure out
‘ how many rows there really are!
txtTotalRecs = rst.RecordCount + _
IIf(frmMain.NewRecord, 1, 0)

‘ Check to see whether or not you’re on the new record.
fAtNew = frmMain.NewRecord

‘ If the form isn’t updatable, then you sure
‘ can’t go to the new record! If it is, then
‘ the button should be enabled unless you’re already
‘ on the new record.
fUpdatable = rst.Updatable And frmMain.AllowAdditions
If fUpdatable Then
cmdNew.Enabled = Not fAtNew
Else
cmdNew.Enabled = False
End If

If fAtNew Then
cmdNext.Enabled = False
cmdLast.Enabled = True
cmdFirst.Enabled = (rst.RecordCount > 0)
cmdPrev.Enabled = (rst.RecordCount > 0)
Else
‘ Sync the recordset’s bookmark with
‘ the form’s bookmark.
rst.Bookmark = frmMain.Bookmark

‘ Move backwards to check for BOF.
rst.MovePrevious
cmdFirst.Enabled = Not rst.BOF
cmdPrev.Enabled = Not rst.BOF

‘ Get back to where you started.
rst.Bookmark = frmMain.Bookmark

‘ Move forward to check for EOF.
rst.MoveNext
cmdNext.Enabled = Not (rst.EOF Or fAtNew)
cmdLast.Enabled = Not (rst.EOF Or fAtNew)
End If

ExitHere:
Me.Repaint
Exit Sub

HandleErrors:
Select Case Err.Number
Case adhcCantDisableFocus
txtCurrRec.SetFocus
Resume
Case Else
MsgBox “Error: ” & _
Err.Description & ” (” & Err.Number & “)”
End Select
Resume ExitHere
End Sub

Private Sub NavMove(lngWhere As AcRecord)
‘
‘ Move to the correct row in the form’s recordset,
‘ depending on which button was pushed. This code doesn’t
‘ really need to check for errors, since the buttons
‘ that would cause errors have been disabled already.
‘
Dim rst As DAO.Recordset
Dim fAtNew As Boolean

Const adhcErrNoCurrentRow = 3021

On Error GoTo HandleErrors
If lngWhere = acNewRec Then
DoCmd.GoToRecord _
acForm, frmMain.Name, Record:=acNewRec
Else
fAtNew = frmMain.NewRecord
Set rst = frmMain.RecordsetClone
rst.Bookmark = frmMain.Bookmark
Select Case lngWhere
Case acFirst
rst.MoveFirst
Case acPrevious
If fAtNew Then
rst.MoveLast
Else
rst.MovePrevious
End If
Case acNext
rst.MoveNext
Case acLast
rst.MoveLast
End Select
frmMain.Bookmark = rst.Bookmark
End If

ExitHere:
Me.Repaint
Exit Sub

HandleErrors:
If Err.Number = adhcErrNoCurrentRow And _
frmMain.NewRecord Then
Resume Next
Else
MsgBox Err.Description & ” (” & Err.Number & “)”
End If
Resume ExitHere
End Sub

Private Sub frmMain_Dirty(Cancel As Integer)
cmdNew.Enabled = True
End Sub

Private Sub txtCurrRec_AfterUpdate()
Dim lngRec As Long
Dim lngTotalRecs As Long

‘ Move to a specified row.

On Error GoTo HandleErrors

lngTotalRecs = txtTotalRecs

‘ If they entered a non-numeric value,
‘ just put the old position back.
If Not IsNumeric(txtCurrRec) Then
txtCurrRec = frmMain.CurrentRecord
Exit Sub
End If
‘ Get the current value, and make sure
‘ it’s a long integer
lngRec = CLng(txtCurrRec)
If Err.Number 0 Then
txtCurrRec = frmMain.CurrentRecord
Exit Sub
End If

‘ If they put in 0 or a negative number,
‘ put 1 in instead.
If lngRec lngTotalRecs Then
lngRec = lngTotalRecs
End If

‘ Move to the correct row. Note that
‘ the form’s CurrentRecord property is
‘ READ-ONLY, so you have to go around
‘ the back, using the form’s Recordset property’s
‘ AbsolutePosition property.
frmMain.Recordset.AbsolutePosition = lngRec – 1
txtCurrRec = lngRec

ExitHere:
Exit Sub

HandleErrors:
Select Case Err.Number
Case Else
MsgBox “Error: ” & _
Err.Description & ” (” & Err.Number & “)”
End Select
Resume ExitHere
End Sub

Private Function AtFirstRow(frm As Form) As Boolean
‘ Not used in any example, but in the book…

‘ This example assumes that you’re not already
‘ at BOF when you call this code.

‘ Return True if at first row, False otherwise.
Dim rst As DAO.Recordset

On Error Resume Next

Set rst = frm.RecordsetClone
rst.Bookmark = frm.Bookmark
rst.MovePrevious
AtFirstRow = rst.BOF

Set rst = Nothing
Err.Clear
End Function

There is currently no code in the frmMasterSub that directly relates to the fsubNavigation form, which I suspect may be the problem??

Thanks,

Kiwi44

Reply | Quote

Aha – I think I have the answer – the response to post #30639 gives me the idea, which I should be able to get to work. The buttons should be handled by the wizards, and then it only remains to put in the record number. Much easier than the ‘text-book’ answer I was looking for!

kiwi44

Edited by Charlotte to add link

Reply | Quote

I think where you had a problem with the code is that it uses automation internally and the code refers to the parent/main form. You haven’t actually provided any navigation for the subform, it’s all for the parent.

Reply | Quote