stopping the scroll 1

[SuryaF]

There are tons of information on the internet on how to stop the scrolling through records. Most of them require an external dll or ActiveX so I had to find something else (due to the way my application is distributed).
I found a way to do this with a few lines of code in VBA, it’s not the most elegant but it works very well in my application. So here it is, in case somebody else can use it.

Situation:
In my application I various forms that display only one record at a time (Single Form view). None of these forms is designed to allow navigation through records, I always open just one record (from a list).
The same form used for editing a record is used for adding a record as well. I never add two records in the same action.
The form has the Cycle property set to Current Record and the AllowAdditions property is set to true only when the form is used for adding a new record (Me.AllowAdditions = Me.DataEntry in the Form_Load event procedure).

The problem:
When I’m in the DataEntry mode (for adding a new record) using the scroll wheel determines unwanted navigation to the next new record.

Solution:

'in the module declarations add:
Dim fromwheel As Boolean 

'in the Form_Current event procedure add
Private Sub Form_Current()
If Me.DataEntry And fromwheel Then
    'this is an unwanted scrolling, go back to the previous record (which is the first one as well)
    fromwheel = False 'clear the flag
    DoCmd.GoToRecord , , acFirst
End If
End Sub

'in the on Form_MouseWheel event procedure add
Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
If Count > 0 And Me.AllowAdditions Then fromwheel = True
'signals to the following Form_Current event that this is an unwanted scrolling
End Sub

 

[SuryaF]

One more thought. The solution above doesn't stop the update that comes from record navigation.

There are situations when you don't want the record to be saved by mistake with the accidental scrolling. To do that add the following code to the Form_BeforeUpdate event procedure:

If Me.DataEntry And fromwheel Then
    'this is an unwanted scrolling, cancel the update
    fromwheel = False 'clear the flag
    Cancel = True
End If

 

[irethedo]

I like this code and tried it on my single form as I am experiencing the same problem with the thumbwheel on the mouse advancing to a new record but this does not stop the problem for me...

I added all the info that you provided above, is there something else missing that I need to do?


Please advise

 

[missinglinq]

No offense meant, but one of the wider used methods of wheel locking is found on Labans site at

http://www.lebans.com/mousewheelonoff.htm)

and requires a dll that is already present on any Windows machine, importing a single module, and one line of code. It works in any Access apps, not just the rather specialized one you describe.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SuryaF]

Hi missinglinq,
You're right, the solution from lebans.com is more elegant and easier to implement but it has a big disadvanage for me: it stops the scroll for the continuous subforms.
That was a big deal for me so I tried to find another way.
Sincere thanks for your input!

 

[SuryaF]

Hi irethedo,
Did you define the variable globally for the entire module?
Can you post the code here? Maybe there's a problem with it.
Cheers!

 

[irethedo]

Hi SuryaF-

I pretty much cut and pasted your code unless I inadvertently left something out...

I hope I can get this to work as I have a few continuous forms in my database too which I want to use one of which opens this form. Is there anything special that i need with the open form command to make this code work?

I have one module called module1 where I placed the code:

Dim fromwheel As Boolean

On my form, in the "on current" event I have the following code:

Private Sub Form_Current()
If Me.DataEntry And fromwheel Then
'this is an unwanted scrolling, go back to the previous record (which is the first one as well)
fromwheel = False 'clear the flag
DoCmd.GoToRecord , , acFirst
End If
End Sub

On my form in the "before update" event I have this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.DataEntry And fromwheel Then
'this is an unwanted scrolling, cancel the update
fromwheel = False 'clear the flag
Cancel = True
End If
End Sub

on my form in the wheel up event I have placed the following code:

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal count As Long)
If count > 0 And Me.AllowAdditions Then fromwheel = True
'signals to the following Form_Current event that this is an unwanted scrolling
End Sub

My form has the other following parameters:

Default View = Single form
Allow form view = yes
Allow datasheet view = No
Allow pivot table view = no
record selectors = no
navigation buttons = no
The record source is a table

Thanks

 

[SuryaF]

Try adding the following code in the Form_Open event:

Me.AllowAdditions=Me.DataEntry

This way when you open for editing, the form won't allow a new record.
The code you have right now only works when you open the form in Data Entry mode (with the acFormAdd option).
HTH

 

[irethedo]

Thanks SuryaF-

Adding the Me.AllowAdditions=Me.DataEntry
in the Form_Open event doesn't change anything for me..

When I have this form open and if I wheel the mouse wheel all of the fields (which I load with values on the load form event) go blank and if I scroll the mouse back up they reappear... at that point I can then select drop down lists but can not select any of the values...

 

[SuryaF]

I can't tell, can you upload your database on an ftp site so I can take a look?

 

[irethedo]

Sorry I do not have an FTP site and my code is huge...

Is there any other way to disable the mouse wheel and then re-enable it?

 

[SuryaF]

Just that form would suffice but if you can't upload it I'm affraid I can't find out why your code is not working.
As missinglinq said, the most common solution is the one given on the lebans.com, try it out.
All the best!

 

[irethedo]

But if I understand you correctly, the solution on Lebans.com also disables the thumbwheel for all forms...

I am not sure if this matters or not but here it he comand that i use to launch the form:

DoCmd.OpenForm "BatchFail form", , , , , acDialog ' Open batch fail form

 

[SuryaF]

a few more questions:

A. How many records do you have in your table? I see you don't apply any WHERE condition to your OpenForm command.

B. Do you ever add records with your form? If not then you simply set the Allow Additions to no and it's all good.

C. Do you navigate between records with your form. If you don't then set the Cycle property to Current Record Only. If you DO, then this code might not be the best choice (as specified in my first post).

your thoughts?

 

[irethedo]

Hi SuryaF-

Sorry for the delay, I have been busy the past few days...
I will answer your questions below:

a few more questions:

A. How many records do you have in your table? I see you don't apply any WHERE condition to your OpenForm command.

Before I open the form, I create one record in a table. In the load event for the form, I load the fields with the record. When I exit the form, I load the data into a different table and delete the 1 record in my original table.

B. Do you ever add records with your form? If not then you simply set the Allow Additions to no and it's all good.

No I do not use the form to add records and I already had the "Allow Additions set to no" ...

C. Do you navigate between records with your form. If you don't then set the Cycle property to Current Record Only. If you DO, then this code might not be the best choice (as specified in my first post).

No I do not navigate between records with my form and already had the "Cycle property set to Current Record".

To see what was happening, I put a record selectors and navigation buttons on my form. When my form loads the data is set into the fields by code in the load event. This data appears on my form and the record selector indicates record #1. If I scroll the mouse wheel, the record selector indicates record #2 and all fields are blank. The mouse wheel will only display record 1 or record 2 even though there is only one record in the table...

If I then look at my one record table there is only one record displayed...

I am baffled on this one..

 

[SuryaF]

Yes, this is quite strange.
You said:

In the load event for the form, I load the fields with the record.

Can't you just have the form linked on that table in the first place?
Can you paste your Form_Load event in here? I think that's what's creating the problem.

 

[irethedo]

Private Sub Form_Load()
Dim db As Database, objrs As DAO.Recordset
Dim fs, f 'file system variables
Dim strTermId As String, test As Integer, evnt As Integer, tevent As String
Const ForReading = 1, TristateUseDefault = 2


'Get terminal ID from root of C:/ drive
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\TermID.txt", ForReading, TristateUseDefault)
strTermId = f.ReadLine
f.Close

Set objrs = CurrentDb.OpenRecordset("SELECT * FROM [oneRecordtbl] WHERE TermID = '" & strTermId & "'")

Me!Operator = objrs("operator")
Me!operater = objrs("Operation")
Me!asm_nbr = objrs("AssemblyNumber")
Me!SerialNumber = objrs("SerialNumber")
Me!KBN = objrs("Kanban")

test = objrs("EventType")

evnt = 0

Select Case test
Case 1 ' first time fail
'Me!TestEvent = 2
pass1 = 0
fails = 1
tfail = 1
spas = 0
evnt = 2
tevent = "First Time Fail"

Case 2 ' subsequent fail
pass1 = 0
fails = 0
tfail = 1
spas = 0
evnt = 4
tevent = "Subsequent Fail"

Case 3 ' first time pass
pass1 = 1
fails = 0
tfail = 0
spas = 0
evnt = 1
tevent = "First Time Pass"

Case 4 ' subsequent pass
pass1 = 0
fails = 0
tfail = 0
spas = 1
evnt = 3
tevent = "Subsequent Fail"

End Select
Me!tevent = tevent

End Sub

 

[SuryaF]

I don't think this is optimized but it's not important.
Please check the Data Entry property of your form. I think you have it set to yes. If this is true please do the following:
Add:
Me.DataEntry=False
Me.AllowAdditions=False
at the end of your Form_Load event.
You can ignore all of the code described in this thread, it doesn't apply to you.
Let me know how it goes.

 

[irethedo]

Thanks SuryaF but no change..

I am not sure what to do about it except make sure that they do not use a mouse with a wheel on it...

 

[scottian]

I have a much easier way of disabling the mouse wheel.
I create a query based on the table/query that the form uses. Then i set it as top 1. once the user had updated the necessary fields simply requery the form. the user then cannot procede to any record until they have dealt with that one.

"My God! It's full of stars...