DLookup() Help w/Multiple Criteria (1 Viewer)

[BrokenBiker]

In my db, I have a button to open a form in 'Add' mode. It's based on a table that has an AutoID as a PK. However, I want to prevent duplicate entries when adding records. I am trying to have it look up the employeeID and the trainingID as a unique identifier on the BeforeUpdate fx. I am having syntax troubles w/the multiple criteria in the DLookup VBA.

If DLookup("[EMP_Tng]" & " " & "[CrsCode_IDAutoNo]", "tbl_TrainingMain", [Forms]![frm_TngCrsAdd]![TngCrsAddPK] & "'") Then

I've tried several different times, but just end up w/various errors. Any help would be appreciated.

 

[jdraw]

In my db, I have a button to open a form in 'Add' mode. It's based on a table that has an AutoID as a PK. However, I want to prevent duplicate entries when adding records. I am trying to have it look up the employeeID and the trainingID as a unique identifier on the BeforeUpdate fx. I am having syntax troubles w/the multiple criteria in the DLookup VBA.

If DLookup("[EMP_Tng]" & " " & "[CrsCode_IDAutoNo]", "tbl_TrainingMain", [Forms]![frm_TngCrsAdd]![TngCrsAddPK] & "'") Then

I've tried several different times, but just end up w/various errors. Any help would be appreciated.

If this is an EmployeeTraining Table, sort of junction table between
tblEmployees and tblCourses, you could just make a composite primary key
out of employeeID and trainingID. This would prevent duplicates. I don't think you need an independent autoNum field for a PK.

If you adjust the structure, then in the code behind the form, you could specifically check for the Duplicate record error, and give a message or whatever.

I'm not sure of your table and field names so can not offer an expression.

As for the DCount , this is the syntax.

"In Access, the DCount function returns the number of records in a specified set of records (or domain).

The syntax for the DCount function is:

DCount ( expression, domain, [criteria] )

expression is the field that you use to count the number of records.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain."


**** Just tried a sample with a test database (not using a form)
I have a tblEmployee with fields firstName, LastName, EmployeeId, and others. This routine correctly Identifies that I have an existing record where FirstName = Dave and LastName = Smith.
Perhaps this gives some info on the syntax you are looking for.

Sub dc()
Dim i As Integer
Dim db As DAO.Database
Set db = CurrentDb
i = DCount("employeeID", "tblemployee", "Firstname = 'Dave' and LastName = 'Smith'")
If i > 0 Then
MsgBox i & "   It worked"
Else
MsgBox "Dcount  is " & i
End If
End Sub

****************another test
Notice the concatenated fields "Firstname & LastName". This also correctly identified an existing record.
Hope this helps with syntax.

Sub dc()
Dim i As Integer
Dim db As DAO.Database
Set db = CurrentDb
i = DCount("employeeID", "tblemployee", "Firstname & LastName = 'Dave'& 'Smith'")
If i > 0 Then
MsgBox i & "   It worked"
Else
MsgBox "Dcount  is " & i
End If
End Sub

 

[BrokenBiker]

You are correct in the assumption that this table is a "junction" table between a 'courses' table and an 'employee' table. I had actually thought about using a composite key w/the fields the employeeID field and CourseID field. However, elsewhere in the db I have buttons on forms (in Split view) to open specifically to that record. The only way I've been able to do this is w/the use of a single PK. So, I just added an AutoID (Autonumber) field to the table as the PK. But...as you can see, that has now come back to bite me in the rear.

I built a quick, small test db to try out your suggestions. For some reason, the first example didn't work, but the second worked great! I probably just had a syntax error in the first attempt. No worries.

Here's what I have now. I'll try it in the 'real' db tomorrow and update the thread for other viewers.

Dim i As Integer
Dim db As DAO.Database
Set db = CurrentDb
i = DCount("autoID", "Table1", "emp & crsID = [forms]![form1]![emp] & [forms]![form1]![crsID]")
If i > 0 Then
    MsgBox i & "   It worked"
    Cancel = True
    Me.Undo
    Me.emp.SetFocus
    Exit Sub
Else
MsgBox "Dcount  is " & i
End If

 

[BrokenBiker]

Works like a champ! In the BeforeUpdate fx for both fields (EmpID and CourseID), I have the following:

Dim i As Integer
Dim db As DAO.Database
Set db = CurrentDb
i = DCount("ID_TngMain", "tbl_TrainingMain", "EMP_Tng & CrsCode_IDAutoNo = [forms]![frm_TngCrsAdd]![EMP_Tng] & [forms]![frm_TngCrsAdd]![CrsCode_IDAutoNo]")

If Me.EMP_Tng <> "" And Me.CrsCode_IDAutoNo <> "" Then
    If i > 0 Then
        MsgBox i & "    Duplicate entry(entries)" & vbCrLf & "Please verify individual's course codes before entry.", , "System Notice"
        Cancel = True
        Me.Undo
    Exit Sub
    Else
    End If
Else
    Exit Sub
End If