Prevent duplications where index contains multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would be very grateful for any help on the following:

I have a database with one main table (tblExperts) and two forms - one to
view experts and one to add them. Three fields: 'FirstName', 'LastName' and
'OrganisationName' are indexed to prevent duplications on tblExperts. I need
to warn users that duplicate records entered on frmAddExperts will not be
saved - and, if possible, take them to the correct record on frmViewExperts.

Thanks

Jenny
 
I would leave the add new form unbound. So this way you can intercept the
data befor it is entered into the database. If you want all fields to have
data before you test if the record is a dup then put your code under the
after update events of each textbox and have it test to see if the other two
fields are not null. If so then all fields have data and then call your
validate function to see if it is a dup. If it is a dup then use the record
number to populate your view form, subform, or other unbound fields.

Good Luck!
 
Thanks for answering - unfortunately, I have no idea how to make Access check
all three controls. I can do Dlookup (just!) to check one textbox against
existing records - but I need something that checks for matching combinations
of values in those textboxes and then warns the user.

I'm new to VBA and I'm struggling.

thanks for your help

Jenny
 
After making a quick example code i noticed a flaw in my logic. If you don't
clear the textboxes after you test and then allow them to edit the data then
you will be adding new records for each change they make. so you get bad
data. I would either clear the textboxes after each valid data entry or close
the form.
There are better and cleaner ways to do this but this is what i could whip
up fast.

Put this behind your addnew form:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function isDataDup() As Boolean
'this function will open a recordset of the current database and test to see
if there is a record with all the fields on the form
'There are other ways to do this but i already had this code sample available.
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim cnn As ADODB.Connection
Dim x As Boolean

On Error GoTo hell

'iam using ADO which u might want to reaseach. it is the standard way to get
records from external/internal databases.
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Make the SQL select statment.
'I would study up on SQL Select statements to get a better understanding of
what's going on
'Also you need to watch out for single quotes.
'When the string gets combined together all text needs single quotes around
if any data from the form already has single quotes in it errors will happen.
'Ask the news group for all the ways to deal with single quotes, and pick
the one that suits your needs best.
sSQL = "SELECT * " & _
"FROM tblExperts " & _
"WHERE FirstName = '" & Me.Text0 & "' AND LastName = '" &
Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' " & _
"ORDER BY LastName ASC "
'adOpenStatic makes it readonly.
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount = 0 Then
'if recordcount = 0 the sets return to 0 telling the caller that there
are no duplicates
x = False
Else
'else there are records with same data and tells caller there are
duplicates by returning something other than 0
x = True
End If
rs.Close
Set rs = Nothing


isDataDup = x

Exit Function
hell:
'Error trapping
'You can change the name of hell i just have a weird sense of humor :)
'If you don't want to see any errors the comment out or delete the msgbox line
MsgBox Err.Description
Resume Next

End Function

Private Sub Text0_AfterUpdate()
Dim retVal As Boolean

If Not Me.Text3 <> "" And Me.Text5 <> "" Then
'got all data, now test it
retVal = isDataDup
If retVal = True Then
' there are dups so show them using retVal
MsgBox "Expert already exists."
'i would make your view form bound to the table/query so you can
jump right there
DoCmd.OpenForm "frmViewExperts", , , "FirstName = '" & Me.Text0 & "'
AND LastName = '" & Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' "

clearFields
Else
'else retVal is a 0 so no dups and ok to make new record
insertExperts
End If
End If

End Sub

Private Sub Text3_AfterUpdate()

Dim retVal As Boolean

If Me.Text0 <> "" And Me.Text5 <> "" Then
'got all data, now test it
retVal = isDataDup
If retVal = True Then
' there are dups so show them using retVal
MsgBox "Expert already exists."
'i would make your view form bound to the table/query so you can
jump right there
DoCmd.OpenForm "frmViewExperts", , , "FirstName = '" & Me.Text0 & "'
AND LastName = '" & Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' "

clearFields
Else
'else retVal is a 0 so no dups and ok to make new record
insertExperts
End If
End If

End Sub

Private Sub Text5_AfterUpdate()

Dim retVal As Boolean

If Me.Text3 <> "" And Me.Text0 <> "" Then
'got all data, now test it
retVal = isDataDup
If retVal = True Then
' there are dups so show them using retVal
MsgBox "Expert already exists."
'i would make your view form bound to the table/query so you can
jump right there
DoCmd.OpenForm "frmViewExperts", , , "FirstName = '" & Me.Text0 & "'
AND LastName = '" & Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' "

clearFields
Else
'else retVal is a 0 so no dups and ok to make new record
insertExperts
End If
End If

End Sub

Private Sub insertExperts()
Dim strMsg As String
Dim retVal As Integer

strMsg = "Do you wish to add a new record?"
myvar = MsgBox(strMsg, vbYesNo, "New Record?")

If myvar = vbYes Then
CurrentDb.Execute " INSERT INTO tblExperts " _
& "(FirstName,LastName, OrganisationName) VALUES " _
& "('" & Me.Text0 & "', '" & Me.Text3 & "', '" & Me.Text5 & "');"

clearFields

End If

End Sub

Private Sub clearFields()
Me.Text0 = ""
Me.Text3 = ""
Me.Text5 = ""
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

The "DoCmd.OpenForm " line of code will filter the form to just show the
record that is a duplicate


Hope this helps
Good Luck!
 
I forgot to remove "Set cnn = New ADODB.Connection" from my code. It istn't
a problem because its not used and was replaced with
CurrentProject.Connection. In my old code cnn allowed me connect to external
databases.

Sorry about that!
 
Thanks - the code looks a bit like double dutch to me, but I'll go through it
until I understand it, and try it on my database.

It was kind of you to post all that for me.

I'll let you know if I can get it to work ;)

Jenny
 
If you have any questions just keep adding to this post. I will keep an eye
out for any updates.
Also the code didn't need to be broken up into sub's but it allows for
better debugging and easier tailoring.
Good Luck!
 
I just notice another flaw in my code. I am so use to doing things the hard
way and sometimes i forget about shortcuts. I forgot that if you use the
Where option in DoCmd.OpenForm that it will find your duplicate or show a
blank record for adding new records. So you don't realy need the duplicate
check part of the code or the insertExperts part, but its still nice to have.
If you remove the dup check then you have no way of knowing in advace if
there are duplicates. So then you can't control what will happen next, or
let him know why it is showing/not showing data. But that might not be that
big of a deal so its all up to you.

If you were to remove the duplicate check part then your code would look
like this:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Text0_AfterUpdate()
'''''''I found another mistake on the line of code bellow in the if then
test-- the old code "if not Me.Text3 <> ..." is wrong so i fixed it by
removing the Not.
If Me.Text3 <> "" And Me.Text5 <> "" Then
DoCmd.OpenForm "frmViewExperts", , , "FirstName = '" & Me.Text0 & "'
AND LastName = '" & Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' "

clearFields
End If

End Sub

Private Sub Text3_AfterUpdate()

If Me.Text0 <> "" And Me.Text5 <> "" Then
DoCmd.OpenForm "frmViewExperts", , , "FirstName = '" & Me.Text0 & "'
AND LastName = '" & Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' "

clearFields
End If

End Sub

Private Sub Text5_AfterUpdate()

If Me.Text3 <> "" And Me.Text0 <> "" Then
DoCmd.OpenForm "frmViewExperts", , , "FirstName = '" & Me.Text0 & "'
AND LastName = '" & Me.Text3 & "' AND " & _
"OrganisationName = '" & Me.Text5 & "' "

clearFields
End If

End Sub

Private Sub clearFields()
Me.Text0 = ""
Me.Text3 = ""
Me.Text5 = ""
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
This code is much easier to understand.
Hope this is what you want
Good Luck!
 
Back
Top