Hiding navigation buttons

P

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.
 
G

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
 
P

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?
 
G

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,
 
P

Paul Henderson

Thanks for your assistance George, but I am still having
problems.

Your advice works when I have a test form based on a
table, or on query where I enter a value in the criteria
field to make the query give me either 1 or 2 records.
the navigation buttons on the form then appear (for 2
records) and disappear (for only one record), as you say.

However, the real form I am using is based on a query
(eg "myquery") and is opened by using a combo selector on
another form, where I select (in this case) a person ID
and the combo's 'On change' property then opens the main
form using a macro with the 'Where...' condition
containing the combo path for the Person ID source. The
main form with the navigation buttons then opens and
displays (in this case) details of marriages that the
selected person has had. However, it seems that the
selection process bypasses the suggested VBA in some way,
whereby the recordcount value of 1 is not recorded and
therefore the navigation buttons (for next
marriage/previous marriage)never disappear when there is
only (as far as I can see) one record.

I have used the suggested VBA with the 'On Open', 'On
load', 'On Activate' and 'On current' properties of the
main form, but the result is always the same - the navi
buttons never disappear.

I then tried to use a different selection mechanism, by
entering the selector combo's path in "myquery" query's
criteria field and deleting the 'Where ...' from the Open
Main Form macro, which should achieve a similar result as
far as opening the main form is concerned, but before the
main form opened, I got an error message which stopped the
VBA at the point of reading the query that the form was
based on (set rst = db.OpenRecordset ("myquery")). The
error message was 'Run time error 3061: Too few
parameters. Expected 1.'

Do you have any ideas as to why this is happening please?
All I want is for the navigation buttons to appear if
there is more than one record to navigate to and to
disappear when there is only one record.
 
Top