Duplicate entry

[Lamb2087]

Is there a way to let the user know that the data already exists if they are entering data into text boxes?I want only one record of each person in the db.

 

[WayneRyan]

Lamb,

You can use the BeforeUpdate event on the textbox.

Then use a DLookUp function to search for its presence in
your table.

If it exists, display a MsgBox, then clear the field.

hth,
Wayne

 

[Lamb2087]

Validation

What code do I put in the BeforeUpdate event and the Dlookup function.

I want the option of assigning a person up to two serial number s but no more.

 

[WayneRyan]

Lamb,

This is from the help files:

Dim intSearch As Integer, varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & intSearch)

Since yours is a textbox, you'll need something like:

Dim intSearch As Integer, varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = '" & Me.YourTextBox & "'")
If varX > 2 Then
MsgBox("Too many")
Me.YourTextBox = ""
End If

You'll have to change the item names to yours.
Notice the single-quotes added to the second example,
indicating a string value.

hth,
Wayne

 

[Lamb2087]

Validation

Dim intSearch As Integer, varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = '" & Me.YourTextBox & "'")
If varX > 2 Then
MsgBox("Too many")
Me.YourTextBox = ""
End If

Is CompanyName a field? I am assuming Shippers is the table name.

Is this how I would write it for me?

DLookup Function
Dim intSearch As Integer, varX As Variant
intSearch = 1
varX = DLookup("[First_Name]","[Middle_Name]","[Last_Name]"_
"[People_ID]"Me.YourTextBox & "'")
If varX > 2 Then
MsgBox("Too many")
Me.YourTextBox = ""
End If

 

[Lamb2087]

Validation

I have this in the BeforeUpdate Event in the People_ID text box under properties.

Private Sub PEOPLE_ID_BeforeUpdate(Cancel As Integer)
Dim intSearch As Integer, varX As Variant
intSearch = 1
varX = DLookup("[People_ID]", "dbo_RifleInventory1", _
"[People_ID] = '" & Me.PEOPLE_ID & "'")
If varX > 2 Then
MsgBox ("Too many")
Me.YourTextBox = ""
End If

End Sub


Is this correct and do I need to put this code under each text box and change the names to match the text box?

 

[WayneRyan]

Lamb,

Looks OK.

You only need it for your Person ID field that you want to check.

Wayne

 

[Lamb2087]

Validation

I need to check People_ID, First_Name, Middle_Name, Last_Name
for duplicates. Do I need to use the same code just substituting different field names?

 

[WayneRyan]

Lamb,

Isn't the People_ID number unique to a person?

And First_Name could have Joe (Smith) and Joe (Jones).

You could switch your code to the BeforeInsert event of the form
and use the DLookUp with multiple clauses (search help on this
forum for DLookUp).

BUT, I don't think that is right. What is the use of having a
Person_ID if it is not unique to them. The Person_ID should
be on this form and have an entry in a "Master" table that
has their first, last, phone, address etc.

If you do this, you can base a combo box on your form,
let them select the last, first name and you can extract the
Person_ID. But now we're getting off the original topic.

Wayne