Solved

" check if a continuous form field has duplicates"

Posted on 2015-01-08
11
286 Views
Last Modified: 2015-01-09
Experts thank you in advance !

I am looking to check if a field [BillItem] has a duplicate on a continuous form and loads accustom message instead of the standard access message.

thought I had sorted using Dcount but the formula I was using does not work for a continuous form as it calls the message box even if the next line entry is not a duplicate.

The code that does not work was used for a form for a single field so not sure how to convert for a continuous form

'***** This Code does not work for a contionous form !!! ********
'Private Sub BillItem_AfterUpdate()
'If DCount("*", "updateprojectbill", "billitem= " & Me.BillItem) > 0 Then
'BillItem.BackColor = RGB(255, 182, 193)
'MsgBox "Duplicate Bill Item! Please allocate a different Bill Item.", vbInformation, "CMWorkflow Data Required"
'Me.BillItem.SetFocus
'BillItem.BackColor = vbWhite
'Me.Undo
'Exit Sub
'End If
'End Sub

Open in new window


Your help to amend the code to work would be much appreciated.  I have attached some screen shots
Standard-Access-Duplicate-Record-Message
Required-Custom-Error-Message.JPG
Form-Design-View.JPG
Form-Query-based-on-Table-Update-Project
0
Comment
Question by:Surveyor1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40538961
You need to trap the error in the Form's Error Event.  I added an else condition that first displays the error number and then displays the Access message so if you find you need to trap for other errors, you will know what the error code is.  to test this, try the duplicate and then try adding a row with a null value in the PK.  The null will throw error 3014 which is not specifically trapped so you'll see the first message which is displayed by the message box and shows the number and then is followed by the access error because you told Access to display its own error message.
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 3022
            MsgBox "Duplicate Bill Item!  Please allocate a different Bill Item.", vbOKOnly
            Response = acDataErrContinue
        Case Else
            MsgBox "Error # " & DataErr
            Response = acDataErrDisplay
    End Select
            
End Sub

Open in new window

Your code is in the AfterUpdate event of the form.  That event only fires if the record was actually saved and in this case it wasn't.  Remove that code and use the code I provided but put it into the Error event.
0
 

Author Comment

by:Surveyor1
ID: 40538999
Thank you for the quick response

I have tested and your answer works well if you leave the record but I want the message to appear when the bill item field is duplicated so after update event.  This will prevent the rest of the record being completed before the user gets the error message

Thanks

Anthony
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40539030
The "After" events fire AFTER something is saved.  If you want to PREVENT the error, you need to do your validation in the BEFORE update event.  It has nothing to do with whether the form is continuous or not.  However, you will have trouble setting formatting properties on a continuous form unless you use the Conditional Formatting tool.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Surveyor1
ID: 40539046
Thank you for your response

What code do I put in the before update ?

The Dcount code I am using ? or the solution you have provided ?

Thanks Anthony
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40539961
>>>  This will prevent the rest of the record being completed before the user gets the error message

If you want the user to be 'locked'  onto the BillItem control before completing the rest of the record, you would have to use the Before Update event of the BillItem textbox:

Private Sub BillItem_BeforeUpdate(Cancel as Integer)
      If DCount("*", "updateprojectbill", "billitem= " & Me.BillItem) > 0 Then
               'BillItem.BackColor = RGB(255, 182, 193)
               MsgBox "Duplicate Bill Item! Please allocate a different Bill Item.", vbInformation, "CMWorkflow Data Required"
                Cancel = True
              'BillItem.BackColor = vbWhite
               'Me.Undo
               'Exit Sub
      End If
End Sub

Open in new window


That said, I generally prefer to do all validation checks at the same time in the FORM before Update event, rather than on a control by control basis (IMO, that is a more pleasant UI).  The code for validating the bill item would be the same.

Also, as Pat mentioned, formatting through VBA in a continuous form is problematic... all records will take on the same formatting since it is a single control despite the multiple records.

In the conditional formatting window, select "Expression IS".  Use the following for your Expression:

DCount("*", "updateprojectbill", "billitem= " & [BillItem]) > 0

Open in new window


(Although I'm not 100% sure that will work if the Before Update event prevents the BillItem from updating.)
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40540177
You would use your original code as adjusted by mbizip in the BeforeUpdate event of the control.  Using Me.Undo is up to you.  mbizip commented it out and I agree.   I tend to not erase the data entered by the client.  I think it is easier for them to see what error they made.  The only time I use Me.Undo is in the case where I am not ever going to let them enter ANYTHING.  For example, I would use Me.Undo in the BeforeInsert event of a subform when I check the mainform and find that they have not yet created a main form record so it makes no sense to let them attempt to create a subform record.

Also, keep in mind that Me.Undo erases EVERYTHING they have typed so far in EVERY control.  To erase only the contents of a specific control, use Me.controlname.Undo.
0
 

Author Comment

by:Surveyor1
ID: 40540546
Thanks

This is what I have opted for

'Code to prevent duplicates in Bill Item
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
BillItem.BackColor = RGB(255, 182, 193)
MsgBox "Duplicate Bill Item!  Please allocate a different Bill Item.", vbInformation, "CMWorkflow Data Required - Duplicate Entry"
Me.BillItem.SetFocus
BillItem.BackColor = vbWhite
'Me.Undo
Response = acDataErrContinue
Case 3058
BillItem.BackColor = RGB(255, 182, 193)
MsgBox "Enter Bill Item!  Please Enter a Bill Item.", vbInformation, "CMWorkflow Data Required - Missing Bill Item"
Me.BillItem.SetFocus
BillItem.BackColor = vbWhite
'Me.Undo
Response = acDataErrContinue
Case Else
MsgBox "Error # " & DataErr
Response = acDataErrDisplay
End Select
End Sub

Open in new window


I will allocate points accordingly

Thanks

Anthony
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40540598
That looks OK.  I don't know whether this is a pasting issue or not but do you not indent your code?  It is much easier to read when properly indented.
0
 

Author Comment

by:Surveyor1
ID: 40540610
I am a complete beginner on code so I will start to do that
0
 

Author Closing Comment

by:Surveyor1
ID: 40540615
Good assistance and guidance !! Thank you
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40541448
You're welcome.  Just remember the "Before" events are where your validation code should go.  In particular, the FORM's BeforeUpdate event since that is the only reasonable place to check for empty fields or for fields such as dates that should have a relationship to each other.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question