Solved

" check if a continuous form field has duplicates"

Posted on 2015-01-08
11
267 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
  • 5
  • 5
11 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:Surveyor1
Comment Utility
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
Comment Utility
>>>  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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
I am a complete beginner on code so I will start to do that
0
 

Author Closing Comment

by:Surveyor1
Comment Utility
Good assistance and guidance !! Thank you
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now