Results 1 to 8 of 8
  1. #1
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45

    Record Submit Validation Rule in Form

    Hello,



    I have created a form to input values into a table. I am using VBA code to send the information based on a submit button. I am trying to create a validation rule that will check the table to see if any record has same account number & date as the form so that no duplicates may be entered. It has to be a combination of the two since the customer record will be repeated based on different dates, and different dates will be repeated based on customer numbers.

    If record set from table contains account number & date
    Deny the submission and alert user

    Else
    add record

    Seems pretty simple but I do not know how to check the table record by record.

    Thanks for the help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    One way is:
    If Not IsNull(DLookup("AccountNumber field","tablename","AccountNumber field='" & Me.AccountTextbox & "' AND Date field=#" & Me.DateTextbox & "#")) Then
    'code to cancel record, not quite sure what this would be
    End If

    Are form controls bound?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    to avoid doubles there: just put an unique index on the fields combination account nr. & date.

    greetings
    NG

  4. #4
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    NoellaG,

    This was a great idea. I implemented this on my tables to ensure they reject any duplicates. Now I am trying to incoporate June7's idea into my form input to create a custom error instead of the Access error that occurs when you try to input a duplicate.

    June7,

    The form controls are Not bound. I am using VBA to send the values to different recordsets.

    I tried to add your code to my form and I am receiving a Type 13 error for data mismatch. If I remove the AND part and use the DLookup for either account number or date, there are no errors. Is the code missing anything?

    If Not IsNull(DLookup("iVueAccount", "NonCoreSales_Index", "iVueAccount='" & Me.iVueAccount & "' AND Delivery_Period=#" & Me.Delivery_Period & "#")) Then

    Above is my code. Delivery_Period is the name of the textbox on the form, and its the field in the table. Also, they are both Short Dates.

    Thanks for your suggestions!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Good point from NoellaG. Serves as reassurance that duplication won't occur and I like that you want to offer friendlier interface to users than the intrinsic Access warnings.

    Sending the values to different recordset - is there duplication of data here?

    I don't see why the compound criteria is not working. Short date is just a display formatting and does not affect the actual date value. However, instead of using the DLookup you could take advantage of the Access error message with error handling code in the procedure. Check out http://allenbrowne.com/ser-23a.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    There is not duplication. That was for a separate form but has been resolved to ensure no duplication.

    I think I will try to use Error Handling to see if that can accomplish this task. Since the index is restricting the use, I only need the error box changed so that users understand what is causing the problem.

    Thanks a lot June7/nG!

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you use error handling, you can check for a specific err.number and present your own customized message. Error number 3022 is attempt to add duplicate record.

    Here's an example
    Private Sub Command6_Click()
    On Error GoTo Err_Command6_Click


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("CustomerRelatedCompanies")

    AnotherRecord:
    rs.AddNew
    rs!CustomerNo = Me.Combo0.Value
    rs!RelatedCustomerNo = Me.Combo2.Value
    rs!RelationType = Me.List4.Value
    rs.Update


    Exit_Command6_Click:
    Exit Sub

    Err_Command6_Click:
    If Err.number = 3022 Then
    MsgBox "That record already exists in the table. Proceed to another record"
    rs.CancelUpdate
    Else
    MsgBox Err.number & " " & Err.Description
    End If

    Resume Exit_Command6_Click

    End Sub
    As a side note, if you do not have the free utility MZTools for Vba, you should download it and use it.

    It can do many things. One that I use a lot is the Error Handler. MZTools will insert error handling logic into a procedure. You can also use it to insert custom header information on Procedures and Modules.

    Check it out http://www.mztools.com/v3/mztools3.aspx

  8. #8
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Quote Originally Posted by orange View Post
    If you use error handling, you can check for a specific err.number and present your own customized message. Error number 3022 is attempt to add duplicate record.

    Here's an example

    As a side note, if you do not have the free utility MZTools for Vba, you should download it and use it.

    It can do many things. One that I use a lot is the Error Handler. MZTools will insert error handling logic into a procedure. You can also use it to insert custom header information on Procedures and Modules.

    Check it out http://www.mztools.com/v3/mztools3.aspx
    Orange, that worked perfectly. I downloaded mztools and will start to use that also. You guys have been very helpful in this thread and I have learned a lot of new things in Access that I did not know existed!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validation Rule
    By ritzzy in forum Access
    Replies: 1
    Last Post: 04-13-2011, 01:33 PM
  2. Validation Rule Form AfterUpdate
    By GraemeG in forum Programming
    Replies: 6
    Last Post: 02-09-2011, 10:22 AM
  3. Validation Rule
    By Megan in forum Access
    Replies: 1
    Last Post: 11-05-2010, 09:45 AM
  4. Validation Rule
    By robomose in forum Access
    Replies: 1
    Last Post: 09-09-2009, 05:59 AM
  5. Validation Rule
    By mistaken_myst in forum Database Design
    Replies: 2
    Last Post: 10-29-2007, 02:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums