Before Update Code Being Ignored (1 Viewer)

[andigirlsc]

Details:
I have a New User form that I inherited. The form adds a new user to the Users table. It works perfectly, but I want to set up validation rules for the form that require First Name, Last Name and Username to be entered BEFORE the Users table is updated. (The password field is set by default to a generic password, so I'm not worried about this field. Users are prompted to change it upon initial usage of their new login.)

Problem:
The way the code is written, the Users table is updated automatically when the user clicks the "Add User" button on the New User form and the code in the Before Update event is completely ignored altogether.

Question:
How do I get the Before Update event to run before the Users table is updated with the new user's record?

What I have tried:
I tried placing the Before Update code in the cmdAdd_Click code, but the form still updates the Users table without all the required fields being filled in even though it shows me the validation messages for the empty fields.

I have tried calling the Before Update event in the cmdAdd_Click event, but then I got a VBA error. The code I used to do this was:

Call Form_BeforeUpdate

Here is the New User form code to add a new user below. It was placed in the On Click event because it is actually a button called Add User. I did not write this code.

'---------------------------------------------------------------------------
' cmdAdd_Click
' This event will add a new user including the following information:
' First name, Last name and Username.
'
'----------------------------------------------------------------------------
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
      Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
 
     Set dbs = CurrentDb
     Set rst = dbs.OpenRecordset("tblUsers", dbOpenDynaset)
 
         With rst
             .AddNew
             ![First Name] = Me.txtFirstName
             ![Last Name] = Me.txtLastName
             ![MI] = Me.txtMI
             ![Password] = Me.txtPassword
             ![UserID] = Me.txtUserID
             ![Active] = Me.chkActive
             ![AccessID] = Me.cboAccessID
             .Update
         End With
 
     If IsLoaded("frmUsers") Then
         Forms!frmUsers.Requery
         Forms!frmUsers.lstUsers.Requery
      End If
         MsgBox "New user has been successfully added to the database.", vbInformation + vbOKOnly, "User Update"
Exit_cmdAdd_Click:
     Exit Sub
Err_cmdAdd_Click:
     MsgBox Err.Description
     Resume Exit_cmdAdd_Click
 
End Sub

Here is the Before Update Code I wrote that doesn't seem to be working:

'---------------------------------------------------------------
' Form_BeforeUpdate
'
' This event will prompt a validation rule that requires a 
' First Name, Last Name, Username, Access Level and 
' View level for each new user.
'---------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Please enter a First Name
If IsNull(Me.txtFirstName) Then
     Dim Msg, Style, Title, Help, Ctxt, Response, MyString
     Msg = "Please enter a First Name."
     Style = vbOK + vbExclamation + vbDefaultButton2
     Title = "Empty Name Field"
     Help = "DEMO.HLP"
     Ctxt = 1000
     Cancel = True
     Response = MsgBox(Msg, Style, Title, Help, Ctxt)
' Please enter a Last Name 
ElseIf IsNull(Me.txtLastName) Then
     Msg = "Please enter a Last Name."
     Style = vbOK + vbExclamation + vbDefaultButton2
     Title = "Empty Name Field"
     Help = "DEMO.HLP"
     Ctxt = 1000
     Cancel = True
     Response = MsgBox(Msg, Style, Title, Help, Ctxt)
 
' Please enter a Username
ElseIf IsNull(Me.txtUserID) Then
     Msg = "Please enter a Username."
     Style = vbOK + vbExclamation + vbDefaultButton2
     Title = "Empty Username Field"
     Help = "DEMO.HLP"
     Ctxt = 1000
     Cancel = True
     Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End If
End Sub

 

[bob fitz]

Why don't you use a form bound to tblUsers

 

[vbaInet]

One or more of your fields isn't Null, it's most likely the empty string "". I've taken the liberty to rearrange your code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Msg As String, Style As String, Title As String, _
        Help As String, Ctxt As String, Response As Integer, _
        MyString As String, blIsNull As Boolean
    
    ' Validate First Name, Last Name and Username
    If Len(Me.txtFirstName & vbNullString) Then
        Msg = "Please enter a First Name."
        Title = "Empty Name Field"
        blIsNull = True
    ElseIf Len(Me.txtLastName & vbNullString) Then
        Msg = "Please enter a Last Name."
        Title = "Empty Name Field"
        blIsNull = True
    ElseIf Len(Me.txtUserID & vbNullString) Then
        Msg = "Please enter a Username."
        Title = "Empty Username Field"
        blIsNull = True
    End If
    
    If blIsNull Then
        Cancel = True
        
        Style = vbOK + vbExclamation + vbDefaultButton2
        Help = "DEMO.HLP"
        Ctxt = 1000
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    End If
End Sub

Also note that when you declare variables you need to give each one of them a type. From the code you wrote only Msg was a type String, all the others were Variant.

 

[andigirlsc]

vbaInet, I tried your solution, but unfortunately, the code is still not being recognized. I tried your code with table validation rules in place as well as with table validation removed, but it didn't work.

I did, however, get table validation rules to work without any Before Update code at all. I just don't like the generic messages that users get, which is why I didn't want to use it to begin with. Seeing messages like "You must enter a value for the tblUsers.UserID field is a bit off-putting.

I am going to try using the Bound form method that Bob Fitz recommended. I did inherit this form, so I don't know why a bound form wasn't used to begin with, but I'm certain the developer had good reason.

 

[vbaInet]

If it's an unbound form you need to put that block of code in a separate function and call it. Before Update event is for bound forms only.

 

[RainLover]

There is no good reason for not using a bound form

Before Update is an event it is not code.

The code for this event goes behind the event.

This is what vbaInet wrote.

Private Sub Form_BeforeUpdate(Cancel As Integer)

This is wrong

I have tried calling the Before Update event in the cmdAdd_Click event, but then I got a VBA error.

 

[vbaInet]

This is wrong

I have tried calling the Before Update event in the cmdAdd_Click event, but then I got a VBA error.

Just fyi andigirlsc, it can be called like this:

Form_BeforeUpdate True

True or False, makes no difference. But it's best you create your own function and act accordingly (i.e. save or not save).

Bound form is the way to go anyway as bob fitz and Rain pointed out.

 

[andigirlsc]

Thank you all for your help! I solved my problem. I created a bound form and inserted the code below into the Save button code. For some reason, the Before Update event did not work properly (i.e. the empty last name field would be recognized, but not the first name field). I'm not sure why my IF statements weren't working in the Before Update event because I use it on several of my other forms.

Regardless, I was able to add the IF statements I needed to the Save button code and have marked it in blue bold text below. This works perfectly. Here is the solution.

'------------------------------------------------------------
' btnSave_Click
'
'------------------------------------------------------------

Private Sub btnSave_Click()

On Error GoTo btnSave_Click_Err
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

[B][COLOR=blue]If IsNull(Me.txtFirstName) Then
MsgBox "Please enter a First Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Name Field"

ElseIf IsNull(Me.txtLastName) Then
MsgBox "Please enter a Last Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Name Field"

ElseIf IsNull(Me.txtUserID) Then
MsgBox "Please enter a UserID Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Name Field"

End If

If Not IsNull(Me.txtFirstName) Then
   If Not IsNull(Me.txtLastName) Then
      If Not IsNull(Me.txtUserID) Then
         MsgBox "New user has been successfully added to the [/COLOR][/B]
 [B][COLOR=blue]        database.",  vbInformation + vbOKOnly, "User Update"

      End If
   End If
End If
[/COLOR][/B]
btnSave_Click_Exit:
Exit Sub

btnSave_Click_Err:
MsgBox Error$
Resume btnSave_Click_Exit
End Sub

 

[vbaInet]

Good to hear! However, I gave you the Len([Field] & vbNullsString) = 0 in place of IsNull([field]) for a reason.

I created a bound form and inserted the code below into the Save button code. For some reason, the Before Update event did not work properly (i.e. the empty last name field would be recognized, but not the first name field). I'm not sure why my IF statements weren't working in the Before Update event because I use it on several of my other forms.

You misunderstand the use of the Before Update event or misunderstood what we said. For a bound form Before Update works on it's own accord, it needs no intervention and needn't be called.

 

[andigirlsc]

Ok, I had to unmark this thread as solved because I am having more issues. The table tblUsers is being updated whether or not all of the IF statement conditions are being met. For example, if I enter a first name, but no last name, the first name still appears in tblUsers, whereas I expected nothing to appear until all conditions were met (first name, last name and username). This requires me to delete the newly created partial record. So I added an undo command in the Cancel button. The problem is, don't want the new information to be written to tblUsers until all conditions are met (first name, last name and username).

Also, I tried changing the If IsNull code to Len([field] & vbNullsString) = 0 and got an error message that says, "Variable not defined" for vbNullsString.

Here is what I coded in the Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
[COLOR=blue][B]If Len([txtFirstName] & vbNullsString) = 0 [/B][/COLOR][COLOR=black]Then[/COLOR]
MsgBox "Please enter a First Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"

[COLOR=blue][B]ElseIf Len([txtLastName] & vbNullsString) = 0[/B][/COLOR] Then
MsgBox "Please enter a Last Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"

[COLOR=blue][B]ElseIf Len([txtUserID] & vbNullsString) = 0 [/B][/COLOR]Then
MsgBox "Please enter a UserID Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"
End If

If Not IsNull(Me.txtFirstName) Then
   If Not IsNull(Me.txtLastName) Then
      If Not IsNull(Me.txtUserID) Then
         MsgBox "New user has been successfully added to the database.", 
          vbInformation + vbOKOnly, "User Update"
       End If
   End If
End If

End Sub

So, I went back to using the following code in the Save button:

'------------------------------------------------------------
' btnSave_Click
'
'------------------------------------------------------------
Private Sub btnSave_Click()
On Error GoTo btnSave_Click_Err
     On Error Resume Next
    DoCmd.RunCommand acCmdSaveRecord
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If
        [B][COLOR=blue]If IsNull(Me.txtFirstName) Then
            MsgBox "Please enter a First Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"
        ElseIf IsNull(Me.txtLastName) Then
            MsgBox "Please enter a Last Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"
        ElseIf IsNull(Me.txtUserID) Then
            MsgBox "Please enter a UserID Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"
        End If
        If Not IsNull(Me.txtFirstName) Then
            If Not IsNull(Me.txtLastName) Then
                If Not IsNull(Me.txtUserID) Then
                    MsgBox "New user has been successfully added to the database.", vbInformation + vbOKOnly, "User Update"
                End If
            End If
        End If
[/COLOR][/B]btnSave_Click_Exit:
    Exit Sub
btnSave_Click_Err:
    MsgBox Error$
    Resume btnSave_Click_Exit
End Sub

Then, to keep the form from saving a partially filled out record, I inserted this code into the Close button of the form:

'---------------------------------------------------------------------------
' cmdClose_Click (Cancel button)
' Closes the form.
'
'----------------------------------------------------------------------------
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
[B][COLOR=blue]'Deletes record
    DoCmd.RunCommand acCmdUndo[/COLOR][/B]
    
    'Closes the form
    DoCmd.Close
Exit_cmdClose_Click:
    Exit Sub
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

Is there a better way to handle this?

 

[vbaInet]

It's vbNullString and not vbNullsString. A typo from my last post but my original code was correct.

Is this a bound or unbound form?

 

[andigirlsc]

I appreciate all of your help so far. I am using a Bound form. I also changed vbNullsString to vbNullString per your notation and put the Before Update event code back into place. I still have the same problem. The table updates when only 1 of the three conditions have been met unless I delete the record using the Cancel button. The ""Variable Not Defined" error is gone, however.

Here is what I just tried using the Before Update event. The Save button code has been commented out:

Private Sub Form_BeforeUpdate(Cancel As Integer)
[B][COLOR=blue]If Len([txtFirstName] & vbNullString) = 0[/COLOR][/B] Then
MsgBox "Please enter a First Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"

[COLOR=blue][B]ElseIf Len([txtLastName] & vbNullString) = 0[/B][/COLOR] Then
MsgBox "Please enter a Last Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"

[B][COLOR=blue]ElseIf Len([txtUserID] & vbNullString) = 0[/COLOR][/B] Then
MsgBox "Please enter a UserID Name.", vbOK + vbExclamation + vbDefaultButton2, "Empty Field"
End If

If Not IsNull(Me.txtFirstName) Then
   If Not IsNull(Me.txtLastName) Then
      If Not IsNull(Me.txtUserID) Then
         MsgBox "New user has been successfully added to the database.", 
         vbInformation + vbOKOnly, "User Update"
      End If
   End If
End If

End Sub

 

[vbaInet]

If the form is bound, go back to post #3 and use that. The code was written to work with the Before Update event of a bound form.

 

[RainLover]

The Save button code has been commented out:

This is one of the best things you have done.

A bound Form will save Automatically when the Record looses focus. So no need to use a button.

If you want to save a Record without leaving that Record and using a Button try:-

Me.Dirty = False

 

[andigirlsc]

Again, thank you all for your help so far, but I am still having the same problem as mentioned in Post #12. My Users table is being updated regardless of whether or not all three of my conditions are being met in my New User form (first name, last name and username must be entered).

Is there a way to get the Before Update event to actually make sure all conditions are met BEFORE it updates? Currently, the event does not seem to be fulfilling its purpose. As it stands, if the user enters only a first name, the record is automatically saved. This means that if they change their mind and click cancel on the form, a new record will have been created regardless of whether or not they completed filling out the form. I have tested the form and found this to be the case.

The best work around I could come up with was to add a delete command in the Cancel button code. At least this way, the record can be deleted. Is there a better way to handle this?

Here is the code for the Bound form Before Update event:

'------------------------------------------------------------
' Form_BeforeUpdate(Cancel As Integer)
' Validation that requires user to enter first name, last
' name and username to save a record.
'
'------------------------------------------------------------

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len([txtFirstName] & vbNullString) = 0 Then
    MsgBox "Please enter a First Name.", vbOK + vbExclamation + 
    vbDefaultButton2, "Empty Field"

ElseIf Len([txtLastName] & vbNullString) = 0 Then
    MsgBox "Please enter a Last Name.", vbOK + vbExclamation + 
    vbDefaultButton2, "Empty Field"

ElseIf Len([txtUserID] & vbNullString) = 0 Then
    MsgBox "Please enter a UserID Name.", vbOK + vbExclamation + 
    vbDefaultButton2, "Empty Field"
End If

If Not IsNull(Me.txtFirstName) Then
    If Not IsNull(Me.txtLastName) Then
        If Not IsNull(Me.txtUserID) Then
            MsgBox "A new user has been successfully added to the 
             database.", vbInformation + vbOKOnly, "User Update"
        End If
    End If
End If
End Sub

 

[vbaInet]

You've asked the question several times and I keep telling you that everything you need is in post #3.

 

[bob fitz]

I believe your missing an instruction to cancel the update.
Try:

'------------------------------------------------------------
' Form_BeforeUpdate(Cancel As Integer)
' Validation that requires user to enter first name, last
' name and username to save a record.
'
'------------------------------------------------------------

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len([txtFirstName] & vbNullString) = 0 Then
    MsgBox "Please enter a First Name.", vbOK + vbExclamation + 
    vbDefaultButton2, "Empty Field"
    [COLOR="Red"][B]Cancel = True[/B][/COLOR]

ElseIf Len([txtLastName] & vbNullString) = 0 Then
    MsgBox "Please enter a Last Name.", vbOK + vbExclamation + 
    vbDefaultButton2, "Empty Field"
    [COLOR="Red"][B]Cancel = True[/B][/COLOR]

ElseIf Len([txtUserID] & vbNullString) = 0 Then
    MsgBox "Please enter a UserID Name.", vbOK + vbExclamation + 
    vbDefaultButton2, "Empty Field"
    [COLOR="Red"][B]Cancel = True[/B][/COLOR]
End If

If Not IsNull(Me.txtFirstName) Then
    If Not IsNull(Me.txtLastName) Then
        If Not IsNull(Me.txtUserID) Then
            MsgBox "A new user has been successfully added to the 
             database.", vbInformation + vbOKOnly, "User Update"
        End If
    End If
End If
End Sub

 

[vbaInet]

I believe your missing an instruction to cancel the update.

@andigirlsc, if you had used the code that was re-written for you, you would have solved this problem many posts before.

 

[andigirlsc]

@vbaInet, I didn't realize a left a few lines of code out. Thank you for your patience! Also, Post #3 was missing the "= 0", so here is the fully functioning code for the benefit of others to use. The code that I originally left out (for some strange reason) is marked in blue.

'------------------------------------------------------------
' Form_BeforeUpdate(Cancel As Integer)
' Validation that requires user to enter first name, last
' name and username to save a record.
'
'------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
    [COLOR=blue][B]Dim Msg As String, Style As String, Title As String, _
    Help As String, Ctxt As String, Response As Integer, _
    MyString As String, blIsNull As Boolean
[/B][/COLOR]    
' Validate First Name, Last Name and Username
If Len(Me.txtFirstName & vbNullString) = 0 Then
    Msg = "Please enter a First Name."
    Title = "Empty Name Field"
    [COLOR=blue][B]blIsNull = True[/B][/COLOR]

ElseIf Len(Me.txtLastName & vbNullString) = 0 Then
    Msg = "Please enter a Last Name."
    Title = "Empty Name Field"
    [COLOR=blue][B]blIsNull = True[/B][/COLOR]

ElseIf Len(Me.txtUserID & vbNullString) = 0 Then
    Msg = "Please enter a Username."
    Title = "Empty Username Field"
    [COLOR=blue][B]blIsNull = True
[/B][/COLOR]End If
    
[COLOR=blue][B]If blIsNull Then
    Cancel = True
        
    Style = vbOK + vbExclamation + vbDefaultButton2
    Help = "DEMO.HLP"
    Ctxt = 1000
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End If
[/B][/COLOR]
 If Not IsNull(Me.txtFirstName) Then
    If Not IsNull(Me.txtLastName) Then
        If Not IsNull(Me.txtUserID) Then
            MsgBox "A new user has been successfully added to the 
             database.", vbInformation + vbOKOnly, "User Update"
        End If
    End If
End If
End Sub

 

[vbaInet]

The code that I originally left out (for some strange reason) is marked in blue.

The code you've just pasted is the code given in post #3.

And I would change your new code block, i.e. the three levels of Not IsNull, to this:

    [COLOR="Blue"]Ctxt = 1000[/COLOR]
    MsgBox Msg, Style, Title, Help, Ctxt
Else
    MsgBox "A new user has been successfully added to the database.", _
               vbInformation + vbOKOnly, "User Update"
[COLOR="blue"]End If[/COLOR]

Blue is original code and everything else is revised code.