Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    San Francisco, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation in a table (Access 2k)

    Hello,
    I have been working with Access for a couple years now and I have never been able to figure this out. I don't know much code other than copying other code samples, so please be easy on me.

    Let's say I have a entry form that contains three fields called Account Name, Tax-ID # and Effective Date. Is it possible to display a message box letting the user know that "this account has already been entered, would you like to continue" when ever the user types the same tax-id # and effective date? I can't set these fields
    to no duplicates because we're an insurance company that may have the same account come up for a quote the following year.

    What I'm doing in the mean time is running the "Find Duplicate Query" which works but I wish it was more automatic.

    Your help is greatly appreciated! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation in a table (Access 2k)

    You can do this in the Before Update event of the form. Let's say that your table is named tblTest, with a unique key field named ID (for example an AutoNumber field), and fields AccountName, TaxID and EffectiveDate. I have assumed that ID is numeric, TaxID is text and EffectiveDate is date/time. This is what the code could look like:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strWhere As String
    ' Assemble where condition
    If Not IsNull(Me.TaxID) Then
    strWhere = strWhere & " And TaxID = " & Chr(34) & Me.TaxID & Chr(34)
    End If
    If Not IsNull(Me.EffectiveDate) Then
    strWhere = strWhere & " And EffectiveDate = #" & Format(Me.EffectiveDate, "mm/dd/yyyy") & "#"
    End If
    ' Nothing filled in - get out.
    If strWhere = "" Then
    Exit Sub
    End If
    ' If record exists, we don't want it to check itself
    If Not IsNull(Me.ID) Then
    strWhere = strWhere & " And ID <> " & Me.ID
    End If
    ' Get rid of first " And "
    strWhere = Mid(strWhere, 6)
    ' Are there duplicates?
    If DCount("*", "tblTest", strWhere) > 0 Then
    If MsgBox("This combination of Tax ID and Effective Date already occur." & vbCrLf & _
    "Do you want to continue?", vbQuestion + vbYesNo) = vbNo Then
    Me.Undo
    Cancel = True
    End If
    End If
    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Validation in a table (Access 2k)

    >>What I'm doing in the mean time is running the "Find Duplicate Query" which works but I wish it was more automatic.<<
    Is this a special query that you run to check for duplicates outside of the form?

    What you can do in the AfterUpdate event of both controls (Tax-ID # and Effective Date) is to use a recordset to check if you have a duplicate. Be sure to check if both controls are not Null first.

  4. #4
    New Lounger
    Join Date
    Jul 2003
    Location
    San Francisco, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation in a table (Access 2k)

    Hi thanks for your help, however, I'm not quite there yet. I'm getting a Run Time error "Run Time Error 2471 The expression you entered as a query parameter produced this error: The object doesn't contain the automation object 'ureuNo."

    I will give you a little more insight on my table: My table name is Prospects, it has a primary key with a unique id called "prospectID" the two fields I'm want to compare are "BureauNo" and "EffectiveDate". I went ahead and copied your code and replaced all the variable and table names, however, I noticed that I didn't indicate anything about "prospect Id". Should I be? When I hit the debug button it points to : "If DCount("*", "Prospects", StrWhere) > 0 Then

    **Also note in your code where it says "If MsgBox("this combination of tax Id and Effective Date already occur." & vbCrlf &_" it doesn't like the underscore so I had to shorten the message to fit it all on one line. Any insight on why it doesn't like the underscore?

    As always thanks for all you assistance!!

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation in a table (Access 2k)

    If you're trying to use the underscore as a continuation character, there needs to be a space ahead of it. The full continuation character is actually space + underscore.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation in a table (Access 2k)

    It seems that you haven't copied the spaces correctly. Charlotte already pointed out that you should have put a space before the underscore (as in my reply), and the error about 'ureauNo' indicates that you probably forgot some spaces in the strings<pre>" And ..."</pre>

    Where I had<pre>If Not IsNull(Me.ID) Then
    strWhere = strWhere & " And ID <> " & Me.ID
    End If</pre>

    you should replace ID by ProspectID.

  7. #7
    New Lounger
    Join Date
    Jul 2003
    Location
    San Francisco, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation in a table (Access 2k)

    Thanks the space after the underscore worked. This is what I have for the code, it keeps giving me the same error and points to the line where If DCount .....
    I'm looking for extra spaces or missing spaces but I must be blind. Please help. thanx! <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim StrWhere As String
    'Assemble where condition
    If Not IsNull(Me.BureauNo) Then
    StrWhere = StrWhere & "And BureauNo = " & Chr(34) & Me.BureauNo & Chr(34)
    End If
    If Not IsNull(Me.EffectiveDate) Then
    StrWhere = StrWhere & "And EffectiveDate = # " & Format(Me.EffectiveDate, "mm/dd/yyyy") & "#"
    End If
    'Nothing filled in get out
    If StrWhere = "" Then
    Exit Sub
    End If
    'if Record exists, we don't want it to check itself

    If Not IsNull(Me.ProspectID) Then
    StrWhere = StrWhere & "And ProspectID <> " & Me.ProspectID
    End If

    'Get Rid of First "And"
    StrWhere = Mid(StrWhere, 6)
    'Are there duplicates?"

    If DCount("*", "Prospects", StrWhere) > 0 Then
    If MsgBox("this already." & vbCrLf & _
    "Do you want to continue?", vbQuestion + vbYesNo) = vbNo Then
    Me.undo
    Cancel = True
    End If
    End If

    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation in a table (Access 2k)

    You still don't have spaces before the "And" in your strings. Furthermore, it looks to me like you will wind up with a mangled Where string, since this:

    StrWhere = Mid(StrWhere, 6)

    Will remove the "And" the space following it and at least the first letter of the field name, which explains your original error message.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation in a table (Access 2k)

    As I suspected, you haven't copied the spaces in my example code correctly. In this case, they are important. I had<pre>strWhere = strWhere & " And ...</pre>

    while you have<pre>StrWhere = StrWhere & "And ...</pre>

    Note that you have no space between the opening quote " and the word And. So your string will look like this immediately before the comment 'Get rid of first " And ":<pre>"And BureauNo = ..."</pre>

    The instruction strWhere = Mid(strWhere, 6) removes the first 5 characters from the string; because of the lacking space, the B of BureauNo will be removed too, leaving "ureauNo = ..." instead of "BureauNo = ..." as was intended.

  10. #10
    New Lounger
    Join Date
    Jul 2003
    Location
    San Francisco, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation in a table (Access 2k)

    THANK YOU.....THANK YOU... It worked!!! I appreciate all your help. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •