• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 833
  • Last Modified:

Check for duplicate records before saving with DLOOKUP or something else.

Hi Experts.

I have a form dedicated to entering new data into a table. On this form I have a 'CHECK AND SAVE' button that, once pressed, will check whether the value entered in my 'companyjobid' field is unique in my table 'tbljob'.

If the value IS unique then the record may be saved.

If the value IS NOT unique, then the record will not be saved and a message box will appear.

I just can't figure out the syntax of DLOOKUP, which is what I think I need to use.

I have seen this solution which is very similar to what I want, but I can't figure out the syntax.

http://www.experts-exchange.com/Databases/MS_Access/Q_20574943.html

I already have some code on this button that check all of my required fields for null values. This works a treat, but I need to get this extra code to stop duplicated entries in my 'companyjobid' field.

FYI this field is not the key.

0
AbacusInfoTech
Asked:
AbacusInfoTech
  • 4
  • 4
1 Solution
 
AbacusInfoTechAuthor Commented:
Pending any answers i've got a bit of code i'vew tried that doesn't work....

If Not IsNull(Me!companyjobid) Then
      If Not IsNull (DLOOKUP ("[companyjobid]","tbljob","[companyjobid]=" & Me!companyjobid)) Then
      MsgBox "A duplicate exists.", vbInformation, "Note"
   Else
      MsgBox "No duplicates", vbInformation, "Note"
   End If
End If
0
 
rockiroadsCommented:
u need to use the Form_BeforeUpdate function

basically do this

IF NZ(DLOOKUP("field to check","tablename",""),"") <> "" then
    msgbox "Already exists"
    cancel = true  'abort save
END IF

0
 
rockiroadsCommented:
Is your company job id numeric or string?

Dim sID as string

sID = NZ(CompanyJobId.Value,"")

if sID <> "" then
    IF NZ(DLOOKUP("CompanyJobID","tblJob","CompanyJobId = " & sID),"") <> "" then
       msgbox "Dups"
    else
        msgbox "No Dup"
    end if
end if

If its a string, then u do this

    IF NZ(DLOOKUP("CompanyJobID","tblJob","CompanyJobId = '" & sID & "'"),"") <> "" then
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AbacusInfoTechAuthor Commented:
Okay, this is better....

Private Sub test_Click()
If Not IsNull(Me!companyjobid) Then
    If Not IsNull(DLookup("[companyjobid]", "tbljob", "[companyjobid]" = Me!companyjobid)) Then
      MsgBox "A duplicate exists.", vbInformation, "Note"
   Else
      MsgBox "No duplicates", vbInformation, "Note"
   End If
End If
End Sub

...I got rid of the ampersand and at least I get a 'no duplicates' box now but it's not correctly looking at my table to find what I know to be a duplicate.

0
 
AbacusInfoTechAuthor Commented:
Thanks, i'll give these a whirl.
0
 
rockiroadsCommented:
in your last post, the code

If Not IsNull(DLookup("[companyjobid]", "tbljob", "[companyjobid]" = Me!companyjobid))

u got the = sign outside the quotes, its got to be inside
i.e.
If Not IsNull(DLookup("[companyjobid]", "tbljob", "[companyjobid] =" & Me!companyjobid))

doing it outside quotes wont work anyway
0
 
AbacusInfoTechAuthor Commented:
Thanks rockiroads....!

This is my code that works fine in the beforeupdate of my field:

Dim sID As String

sID = Nz(companyjobid.Value, "")

If sID <> "" Then
    If Nz(DLookup("companyjobid", "tbljob", "companyjobid = '" & sID & "'"), "") <> "" Then
       MsgBox "Dups"
    Else
        MsgBox "No Dup"
    End If
End If
0
 
rockiroadsCommented:
ok, cool
that means its a string

remember the difference in using strings/numbers

use of quotes!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now