Hiding navigation buttons

[Paul Henderson]

I have a form that is loaded with a recordset of one, two
or three records. I have created navigation command
buttons to scroll through the records, but I do not want
the buttons to appear if the form's recordset has only one
record (mimicking the action of the in-built form buttons
at the lower left of any form).

I assume that it is the buttons visible property that
needs to toggle, but I don't know what condition to link
it to.

I have dabbled a little with VBA (mainly to hide/show
objects) but am not a programmer.

Can you suggest a solution please? Thank you in advance.

 

[Guest]

This should work.
Replace tbl with the data source ie the table or query
where the date comes from and btn with the name of your
button, copy these for however many buttons you have.
Use it in the form on open or on load event.

Private Sub Form_Open(Cancel As Integer)

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl")

If rst.RecordCount = 1 Then
Me.btn.Visible = False
Else
Me.btn.Visible = True
End If

End Sub

 

[Paul Henderson]

I tried the VBA you suggested, but it hides the buttons
all of the time, not just when there is only one record in
the recordset. So it worked to a point, but could not
differentiate between one record and two (which requires
the buttons to remain visible).

I have tried several times to get it to work properly, but
to no avail. I am using Access 2002, if that helps.

Can you please suggest a solution?

 

[George Nicholson]

The Recordcount property is unreliable unless you specifically force the
table to populate itself. This is customarily done with a MoveLast command.
Until you do this Recordcount is quite likely to return a value of 1,
regardless of the actual number of records. This behaviour is by design,
not a bug (and it also makes sense once you think about it).

The following is a revision of a part of the suggested code with the 2nd &
3rd lines added (one to populate the recordset, the other to reposition the
pointer back at the 1st record - which you may not need to do).
Note: MoveLast can be very expensive if you have a large recordset.

Set rst = db.OpenRecordset("tbl")
rst.MoveLast
rst.MoveFirst
If rst.RecordCount = 1 Then

Hope this helps,