By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

Message Box for a Duplicate Entry

bwesenberg
P: 17
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
Sep 15 '08 #1
Share this Question
Share on Google+
7 Replies


missinglinq
Expert 2.5K+
P: 3,532
Is PolicyNumber Numeric or Text?

Linq ;0)>
Sep 15 '08 #2

NeoPa
Expert Mod 15k+
P: 31,476
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]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. StudentID       AutoNumber    PK
  3. Family          String        FK
  4. Name            String
  5. University      String        FK
  6. Mark            Numeric
  7. LastAttendance  Date/Time
Sep 15 '08 #3

ADezii
Expert 5K+
P: 8,628
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub txtEffectiveDate_AfterUpdate()
    2. Dim strMsg As String
    3.  
    4. strMsg = "A Policy Number of [" & Me![txtPolicyNumber] & "] with an Effective Date of [" & _
    5.          Me![txtEffectiveDate] & "] already exists in the database!"
    6. 'Make sure that both Fields contain values
    7. If Not IsNull(Me![txtPolicyNumber]) And Not IsNull(Me![txtEffectiveDate]) Then
    8.   'See if the Policy Number exists in tblAudits
    9.   If DCount("*", "tblAudits", "[PolicyNumber] = '" & Me![txtPolicyNumber] & "'") > 0 Then
    10.     'See if the Effective Date exists in tblAudits
    11.     If DCount("*", "tblAudits", "[EffectiveDate] = #" & Me![txtEffectiveDate] & "#") > 0 Then
    12.       'A Duplicate based on your definition
    13.       MsgBox strMsg, vbExclamation, "Duplicate Record"
    14.     End If
    15.   End If
    16. End If
    17. End Sub
Sep 15 '08 #4

ADezii
Expert 5K+
P: 8,628
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]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. StudentID       AutoNumber    PK
  3. Family          String        FK
  4. Name            String
  5. University      String        FK
  6. Mark            Numeric
  7. LastAttendance  Date/Time
Sorry for stepping on your toes, NeoPa. We both must have been replying at the exact same time.
Sep 15 '08 #5

NeoPa
Expert Mod 15k+
P: 31,476
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.
Sep 15 '08 #6

NeoPa
Expert Mod 15k+
P: 31,476
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtEffectiveDate_AfterUpdate()
  2. Dim strMsg As String
  3.  
  4. strMsg = "A Policy Number of [" & Me![txtPolicyNumber] & "] with an Effective Date of [" & _
  5.          Me![txtEffectiveDate] & "] already exists in the database!"
  6. 'Make sure that both Fields contain values
  7. If Not IsNull(Me![txtPolicyNumber]) And Not IsNull(Me![txtEffectiveDate]) Then
  8.   'See if the Policy Number exists in tblAudits
  9.   If DCount("*", "tblAudits", "[PolicyNumber] = '" & Me![txtPolicyNumber] & "'") > 0 Then
  10.     'See if the Effective Date exists in tblAudits
  11.     If DCount("*", "tblAudits", "[EffectiveDate] = #" & Me![txtEffectiveDate] & "#") > 0 Then
  12.       'A Duplicate based on your definition
  13.       MsgBox strMsg, vbExclamation, "Duplicate Record"
  14.     End If
  15.   End If
  16. End If
  17. 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.
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2. ...
  3. strWhere = "([PolicyNumber]='%P') AND ([EffectiveDate]=#%D#)"
  4. strWhere = Replace(strWhere, "%P", Me.txtPolicyNumber)
  5. strWhere = Replace(strWhere, "%D", Format(Me.txtEffectiveDate, "m/d/yyyy"))
  6. If DCount(Expr:="*", Domain:="[tblAudits]", Criteria:=strWhere) > 0 Then
  7. ...
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]
Sep 15 '08 #7

NeoPa
Expert Mod 15k+
P: 31,476
@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.
Sep 15 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.