Subform Navigation

J

Jeefgeorge

I have a subform with custom navigation controls including a current / total
record text boxes. When the main form loads, the total record on the subform
is 1. If I manually navigate to the last record on the subform, then go back
to the first record, the total record shows correctly. However if the main
form record changes, the total on the subform resets to 1. I have opened the
subform without the main form and the total text box shows the correct
number. I cannot figure out why this text box doesn't show the correct total
with the main form.

Code for the subform:
Private Sub Form_Current()
On Error GoTo Err_Current
Dim First As String
'Populate the Record of Records used for Navigation
With Me.RecordsetClone
If Not .EOF Then .MoveLast
Me.Current = Me.CurrentRecord
End With
If Me.Current.Value > Me.RecordsetClone.RecordCount Then
Me.Total = "of " & Me.RecordsetClone.RecordCount + 1
Else
Me.Total = "of " & Me.RecordsetClone.RecordCount
End If
 
D

Dale Fye

How about using this instead:

Private Sub Form_Current()

On Error GoTo Err_Current

Dim First As String
Dim lngRecCount as long

'Populate the Record of Records used for Navigation
lngRecCount = me.recordsetclone.recordcount
me.Current = me.CurrentRecord
If me.newRecord then
Me.Total = "of " & lngRecCount + 1
Else
Me.Total = "of " & lngRecCount
End If


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
J

Jeefgeorge

Closer....but still doesn't completely fix my problem....when the record on
the main form changes the total record on the subform displays 1 once the
record is changed on the subform the total displays the correct value. (I
don't have to navigate to the last record, just any record)
 
D

Dale Fye

Change the declaration of the subform current event from Private to Public

Private Sub Form_Current( )
becomes: Public Sub Form_Current

Then, in the main forms Current event, call the subforms current event.
It's been a while since I did this, but it should look something like:

Call me.SubControlName.Form.form_Current

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
J

Jeefgeorge

Didn't help....but I figured out what the problem was....
On the Main_Form_Load() I had

DoCmd.GoToRecord, , acNewRec

to begin with a new project, when I comment out this line the sub form works
perfectly...even when a new record is added to the main form and then
navigated away from. So instead on the Main_Form_Load() I have referenced
the code for the New_Record command button everything is working great now. I
still don't understand why hard coding in the new record caused errors...but
this works.

Main_Form_Load()
New_Click
End Sub

Public Sub New_Click()
On Error GoTo Err_New
DoCmd.GoToRecord , , acNewRec
Me.Quantity.SetFocus
Exit_New:
Exit Sub
Err_New:
MsgBox Err.Description
Resume Exit_New
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top