Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find a value in a table that matches value on form (XP SR 2)

    Hello Access Gurus,

    I am wanting to check that a particular Bill code that has been typed into a form does not exist in another table of Deleted codes. I presume this would be built behind the BeforeUpdate event for the Bill Code control on the form however, I am having trouble with the code. I would like to display a message to the user that states that they can't use this code as it has already been used and deleted.

    Looking forward to hearing from you.

    Regards,

    Jocelyn

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

    Re: Find a value in a table that matches value on form (XP SR 2)

    You can use the DCount function for this. Here is an example; you will have to adapt it for your situation, of course. If you type DCount in the Visual Basic Editor and press F1, you'll get online help for this function

    Private Sub txtBillCode_BeforeUpdate(Cancel As Integer)
    If DCount("*", "tblDetetedCodes", "BillCode = " & Me.txtBillCode) > 0 Then
    MsgBox "The code " & Me.txtBillCode & " has already been used.", vbExclamation
    Cancel = True
    End If
    End Sub

    This assumes that the Bill Code is a number field. If it is text. use Chr(34) to surround the value with quotes:

    DCount("*", "tblDetetedCodes", "BillCode = " & Chr(34) & Me.txtBillCode & Chr(34))

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a value in a table that matches value on form (XP SR 2)

    As usual Hans, your blood is worth bottling. Thanks again for your help.

    Cheers,

    Jocelyn

Posting Permissions

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