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:
Code:
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:
Code:
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:
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.