Error 3021 when function is called from both form and subform

[BruceM]

I have the following code in a standard module:

Public Function CurrentEvents(frm As Form)

' Inserts current record number and total number of records
' (strCurrent, strTotal, strOf are defined in Declarations
' section of this module)

strCurrent = frm.CurrentRecord
frm.RecordsetClone.MoveLast
strTotal = frm.Recordset.RecordCount
strOf = strCurrent & " of " & strTotal

' Enable navigation buttons as needed
frm.cmdFirst.Enabled = Not frm.CurrentRecord = 1
frm.cmdPrevious.Enabled = Not frm.CurrentRecord = 1
frm.cmdNext.Enabled = (frm.CurrentRecord = 1 And
frm.Recordset.RecordCount > 1) _
Or frm.CurrentRecord < frm.Recordset.RecordCount
frm.cmdLast.Enabled = Not (frm.cmdNext.Enabled = False)

End Function

I call the function from the Current event of a form or subform. cmdFirst,
etc. are custom navigation buttons. I combine strOf with something specific
to the data displayed on the form (1 of 86 Employees, 4 of 8 Departments,
etc.), which is why I usually call it from the form's Current event rather
than placing the function name directly into the line for Current event on
the property sheet. Also, there is often something else in the Current
event.
As an aside, I don't know what to call it when I place a function name
directly into the space next to, say, a command button's Click event on the
button's property sheet, as opposed to using VBA to call the function from
within the Click event. It doesn't matter all that much, I suppose, but I
would like to know how to refer to the practice.
Anyhow, one of my forms contains two subforms, both of which contain the
same command buttons as the main form, and both of which call the
CurrentEvents function described above. Subform1 invariably contains at
least one record, but Subform2 may not contain any records (I know the
records are contained in the table to which the subform is bound and not in
the subform itself, and I ask that you accept the shorthand way of
expressing the situation). If both subforms in the record to which I am
navigating contain at least one record apiece everything is fine, but when
one of the subforms contains no records I receive Error 3021, No Current
Record in the CurrentEvents function.

For now I have used the function's error handling to bypass the error, but I
wonder if there is a better way. I have tried this after the strOf line:

If strTotal = 0 Then
Cancel = True
End If

I have also tried it with If strTotal = "0" and If frm.Recordset.RecordCount
= 0, but I get a "Variable not defined" compile error at Cancel. I tried
adding Cancel as Integer in the parentheses in the function name: Public
Function CurrentEvents(frm as Form, Cancel as Integer), but then I receive
an "Argument not optional" compile error (or type mismatch if Cancel as
Integer precedes frm as Form), but in any case it is a flawed approach, most
likely because there are large gaps in my knowledge of VBA. I would rather
address the cause of the error rather than ignoring it in the error
handling, unless ignoring it is a sound approach.

 

[Klatuu]

An empty recordset for a form or sub form will return 0 for
strTotal = frm.Recordset.RecordCount

I question either your variable typing or your naming. Using standard
naming conventions, strTotal would be expected to be a string variable. The
property you are querying will return a Long. It should be Dimmed as Long
and named lngTotal.

Here is a similar procedure I use that does work without errors. If you
want show the # of ##, you could just add the code to it, our you can just
use this for ideas.

 

[BruceM]

I see what you're saying. I will change that to Long. I checked Help to
see the difference between Integer and Long, and learned that they are
16-bit and 32-bit respectively, but also that Long is "signed". What does
that mean, and to what extent are Integer and Long interchangeable?

Also, you said "Here is a similar procedure". I would definitely be
interested in taking a look, but I don't see it.

 

[Klatuu]

ooops. I didn't paste it

'---------------------------------------------------------------------------------------
' Procedure : SetNavButtons
' DateTime : 2/6/2006 09:36
' Author : Dave Hargis
' Purpose : Enables and Disables Nav buttons based on current record
position
'---------------------------------------------------------------------------------------
'
Sub SetNavButtons(ByRef frmSomeForm As Form)

On Error GoTo SetNavButtons_Error

With frmSomeForm
Select Case .CurrentRecord
'First Record
Case 1
.cmdNextRec.Enabled = True
.cmdLastRec.Enabled = True
.cmdNextRec.SetFocus
.cmdFirstRec.Enabled = False
.cmdPreviousRec.Enabled = False
'Last Record
Case Is = .Recordset.RecordCount
.cmdFirstRec.Enabled = True
.cmdPreviousRec.Enabled = True
.cmdPreviousRec.SetFocus
.cmdNextRec.Enabled = False
.cmdLastRec.Enabled = False
'Somewhere in the middle
Case Is <> 0
.cmdFirstRec.Enabled = True
.cmdPreviousRec.Enabled = True
.cmdNextRec.Enabled = True
.cmdLastRec.Enabled = True
'No Records in the recordset
Case Else
.cmdFirstRec.Enabled = False
.cmdPreviousRec.Enabled = False
.cmdNextRec.Enabled = False
.cmdLastRec.Enabled = False
End Select
End With

I don't know why it identifies Long as signed but not Integer. Signed means
it carries a bit to identify whether it is positive or negative. Longs have
some advantages. First, they will hold a larger range of numbers. The most
important value is because it is a 32 bit value, it is faster than the Int.
That is because memory is in 32 bit "words". It takes extra time to convert
and store an inteiger.

VBA handles data type conversion behind the scenes when it can. So if it is
asked to store a variable or property that is Long in an integer variable, it
first has to be converted to the correct type to be stored.

 

[BruceM]

Thanks for the code, and for the explanation of Long and Integer. I got
some ideas from the code, although it led to the same 3021 error when I
tried it as a user-defined function. The reason was that one of the
subforms may contain no records. The Current event for each subform is
something like this:

Call CurrentEvents(Form)

If Me.NewRecord Then
' Record counter text box reads "New Record"
Else
' Record counter text box reads "x of y"
End If

All I needed to do was to move the function call:

If Me.NewRecord Then
' Record counter text box reads "New Record"
Else
Call CurrentEvents(Form)
' Record counter text box reads "x of y"
End If

I have used comments instead of the actual code regarding the record counter
text box, of course. When there are subform records the subforms open to
the first record; otherwise it a new record as far as Access is concerned,
and the function is not called.

There is still a difficulty in that if I am at a main form record, and I go
to a new subform record (in a situation where there is already at least one
subform record), CurrentEvents is not called. I am at the second subform
record, but can't navigate back to the first record because the Previous
button is disabled. I think I can get past that by something like setting a
Boolean to True in the main form's Current event, then setting it to False
if I click the subform's New Record button. The subform's Current event
could test for the Boolean value, and either run Current Events or not
depending on the Boolean value. I haven't thought through the details, and
I need to finish a few other things unrelated to this project, but I think
it's getting on track.

Thanks for taking a look at this. If you have any thoughts on this latest I
would be very glad to hear them.

 

[Klatuu]

It may have something to do with it being in a sub form. I even tried
testing in with a regular form and an empty recordset, and it worked as
expected. Since I don't use nav buttons in subforms, I have no experience
there.
In that you pass the form as a form to the function, it may that Access is
getting confused about which object it is working with.

 

[BruceM]

I know that subforms work oddly in some other ways. For instance, I need to
use MoveLast before a RecordCount line of code in order to get the record
count in a subform. As I recall that isn't necessary in a form, or in any
case it works somewhat differently there, but the details of the difference
elude me at the moment. Once again, thanks for your interest and your help
with this.