Don't print if no record

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

Answer 1

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)

Answer 2

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.

Answer 3

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

Answer 4

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)

Answer 5

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

Answer 6

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