Preventing Duplicate entry's (1 Viewer)

[rhett7660]

Hello..

I found this code in another thread and I can't seem to get it to work.

Private Sub form_beforeupdate(Cancel As Integer)
Dim icount As Long
icount = Nz(DLookup("EmployeeNumber", "tblMain", txtEmployeeNumber=" & Me.txtEmployeeNumber), 0)
If icount <> 0 Then
Beep
MsgBox "Worker ID all ready exists. You must choose an uniqe ID number!"
Cancel = True
Undo
End If
End Sub

It won't let me add any new entry's into the database. I have a search function that allows me to check if there an employee number currently in the database. If not, I try to make the entry and it kicks up an error that the "employeenumber" is already in the database. Can't seem to figure out what is going on.

Here is the code I am using to search just in case it might have something to do with it.

Private Sub cmdSearch_Click()
Dim strEmployeeNumber As String
Dim strSearch As String
    
'Check txtSearch for Null value or Nill Entry first.

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
Exit Sub
End If

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("txtEmployeeNumber")
    DoCmd.FindRecord Me!txtSearch
        
    txtEmployeeNumber.SetFocus
    strEmployeeNumber = txtEmployeeNumber.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

    If strEmployeeNumber = strSearch Then
        'MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        txtEmployeeNumber.SetFocus
        txtSearch = ""
    
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtSearch.SetFocus
    End If
End Sub

Thanks for looking and if you need anything else please let me know.

 

[vbaInet]

Take note of and amend the highlighted bit:

Private Sub form_beforeupdate(Cancel As Integer)
    If DCount("EmployeeNumber", "tblMain", "[COLOR=Red][B]EmployeeNumberFIELD[/B][/COLOR]=" & Me.txtEmployeeNumber) <> 0 Then
        Cancel = True
        Beep
        MsgBox "Worker ID already exists." & vbnewline & vbnewline & "You must choose a uniqe ID number!", _
                vbexclamation, "Worker ID exists"
    End If
End Sub

 

[rhett7660]

vbaInet

Just so I understand, the item you highlighted should be the field from the table? IE


"tblMain", (Main datatable) "EmployeeNumber"=" this is the actual field from the tbleMain?

So I would be calling the field "EmployeeNumber" twice, once before the tblMain and once after. Just want to make sure I understand correctly.

Thanks again for your help!

 

[vbaInet]

The one on the left is the name of the field in tblMain, the other is the textbox that the value is being looked up from.

 

[rhett7660]

The one on the left is the name of the field in tblMain, the other is the textbox that the value is being looked up from.

That is correct, I have it named txtEmployeeNumber on the form itself. This is how it should be correct? Or in my previous statement it should be looking up the same field from tblMain?

 

[vbaInet]

If the field in the form's record source is also called EmployeeNumber then your DCount can look like this:

DCount("EmployeeNumber", "tblMain", "EmployeeNumber=" & Me[COLOR=Red][B]![/B][/COLOR]EmployeeNumber)

 

[rhett7660]

If the field in the form's record source is also called EmployeeNumber then your DCount can look like this:

DCount("EmployeeNumber", "tblMain", "EmployeeNumber=" & Me[COLOR=Red][B]![/B][/COLOR]EmployeeNumber)

Ok..... Just so I understand:

first EmployeeNumber = Field in table
tblMain = database table EmployeeNumber resides in
Second EmployeeNumber = Also Field in table?
Third EmployeeNumber = field name on form in question? The txtEmployeeNumber is the actual field name on the form.

The way I had it setup in the first post is this:

first EmployeeNumber = Field in table.
tblMain = database table EmployeeNumber resides in.
first txtEmployeeNumber = field name on form.
second txtEmployeeNumber = field name on form.

I am thinking we are setting it up the same just the name is confusing me.

 

[vbaInet]

I think you're confusing a control from a field. A control is like a textbox, combo box etc. A field is like EmployeeNumber in your table tblMain.

txtEmployeeNumber is the name of your CONTROL on the form, not the name of the field on the form. This textbox control is bound to the FIELD EmployeeNumber, which is why you can actually use Me.txtEmployeeNumber or Me!EmployeeNumber (because it is bound to the textbox).

The EmployeeNumber on the left MUST be the name of the field, the one on the right is returning a value so you can use the name of the control to return the control's current value OR you can use Me!EmployeeNumber to return the current EmployeeNumber of the current record.

 

[rhett7660]

I think you're confusing a control from a field. A control is like a textbox, combo box etc. A field is like EmployeeNumber in your table tblMain.

txtEmployeeNumber is the name of your CONTROL on the form, not the name of the field on the form. This textbox control is bound to the FIELD EmployeeNumber, which is why you can actually use Me.txtEmployeeNumber or Me!EmployeeNumber (because it is bound to the textbox).

The EmployeeNumber on the left MUST be the name of the field, the one on the right is returning a value so you can use the name of the control to return the control's current value OR you can use Me!EmployeeNumber to return the current EmployeeNumber of the current record.

You are correct I am seeing way to many EmployeeNumbers! LOL...


The part that is getting to me, is there are two EmployeeNumbers left of the tblMain.

The first one should the field in the table tblMain
the one right after should be the control on the form? Which is what I was doing before hand.


Which one on the left? You highlighted the second one. This one should also be the field from tblMain?

I am sorry for the confusion.

If DCount("EmployeeNumber" (name of field from within tblMain), "tblMain" (main database table that has EmployeeNumber"), "EmployeeNumber" (This should be the same, name of the field from within tblMain?)=" & Me!txtEmployeeNumber) (This is the only one that should be the control on the form correct?)

Thanks again.

 

[vbaInet]

Ok got you. Maybe this will help clear things up:

DCount("*", "tblMain", "EmployeeNumber= 123")

I was referring to the left and right of the equal to sign. Notice I've now used an asterisk. This interprets, Count all (i.e. *) the records in tblMain where EmployeeNumber is equal to 123.

If I put back EmployeeNumber:

DCount("EmployeeNumber", "tblMain", "EmployeeNumber=123")

This interprets, Count all the EmployeeNumbers in tblMain where EmployeeNumber is equal to 123.

Let's now change it in light of your current situation (and you can stick to the asterisk instead):

DCount("*", "tblMain", "EmployeeNumber=" & Me[COLOR=Red][B]![/B][/COLOR]EmployeeNumber)

It reads, Count all the records in tblMain where EmployeeNumber (in the domain, which you have put as tblMain) is equal to the current EmployeeNumber on my form.

On the other hand:

DCount("[B][COLOR=Red]*[/COLOR][/B]", "tblMain", "EmployeeNumber=" & Me[COLOR=Red][B].[/B][COLOR=Blue]txt[/COLOR][/COLOR]EmployeeNumber)

Count all the records in tblMain where EmployeeNumber (in the domain, which you have put as tblMain) is equal to the current value of txtEmployeeNumber textbox control.

 

[rhett7660]

Ahhhhhhhhhhhhhhhhhhhhh...... Yes I was getting the two after the " = " sign confused.

Ok, reading I understand there is a difference, but is there a difference in doing one way or the other? They should do the same thing correct?

 

[vbaInet]

Me.txtEmployeeNumber and Me!EmployeeNumber will return the same value as long as txtEmployeeNumber is bound to the EmployeeNumber field, so it doesn't really matter.

 

[rhett7660]

vbaInet...

Thank you very very much for taking the time to talk me through this!!!!

 

[vbaInet]

You're welcome.

 

[rhett7660]

Just wanted to report back, forgot to do it the other day. Worked like a charm!!!! Thanks again.