Preventing duplicate entries of data - Form Input

[FriQenstein]

Greetings all,



I'm trying to sort out the bit of code that will prevent a user from entering something that already exists.
EX: User tries to enter a unit with a Serial Number that already exists... Msg pops up saying that Serial Number already exists.

First here is my Table/Data layout:
Table with the data: Full_Inv
Field to run criteria against: Serial Number (inside Full_Inv table)

I was thinking about setting the No Duplicates rule to that field in the table, if possible, but I got to thinking what about NULL values? Since everything is in one single Table, some items don't have a Serial Number, so I'm using "N/A" as the value for that field. I'm guessing trying to use a No Duplicates rule on this would probably not work as there will be several items already with the "N/A" value in the Serial Number field.

So I started looking at some examples of the DLookup code.
Having a bit of a struggle trying to get it nailed down as to what I need it to do.
I started out with this example:

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
  
 Else:
 End If
End Sub

So I'm trying to figure out 2 things:
1) Where to put this? I'm assuming in a code builder for the input text box on the form right?
2) How to structure it with my data values/fields/table name, etc.
Here is what I've tried so far and it still allows a duplicate entry:

Private Sub SerialNumber_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[Serial Number]", "Full_Inv", "[SerialNumber] = '" & Me.SerialNumber & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Serial Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SerialNumber.Undo
  
 Else:
 End If
End Sub

So, I'm a bit unclear as to how exactly to implement my scenario into this example.
From a search on the forums, I did find a few other examples. The most simplistic one, from Colin , was a point to this bit of code:

DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

So I understand the first parts... DLookup is going to search the field Serial Number in table Full_Inv for Criteria... but I'm uncertain about how to define the criteria.

Should I continue with trying to fit this code and make it work?
Or will a No Duplicates rule on the Serial Number field in the Full_Inv table work?

T.I.A.
Regards.

[FriQenstein]

Partially-Resolved

Ok, here is what I ended up doing:

Private Sub Serial_Number_BeforeUpdate(Cancel As Integer)
 Dim Answer As Variant
 Answer = DLookup("[Serial Number]", "Full_Inv", "[Serial Number] = '" & Me.Serial_Number & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Serial Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.Serial_Number.Undo
  
 Else:
 End If
End Sub

I managed to get the code bit working by kick-starting my brain this morning, finally, and sorted out how/where to place the Field name vs. the function name.
So, for now, this appears to work. When the user inputs a Serial Number that already exists, it will pup-up a message box saying Duplicate, blah blah blah.

The only issue is... it still creates a new record entry in the Table, which ends up being a blank record entry and wasting a Ref_ID. Not that big of a deal, but I'm trying to keep the DB clean, and not have discrepancies such as missing ID numbers or blank records that I have to eventually go and manually delete.

Continuing my search...

[ranman256]

If you key the table on serial#, it won't let you enter duplicates.
no code needed.

[Minty]

If you key the table on serial#, it won't let you enter duplicates.
no code needed.

According to the OP, there are records with Null values - so this won't work.

@OP - Try using Me.Undo instead of Me.Serial_Number.Undo
That should stop the blank records, but might still give you missing ID's

[FriQenstein]

If you key the table on serial#, it won't let you enter duplicates.
no code needed.

Will this work even for entries with no serial number?
And
By using a key on the serial number, do I have to delete the original IDKEY or can you have more than 1 key in a Table? From what I remember, long ago, you can only use 1 key per table.

Thanks for the response.

[ArviLaanemets]

Will this work even for entries with no serial number?

I'm afraid it will not. To prevent double values for field, you must have an unique index on this field. The field with uniqie index can't have Null values.

By using a key on the serial number, do I have to delete the original IDKEY or can you have more than 1 key in a Table? From what I remember, long ago, you can only use 1 key per table.

No. You can have one primary key (either single-field or multi-field one) for table, but you can several additional indexes - unique ones included.

[FriQenstein]

According to the OP, there are records with Null values - so this won't work.

@OP - Try using Me.Undo instead of Me.Serial_Number.Undo
That should stop the blank records, but might still give you missing ID's

Ok I will try this.
Currently, when a user enters a number that already exists, it does create a record entry, BUT as long as the user then enters a different serial number, it will utilized that current empty record. (that make sense?)

This is ok, as long as the user has more than 1 item to enter. If, for example, the user has 1 item, and that Item already exists, then there will be a blank record entry.
I think I can utilize this for the time being until I can sort out a more advanced way to work with the record entry.

I appreciate the help guys.
Love this forum. Always helpful and always nice.

Regards. (p.s. You've all gained reputation from the posts)

[Minty]

As an additional thought, you could enter the serial number into an unbound control, check for duplication before starting the new record?
If it's not a duplicate copy the unbound text into your serial number field.

[ArviLaanemets]

You can consider BeforeUpdate event, which locates for identical serial number, and when finds it, cancels record update (Cancel = True). By default user gets back to same record, with record not updated. You can add message about reason for canceling of update, with e.g. ID of record having same serial number displayed in message box.

[accesstos]

Hi all!

I prefer to use unbound forms to append new items in a table because gives the chance to make all the necessary checks before create a new record.

This example could be the code of a simple form for adding new persons:

Option Compare Database
Option Explicit

Private Function CanSave() As Boolean
    Dim strWhere As String
    'Police ID required
    With Me.txtPID
        If IsNull(.Value) Then
            MsgBox .ValidationText
            .SetFocus
            Exit Function
        Else
            strWhere = "PID = '" & .Value & "'"
        End If
    End With
    'Check for duplicate police ID
    If Not IsNull(DLookup("personID", "tblPersons", strWhere)) Then
        MsgBox "Duplicate Police ID Found!", vbCritical, "Dublicate"
        Exit Function
    End If
    'First name required
    With Me.txtFName
        If IsNull(.Value) Then
            MsgBox .ValidationText
            .SetFocus
            Exit Function
        Else
            strWhere = "FName = '" & .Value & "'"
        End If
    End With
    'Last name required
    With Me.txtLName
        If IsNull(.Value) Then
            MsgBox .ValidationText
            .SetFocus
            Exit Function
        Else
            strWhere = strWhere & " AND LName= '" & .Value & "'"
        End If
    End With
    'Look for duplicate persons
    If Not IsNull(DLookup("personID", "tblPersons", strWhere)) Then
        If MsgBox("A person with this name is already saved. " _
                  & "Are you sure that is a differend person?", _
                  vbQuestion + vbYesNo, "New person") = vbYes Then

        Else
            Exit Function
        End If
    End If
    CanSave = True
End Function

Private Sub cmdSave_Click()
    If CanSave() Then
        'CurrentDb.Execute "INSERT INTO..."
        MsgBox "Saved!", vbInformation, "New person"
    End If
End Sub

I think that this is better than the "Go to new record" action of a bound form.

[Micron]

I retract my statement as table wasn't set up right when tested.

[FriQenstein]

Hey all,

Thanks again for the great replies.
I'm using the following code to prevent duplicate serial numbers:

Private Sub Serial_Number_BeforeUpdate(Cancel As Integer) Dim Answer As Variant
 Answer = DLookup("[Serial Number]", "Full_Inv", "[Serial Number] = '" & Me.Serial_Number & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Serial Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.Serial_Number.Undo
  
 Else:
 End If
End Sub

However, because there are a quite a few items that do NOT have a serial number, I was using N/A as the field value in the serial number field. Of course this will generate an error when trying to use it again on a new item because others already have an N/A value.

Is there a way I can alter my code to allow duplicate N/A but not duplicate actual serial numbers?

Regards.

[Micron]

Can't see any serial number data here so can only guess. Maybe you could use IsNumeric function. However my suggestion would be to update all N/A to null and your code should be fine. Of course you stop entering n/a from then on.

[FriQenstein]

Can't see any serial number data here so can only guess. Maybe you could use IsNumeric function. However my suggestion would be to update all N/A to null and your code should be fine. Of course you stop entering n/a from then on.

Yep. I though about just leaving it NULL for units with no S/N, but upper management is picky about that kind of stuff. I think I'll just tell them that this is the way it is and they'll have to deal.
It's just easier in the long run, plus, there is less clutter on the Reports that are generated as well. A bunch of N/As just add to the clutter causing the eyes to glaze over.

Thanks.

[Micron]

Sometimes you have to be convincing in order to win, but it helps to be diplomatic I guess. Therein lied my problem. I tended to say things like "if you opened a bread bag and there was no bread in it, would that confuse you?" Of course they say no. "Then why would a record with no serial number in it confuse anyone?"

IsNumeric could work on a N/A record but if your serials were like A123456 then no. You'd need some other test like checking if [SN] length > 3, or examining nth or last character. Many of these fail due to unexpected variations in data that wasn't evident at the time.

Another approach that can be used in rare (I think) situations is to store ZLS (zero length strings, or ""). I posted here not too long ago about composite indexes with Null because it didn't perform as I expected. The answer was to use "" rather than Null, but your simplest and best solution is to use null rather than n/a.

Most of us (I think) easily accept null as the absence of a value, but in some circles, it's not acceptable. One of those would be QS type systems where no data can mean either "not applicable" or "we forgot to add it" and that's usually frowned on.