VBA help please!

[annie]

I have created a VBA procedure for a form intended to prevent the user *from
moving out of the current record* if the date entered in one field is not
more recent than the date entered in another field.

I initiate the procedure using the BeforeUpdate event for the form, and a
message box is displayed if the error condition is true.

The problem is, I don't know how to keep the user from moving to a different
record. I want to retain but not save any changes that the user may have
made to field values in the current record, but prevent the user from moving
on until they have corrected the error for that record.

Thanks in advance from a VBA newbie!

 

[Arvin Meyer]

You can hide the navigation buttons until the date is entered. Change the
cycle property of your form to the Current record, and add this code to your
before update event:

If Len([DateField] & vbNullString) = 0 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

Disable or lock anything that would allow a move.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[tina]

you should be able to accomplish that simply by setting Cancel equal to True
in the BeforeUpdate procedure, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me!OneDate > Me!TwoDate Then
MsgBox "TwoDate must be in the future of OneDate."
Cancel = True
End If

End Sub

the update is stopped, the focus stays on the current record, and the data
entered in the current record is not erased or changed in any way. the user
must either change the data so that it passes the "If..." test, or manually
erase all changes to the record (by pressing the Esc key, for instance).

if you're setting the Cancel value to True but not getting this result,
suggest you post your code so we can look at it.

hth

 

[annie]

This would work if the user needed to click on a navigation button to move
from the current record, but this is not the case. The records are being
displayed as continuous and not single.

Thanks

You can hide the navigation buttons until the date is entered. Change the
cycle property of your form to the Current record, and add this code to
your
before update event:

If Len([DateField] & vbNullString) = 0 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

Disable or lock anything that would allow a move.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I have created a VBA procedure for a form intended to prevent the user *from
moving out of the current record* if the date entered in one field is not
more recent than the date entered in another field.

I initiate the procedure using the BeforeUpdate event for the form, and a
message box is displayed if the error condition is true.

The problem is, I don't know how to keep the user from moving to a different
record. I want to retain but not save any changes that the user may have
made to field values in the current record, but prevent the user from moving
on until they have corrected the error for that record.

Thanks in advance from a VBA newbie!

 

[Arvin Meyer]

I'm not sure why the setting Cancel argument in the BeforeUpdate event isn't
working for you. I've never found it to fail to stop the process unless the
user hits Ctrl+Alt+Delete. Can you tell us more about how it fails? What
error messages are you getting, if any?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

This would work if the user needed to click on a navigation button to move
from the current record, but this is not the case. The records are being
displayed as continuous and not single.

Thanks

You can hide the navigation buttons until the date is entered. Change the
cycle property of your form to the Current record, and add this code to
your
before update event:

If Len([DateField] & vbNullString) = 0 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

Disable or lock anything that would allow a move.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I have created a VBA procedure for a form intended to prevent the user *from
moving out of the current record* if the date entered in one field is not
more recent than the date entered in another field.

I initiate the procedure using the BeforeUpdate event for the form, and a
message box is displayed if the error condition is true.

The problem is, I don't know how to keep the user from moving to a different
record. I want to retain but not save any changes that the user may have
made to field values in the current record, but prevent the user from moving
on until they have corrected the error for that record.

Thanks in advance from a VBA newbie!

 

[annie]

I hadn't said that setting Cancel didn't work; I simply had not tried it
before I responded to your reply - sorry for the confusion. I tried setting
Cancel today and it worked just fine.

Thanks for your suggestion. Now I know that I can control the navigation
buttons programmatically!

I'm not sure why the setting Cancel argument in the BeforeUpdate event
isn't
working for you. I've never found it to fail to stop the process unless
the
user hits Ctrl+Alt+Delete. Can you tell us more about how it fails? What
error messages are you getting, if any?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

This would work if the user needed to click on a navigation button to
move
from the current record, but this is not the case. The records are being
displayed as continuous and not single.

Thanks

You can hide the navigation buttons until the date is entered. Change the
cycle property of your form to the Current record, and add this code to
your
before update event:

If Len([DateField] & vbNullString) = 0 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

Disable or lock anything that would allow a move.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I have created a VBA procedure for a form intended to prevent the user
*from
moving out of the current record* if the date entered in one field is not
more recent than the date entered in another field.

I initiate the procedure using the BeforeUpdate event for the form,
and a
message box is displayed if the error condition is true.

The problem is, I don't know how to keep the user from moving to a
different
record. I want to retain but not save any changes that the user may have
made to field values in the current record, but prevent the user from
moving
on until they have corrected the error for that record.

Thanks in advance from a VBA newbie!