Command Button visibility

Question

I have a Form(ClientF) that will be used as a SubForm. Basically, the form(CompanyF) has the Company info and the ClientF subform will display contacts for that company. I have removed the navigation and record selectors on the subform and added my own Command buttons to control moving from one record to another within the SubForm. 

What I am having trouble figuring out is... 

I would like the buttons to either change color or be invisible if there is no next/previous record. I am able to alter the code in an embedded macro to change the color once the user clicks the button and reaches the last record, but it remains that color going forward unless the form is closed and reopened. Is it possible to have the buttons formatting change once the user is viewing the last/first record and only if it's currently on the last/first record? 

Currently I have the Buttons (PrevRecBTN & NextRecBTN) using an basic embedded macro, and a current_form Macro disabling the buttons with the following code: 

Private Sub Form_Current()

    cmdPrevRecBTN.Enabled = Not (CurrentRecord = 1)

    cmdNextRecBTN.Enabled = Not (CurrentRecord = DCount(AnyField, RecordSource))

End Sub

Any suggestions on another way to accomplish the formatting/visibility on the buttons?

Answer 1

Thanks Bill, 

There's just too much that's unneeded in your code and it still doesnt do what Im looking for. 

I only need the Previous and Next buttons on my form and all I want is for them to be visible (either remove the button or change color) only if there is a previous or next record available.

Answer 2

Or more generically, replace:

lngRecCt = DCount("*", "DataExtractionAccessLog")

with:

with me.recordsetclone

  if .recordcount>0 then .movelast

  lngRecCt = .recordcount

end with

Answer 3

Here is a routine I use for custom record navigation buttons. Add a tiny text control named txtFocus. This will be used to take the focus off of the buttons. Name your buttons the way I did (cmdGoFirst, cmdGoLast, etc). Call the routine in your form's Current event and each nav button Click event.

Public Sub CheckPosition(frm As Form, ParamArray ctrls())

'Purpose  : Enable/disable nav buttons.

'           txtFocus is a textbox that is used only to get

'           Focus during enabling/disabling. It is just a

'           dot on the form plced near nav buttons.

'DateTime : 11/4/2013 12:08

'Author   : Bill Mosca

    Dim lngRecNum As Long

    Dim lngRecCt As Long

    Dim x As Integer

    lngRecNum = frm.CurrentRecord

    lngRecCt = DCount("*", "DataExtractionAccessLog")

    frm.txtFocus.SetFocus

    frm.cmdGoFirst.Enabled = lngRecNum <> 1

    frm.cmdGoLast.Enabled = lngRecNum <> lngRecCt

    frm.cmdGoNext.Enabled = lngRecNum <> lngRecCt

    frm.cmdGoPrev.Enabled = lngRecNum <> 1

    frm.cmdGoNew.Enabled = lngRecCt <> 0

    'No Additions should disable "New" button

    If frm.AllowAdditions = False Then

        frm.cmdGoNew.Enabled = False

    End If

    'If any cbo's or lst's have a user-defined function for RowSourceType

    'they will need to be requeried.

    If Not IsNull(ctrls) Then

        For x = 0 To UBound(ctrls)

            If ctrls(x).ControlSource = "" Then ctrls(x) = Null

            ctrls(x).Requery

        Next

    End If

End Sub