Results 1 to 4 of 4

Thread: dcount issue

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: dcount issue

    Hello,

    I am trying to do a before update check of a table (Purchases) to ensure a duplicate invoice is researched. There can be duplicate invoices due to invoices being received from many different distributors or due to internal controls.

    I am trying to accomplish this using the following code in the before update():

    If Me.NewRecord Then
    If DCount("[InvNum]", "Purchases", "[InvNum] = " & (Me.InvNum)) > 0 Then
    MsgBox "This is a Duplicate Invoice, research before continuing. Select research button and input Invoice Number", vbQuestion + _
    vbYesNo, "Duplicate Invoice Error" = vbYes _
    , vbExclamation, "Invoice Number Validation"
    Cancel = True
    End If

    When I test this piece of code I get the following error: "Data type mismatch in criteria expression". The field "InvNum" in the Purchases table is a text field and the field in the form is connected to the "InvNum".

    FYI, I am in Access 2007 and the database that I have inherited (and expected to maintain, improve, etc) is Access 2000. Not sure if that has anything to do with the issue. Also, I cannot convert this database to 2007 until I can complete full regression testing on it.

    Thanks for any and all help I can get.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    It sounds like your Invoice Number is a text field, which is as it should be.
    Try this:
    If DCount("[InvNum]", "Purchases", "[InvNum] = '" & Me.InvNum & "'") > 0 Then
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Apr 2011
    Posts
    2

    dcount issue

    Thanks! That did the trick! I appreciate the help.

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    Excellent! Glad we could help.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

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