Message Boxes...Help please!

G

giwalls

Please can anyone help. I want a message box to pop up if a value in a form
is changed. It is a booking rate for a village hall. If a change is made I
want the user to see a dialog box saying "Are you sure you want to change the
current booking rate?" and then have a yes/no option. When I have tried it
so far I only get an okay button which does not help to prevent user error.
Any ideas please?
 
T

Tom Wickerath

Hi Giwalls,

Try the following Form_BeforeUpdate event procedure. To use this code, open
the form in design view. Click on View > Code. Copy the procedure shown below
and paste it into the form's code module. Then click on Debug > Compile
ProjectName.


Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Dim lngResult As Long

lngResult = MsgBox("Would you like to save your change?", _
vbQuestion + vbYesNo, "Data Change Detected...")

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub


If you want this message to display only for existing records, but not when
you are adding new records, then add an IF....THEN test, as indicated below:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

If Not Me.NewRecord Then
Dim lngResult As Long

lngResult = MsgBox("Would you like to save your change?", _
vbQuestion + vbYesNo, "Data Change Detected...")

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Please can anyone help. I want a message box to pop up if a value in a form
is changed. It is a booking rate for a village hall. If a change is made I
want the user to see a dialog box saying "Are you sure you want to change the
current booking rate?" and then have a yes/no option. When I have tried it
so far I only get an okay button which does not help to prevent user error.
Any ideas please?
 
G

giwalls

Tom

It gave the following error "Invalid use of Me keyword" in the string

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If

I am well out of my depth here!

Do you know why this is saying this?

giwalls
 
T

Tom Wickerath

Hi Giwalls,

It sounds like you pasted the code into a new general module instead of into
a module associated with the form. The Me keyword is only valid for code in
form and report modules; it will produce the exact error you quoted if used
in a stand-alone module. Me refers to the name of the parent form or report.
A stand alone module has no such property. You could modify the code I
provided, by passing in the name of the form as a parameter, but that's above
the scope of my original answer.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom

It gave the following error "Invalid use of Me keyword" in the string

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If

I am well out of my depth here!

Do you know why this is saying this?

giwalls
__________________________________________

:

Hi Giwalls,

Try the following Form_BeforeUpdate event procedure. To use this code, open
the form in design view. Click on View > Code. Copy the procedure shown below
and paste it into the form's code module. Then click on Debug > Compile
ProjectName.


Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Dim lngResult As Long

lngResult = MsgBox("Would you like to save your change?", _
vbQuestion + vbYesNo, "Data Change Detected...")

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub


If you want this message to display only for existing records, but not when
you are adding new records, then add an IF....THEN test, as indicated below:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

If Not Me.NewRecord Then
Dim lngResult As Long

lngResult = MsgBox("Would you like to save your change?", _
vbQuestion + vbYesNo, "Data Change Detected...")

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Please can anyone help. I want a message box to pop up if a value in a form
is changed. It is a booking rate for a village hall. If a change is made I
want the user to see a dialog box saying "Are you sure you want to change the
current booking rate?" and then have a yes/no option. When I have tried it
so far I only get an okay button which does not help to prevent user error.
Any ideas please?
 
G

giwalls

Tom, thanks again. You were right I somehow managed to put the code into a
new module not into the form's code. It worked a treat. Thanks for help.

giwalls
 
T

Tom Wickerath

You're welcome. And thank you for marking two of my replies as answers. This
is appreciated.

Good luck on your project.

Tom
_____________________________________________

:

Tom, thanks again. You were right I somehow managed to put the code into a
new module not into the form's code. It worked a treat. Thanks for help.

giwalls
 
T

Tom Wickerath

Correction:

Me refers to the name of the parent form or report.
Click to expand...

I should have wrote:
Me refers to parent form or report.

Me.Name refers to the name of the parent form or report.

Tom
______________________________________________

:

Hi Giwalls,

It sounds like you pasted the code into a new general module instead of into
a module associated with the form. The Me keyword is only valid for code in
form and report modules; it will produce the exact error you quoted if used
in a stand-alone module. Me refers to the name of the parent form or report.
A stand alone module has no such property. You could modify the code I
provided, by passing in the name of the form as a parameter, but that's above
the scope of my original answer.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom

It gave the following error "Invalid use of Me keyword" in the string

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If

I am well out of my depth here!

Do you know why this is saying this?

giwalls
__________________________________________

:

Hi Giwalls,

Try the following Form_BeforeUpdate event procedure. To use this code, open
the form in design view. Click on View > Code. Copy the procedure shown below
and paste it into the form's code module. Then click on Debug > Compile
ProjectName.


Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Dim lngResult As Long

lngResult = MsgBox("Would you like to save your change?", _
vbQuestion + vbYesNo, "Data Change Detected...")

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub


If you want this message to display only for existing records, but not when
you are adding new records, then add an IF....THEN test, as indicated below:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

If Not Me.NewRecord Then
Dim lngResult As Long

lngResult = MsgBox("Would you like to save your change?", _
vbQuestion + vbYesNo, "Data Change Detected...")

If lngResult = vbNo Then
Cancel = True
Me.Undo
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Please can anyone help. I want a message box to pop up if a value in a form
is changed. It is a booking rate for a village hall. If a change is made I
want the user to see a dialog box saying "Are you sure you want to change the
current booking rate?" and then have a yes/no option. When I have tried it
so far I only get an okay button which does not help to prevent user error.
Any ideas please?
 
Top