Checking for duplicate data before entering 'new' data

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Looking for opinions and validation.

The structure of the portion of the DB I'm working with is three tables; a Company table, a Contact table and a Link table that stores the the ContactID's associated with CompanyID's.

When a person starts to add a new contact to a company, I'd like to check that the contact is not already in the database. If the new contact name is in the database then that contact is associated with the company and a duplicate contact name record is not created in the Contact table. If, the contact name is new then it is associated with the company and a new contact record is created.

What I'm thinking of adding is VBA on the lastname textbox 'afterupdate' event to check the contact table for all last names whose first two letters are LIKE the last name typed in the lastname textbox.
If the check of similar last names is not null then I open another form which lists all of the names in the database with the similar last name.

If the Name is already in the database then the user can select that name and push a 'Yes' button which closes the popup form and passes the value of the ContactID back to the original form, erases the 'new' record that was being added and substitutes the contact selected in the popup form.

If the Name is not in the database then I close the popup form and continuing adding the 'new' name from the calling form.

My problem is that, unless I use global variables, I am unsure that I will have a good way to pass the information I require. When I open the popup, I want to pass the lastname and the ID of the link table so that if the name is already in the database, then I can change the contactID in the link table to the correct contactID. And I wonder if I'll have to close the calling form to force it to refresh.

Anyway, this is much longer than I'd like but does this sound like a reasonable approach? Is this using a hammer to solve a problem? Does anyone know of some elegant example code in MSDN or wherever on the web that they would consider to be a great way to handle this sort of checking process?

Karen
 
yes, you need gloable varaible to pass the value, or you
can directly refer to the value like Forms!formname!
controlname without closing the main form. you are on the
right track. I found the following information, hope it
helps.

Below is sample code which uses DAO to check if record
exist. The function
resides in a module, as it is called all the time. Make
sure you have a DAO
reference set.

Private Sub Address_ID_BeforeUpdate(Cancel As Integer)

If Not CountRecords("[Fixes Details Queue]", "[Fixes
Details
Queue].[Fixes ID]=" & Me![Fixes ID] & " And [Fixes
Details Queue]![Address
ID]= " & Me![Address ID]) = 0 Then
MsgBox "Already Exists!"
Cancel = True
Me.Undo
End If

End sub


Function CountRecords(tablename, criteria As String)
Dim rstCount As Recordset

Set rstCount = CurrentDb().OpenRecordset("Select *
From " & tablename &
" Where " & criteria, dbOpenSnapshot)
With rstCount
If Not .EOF Then .MoveLast
CountRecords = .RecordCount
.Close
End With

End Function


-----Original Message-----
Looking for opinions and validation.

The structure of the portion of the DB I'm working with
Click to expand...
is three tables; a Company table, a Contact table and a
Link table that stores the the ContactID's associated
with CompanyID's.
When a person starts to add a new contact to a company,
Click to expand...
I'd like to check that the contact is not already in the
database. If the new contact name is in the database
then that contact is associated with the company and a
duplicate contact name record is not created in the
Contact table. If, the contact name is new then it is
associated with the company and a new contact record is
created.
What I'm thinking of adding is VBA on the lastname
Click to expand...
textbox 'afterupdate' event to check the contact table
for all last names whose first two letters are LIKE the
last name typed in the lastname textbox.
If the check of similar last names is not null then I
Click to expand...
open another form which lists all of the names in the
database with the similar last name.
If the Name is already in the database then the user can
Click to expand...
select that name and push a 'Yes' button which closes the
popup form and passes the value of the ContactID back to
the original form, erases the 'new' record that was being
added and substitutes the contact selected in the popup
form.
If the Name is not in the database then I close the
Click to expand...
popup form and continuing adding the 'new' name from the
calling form.
My problem is that, unless I use global variables, I am
Click to expand...
unsure that I will have a good way to pass the
information I require. When I open the popup, I want to
pass the lastname and the ID of the link table so that if
the name is already in the database, then I can change
the contactID in the link table to the correct
contactID. And I wonder if I'll have to close the
calling form to force it to refresh.
Anyway, this is much longer than I'd like but does this
Click to expand...
sound like a reasonable approach? Is this using a hammer
to solve a problem? Does anyone know of some elegant
example code in MSDN or wherever on the web that they
would consider to be a great way to handle this sort of
checking process?
 
Jacky,

Thanks a lot, it helped just to be reminded that Forms!formname!controlname
was a viable option.

Karen
 
Back
Top