Results 1 to 6 of 6

Thread: Macro Help

  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query that checks to see if I have a duplicate record. I invoke the query via a macro on my switchboard. Is it possible (via code) to return a "no records found" IF the query returns no records?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at using DCount in the macro

    You will need to set it in the Condition Part of the macro (Assuming you mean Macro NOT VBA)

    [attachment=89612Count.jpg]
    Attached Images Attached Images
    Andrew

  3. #3
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect!

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    Nice! Gets a Thumbs Up from me.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If records were found, would it be possible to display the count?

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think NOT in a Macro (someone else might correct me on that) unless the Macro is calling a VBA Function with RUNCODE Action

    You could have a Macro with RunCode and the Function Call Such as CheckRecords()

    Then Have a VBA Function in a Module with

    Code:
    Function CheckRecords()
    
    Dim strMSG as string, lngRecs as Long
    
    lngRecs=Dcount("*","Name Of Your Query")
    
    IF lngRecs=0 Then
        strMSG="There are NO Records to View etc (Whatever You Want..."
    Else
        strMSG=lngRecs & " Records Found in ... etc "
    Endif
    
    MsgBox strMSG,vbInformation
    
    End Function
    Or if you want it to be a bit more flexible, you could pass the name of the Query to the Function as a parameter

    e.g.

    Macro Action Is RunCode with a Function as CheckRecords("Name of Query Goes Here")

    Then the Function Would be

    Code:
    Function CheckRecords(strQuery As String)
    
    Dim strMSG as string, lngRecs as Long
    
    lngRecs=Dcount("*",strQuery)
    
    IF lngRecs=0 Then
        strMSG="There are NO Records to View etc (Whatever You Want...) IN " & strQuery
    Else
        strMSG=lngRecs & " Records Found in ... etc IN " & strQuery
    Endif
    
    MsgBox strMSG,vbInformation
    
    End Function
    Andrew

Posting Permissions

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