Prevent Record Navigation through Scroll-Down and Wheel-Down

[jaytech]

I have a table with one record. That record is used to populate a form with information. I needed to prevent a user from paging down to the next “blank” record in the table. I turned off all of the record nav options in the form, but the user could still use page-down and mouse wheel-down to navigate to the next record.

So, I googled it, and found a couple solution that didn’t work for me. After thinking about it for a couple minutes a solution came to me. And since I didn’t see it anywhere on the “interweb”, I figured I’d post it here for others to enjoy/flame.

These two subroutines, copied and pasted into the form's VBA, will counter these two methods. Basically, if the user Wheels Down or hits Page-Down, it will send a Page-Up to counter. Simple, elegant... here it is:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
‘If user hits page-down, send page-up to counter
If KeyCode = 34 Then SendKeys "{PGUP}"
End Sub

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
‘If user uses the mouse to wheel-down, send page-up to counter
If Count = 3 Then SendKeys "{PGUP}"
End Sub

Note: You must set “Key Preview” to Yes in the forms properties for this to work.
I tried adding the ability to block wheelups and page-ups, but it gets tricky because of an infinite loop (user presses page-up, subrountine presses page-down, subroutine presses page-up, subroutine presses page-down, etc.). I'm sure there is a way around this, but I'm lazy.

 

[missinglinq]

With the exception of Stephen Lebans' hack for this task, and one other (sorry, don't remember the author) every one I've ever seen works "except for one thing!"

But since you only have/need a single record, this is much simpler, and doesn't require use of the problematic SendKeys:

Private Sub Form_Current()
 If Me.RecordsetClone.RecordCount > 0 Then
  Me.AllowAdditions = False
 Else
  Me.AllowAdditions = True
 End If
End Sub

I've used this for years to only allow one record in a table that is used to store company contact info for use in letterheads of forms, invoices, etc.

 

[jaytech]

Hah, I tried messing around with the Form_Current() Event, but couldn't make it do my bidding. Thanks!

 

[missinglinq]

You're most welcome!