PREVENT DUPLICATES

[mercystone]

Is there any vba code or a function which will instruct database user msgbox "SORRY YOU CANNOT USE THIS NUMBER BECAUSE IT IS BEING USED BY AND SHOW THE SAVED USER"
Thanks.

 

[Cronk]

Yes. A number of ways.

One would be to use dlookup to see if the number exists

If dLookup("YourNumberField", "tblYourData", "YourNumberField=" & yourNumberBeingTested)>1 then
   msgbox "SORRY YOU CANNOT USE THIS NUMBER BECAUSE IT IS BEING USED BY " & dlookup(SavedUser","tblYourData", "YourNumberField=" & yourNumberBeingTested)
endif

 

[mercystone]

Yes. A number of ways.

One would be to use dlookup to see if the number exists

If dLookup("YourNumberField", "tblYourData", "YourNumberField=" & yourNumberBeingTested)>1 then
   msgbox "SORRY YOU CANNOT USE THIS NUMBER BECAUSE IT IS BEING USED BY " & dlookup(SavedUser","tblYourData", "YourNumberField=" & yourNumberBeingTested)
endif

Thanks

 

[arnelgp]

maybe it meant to be DCount()?

 

[Pat Hartman]

AND SHOW THE SAVED USER"

indicates you want a value from the record rather than to just know if it exists

 

[The_Doc_Man]

You have a range of possibilities, but you eliminated a couple of them with the second part "AND SHOW...."

The SIMPLEST way to do this is to put an index on the field and make it "NO DUPS" - so then your user gets an error when trying to enter a duplicate name or number. Note that having the index isn't a bad thing even if you should choose some other method to get the data you wanted to show - because that makes the lookup that much easier.

Once you want to show "something else" than the duplication, you need to look up ahead of time before you try to store the new record. The DLookup suggestion would work if you only needed to show ONE other field. If you wanted to show more than one field then DLookup becomes somewhat of a burdensome pile of overhead because you would need one DLookup per field to be displayed.

If you describe exactly what you wanted to see, including how many fields are involved, we could perhaps give you a more targeted answer.

 

[Pat Hartman]

I agree with Doc. When something is unique, it needs a unique index. However, using a domain function which allows you to give the user a better error message is good practice.

 

[theDBguy]

Probably something along these lines?

Dim strUser As String

strUser = Nz(DLookup("User", "TableName", "NumberField=" & Nz(Me.NumberField,0)),"")

If strUser <> "" Then
    MsgBox "Sorry, that number is already in use by " & strUser, vbInformation, "Info"
End If

Hope that helps...

 

[I am alive]

Probably something along these lines?

Dim strUser As String

strUser = Nz(DLookup("User", "TableName", "NumberField=" & Nz(Me.NumberField,0)),"")

If strUser <> "" Then
    MsgBox "Sorry, that number is already in use by " & strUser, vbInformation, "Info"
End If

Hope that helps...

Hi. I have the following fields in my table tblStudents:
StudentID - AUTONUMDER (PRIMARY KEY)
ADM NO - Short text
LAST NAME - Short text
FIRST NAME - Short text

I want to prevent duplicates if ADM NO has been entered.
Msgbox "Sorry ADM NO is already in use by LAST NAME and FIRST NAME"
THANKS.

 

[theDBguy]

Hi. I have the following fields in my table tblStudents:
StudentID - AUTONUMDER (PRIMARY KEY)
ADM NO - Short text
LAST NAME - Short text
FIRST NAME - Short text

I want to prevent duplicates if ADM NO has been entered.
Msgbox "Sorry ADM NO is already in use by LAST NAME and FIRST NAME"
THANKS.

You could try:

Dim var As Variant
var = DLookup("[LAST NAME] & ' and ' & [FIRST NAME]", "tblStudents", "[ADM NO]='" & Nz(Me.[ADM NO],0) &"'")
If Not IsNull(var) Then 
    MsgBox "Sorry " & Me.[ADM NO] & " is already in use by " & var
End If

Hope that helps...

 

[I am alive]

You could try:

Dim var As Variant
var = DLookup("[LAST NAME] & ' and ' & [FIRST NAME]", "tblStudents", "[ADM NO]='" & Nz(Me.[ADM NO],0) &"'")
If Not IsNull(var) Then 
    MsgBox "Sorry " & Me.[ADM NO] & " is already in use by " & var
End If

Hope that helps...

Hi. When I try this it gives me the same Student even if I have 100 Students. It only gives the name of the first Student with StudentID 1

 

[EliiotM]

Hi. When I try this it gives me the same Student even if I have 100 Students. It only gives the name of the first Student with StudentID 1

Me too

 

[CJ_London]

it only gives the name of the first Student with StudentID 1

put debug.print me.[adm no] before the var= line - what does it say?

 

[I am alive]

put debug.print me.[adm no] before the var= line - what does it say?

Thanks once again. Kindly put the above code.

 

[CJ_London]

don't know what you mean?