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

[Hell666]

Hi,

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

I have the following event procedure:

 Private Sub Form_AfterUpdate()
  If Nz(Me.ClientName, "") <> "" 
     And Nz(Me.Gender, "") <> ""
  Then 
  Exit Sub
  End If
 
  If Nz(Me.Gender, "") = "" Then
     MsgBox "Select Gender", vbInformation
     Exit Sub
     End If
  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!

[Nauticalgent]

Use the code on the BeforeUpdate event instead of the AfterUpdate and add

 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:

Private Sub FormBeforeUpdate(Cancel As Integer)
    If Nz(Me.ClientName, "") <> "" And Nz(Me.Gender, "") <> "" Then 
         Exit Sub
       End If
 
       If Nz(Me.Gender, "") = "" Then
              MsgBox "Select Gender", vbInformation
             Cancel = True
            Exit Sub ' this could be removed
              Me.Gender.SetFocus ' Recomended
    End If
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

[zmbd]

Hell666,

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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If ((Me.Gender & "") = "") Then
    MsgBox "Sorry please enter a Gender for this record"
    Cancel = True
  End If
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...

[zmbd]

@Nauticalgent
OP double posted so I didn't see your answer until I started cleaning up the forum.
-Z

[Nauticalgent]

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!