423,873 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,873 IT Pros & Developers. It's quick & easy.

MsAccess Form - Preventing of going to a new record after an existing one removed

P: 1

Q: MsAccess Form - AfterUpdate () event procedure - How not to allow a cursor to go to t...

I have the following event procedure:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_AfterUpdate()
  2.   If Nz(Me.ClientName, "") <> "" 
  3.      And Nz(Me.Gender, "") <> ""
  4.   Then 
  5.   Exit Sub
  6.   End If
  8.   If Nz(Me.Gender, "") = "" Then
  9.      MsgBox "Select Gender", vbInformation
  10.      Exit Sub
  11.      End If
  12.   End Sub
What do I have to add after the
Message Box "Select Gender" in order to not let user to
go to a new record

I thought of adding something like
GoToNextRecord = False and define it as Integer

But it showed as mistake.

Please modify my code to perform that action
Sorry, I am not a VBA pro.

Thanks in an advance!
Jul 20 '18 #1
Share this Question
Share on Google+
4 Replies

P: 73
Use the code on the BeforeUpdate event instead of the AfterUpdate and add
Expand|Select|Wrap|Line Numbers
  1.  Cancel = True
after your MsgBox line. The AfterUpdate event fires after you have moved off the current record to a different record or if the form has been refreshed or requeried.

This type of validity check should happen before the record is updated, hence the BeforeUpdate event and not the AfterUpdate.

In other words, move your code to the BeforeUpdate event and delete the AfterUpdate event.

Your code should look like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub FormBeforeUpdate(Cancel As Integer)
  2.     If Nz(Me.ClientName, "") <> "" And Nz(Me.Gender, "") <> "" Then 
  3.          Exit Sub
  4.        End If
  6.        If Nz(Me.Gender, "") = "" Then
  7.               MsgBox "Select Gender", vbInformation
  8.              Cancel = True
  9.             Exit Sub ' this could be removed
  10.               Me.Gender.SetFocus ' Recomended
  11.     End If
  12. End Sub
This answers your immediate question, however this Sub could be written much better. If you are interested, we can provide a few suggestions
Jul 20 '18 #2

Expert Mod 5K+
P: 5,285

Welcome to Bytes.com.

I've moved as much of your question as possible in to your post from the title and added the mandatory [code][/code] tags

To be honest, if you want the [Gender] field to be mandatory, IMHO, your best solution is to implement this at the table level by setting the "required" property to "Yes" and setting up some validation rules to ensure that only valid data is entered, along with a custom error message that triggers from the table. IMHO this works better in that VBA doesn't need to be enabled (don't get me wrong, VBA is my stock and trade with Access databases, but why use a Jack-hammer when a rolled up piece of newspaper will do the job?).

Your second best option would be the Before_Update event of the FORM, not the After_Update

The Before_Update event will allow you to cancel the save or update of the record while allowing the user to [ESC] to abort the record entry or update. Best thing here is that the user can't go back to older entries and attempt to delete the value in that field and save the record.

A VERY simple example would be:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   If ((Me.Gender & "") = "") Then
  3.     MsgBox "Sorry please enter a Gender for this record"
  4.     Cancel = True
  5.   End If
  6. End Sub
This needs a lot more code such making sure the [ctrl_Gender] receives the focus, only the proper values are entered (ideally, these should be validated at the table level either with validation rules or by design) error trapping, etc...
Attached Images
File Type: png 971095_ReqField.png (14.0 KB, 51 views)
Jul 20 '18 #3

Expert Mod 5K+
P: 5,285
OP double posted so I didn't see your answer until I started cleaning up the forum.
Jul 20 '18 #4

P: 73
No worries! And I like your solution at the table level - much better to prevent a problem then fix it.

And your explanation is more eloquent than mine - I will always be a work in progress, just ask my wife!
Jul 20 '18 #5

Post your reply

Sign in to post your reply or Sign up for a free account.