Stuck with my code

J

jjc130

Registered User.
Local time
Today, 22:38
Joined
Apr 22, 2013
Messages
12
Ok Basically Ive got a table with a field that is a yes/no named Used how to i alert the user when theirs already an account with that Key?
My Codeis below Help would be great


Private Sub Command4_Click()


If IsNull(txtEmployeeKey) Or txtEmployeeKey = "" Then
MsgBox "Please Enter Valid Employee Key", vbOKOnly, "Required"
Me.txtEmployeeKey.SetFocus
Exit Sub
End If

Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'")

If Me.txtEmployeeKey.Value = Me.txtEmployeeKey.Value Then
MsgBox "Thank You for Registering"
DoCmd.close
DoCmd.OpenForm "Form1"

Else
MsgBox "Incorrect Employee Key Or Username already in use", vbOKOnly, "Incorrect Infomation"

End If
End Sub
 
I see several potential issues - what happen when you run the code?
 
it works perfectly but ive set the employeekey in my table to Indexed(Yes no Dups) and what happens is it does not save if there is a value the same which is what i want but i want to be notified
 
Your If statement will never hit the Else. It's impossible for Me.txtEmployeeKey.Value to ever not = Me.txtEmployeeKey.Value. You need to define a separate variable to return the Dlookup value to, or just combine it into one line:

Code: 
If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'") then
    do stuff
Else
    do other stuff
End If
 
nschroeder said:
Your If statement will never hit the Else
Click to expand...
What do you mean it will never come back with Incorrect Employee Key or username already in use Because this is not the case as it comes up with that when uve entered it wrong and when i enter it right it procceds
 
Code: 
If Me.txtEmployeeKey.Value = Me.txtEmployeeKey.Value Then

Please explain how the above condition could ever produce a False result.
 
nschroeder said:
Code: 
If Me.txtEmployeeKey.Value = Me.txtEmployeeKey.Value Then
Please explain how the above condition could ever produce a False result.
Click to expand...
Yes that makes sense but the Form run perfectly? but i get what your saying and have changed it to If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'") but im still stuck with the user being informed when it is already used?

Joshua
 
Code: 
If Me.txtEmployeeKey.Value <> DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'")
Then
    MsgBox "Thank You for Registering"
    DoCmd.close
    DoCmd.OpenForm "Form1"
Else
    MsgBox "Incorrect Employee Key Or Username already in use", vbOKOnly, "Incorrect Infomation"
End If

Assuming the above is what you ended up with, it should work fine. Am I missing something?
 
Whats the difference between
If Me.txtEmployeeKey.Value <> DLookup(
and If Me.txtEmployeeKey.Value = Dlookup(
??
 
jjc130 said:
Whats the difference between
If Me.txtEmployeeKey.Value <> DLookup(
and If Me.txtEmployeeKey.Value = Dlookup(
??
Click to expand...

First one rings true when the Value does not equal a DLookup() value
Second one rings true when the Value does equal a DLookup() value

In order for an If to process the statement(s) within the If block, what the If tests is required to all evaluate to True. If an Else is present within the If block, that is processed in the case that the If condition evaluates to False.

The fact that you have two evaluations within the single If statement... both of those must evaluate to True in order for the If statements to be processed. (The first one must not be / the second one must be... THEN the If will evaluate true.)
 
Last edited:
so its better to use <> then = for what i want to do?
 
You can use = if you want, but then you need to reverse the then/else code.
Code: 
If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'")
Then
    MsgBox "Incorrect Employee Key Or Username already in use", vbOKOnly, "Incorrect Infomation"
Else
    MsgBox "Thank You for Registering"
    DoCmd.close
    DoCmd.OpenForm "Form1"
End If
Either way works the same. "If" statements are usually easier to read and think through with the positive "=" than with the negative "<>", but I also like to have it so that the most likely result is on top of the Else. It's just personal preference.
 
Code: 
Private Sub cmdCA_Click()
[COLOR=Lime]'Checks Username Field Has Been Filled in[/COLOR]
If IsNull(Me.txtUsername) Then
        MsgBox "Please Enter a Username", vbOKOnly, "Information Required"
        Me.txtUsername.SetFocus
        Exit Sub
    End If
[COLOR=Lime]'Checks if there is already a username the same[/COLOR]
If Me.txtUsername.Value = DLookup("[Username]", "tblUsers", "Username= '" & [Username] & "'") Then
        MsgBox "This Username is already Taken", vbOKOnly, "Username In Use"
        Exit Sub
    End If
[COLOR=Lime]'Check Password Field Has been Filled in[/COLOR]
If IsNull(Me.txtPassword) Then
        MsgBox "Please Enter a Password", vbOKOnly, "Information Required"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
[COLOR=Lime]'Check to see if The EmployeeKey has got A key in[/COLOR]
If IsNull(txtEmployeeKey) Or txtEmployeeKey = "" Then
        MsgBox "Please Enter Valid Employee Key", vbOKOnly, "Required"
        Me.txtEmployeeKey.SetFocus
        Exit Sub
    End If
[COLOR=Lime]'Checks to see if the Key is already in use[/COLOR]
If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblUsers", "EmployeeKey ='" & [EmployeeKey] & "'") Then
        MsgBox "That Key is already in use", vbOKOnly, "Invalid Key"
        Exit Sub
    End If
[COLOR=Lime]'if EmployeeKey is correct It Is allowed[/COLOR]
If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'") Then
        MsgBox "Thank You for Registering"
        DoCmd.Close
        DoCmd.OpenForm "Form1"
[COLOR=Lime]'If EmployeeKey is Incorrect it returns[/COLOR]
Else
        MsgBox "Incorrect Employee Key", vbOKOnly, "Incorrect Infomation"

    End If
End Sub
Is there anything on here i can simplifi?
or is that the best im gunna get?
Sorry for all the questions but im new to Programming??
 
Last edited:
jjc130 said:
Is there anything on here i can simplifi?
Click to expand...

Chuckle... ;) Validation code can become quite extensive.

I do validation a bit more complicated that I spin through the fields searching for bad values, turn the bad value fields background color to red, then if there was a bad field pop one error box after all fields have been checked / marked.

The validation code sets the field background back to normal each time validation approves the value. As perhaps someone had two bad fields, fixes one... that should get reset right away even if "the other" bad field is still not acceptable.
 
What you have is fine.

There are at least two other ways it could be done. All will give the same results. Again, it's personal preference:

Code: 
Private Sub cmdCA_Click()
If IsNull(Me.txtUsername) Then
    MsgBox "Please Enter a Username", vbOKOnly, "Information Required"
    Me.txtUsername.SetFocus
Else
    If Me.txtUsername.Value = DLookup("[Username]", "tblUsers", "Username= '" & [Username] & "'") Then
        MsgBox "This Username is already Taken", vbOKOnly, "Username In Use"
    Else 
        If IsNull(Me.txtPassword) Then
            MsgBox "Please Enter a Password", vbOKOnly, "Information Required"
            Me.txtPassword.SetFocus
        Else
            If IsNull(txtEmployeeKey) Or txtEmployeeKey = "" Then
                MsgBox "Please Enter Valid Employee Key", vbOKOnly, "Required"
                Me.txtEmployeeKey.SetFocus
            Else
                 If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblUsers", "EmployeeKey ='" & [EmployeeKey] & "'") Then
                     MsgBox "That Key is already in use", vbOKOnly, "Invalid Key"
                 Else
                     If Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'") Then
                         MsgBox "Thank You for Registering"
                         DoCmd.Close
                         DoCmd.OpenForm "Form1"
                     Else
                         MsgBox "Incorrect Employee Key", vbOKOnly, "Incorrect Infomation"
                    End If
                End If
            End If
        End If
    End If
End If
End Sub

OR

Code: 
Private Sub cmdCA_Click()
If IsNull(Me.txtUsername) Then
    MsgBox "Please Enter a Username", vbOKOnly, "Information Required"
    Me.txtUsername.SetFocus
ElseIf Me.txtUsername.Value = DLookup("[Username]", "tblUsers", "Username= '" & [Username] & "'") Then
    MsgBox "This Username is already Taken", vbOKOnly, "Username In Use"
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please Enter a Password", vbOKOnly, "Information Required"
    Me.txtPassword.SetFocus
ElseIf IsNull(txtEmployeeKey) Or txtEmployeeKey = "" Then
     MsgBox "Please Enter Valid Employee Key", vbOKOnly, "Required"
     Me.txtEmployeeKey.SetFocus
ElseIf Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblUsers", "EmployeeKey ='" & [EmployeeKey] & "'") Then
     MsgBox "That Key is already in use", vbOKOnly, "Invalid Key"
ElseIf Me.txtEmployeeKey.Value = DLookup("[EmployeeKey]", "tblEmpKey", "EmployeeKey ='" & [EmployeeKey] & "'") Then
     MsgBox "Thank You for Registering"
     DoCmd.Close
     DoCmd.OpenForm "Form1"
Else
     MsgBox "Incorrect Employee Key", vbOKOnly, "Incorrect Infomation"
End If
End Sub
 
how do i set the Colour of the textbox as well as .setfocus??

Joshua
 
jjc130 said:
how do i set the Colour of the textbox as well as .setfocus??
Click to expand...

First bit find out here:

Example of Part Number search with Validation
http://www.access-programmers.co.uk/forums/showthread.php?t=233975#post1193930

As far as setting focus, a bit more complex of an example, principle is the same in a more simplistic context as well:

How to SetFocus to a specific control on a Subform of a Form
http://www.access-programmers.co.uk/forums/showthread.php?t=221705#post1132324
 
Ok Ive done everything but now, No idea how but its saving the Record even if its wrong have no idea why because it hasnt done it befor?

Joshua
 
You must log in or register to reply here.

Similar threads

C
Checking if records exist
Replies
9
Views
1,757
cheekybuddha
cheekybuddha
D
Solved Change password using tempvar not passing to following form
Replies
4
Views
813
Gasman
Gasman
Mike Krailo
Solved Need help with before update code logic?
Replies
10
Views
1,152
Mike Krailo
Mike Krailo
S
Should my code embrace more of the "Single Responsibility Principle"?
Replies
5
Views
1,101
jwcolby54
jwcolby54
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
2,704
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom