Avoid/Prevent Can't Save Record Error Message

[LinusIT]

Hi

I have a form which is used to enter car parts, the form has a Save button and the X at the top right hand corner. I have the following code in place to make sure the user enters the required fields.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control, Source As String
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Controls(0).Caption & "' field" & nl & _
"You can't save this record until this data is provided" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next
End Sub

I have a number of problems with this though:

1) If the user moves the wheel on the mouse, it tries to move to a new record and gives validation error.

Is there any way to remove the ability of mouse scrolling?

2) If the user presses the X in the top corner, it gives the validation error message and also shows the "You can't save this record at this time" error message.

Is there a way of changing that error message and upon the user clicking "yes" cancelling the data and closing the form?

I have searched for a solution but come up with nothing.

 

[ajetrumpet]

the mouse scrolling issue is huge, and i'm sure it's been solved but you will have to search heavily to find out how to turn it off, unless someone just flat out tells you here.

as far as the red x, i would turn them off in the props. make the user close the form from controls that you have made. shut the access default objects like form buttons off completely.

 

[missinglinq]

Doesn't take much of a search! A gentleman by the name of Stephen Lebans has had a a sample database out there for years that does this and it can be downloaded at:

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

First, download and unzip the db and take a look. Go into your db and goto File > External Data > Import and import the module modMouseHook from the sample database. Next make sure you have the included file, MouseHook.dll, in the same folder your database resides in. The following code needs to run before the mousewheel will be locked:

Private Sub Form_Load()
 'Turn off Mouse Scroll
 blRet = MouseWheelOFF
End Sub

If you have one form that always loads first in your db, place the code there. If the first form to load varies, place the same code in each form.

You should be set now. BTW, Microsoft finally addressed this problem in Access 2007, with a property that allows you to control the mousewheel.

 

[ajetrumpet]

Microsoft finally addressed this problem in Access 2007, with a property that allows you to control the mousewheel.

seriously!? where is it at!? I never noticed.

 

[Rich]

Doesn't take much of a search! A gentleman by the name of Stephen Lebans has had a a sample database out there for years that does this and it can be downloaded at:

There's been one on here for years too
http://www.access-programmers.co.uk/forums/showthread.php?t=38364&highlight=Mouse

 

[ghudson]

There's been one on here for years too
http://www.access-programmers.co.uk/forums/showthread.php?t=38364&highlight=Mouse

Yes Rich, an oldie but a goodie... A Better Mouse Trap? ;-)

 

[LinusIT]

Cool, that's great. That's 1 problem sorted.

Any ideas on the "Can't save record" issue.

 

[missinglinq]

seriously!? where is it at!? I never noticed.

I don't run 2007, but it is there and I understand that it is a form property.

 

[SOS]

I don't run 2007, but it is there and I understand that it is a form property.

Yep, and you can set an action to work with it or you can just leave it blank to disable the mousewheel.

 

[ghudson]

Yes, Access 2007 has the Form.MouseWheel Property ... http://msdn.microsoft.com/en-us/library/bb256786.aspx