PREVENT DUPLICATES (1 Viewer)

mercystone

Member
Local time
Today, 20:58
Joined
Sep 20, 2021
Messages
108
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

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 4, 2013
Messages
2,660
Yes. A number of ways.

One would be to use dlookup to see if the number exists
Code:
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

Member
Local time
Today, 20:58
Joined
Sep 20, 2021
Messages
108
Yes. A number of ways.

One would be to use dlookup to see if the number exists
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:58
Joined
May 7, 2009
Messages
16,116
maybe it meant to be DCount()?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:58
Joined
Feb 28, 2001
Messages
22,554
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

Super Moderator
Staff member
Local time
Today, 13:58
Joined
Feb 19, 2002
Messages
35,914
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

I’m here to help
Staff member
Local time
Today, 10:58
Joined
Oct 29, 2018
Messages
18,788
Probably something along these lines?
Code:
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

Member
Local time
Today, 20:58
Joined
Aug 29, 2020
Messages
110
Probably something along these lines?
Code:
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

I’m here to help
Staff member
Local time
Today, 10:58
Joined
Oct 29, 2018
Messages
18,788
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:
Code:
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

Member
Local time
Today, 20:58
Joined
Aug 29, 2020
Messages
110
You could try:
Code:
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:58
Joined
Feb 19, 2013
Messages
14,219
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?
 

Users who are viewing this thread

Top Bottom