none
Don't print if no record RRS feed

  • Question

  • I have a macro that print a report, this report is based on a query with parameters, I wish that macro not to be executed if the recordset is empty.

    Here is the query in design mode: ("CouponCuisine Requête pour impression")

    So when I am in my invoicing form "Détails commandeBT" a coupon is sent to the kitchen for the order when the macro is executed, however, I have 2 places where that macro can be runs, if the code was already printed, then I want the coupon not to print. At the moment everything works fine except that an empty coupon is printing out if the user uses the second place where that code can be run.

    So my goal is to insure that if the recordset is empty, don't print a blank coupon,

    Here is the code I tried and I received an error (See after the code)

    Dim dbs As DAO.Database
    Dim qry As QueryDef
    Dim rst2 As DAO.Recordset
    Set dbs = CurrentDb
    
    Set qry = dbs.QueryDefs("CouponCuisine Requête pour impression")
    Set rst2 = qry.OpenRecordset()
    
    If rst2.RecordCount <> 0 Then
    DoCmd.RunMacro "EnvoyerCouponVersCuisine"
    End If

    Here is the error message: Traduction "To few parameters, 2 expected"

    I guess that if my query contains 2 parameters this is something to do with that? what can I do?

    Thank you

    Claude from Québec, Canada


    Claude Larocque



    Saturday, September 19, 2015 11:25 AM

Answers

  • Good Afternoon Claude,

    and please pardon the intrusion if I am off base.

    Are the Fields where you have False and True, two separate conditions if so maybe something like Sorry I cannot read the full field so substitute This for your two fields

      Dim dbs As DAO.Database
        Dim qry As QueryDef
        Dim rst2 As DAO.Recordset
        Set dbs = CurrentDb

        Set qry = dbs.QueryDefs("CouponCuisine Requête pour impression")

        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf commande]") = _
            [Forms]![Détails CommandeBT].[Réf commande]
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf client]") = _
            [Forms]![Détails CommandeBT].[Réf client]
     And This False Field
    OR
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf commande]") = _
            [Forms]![Détails CommandeBT].[Réf commande]
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf client]") = _
            [Forms]![Détails CommandeBT].[Réf client]
     And This True Field  

     Set rst2 = qry.OpenRecordset

        If rst2.RecordCount <> 0 Then
            DoCmd.RunMacro "EnvoyerCouponVersCuisine"
        End If

    Thank You!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, September 21, 2015 8:53 PM

All replies

  • Does this work?

        Dim dbs As DAO.Database
        Dim qry As QueryDef
        Dim rst2 As DAO.Recordset
        Set dbs = CurrentDb
    
        Set qry = dbs.QueryDefs("CouponCuisine Requête pour impression")
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf commande]") = _
            [Forms]![Détails CommandeBT].[Réf commande]
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf client]") = _
            [Forms]![Détails CommandeBT].[Réf client]
        Set rst2 = qry.OpenRecordset
    
        If rst2.RecordCount <> 0 Then
            DoCmd.RunMacro "EnvoyerCouponVersCuisine"
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, September 19, 2015 11:36 AM
  • Here is the error message: Traduction "To few parameters, 2 expected"


    Hi Claude,

    Mostly this message is generated when the query or QueryDef contains a field that is not available in the recordset.

    So check your QueryDef.

    Imb.

    Saturday, September 19, 2015 9:31 PM
  • Hi Imb and Hans, glad to see you answer my question because usually it works after your advices. However, in the code that you send me Hans, I have now and error 3265 - Item not found in this collection. I went to check other question on this error and it seems like a field isn't available in the recordset.

    But if I remove the query parameters and just print the macro, everything is printing correctly, so the recordset has to be good otherwise it will generate an error.

    Sometimes it is frustrating I wish Access could give us more explanation when an error is generated, if a field isn't available, why not giving us the field they found missing... just my 2 cents.

    Hard to find!

    Claude


    Claude Larocque

    Sunday, September 20, 2015 1:07 AM
  • Does it help if you open the query 'CouponCuisine Requête pour impression' in design view and declare the parameters explicitly in the Parameters dialog?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 20, 2015 9:06 AM
  • Hi Hans,

    If you look at the image of the query the parameters are explicitely in the parameters dialog, however, if I add your code minus the parameters because it is already set explicitely in the design, I still received an error 3061...here is the code shorten of the parameters:

        Dim dbs As DAO.Database
        Dim qry As QueryDef
        Dim rst2 As DAO.Recordset
        Set dbs = CurrentDb
    
        Set qry = dbs.QueryDefs("CouponCuisine Requête pour impression")
        Set rst2 = qry.OpenRecordset
    
        If rst2.RecordCount <> 0 Then
            DoCmd.RunMacro "EnvoyerCouponVersCuisine"
        End If

    Thanks for your time

    Claude


    Claude Larocque

    Monday, September 21, 2015 7:41 PM
  • Good Afternoon Claude,

    and please pardon the intrusion if I am off base.

    Are the Fields where you have False and True, two separate conditions if so maybe something like Sorry I cannot read the full field so substitute This for your two fields

      Dim dbs As DAO.Database
        Dim qry As QueryDef
        Dim rst2 As DAO.Recordset
        Set dbs = CurrentDb

        Set qry = dbs.QueryDefs("CouponCuisine Requête pour impression")

        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf commande]") = _
            [Forms]![Détails CommandeBT].[Réf commande]
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf client]") = _
            [Forms]![Détails CommandeBT].[Réf client]
     And This False Field
    OR
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf commande]") = _
            [Forms]![Détails CommandeBT].[Réf commande]
        qry.Parameters("[Formulaires]![Détails CommandeBT].[Réf client]") = _
            [Forms]![Détails CommandeBT].[Réf client]
     And This True Field  

     Set rst2 = qry.OpenRecordset

        If rst2.RecordCount <> 0 Then
            DoCmd.RunMacro "EnvoyerCouponVersCuisine"
        End If

    Thank You!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, September 21, 2015 8:53 PM