Code not following expected path in If-Then-Else statement

[Seth Schrock]

I have created a Next button to move to the next record on my form. I want the Next button to become invisible when the form is on the last record. The simplest way to figure out if the current record was the last one was to compare Me.CurrentRecor d to Me.Recordset.Re cordCount. Here is my code:

Private Sub Form_Current()
Debug.Print Me.Recordset.RecordCount
Debug.Print Me.CurrentRecord

If Me.CurrentRecord = Me.Count Then
    Me.cmdFinish.Enabled = True
    Me.cmdNext.Visible = False
Else
    Me.cmdFinish.Enabled = False
    Me.cmdNext.Visible = True
End If

End Sub

When my Next button didn't become invisible, I added the Debug.Print lines to make sure that the numbers were what I would expect. They were. However, when both Me.CurrentRecor d and Me.Recordset.Re cordCount equaled 3, it still jumped to the Else portion and executed lines 9 and 10. That is what I don't understand. I checked to make sure that the values returned were of the same type, (which they were) but then I realized that if one returned a text and the other a long, then I would get a type mismatch error. I'm guessing that there is some logical explanation of why these can't be compared in this way, but I can't figure it out.

If it makes any difference, the code I'm using to move to the next record is:

Me.Recordset.MoveNext

Not sure if this triggers the OnCurrent event slightly different that would affect the record counting (I don't think so, but just want to provide the information in case it does make a difference).

[ADezii]

Try:

Private Sub Form_Current()
Dim intNumOfRecs As Integer

intNumOfRecs = Me.Recordset.RecordCount

Me.cmdNext.Visible = Not (Me.CurrentRecord = intNumOfRecs)
Me.cmdFinish.Enabled = (Me.CurrentRecord = intNumOfRecs)
End Sub

[Seth Schrock]

That did it. I assume that the main difference is assigning Me.Recordset.Re cordCount to a variable and using the variable in the comparison. Why does that work but using the actual expression doesn't?

[zmbd]

Now I'm sure there's a some reason I shouldn't be doing it this way; however, I've been using the following code for ages on forms when I've needed to re-create my own record navigation buttons. I've never seen the reason to clone and use fancy bookmarking etc... but then again, I've normally used just the DAO record sets and I've yet to have this fail on the FE connected to the SQLServer.
I use the enabled property; however, you can always use the visible.
This is the simplest version of this code.

You will note: The move next button will NOT goto the new record, it stops on the last saved record - this is by design.
I have also removed the code that related to the movefirst, movelast, and newrecord controls as it does not relate to the question.

You should be able to modify this for use in your on_current event; however, I don't see why you would need to do such an action.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo zerrortrap
    With Me
        If .Recordset.RecordCount > 0 Then
            .Recordset.MoveFirst
            !z_ctrl_cmd_moveprevious.Enabled = False
            !z_ctrl_cmd_movenext.Enabled = True
        Else
            !z_ctrl_cmd_moveprevious.Enabled = False
            !z_ctrl_cmd_movenext.Enabled = False
        End If
    End With
Exit Sub
zerrortrap:
    MsgBox Err.Number & vbCrLf & Err.Description, , "error form load event"

End Sub

Private Sub z_ctrl_cmd_movenext_Click()
    On Error GoTo zerrortrap
    With Me
        .Recordset.MoveNext
        If .Recordset.EOF Then
            !z_ctrl_cmd_moveprevious.Enabled = True
            !z_ctrl_cmd_moveprevious.SetFocus
            !z_ctrl_cmd_movenext.Enabled = False
        Else
            !z_ctrl_cmd_moveprevious.Enabled = True
        End If
    End With
Exit Sub
zerrortrap:
    MsgBox Err.Number & vbCrLf & Err.Description, , "error move next click"
End Sub

Private Sub z_ctrl_cmd_moveprevious_Click()
On Error GoTo zerrortrap
    With Me
        .Recordset.MovePrevious
        If .Recordset.BOF Then
            !z_ctrl_cmd_movenext.Enabled = True
            !z_ctrl_cmd_movenext.SetFocus
            !z_ctrl_cmd_moveprevious.Enabled = False
        Else
            !z_ctrl_cmd_movenext.Enabled = True
        End If
    End With
Exit Sub
zerrortrap:
    MsgBox Err.Number & vbCrLf & Err.Description, , "error move previous click"
End Sub

The code has been in use in V97, V2003, and V2010 test and current production databases.

the naming convention before anyone asks:
"z_" mainly because this groups all of the controls in one spot in the intellisense, it's under my little finger, I could have use "A" however it starts my name so a little vanity in my code :)
"ctrl" I use this to designate controls that are not bound to a record set. Started doing this as a troubleshooting step... now I have other reasons.
"cmd" commmand button, "CBO" combobox, "Lst" listbox, etc...

[ADezii]

Below, you are making the comparison against Me.Count which returns the Number of Items in the Default Collection of Me. The Collection that I am referring to would be the Controls Collection of the Form, which of course has nothing to do with the number of Records in the underlying Record Source.

If Me.CurrentRecord = Me.Count Then

[Seth Schrock]

I had tried the Me.Count because I was having issues with Me.Recordset.Re cordCount, but I thought that I had changed it back. Oops. Well that would explain my problem.

[Seth Schrock]

Correction, it works if I step through the code. However, if I just open the form, cmdNext is not visible. Any ideas?

I'll try yours Z to see if that works. It just frustrating that what I have isn't working when I think it should.

[zmbd]

The Open event occurs, then Load event occurs, etc..., then current event. Order of events for database objects open the section on forms/subforms. Therefor, under the automation, the record set hasn't completely loaded so when the on_current triggers, the record is "1" and the recordcount is "1". I've had that happen... don't know why; however, it does.
One possible indication if this is true, then using ADezzii's code, cmdNext should be hidden and cmdFinish should be enabled (say, in design view, disable the cmdFinish in the properties tab - if it is enabled after load then you know that this is exactly what happened!).

I get around such things by using a movelast then movefirst in the on_load event when I need a true record count. In my code block, I'm only interested checking for at least one record... I guess I could have used EOF check instead; however, I don't trust that because on a large record set I've had that check fail whereas I've not had checking for at least one record fail.

OK, time to put the Kids in their cage... I mean Beds and close the door for the night (although one would think we were actually putting them in jail the way they act sometimes!)

[Seth Schrock]

I did a Debug.Print for the two values at the beginning of the OnCurrent event and you were right; both values were 1. I then added the MoveLast/MoveFirst to the OnLoad event and it worked perfectly. Thanks Z.

[zmbd]

one bit of caution there... with a very large data set or over network, the movelast/first can take a performance hit; thus, it might slow the opening of your form.

[Seth Schrock]

Normal record numbers opened would be about 3-4 with 10 being a rarity. However, that information is nice to know Thanks Z.