Trying to avoid duplicate board position titles

J

Jim Ory

Using WinXP & Access2K2

In my database board members are coded as President, Vice-President,
Secretary, and so on. There are a total of 7 positions.

Two tables take care of this, one table for the "entity" and one table for
the "position". An "Add/Edit" form for the Entities and a subform for the
Positions handles the data. Each year, one or more of these entities become
board members, non-board members, or they change board positions. As these
changes are being entered into the database, there is a strong possibility
that adding or changing a position of an entity will cause duplicate
positions.

I would like to add code in an event that would check for duplicate
positions before allowing the data entering person to proceed. If there are
duplicate positions, give them a message box telling them what the problem is
and how to remedy the duplicate entries.

In the after update event, I put code to run a "Find Duplicates" query. It
is quite crude and would require that I train all data entry persons what to
do with the query after it has run. It is fine for me, but not so elegant and
simple for others.

Thanks in advance.
 
S

s4

You could use dlookup in the afterupdate:

Private Sub x_AfterUpdate()
If Me.x = DLookup("x", "table", "[x] ='" & Me.x & "'") Then
MsgBox "This position is already filled" & Chr(13) & "" & Chr(13) & "Person:
" & DLookup("name(orwhatever)", "table", "[x ='" & Me.x & "'"), vbInformation
+ vbOKOnly, "POSITION ALREADY TAKEN!!"
Me.X = Null
End If
End Sub

Replace x with the name of the control that is being updated and table with
the name of the table that contains that field.
If you're checking the value for a different table,
change the x in dlookup("x" to the name of the control in the other table
and the x in the me.x to the control you've just updated. Hope this helps.
 
A

Anthony Bollinger

Use a count query on the position titles and persons. If any count is > 1,
you have a problem.
 
J

Jim Ory

Great solution "S4", works exactly like I wanted it to. Thanks.
--
Jim Ory


s4 said:
You could use dlookup in the afterupdate:

Private Sub x_AfterUpdate()
If Me.x = DLookup("x", "table", "[x] ='" & Me.x & "'") Then
MsgBox "This position is already filled" & Chr(13) & "" & Chr(13) & "Person:
" & DLookup("name(orwhatever)", "table", "[x ='" & Me.x & "'"), vbInformation
+ vbOKOnly, "POSITION ALREADY TAKEN!!"
Me.X = Null
End If
End Sub

Replace x with the name of the control that is being updated and table with
the name of the table that contains that field.
If you're checking the value for a different table,
change the x in dlookup("x" to the name of the control in the other table
and the x in the me.x to the control you've just updated. Hope this helps.


Jim Ory said:
Using WinXP & Access2K2

In my database board members are coded as President, Vice-President,
Secretary, and so on. There are a total of 7 positions.

Two tables take care of this, one table for the "entity" and one table for
the "position". An "Add/Edit" form for the Entities and a subform for the
Positions handles the data. Each year, one or more of these entities become
board members, non-board members, or they change board positions. As these
changes are being entered into the database, there is a strong possibility
that adding or changing a position of an entity will cause duplicate
positions.

I would like to add code in an event that would check for duplicate
positions before allowing the data entering person to proceed. If there are
duplicate positions, give them a message box telling them what the problem is
and how to remedy the duplicate entries.

In the after update event, I put code to run a "Find Duplicates" query. It
is quite crude and would require that I train all data entry persons what to
do with the query after it has run. It is fine for me, but not so elegant and
simple for others.

Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

avoid duplicates on label report? 5
return previous employee 2
table structure 3
Create duplicate data 1
Manning Database 6
avoid duplicate record. 1
SQL Join Question 0
Forms 3

Top