Do Not Append to Table If records Already Exist

[Amy Adams via AccessMonster.com]

I have a complicated situation that I will do my best to explain. Been
trying to puzzle this one our for a couple days now.

I have 4 tables

1. Physicians - Primary Key "UPIN"
2. Groups - Primary Key "Group ID"
3. Plans - Primary Key "PlanID"
4. PhysicianPlan - Combines all 3 primary keys in large table UPIN,
PlanID, and GroupID this table has over 34,000 records

The following is an example of PhysicianPlan

UPIN PlanID GroupID
1 2 3
1 2 4
1 2 5
1 2 6

Etc

If a new physican needs to be added to the database it is done through a
form. This form contains a command button that runs an append query.
Append query add all necessary plans and groups to new physician. What has
been happening occasionally is that a user appends a physician that does
not need appending and duplicate records are added to my PhysicianPlan table

Example:

UPIN PlanID GroupID
1 2 3
1 2 4
1 2 5
1 2 6
1 2 3
1 2 4
1 2 5
1 2 6

How can I stop the duplicates? Can I give my user a warning that says
"This physician is already updated with Plans and Groups. Proceed Anyway?"
and give thme a choice.

Here are the 3 things I have thought to try:

1. I have thought about using Dlookup and was looking at the previous post
titled "3 Fields together unique/no duplicates" where code looked like
this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim vFound As Variant
vFound = DLookUp("PersonID", "your-table-name", "[FName] = """ & _
Me!txtFName & """ AND LName = """ & Me!txtLName & _
""" AND PostCode = '" & Me!txtPostCode & "'")
If Not IsNull(vFound) Then
MsgBox "This person's name already exists. Proceed anyway?", _
vbYesNo)
If iAns = vbNo Then Cancel = True
End If
End Sub

But cannot seem to get it to work for me.

2. I also, looked into trying to add critera to my append query but cannot
figure that out.

3. Finally I looked into adding something into the condition statement of
my macro that runs the append query. to stop macro if criteria already
exists.

Any suggestions?

 

[Gina]

Hi Amy

I had a similar problem. I finally didn't even use the rs.RecordCount
because it gave back 1 when there was actually no such record in the table.

use the SQL generated by your (select!)query

if the function returns 1 the record already exists otherwise a 0 is
returned

maybe I could give you an idea ?!
______________________________________________________________________
Public Function CheckPersonAlreadyIn(ByVal personName As String) As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Dim numRec As Integer

numRec = 0
Set db = CurrentDb
SQL = "SELECT Count(tblPhysicians.Name) AS Counted FROM tblPhysicians
WHERE (((tblPhysicians.Name)= '" & personName & "'));"

'Debug.Print SQL

Set rs = db.OpenRecordset(SQL)
If Not IsNull(rs) Then
numRec = rs("Counted")
Debug.Print numRec
CheckPersonAlreadyIn= numRec
End If

End Function
_______________________________________________________________________

Cheers,
Gina

I have a complicated situation that I will do my best to explain. Been
trying to puzzle this one our for a couple days now.

I have 4 tables

1. Physicians - Primary Key "UPIN"
2. Groups - Primary Key "Group ID"
3. Plans - Primary Key "PlanID"
4. PhysicianPlan - Combines all 3 primary keys in large table UPIN,
PlanID, and GroupID this table has over 34,000 records

The following is an example of PhysicianPlan

UPIN PlanID GroupID
1 2 3
1 2 4
1 2 5
1 2 6

Etc

If a new physican needs to be added to the database it is done through a
form. This form contains a command button that runs an append query.
Append query add all necessary plans and groups to new physician. What has
been happening occasionally is that a user appends a physician that does
not need appending and duplicate records are added to my PhysicianPlan table

Example:

UPIN PlanID GroupID
1 2 3
1 2 4
1 2 5
1 2 6
1 2 3
1 2 4
1 2 5
1 2 6

How can I stop the duplicates? Can I give my user a warning that says
"This physician is already updated with Plans and Groups. Proceed Anyway?"
and give thme a choice.

Here are the 3 things I have thought to try:

1. I have thought about using Dlookup and was looking at the previous post
titled "3 Fields together unique/no duplicates" where code looked like
this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim vFound As Variant
vFound = DLookUp("PersonID", "your-table-name", "[FName] = """ & _
Me!txtFName & """ AND LName = """ & Me!txtLName & _
""" AND PostCode = '" & Me!txtPostCode & "'")
If Not IsNull(vFound) Then
MsgBox "This person's name already exists. Proceed anyway?", _
vbYesNo)
If iAns = vbNo Then Cancel = True
End If
End Sub

But cannot seem to get it to work for me.

2. I also, looked into trying to add critera to my append query but cannot
figure that out.

3. Finally I looked into adding something into the condition statement of
my macro that runs the append query. to stop macro if criteria already
exists.

Any suggestions?

 

[Amy Adams via AccessMonster.com]

Gina:

Thanks so much for the reply. Since I am kind of a medium Acces user (not
beginner not adanced) I do not get into SQL much. When you say "use the
SQL generated by your (select!)query" what does this mean exactely. Where
is it I am putting this code?

Thanks so much it is greatly appreciated

 

[Gina]

Amy .... Good Morning.

every query has a design view and when you open it some kind of run-time
view. in addition to that when your query is e.g. opened in design view
there is a tab (drop-down at top left corner in access) where a 3rd
possibility - SQL view - is given.

all queries generate sql in order to get out data from the database .... the
access queries are only a 'graphical' way of generating them

cheers,
Gina

 

[Gina]

sorry ... forgot to mention that in order to do something like a COUNT on a
query, you must turn on the 'Functions' when in design view of query (lower
part - right click.)

try to generate this (separate new!) count query first

then you test it by putting a e.g. a persons name 'Smith' into the criteria
of column physiciansName.... run it - if Mr Smith is already in you'll get
a 1 or maybe even a 2 if there are 2 Smiths....

you only run your append query when there is a result of 0 - so 0 persons
are found with that name ....

(maybe test on surname AND forename as the criteria)

the criteria comes from your loaded! form - so you could use the 'expression
builder' .. it should then look somehow like this :
[Forms]![yourformname]![textboxPersonsName] and this is the criteria

what I meant with take the code from your(select!) query ... is the above
count query ... and where to put it..... inside the function' SQL I posted
.... instead of mine .... the ByVal PersonName is what is in
textboxPersonsName

Maybe I was just confusing you with so much sql stuff I guess

I hope that gives you a bit of a starting point but someone else may have a
better way for you to solve this

Cheers,
Gina