Allowing Duplicates Conditionally

[Chu Gai]

In a form called, frm CCC Follow-ups (Access 2003), there are two
fields, Development & Ticket No. Different Developments can have the
same ticket number. No problem there. Normally, ticket numbers are
unique within a particular Development. However, if the job associated
with the ticket number is completed (closed), it is 'possible' that
the ticket number can be reused. These numbers are generated by an
external department and there is no way to require them to only
generate unique numbers.

So, my question is how can I have a message box pop-up that alerts the
user when a ticket number for a Development has already been used? The
user would then be able to double check their data entry and enter the
duplicate Ticket No with the knowledge that it's not due to an error
on their part. In the past, the Development & Ticket No fields were
indexed to prevent duplicates but right now I've had to remove that
constraint. I assume this might be on an after update event.

 

[Tom van Stiphout]

On Wed, 17 Sep 2008 04:09:00 -0700 (PDT), Chu Gai

You could use DCount or a similar query of your own to count the
duplicates. Run this code in the Form_BeforeUpdate and set Cancel=True
if the user acknowledges she made a mistake.

-Tom.
Microsoft Access MVP

 

[Chu Gai]

Thanks Tom, but as a person who is really a rank amateur and doing
this to help out my wife, I'm afraid I need a bit more guidance here.

 

[Klatuu]

What Tom is suggest is to use a Domain Aggregate function to determine if
the Ticket Number already exists in the table. He suggests a DCount. I
prefer to use a DLookup. This is typically done in the Before Update event
of the form because you can Cancel the update if the data is not correct.
It would go something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If not IsNull(DLookup("[TicketNumber]", "[Development] = " &
Me.Development & " And " & [TicketNumber] = " & Me.TicketNumber)) Then
If MsgBox("Duplicate Ticket Number " & Me.TicketNumber & " Found For
" & Me.Development & vbNewLIne & "Allow This Duplicate", vbQuestion +
vbYesNo) = vbNo Then
Cancel = True
End If
End If

Note the syntax for the DLookup assumes both table fields are numeric. If
they are not, you will need to use different syntax. In the line below, for
example, it assumes they are both text:

If not IsNull(DLookup("[TicketNumber]", "[Development] = """ &
Me.Development & """ And """ & [TicketNumber] = """ & Me.TicketNumber &
"""")) Then

To do this, you use the Before Update event of the form where