Luis Luis - 6 months ago 36
SQL Question

Access DoCmd.OpenReport Where condition issue

I'm trying to print a report, like a invoice. But I have an issue with the Cmd.OpenReport Function.

I have a query for that invoice with a parameter.

SELECT EMPLEADOS.CODIGO, EMPLEADOS.NOMBRE, PAGOS.Fecha, PAGOS.Descripcion,
CONCEPTOS.Descripcion, DETALLE_PAGOS.Monto
FROM ((EMPLEADOS INNER JOIN PAGOS ON EMPLEADOS.CODIGO = PAGOS.EmpleadoID)
INNER JOIN DETALLE_PAGOS ON PAGOS.IDPago = DETALLE_PAGOS.IDPago)
INNER JOIN CONCEPTOS ON CONCEPTOS.ConceptoID = DETALLE_PAGOS.Concepto
WHERE PAGOS.IDPago = [COD_PAGO];


I join this query with a report. When I open the report, I have to type the ID of the invoice.

enter image description here

That is OK. But I have to print this report with a Form, I have created a Button to print, and the code is

Private Sub txtPrint_Click()
If MsgBox("┬┐Deseas imprimir?", vbYesNo) = vbYes Then
//OTHER FUNCTIONS

DoCmd.OpenReport "ReportePago", acViewPreview, , "[COD_PAGO]=" & IDPago.Value

End If


End Sub

IDPago.Value -> I have a hidden TextBox that store the id of invoice.
But when I click on print button again appear the message box

enter image description here

If I insert the id of the invoice, print the report, but I don't want to insert the id to print. What can I do to solve this?

Thanks.

Answer

Remove the WHERE clause from the query, include the PAGOS.IDPago column in the resultset, then pass the condition without the table alias in your call to OpenReport:

DoCmd.OpenReport "ReportePago", acViewPreview, , "IDPago=" & IDPago.Value