Message Box for a Duplicate Entry

[bwesenberg]

Hello All

I have a form that my users enter their Audits in. The Primary key is the RECID field.
They enter a policy number and an effective date in this form as well.
What they want to happen is after they enter the policy number tab and enter the effective date if the Policy Number and the Effictive date is a duplicate record they want an warning message. However they want to be able to continue to enter this record.
They just want to be made aware that it is a duplicate.

Can this be done. I know it will be code but if it can be done can you help me out with the code.

Thanks

[missinglinq]

Is PolicyNumber Numeric or Text?

Linq ;0)>

[NeoPa]

It can be done, and we can help with the code.

To know how to pitch this we will need to know whereabouts you are with your understanding and progress.

I will start off by saying that a DLookup() call based on the [Policy Number] and the [Date] will be required.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[ADezii]

First, a couple of assumptions:

1. Your Table name is tblAudits with the following Fields:
   • [PolicyNumber] - {TEXT}
   • [EffectiveDate] - {DATE/TIME}
2. The Text Fields on your Form for the Policy Number and Effective Date are txtPolicyNumber and txtEffectiveDate respectively.
3. Policy Number is a Text Field.
4. Only if both the Policy Number and Effective Date are duplicated does the Message Box appear.
5. This code should probably be placed in the AfterUpdate() Event of both Fields in order to be 100% effective.
   

   Private Sub txtEffectiveDate_AfterUpdate()
   Dim strMsg As String
    
   strMsg = "A Policy Number of [" & Me![txtPolicyNumber] & "] with an Effective Date of [" & _
            Me![txtEffectiveDate] & "] already exists in the database!"
   'Make sure that both Fields contain values
   If Not IsNull(Me![txtPolicyNumber]) And Not IsNull(Me![txtEffectiveDate]) Then
     'See if the Policy Number exists in tblAudits
     If DCount("*", "tblAudits", "[PolicyNumber] = '" & Me![txtPolicyNumber] & "'") > 0 Then
       'See if the Effective Date exists in tblAudits
       If DCount("*", "tblAudits", "[EffectiveDate] = #" & Me![txtEffectiveDate] & "#") > 0 Then
         'A Duplicate based on your definition
         MsgBox strMsg, vbExclamation, "Duplicate Record"
       End If
     End If
   End If
   End Sub

[ADezii]

It can be done, and we can help with the code.

To know how to pitch this we will need to know whereabouts you are with your understanding and progress.

I will start off by saying that a DLookup() call based on the [Policy Number] and the [Date] will be required.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

Sorry for stepping on your toes, NeoPa. We both must have been replying at the exact same time.

[NeoPa]

Only if it takes you 1/2 an hour to post ADezii :D

You know I'm kidding, and you're not stepping on toes anyway. You have every right to post an answer of course.

[NeoPa]

Private Sub txtEffectiveDate_AfterUpdate()
Dim strMsg As String
 
strMsg = "A Policy Number of [" & Me![txtPolicyNumber] & "] with an Effective Date of [" & _
         Me![txtEffectiveDate] & "] already exists in the database!"
'Make sure that both Fields contain values
If Not IsNull(Me![txtPolicyNumber]) And Not IsNull(Me![txtEffectiveDate]) Then
  'See if the Policy Number exists in tblAudits
  If DCount("*", "tblAudits", "[PolicyNumber] = '" & Me![txtPolicyNumber] & "'") > 0 Then
    'See if the Effective Date exists in tblAudits
    If DCount("*", "tblAudits", "[EffectiveDate] = #" & Me![txtEffectiveDate] & "#") > 0 Then
      'A Duplicate based on your definition
      MsgBox strMsg, vbExclamation, "Duplicate Record"
    End If
  End If
End If
End Sub

Rather than checking both items individually (which will only tell you when both the date and the policy number have previously been used), you should check for at least one item where BOTH date and policy number match in the same record.

Dim strWhere As String
...
strWhere = "([PolicyNumber]='%P') AND ([EffectiveDate]=#%D#)"
strWhere = Replace(strWhere, "%P", Me.txtPolicyNumber)
strWhere = Replace(strWhere, "%D", Format(Me.txtEffectiveDate, "m/d/yyyy"))
If DCount(Expr:="*", Domain:="[tblAudits]", Criteria:=strWhere) > 0 Then
...

NB. To avoid problems with European dates you should always ensure that dates are inserted into your SQL (including the Criteria clause of a Domain Aggregate function) in m/d/yyyy (or any unambiguous) format.

PS. [Whispers]That'll teach him to butt in on my thread - he he he[/Whispers]

[NeoPa]

@Becky, I'm sorry, you've found us in a silly mood. Naturally I blame the others in the thread (Linq didn't actually say anything silly I know but he was certainly THINKING it). You can blame it on our extreme youth :D

PS. We will still do what we can to assist of course. ADezii has already supplied some code to start you off with. Let us know how you make out.