Display Current Record of Total Records in Text Box 1

[wbwillson]

I'm trying to display the record numbers on a form. For example (Record 1 of 9). I've created a module with the below code. Then I set my Control Source to =hpFormRecCount() on the text box. This produces the following error "The expression you entered has a function containing the wrong number of arguments" Any ideas what I'm doing wrong? Thanks.

Bill


'Building a string containg 'curent record' of 'num of
records' on a form.

Function hpFormRecCount(Form As Form) As String
Dim lngRecPos As Long
Dim lngRecCount As Long
Dim strTemp As String

If Form.NewRecord And Form.RecordsetClone.BOF Then
strTemp = "1 of 1"
Exit Function

ElseIf Form.NewRecord And Not Form.RecordsetClone.BOF Then
lngRecPos = Form.CurrentRecord
lngRecCount = (Form.RecordsetClone.RecordCount + 1)
Else
Form.RecordsetClone.MoveLast
lngRecPos = Form.CurrentRecord
lngRecCount = Form.RecordsetClone.RecordCount
End If

strTemp = str(lngRecPos) & " of " & str(lngRecCount)
hpFormRecCount = strTemp

End Function

 

[JeremyNYC]

Your function declaration requires a form variable to be passed in. Your control source does not include that form variable.

Try making your control source
=hpFormRecCount(forms!NameOfThisForm)

You could try the Me keyword inside the parens instead, but I'm not sure that will work.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[wbwillson]

Jeremy,

Once again you've saved the day! I always thought by just using hpFormRecCount() <-- that it would call the function for the current form? Thanks again for your help! You are a VBA God!

Bill

 

[FancyPrairie]

Here's another way of doing it (with less code).

ControlSource... =hpFormRecCount()

Function hpFormRecCount() As String

Dim frm As form

Set frm = Screen.ActiveForm

hpFormRecCount = frm.CurrentRecord & &quot; of &quot; & frm.RecordsetClone.RecordCount + IIf(frm.NewRecord, 1, 0)

End Function