EOF to gray out nextButton

[BayerMeister]

Hi. What is wrong about this:?

Private Sub Form_Current()
    Forms!A.btnNext.Enabled = (Not Forms!A.Recordset.EOF)
End Sub

There is a Form "A" and a button "btnNext" on it. onCurrent I'd like to gray or not to gray it out depending on whether the last record has been reached or not.

What is wrong about the code above? It doesn't work.
Forms!A.Recordset.EOF seems to return only FALSE (even at end of RecordSet).

[ryan1313]

The EOF Property is only set to true when no record exists, so as long as there is a current record, it will return false.

To do what you want to do you can do this:

 
Dim rst As Recordset
Set rst = Forms!A.RecordsetClone
If Forms!A.CurrentRecord = rst.RecordCount Then
    Forms!A.btnNext.Enabled = False
Else
   Forms!A.btnNext.Enabled = True
End If
set rst = Nothing

[BayerMeister]

Thanks. I thought BOF and EOF are for telling if on the beginning and the end of a set. Fine. Will do it your way.

[BayerMeister]

One issue remains: What event should this code be fired?

I assumed onCurrent is the right one, but it has a ... "imperfection":

The first onCurrent seems to be fired before the all records are loaded.
So when walking through the records, this works fine, but in the beginning (opening the form) the onCurrent is fired and the RecordCount is "1" (I have 3 records and the number should be therefore "3"). So the "next" and "previous" buttons are grayed out from the beginning. The event is not fired again until I move (change record)... which is impossible if both buttons are grayed out.

Ideas? I have a message box on most events and it seems no one is firing (even Paint events do not fire) only Form_Open, Form_Load and Form_Current – in this order with the RecordCount == 1

[RuralGuy]

Try this:

Dim rst As Recordset
Set rst = Forms!A.RecordsetClone
rst.MoveLast
If Forms!A.CurrentRecord = rst.RecordCount Then
    Forms!A.btnNext.Enabled = False
Else
   Forms!A.btnNext.Enabled = True
End If
set rst = Nothing

[BayerMeister]

The code from Ryan1313 works fine. What difference would this make? It looks like it just moves the current position to the end... and thereby wouldn't provide relevant results (would always disable the btnNext).

The remaining question is: What events to use. Where and when do I execute this code?

And thats a thing I'm going to (try to) find out right now.

[BayerMeister]

EDIT: All wrong. See next post!

I found all the trouble comes in with the subform. In other words the event onCurrent is fine and all works when you have a simple form. When I add a subform, it gets confusing.

What I wrote above ("One issue remains..."), that applies only to this situation.

Here's an illustration to make this more fun. My form:

• When opening the form (A) during the onCurrent event rst.RecordCount is 1 (thereby the code doesn't work - buttons grayed out)
• The form B has almost the same code. When inserted as a subform the execution stops: Form B is not known. It can be fixed with changing "B" to "A.B.Form", where the last "B" is the name of the subform component on the "A" form.
• Anyway, let's leave B aside: How do I get the A form running?

I attach the database (zipped Access 2007 db). If someone knows how to fix, I'll be grateful.

[BayerMeister]

New knowledge acquired:
As I often do so, I made the form from scratch again. You wouldn't believe it - it worked! Why?

I was looking for differences between almost identical pairs of forms and found the difference "that makes a difference".

As soon as I hide the built-in navigation buttons (at the bottom of the form - Form.NavigationButtons Property) the gray-out code stops working. It is not about a subforms, just about the navigation buttons displayed or hidden.

So, is that a feature or what? Anyone heard about this "feature"? What should I do?
I believe everyone understands that my buttons make sense only iff the buil-in ones are hidden.

Please could anyone try to make a form with simple navigation buttons (next and previous suffice) that would work and mimic the behavior of the built in NavigationButtons (with gra-yout!). Thanks. I'll try it again, maybe I'll come over something else.

[maximus]

I have a table tbltech a form tbltech. I have used this two simple code in the onClick event of the previous and next record buttons.


Previuos Button:
Dim intFormCount As Integer
Dim intTotalRecordCount As Integer
Me.Command9.Enabled = True
intFormCount = Forms!tbltech.CurrentRecord
intTotalRecordCount = IIf(IsNull(DCount("[techid]", "tbltech")), 0, DCount("[techid]", "tbltech"))
If intFormCount = 1 Or intTotalRecordCount = 0 Then
MsgBox "End of record"
DoCmd.GoToControl "techid"
Me.Command11.Enabled = False
Exit Sub
End If
DoCmd.GoToRecord , , acPrevious

Next Button:

Dim intFormCount As Integer
Dim intTotalRecordCount As Integer
Me.Command11.Enabled = True
intFormCount = Forms!tbltech.CurrentRecord
intTotalRecordCount = IIf(IsNull(DCount("[techid]", "tbltech")), 0, DCount("[techid]", "tbltech"))
If intFormCount = intTotalRecordCount Or intTotalRecordCount = 0 Then
MsgBox "End of record"
DoCmd.GoToControl "techid"
Me.Command9.Enabled = False
Exit Sub
End If
DoCmd.GoToRecord , , acNext

[BayerMeister]

I see, that avoids the warning message if going out of borders. I can make good use of that. Thanks!

It would be much more elegant to do it the gray-out way though.

[maximus]

well look at the code carefully the buttons are disabled when the last record is reached:

DoCmd.GoToControl "techid"
Me.Command9.Enabled = False

just leave the comment the msgbox line in the code.

[BayerMeister]

Oh yes, I think I noticed when I was reading the code.

It does not solve the start of the form (gray out or not to gray out). And I don't know yet (haven't tested) how often the form submits changes to the table (so that "tbltech" would be outdated). Like when an item gets added, removed... and these are by the way other events that need this code.

The other way (use events) would cover it in an elegant way (if it worked). I'll try to use a piece of your code there
IIf(IsNull(DCount("[techid]", "tbltech")), 0, DCount("[techid]", "tbltech"))
instead of the RecordCount. Maybe this will go together.

Will post the result, but won't have a chance to do so the next 3 hours.

[maximus]

well u say u have read the code thats nice. Let me draw you attention to the following:

intFormCount = Forms!tbltech.CurrentRecord
intTotalRecordCount = IIf(IsNull(DCount("[techid]", "tbltech")), 0, DCount("[techid]", "tbltech"))
If intFormCount = 1 Or intTotalRecordCount = 0 Then
MsgBox "End of record"
DoCmd.GoToControl "techid"
Me.Command11.Enabled = False

intFormCount = Forms!tbltech.CurrentRecord
intTotalRecordCount = IIf(IsNull(DCount("[techid]", "tbltech")), 0, DCount("[techid]", "tbltech"))
If intFormCount = intTotalRecordCount Or intTotalRecordCount = 0 Then
MsgBox "End of record"
DoCmd.GoToControl "techid"
Me.Command9.Enabled = False

The previous button will be disable whe u try to go beyond the first record this is ensured by the line:

If intFormCount = 1 Or intTotalRecordCount = 0 Then

so when the form loads the button is still enabled.

in Case of the next button the next button is disabled when you try to go past the last record. this is ensured by :
If intFormCount = 1 Or intTotalRecordCount = 0 Then

so the conclusion when the for is opened both the buttons are enable. If u press the previous button on the first record it is disabled. When you press the next button to navigate to the next record the previous button is enabled again. when you navigate to the last entry on the form and try to go beyond it the next button is disabled now u can use the previous button to navigate back to the previous record.



"It does not solve the start of the form (gray out or not to gray out). And I don't know yet (haven't tested) how often the form submits changes to the table (so that "tbltech" would be outdated). Like when an item gets added, removed... and these are by the way other events that need this code."

Items getting added and deleted has no bearing on the navigation using these buttons.

I hope I was able to explain.

[BayerMeister]

Thanks for the inspiration. It works now the way I wanted and here's what there is.

A brief description of variables is in a previous post (look for pictures).

The Current event of the form A

Private Sub Form_Current()
    intCurrent = Forms!A.CurrentRecord
    intTotalRecordCount = IIf(IsNull(DCount("[id]", "A")), 0, DCount("[id]", "A"))
    Forms!A.btnNext.Enabled = (intCurrent < intTotalRecordCount)
    Forms!A.btnPrev.Enabled = (intCurrent > 1)
End Sub

The Current event of the subform B embedded within the A form:

Private Sub Form_Current()
    aId = Forms!A.id
    intCurrent = Forms!A.subformB.Form.CurrentRecord
    
    intTotalRecordCount = DCount("[id]", "B", "a_id = " & aId)
    intTotalRecordCount = IIf(IsNull(intTotalRecordCount), 0, intTotalRecordCount)
    
    Forms!A.subformB.Form.btnNext.Enabled = (intCurrent < intTotalRecordCount)
    Forms!A.subformB.Form.btnPrev.Enabled = (intCurrent > 1)
End Sub

All working OK. I'm not doing anything in the button events, just in the Current event.

[maximus]

well I am gled everything worked out this looks much better. Mark the thread solved! Well Done