Solved Unbound form required using VBA (1 Viewer)

[donkey9972]

Hi,

I was wondering if someone could help me with a little problem. I am making a login form which has a user account creation page. The problem I am having is on the user account creation page. I am using the following code:

Private Sub btnSave_Click()
DoCmd.SetWarnings False
If IsNull(Me.txtxID) Then
    If Not Me.txtConfirmPassword = Me.txtPassword Then
        MsgBox "Please check your password!", vbInformation, "Information"
        Exit Sub
    Else
        DoCmd.RunSQL ("INSERT into tlogin (firstname, lastname, username, password) Values (""" & Me.txtFirstName & """ , """ & Me.txtLastName & """ , """ & Me.txtusername & """ , """ & Me.txtPassword & """  )")
        MsgBox "account created", vbInformation, "Information"
        DoCmd.Close
        DoCmd.OpenForm "flogin"
    End If
End If
End Sub

My problem arises when you click save. What I want to happen is a way to ensure that all fields (First name, Last name, Username, Password) are all filled in. If one of the fields are not filled in, then I want a message box to appear and inform the user-to-be that they missed something. I have set the following conditions on the table:

Validation rule = is not null
Validation text = "required"
Required = Yes
Allow zero length = No

I relized after that fact my fields are unbound on my form, so those would not do anything. I then tried to use the following code in the form before update:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.txtFirstName) Or Me.txtLastName Or Me.txtusername Or Me.txtPassword = "" Then

        MsgBox "This field is required.", vbExclamation, "Required Field"

        Me.txtRequiredField.SetFocus

        Cancel = True

    End If

End Sub

But it has no effect on anything, the account is still created even if information is missing. Other than this issue, my form works perfectly.

 

[Gasman]

Please use code tags to keep indentation.
You need to qualify each control.

If IsNull(Me.txtFirstName) Or Me.txtLastName = "" Or Me.txtusername = "" Or Me.txtPassword = "" Then

You could also try

If "" = Me.txtLastName Or Me.txtusername Or Me.txtPassword

but I have never used that syntax myself.

 

[donkey9972]

My apologies about the code tags, i will edit. and I will also try what you have suggested. Thank you.

 

[donkey9972]

Well unfortunately it is still having the same result. It still creates the account without ensuring all fields are entered. Thank you for the options and alternate way to try to write this.

 

[arnelgp]

is the first code on the Save button? it should be.

If IsNull(Me.txtFirstName) Then
    Msgbox "Please fillup the firstname."
    Me.txtFirstName.setfocus
    exit sub
End If
If IsNull(Me.txtLastName) Then
    Msgbox "Please type the lastname."
    Me.txtLastName.setfocus
    Exit Sub
End If
If IsNull(Me.txtusername) Then
    Msgbox "Please enter the username"
    Me.txtusername.setfocus
    Exit Sub
End If
If IsNull(txtPassword) Then
    Msgbox "Please enter your password."
    Me.txtPassword.SetFocus
    Exit Sub
End If
IF IsNull(Me.txtConfirmPassword) Then
    Msgbox "Please retype your password on Confirm password textbox."
    Me.txtConfirmPassword.SetFocus
    Exit Sub
End If
If IsNull(Me.txtxID) Then
    If Not Me.txtConfirmPassword = Me.txtPassword Then
        MsgBox "Please check your password!", vbInformation, "Information"
        Exit Sub
    Else
        DoCmd.RunSQL ("INSERT into tlogin (firstname, lastname, username, password) Values (""" & Me.txtFirstName & """ , """ & Me.txtLastName & """ , """ & Me.txtusername & """ , """ & Me.txtPassword & """ )")
        MsgBox "account created", vbInformation, "Information"
        DoCmd.Close
        DoCmd.OpenForm "flogin"
    End If
End If

 

[donkey9972]

Oh yes it is, sorry I forgot to include that part. I have edited my post again to reflect it as such.

 

[donkey9972]

Wow I had the correct code just not placing it in the correct location. Thank you arnelgp. It is working now how I wanted it to.

 

[Pat Hartman]

Condition checking is not transitive. You must repeat the ENTIRE expression.

INCORRECT:
If IsNull(Me.txtFirstName) Or Me.txtLastName Or Me.txtusername Or Me.txtPassword = "" Then
If "" = Me.txtLastName Or Me.txtusername Or Me.txtPassword

CORRECT:
If IsNull(Me.txtFirstName) Or Me.txtLastName = "" Or Me.txtusername = "" Or Me.txtPassword = "" Then

However, Null and ZLS are not the same and you need to consider both values when validating input. There are two simple ways to do this in a single condition:

If Me.txtFirstName & "" <> "" Then --- this handles both null and ZLS
If Len(Me.txtFirstName) > 0 Then --- this also handles both null and ZLS

When you validate data in the form's BeforeUpdate event, you MUST take care to cancel the save when errors are found or you can raise as many error messages as you want to but the bad data will still be saved.

A complete test would be:

If Me.txtFirstName & "" = "" Then
    Msgbox "Please fillup the firstname."
    Me.txtFirstName.setfocus
    Cancel = True
    exit sub
End If
If Me.txtLastName & "" = "" Then
    Msgbox "Please type the lastname."
    Me.txtLastName.setfocus
    Exit Sub
End If
If IsNull(Me.txtusername) Then
    Msgbox "Please enter the username"
    Me.txtusername.setfocus
    Cancel = True
    Exit Sub
End If
If txtPassword & "" = "" Then
    Msgbox "Please enter your password."
    Me.txtPassword.SetFocus
    Cancel = True
    Exit Sub
End If
IF Me.txtConfirmPassword & "" = "" Then
    Msgbox "Please retype your password on Confirm password textbox."
    Me.txtConfirmPassword.SetFocus
    Cancel = True
    Exit Sub
End If
If IsNull(Me.txtxID) Then
    If Not Me.txtConfirmPassword = Me.txtPassword Then
        MsgBox "Please check your password!", vbInformation, "Information"
        Cancel = True
        Exit Sub
    Else
        DoCmd.RunSQL ("INSERT into tlogin (firstname, lastname, username, password) Values (""" & Me.txtFirstName & """ , """ & Me.txtLastName & """ , """ & Me.txtusername & """ , """ & Me.txtPassword & """ )")
        MsgBox "account created", vbInformation, "Information"
        DoCmd.Close
        DoCmd.OpenForm "flogin"
    End If
End If

I'm assuming that you are intending to allow any user to open your application and create his own log in record. Not sure why you would do this. Hope you are not thinking that there is any security in this method. Normally, creating accounts is an Admin task and only Admin users would be allowed to add new users.

If you don't set the Cancel argument to true, then the bad data will ALWAYS be saved no matter how many warnings you give the user unless you have some validation defined at the table level which the record fails.

 

[vanzie]

Just saw this thread and have the same concept with my registration form. I used the following code that will give you a pop up of unfilled fields and added code to check for a duplicate value if you have something like I do that includes an employee code:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim duplicateFound As Boolean

'Check for required Fields
    Dim ctl As Control
    Dim errorMsg As String
    Dim allValid As Boolean
    allValid = True

    ' Loop through all controls in the form
    For Each ctl In Me.Controls
        ' Check if the control is a text box or combo box (add more as needed)
        If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) Then
            ' Check if the control is empty
            If IsNull(ctl.Value) Or ctl.Value = "" Then
                errorMsg = "The field '" & ctl.Name & "' is required."
                MsgBox errorMsg, vbExclamation, "Required Field"
                ctl.SetFocus
                allValid = False
                Exit For
            End If
        End If
    Next ctl

    ' Proceed if all fields are valid
    
    ' Initialize flag
    duplicateFound = False
    
    ' Get the database reference
    Set db = CurrentDb
    
    ' Query the table to check for duplicates
    strSQL = "SELECT EmployeeNo FROM Users WHERE EmployeeNo = '" & Me.EmployeeNo & "';"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Check if any record exists
    If Not rs.EOF Then
        duplicateFound = True
    End If
    rs.Close
    
    ' Handle duplicate detection
    If duplicateFound Then
        MsgBox "A record with Employee Number '" & Me.EmployeeNo & "' already exists. Please enter a unique Employee Number.", vbExclamation, "Duplicate Entry"
        Me.EmployeeNo.SetFocus
        Exit Sub
    End If

 

[Pat Hartman]

I actually think the OP's problem was that he was using an unbound form. The BeforeUpdate event doesn't run when the form is unbound.