I have a main form with navigation buttons on it and a label showing
for example Record 1 of 15 using recordsetclone on it and eveything
works fine. When I move through the records the record number changes
fine. Within this main form I have a subform detailing distribution
records for the contact (main form is based on this) that also has
navigation buttons and a label showing Distribution 1 of ##. This is
where the problem lies. The first main record has two records in its
subform and shows Distribution 1 of 2. When I move to the next record
on the main form, the subform still shows Distribution 1 of 1, when
this record doesn't actually have any sub records. This happens for
all records I scroll until I reach 13 of 15 which actually has a
subrecord and displays Distribution 1 of 1. When I scroll to previous
records each one now shows Distribution 1 of 1 whereby before I
reached this point is showed Distribution 1 of 2. When I get back to
Record 1 of 15 on the main form the subform label shows Distribution 1
of 2 again and repeats this for each until record 13 of 15 is reached
again. It doesn't seem to update or calculate how many records this
subform is showing and just repeats through the records until a record
actually has data in it.
Can I just add that the subform is showing the correct records it's
just that the label I have used to display the Distribution # of ## is
not updating properly when using the naviagtion buttons.
The subform is based on a table called tblDistribution which records
how many publication distributions a Contact has had. The contact is
the main form/table. As it stands the tblDistribution has 3 records
in it and as I stated above the label is not displaying the amount
each person has correctly.
I need the subform to kind of look at the current contact and
calculate how many distributions they have had and that should be the
value used in the label, but using the clone doesn't seem to do this.
Any help would be greatly appreciated. Thanks in Advance. Carl J
Barrett.
Here is a snip of the code I am using. I haven't included any of the
navigation areas as these work OK.
Dim recClone As Recordset
Dim intTotalRecords As Integer
Dim intCurrentRecord As Integer
'Make a clone of the recordset underlying the form so
'we can move around that without affecting the form's
'recordset. This clones tblDistribution which currently
'has 3 in it.
Set recClone = Me.RecordsetClone()
'move to the last record in the recordset
recClone.MoveLast
'synchronize the form's and recordset's bookmarks
recClone.Bookmark = Me.Bookmark
'set the intCurrentRecord var. Because recordsets are zero based, add
1 to the value
intCurrentRecord = recClone.AbsolutePosition + 1
'set intTotalRecords var
intTotalRecords = recClone.RecordCount
'set RecordDetails caption
lblDistributionDetails.Caption = "Distribution: " & intCurrentRecord &
" of " & intTotalRecords
'And finally close the cloned recordset
recClone.Close
