function assistance

[Guest]

I have the following that I am placing into a module as a function since it
was decided to use it on all forms to show the number of records each client
has. But, I am running into an error that points back to the Me in the line
Set rst = Me. Recordset.Clone. Originally, this was only on one form and
worked fine. Now it is to go on all applicable forms. How would the Me be
worked around so this function can be applied to all forms?

Function Form_Current()
On Error GoTo Form_Current_Err

Dim lngCount As Long
Set rst = Me.Recordset.Clone

If rst.EOF = False Then
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "Record " & Me.CurrentRecord & " of " &
lngCount & " for " & [Clientcode]

Else
Me.txtRecordNumber = "Record 0" & " of 0"

End If

Form_Current_Exit:
Exit Function

Form_Current_Err:
MsgBox "The number of records will not be available.", vbOKOnly, "Record
Count"
Resume Form_Current_Exit

End Function

Thanks to anyone who responds.
*** John

 

[fredg]

I have the following that I am placing into a module as a function since it
was decided to use it on all forms to show the number of records each client
has. But, I am running into an error that points back to the Me in the line
Set rst = Me. Recordset.Clone. Originally, this was only on one form and
worked fine. Now it is to go on all applicable forms. How would the Me be
worked around so this function can be applied to all forms?

Function Form_Current()
On Error GoTo Form_Current_Err

Dim lngCount As Long
Set rst = Me.Recordset.Clone

If rst.EOF = False Then
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "Record " & Me.CurrentRecord & " of " &
lngCount & " for " & [Clientcode]

Else
Me.txtRecordNumber = "Record 0" & " of 0"

End If

Form_Current_Exit:
Exit Function

Form_Current_Err:
MsgBox "The number of records will not be available.", vbOKOnly, "Record
Count"
Resume Form_Current_Exit

End Function

Thanks to anyone who responds.
*** John

You cannot use the Me! keyword in a module, as there is no Me! object
once you remove the code from the Form code.

Not fully sure of why you are doing this, but if you wish to have just
one code module to return the record count for several different
forms, try it this way:

In a module:
Function GetRecCount(FormIn As Form) As String

Dim rst As Recordset
Set rst = FormIn.RecordsetClone

On Error GoTo Err_Handler
Dim lngCount As Long
If Not rst.EOF Then
With rst
.MoveFirst
.MoveLast
GetRecCount = .RecordCount
End With
Else
GetRecCount = 0
End If

GetRecCount_Exit:
Set rst = Nothing
Exit Function

Err_Handler:
MsgBox "The number of records will not be available.", vbOKOnly,
"Record count "
Resume GetRecCount_Exit

End Function
========

You will need an unbound control named txtRecordNumber in each form.
In the current event of each form:

[txtRecordNumber] = "Record " & Me.CurrentRecord & " or " &
GetRecCount(Me)

You can use Me here, because this is located in the Form's code.

What you are doing with the rest of your txtRecordNumber (& " for "
& [Clientcode]) I don't know but I guess you can just add it to the
current event code.

I don't see how that is any easier than simply coding the Current
event with:
[txtRecorNumber] = "Record " & Me.CurrentRecord & " of " &
Me.RecordsetClone.RecordCount

 

[Marshall Barton]

I have the following that I am placing into a module as a function since it
was decided to use it on all forms to show the number of records each client
has. But, I am running into an error that points back to the Me in the line
Set rst = Me. Recordset.Clone. Originally, this was only on one form and
worked fine. Now it is to go on all applicable forms. How would the Me be
worked around so this function can be applied to all forms?

Function Form_Current()
On Error GoTo Form_Current_Err

Dim lngCount As Long
Set rst = Me.Recordset.Clone

[snip]

The Me object refers to the Class module that the code is
in. When you move the code to another module, Me either
doesn't exist (standard module) or refers to a different
object (class module).

The usual way to take care of this is to add a form object
argument to the function:

Function Form_Current(frm As Form)
On Error GoTo Form_Current_Err

Dim lngCount As Long
Set rst = frm.RecordsetClone
. . .

Note that while frm.Recordset.Clone is valid in the later
versions of Access, it is more common to use built-in
frm.RecordsetClone method, which is somewhat optimized for
this purpose.