Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004

    Unanswered: Conditional Macro based on query results

    OK, here's my situation. I have a database that generates check request forms. An amount is provided for each account. For some of the accounts, the amount is broken down into multiple checks based on a percentage that is in the database. I want to add some additional controls to the database. I already have a query that sums up the percentages. If anything is outside of a small tolerance level I set around 100%, it shows up on the query. If everything is OK, the query is blank. What I would like to do is set up a macro that runs at several points during the process. If the query described above is blank, it will transparent to the user. If something is in the report, an error message will appear. I've searched the web for conditional macro and vba solutions, but everything I try always gives me an error. If I can just figure out how to return a certain value if the query is blank and another value if it isn't, I can do all the rest.

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Personally I wouldn't use a macro. It would have helped if you posted the code that gave you an error. You could open a recordset on the query, then:

    If rs.EOF Then
      'query returned no records
      'query returned something
    End If

  3. #3
    Join Date
    Jul 2006
    to be exact, you could checkout the "where" command of the macro if your using a filter, or you could just right click the top of the macro window and hit "conditions"

  4. #4
    Join Date
    Sep 2004
    Thanks for the info.....used it as a guide and tried a couple of different things until I got this, which seems to work very well so far. It automatically runs when the percentage editing form is closed, and also before check requests go out.

    Function Percentage_Check()
    Const strQueryName = "qryPercentageCheck"
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strQueryName)
    If Not rs.EOF Then
    MsgBox "One or more Profit Centers with multiple checks do not add up to 100%. Please review and correct data on the following report before proceeding", 0, "Percentage Check Error!!"
    DoCmd.OpenReport "rptPercentageCheck", acViewPreview
    End If

    End Function

Posting Permissions

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