Trying to avoid duplicate board position titles

[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.

 

[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.

 

[Anthony Bollinger]

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

 

[Jim Ory]

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

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.

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.