Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Unanswered: DLookup Question

    Question, I am trying to test within VBA using DLookup if a records exists in table called tblAllocations prior to another action taking place. For some reason I cannot get the desired results. I want to lookup if intTransactionID exist in tblAllocations, if so it should display and MsgBox otherwise it should continue with the code. For example, from my form called frmTransactions if idsTransactionID (control name) has a matching record in tblAllocations for intTransactionID (field name) then it should display MsgBox and exit the sub; otherwise continue with code.

    Dim LAllocationID As Integer
    If LAllocationID = DLookup("intTransactionID", "tblAllocations", "intTransactionID = " & Forms!frmTransactions!idsTransactionID) Then
    MsgBox whatever will go here.
    Exit Sub

    Any help would be greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If it's only for testing if a record exists in a table, you don't need to use DLookup, you can use DCount:
    Code:
    If DCount("intTransactionID", "tblAllocations", "intTransactionID = " & Forms!frmTransactions!idsTransactionID) <> 0 Then
    This frees you from having to care for Null values, which is what DLookup will return when no row matches the criteria. Your test should be:
    Code:
    If Not IsNull(DLookup("intTransactionID", "tblAllocations", "intTransactionID = " & Forms!frmTransactions!idsTransactionID)) Then
    Have a nice day!

  3. #3
    Join Date
    Feb 2009
    Posts
    5
    Worked like a champ!!!

    Thanks
    Pat

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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