Hi I have an App with nested sub-forms, the record navigation is confusing and I've started moving the Record Navigation controls to the top of the forms to improve that. I'm stuck on the 'n of n' field, is there a generic property(s)/query that will give me this?
+1
A:
For the first n, use the AbsolutePosition property of the form's RecordsetClone. see MSDN
For the second n, use the RecordCount property.
Update: I prefer Mike's approach using CurrentRecord. I created a form with 2 text boxes: txtCurrentRecord and txtRecordCount. Then this procedure for the form's On Current event causes the text boxes to display the same values as the built-in navigation bar.
Private Sub Form_Current()
Me.txtCurrentRecord = Me.CurrentRecord
If Not Me.NewRecord Then
Me.txtRecordCount = Me.RecordsetClone.RecordCount
Else
Me.txtRecordCount = Me.CurrentRecord
End If
End Sub
Update2: I added a procedure to Form Load to make sure RecordCount is accurate.
Private Sub Form_Load()
Me.RecordsetClone.MoveLast
Me.RecordsetClone.MoveFirst
End Sub
HansUp
2010-09-27 05:31:44
MikeAinOz
2010-09-27 06:23:03
Thanks, Mike. I didn't notice CurrentRecord. Sounds like you got what you want, but I revised my answer in case it can help someone else.
HansUp
2010-09-27 18:11:10
Always use the RecordsetClone for navigation, not the form's Recordset. Also, note that the RecordCount property won't necessarily be accurate when you open the form, so you need to do a .MoveLast for it to be accurate.
David-W-Fenton
2010-09-27 18:19:24
@David Not sure I understood your point about navigation; my code didn't navigate between records. Does that apply to `Me.CurrentRecord`? I added Form_Load to address the second point. Seems that could slow things down with a big honkin' recordset ... but I prefer to avoid big honkin' recordsets.
HansUp
2010-09-27 18:36:21
Your first sentence mentions the option of using the form's Recordset. The question is about custom navigation controls. My point is to tell the reader not to use the form's recordset for navigation, regardless of what your specific code does. Personally, I could live without a Recordset property on forms. It seems to me like an open invitation to do things that are just a bad idea.
David-W-Fenton
2010-09-29 00:33:41
OK, thanks. I discarded Recordset.
HansUp
2010-09-29 01:01:32