Solved Failed to set the recordsource of a subreport (1 Viewer)

zelarra821

Registered User.
Local time
Today, 06:03
Joined
Jan 14, 2019
Messages
803
Good Morning

I have a report with a subreport, and I want, depending on whether it is a budget or an invoice, that I change the recordsource of both the parent report and the subreport. I did this with the parent report, but the subreport gives me the error: "Unable to set record source property in print preview mode, even after printing has started."

This is the code that I have put in the subreport:

Code:
Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SELECT TFacturasSubtabla.CodigoFactura, TFacturasSubtabla.ID, TFacturasSubtabla.Caracteristicas, TFacturasSubtabla.Imagen" _
        & " FROM TFacturasSubtabla WHERE " & Me.Parent.Filter
    Me.GroupLevel(0).ControlSource = "CodigoFactura"
End Sub

I honestly don't know what's wrong, because I have it in another database like that, and it works correctly.

Do you know what may be happening and how to fix it?

Thank you very much.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 05:03
Joined
Sep 21, 2011
Messages
14,048
Well subforms load before the mainform I believe?

Is it the same for Reports?
 

zelarra821

Registered User.
Local time
Today, 06:03
Joined
Jan 14, 2019
Messages
803
Hi, Gasman. How can I know that?

I explain the process:

I have a button to print the invoice (factura) in a form, with that code:

Code:
DoCmd.OpenReport "IPresupuesto", acPreview, , "[CodigoFactura]='" & Me.TxtCodigoFactura & "'", , Me.TxtCodigoFactura & "#" & "Factura"

In the main report, when you open it, I have the following code:

Code:
Private Sub Report_Open(Cancel As Integer)
    Me.PageHeader = 1
    If Split(Me.OpenArgs, "#")(1) = "Factura" Then
        Me.Caption = "Factura " & Split(Me.OpenArgs, "#")(0)
        Me.RecordSource = "SELECT TFacturas.CodigoCliente, TClientes.Nombre AS Cliente, TClientes.Domicilio, TClientes.CodigoPostal, TClientes.Ciudad, TClientes.Provincia, TClientes.Movil, TClientes.NIF, TFacturas.FechaSolicitud, TFacturasSubtabla.CodigoFactura, TFacturasSubtabla.Caracteristicas, TFacturasSubtabla.Precio, [Precio]*[Cantidad] AS BaseImponible, TIVA.PorcentajeIVA, ([Precio]*[Cantidad])*[PorcentajeIVA] AS CuotaIVA, ([Precio]*[Cantidad])+(([Precio]*[Cantidad])*[PorcentajeIVA]) AS Importe, TFacturasSubtabla.Cantidad, TFacturas.FechaEntrega, TComerciales.Nombre AS Comercial, TFacturasSubtabla.ID, TFacturas.Obra, TFacturasSubtabla.Posicion" _
            & " FROM TIVA INNER JOIN ((TComerciales INNER JOIN (TClientes INNER JOIN TFacturas ON TClientes.CodigoCliente = TFacturas.CodigoCliente) ON TComerciales.CodigoComercial = TFacturas.CodigoComercial) INNER JOIN TFacturasSubtabla ON TFacturas.CodigoFactura = TFacturasSubtabla.CodigoFactura) ON TIVA.CodigoIVA = TFacturasSubtabla.CodigoIVA"
        Me.LblPresupuesto.Caption = "Factura"
        Me.CodigoPresupuesto1.ControlSource = "CodigoFactura"
        Me.LineasDeCortesia.Visible = False
        Me.LblFormaDePago.Visible = False
        Me.NombreFormaDePago.ControlSource = "CodigoFactura"
        Me.NombreFormaDePago.Visible = False
        Me.LblCuentaBancaria.Visible = False
        Me.CuentaBancaria.Visible = False
        Me.Validez.Visible = False
        Me.PresupuestoSubinforme.LinkMasterFields = "ID; CodigoFactura"
        Me.PresupuestoSubinforme.LinkChildFields = "ID; CodigoFactura"
        Me.GroupLevel(0).ControlSource = "CodigoFactura"
    End If
    If Split(Me.OpenArgs, "#")(1) = "Presupuesto" Then
        Me.Caption = "Presupuesto " & Split(Me.OpenArgs, "#")(0)
        Me.RecordSource = "SELECT TPresupuestos.CodigoCliente, TClientes.Nombre AS Cliente, TClientes.Domicilio, TClientes.CodigoPostal, TClientes.Ciudad, TClientes.Provincia, TClientes.Movil, TClientes.NIF, TPresupuestos.FechaSolicitud, TPresupuestosSubtabla.CodigoPresupuesto, TPresupuestosSubtabla.Caracteristicas, TPresupuestosSubtabla.Precio, [Precio]*[Cantidad] AS BaseImponible, TIVA.PorcentajeIVA, ([Precio]*[Cantidad])*[PorcentajeIVA] AS CuotaIVA, ([Precio]*[Cantidad])+(([Precio]*[Cantidad])*[PorcentajeIVA]) AS Importe, TPresupuestosSubtabla.Cantidad, TPresupuestos.FechaEntrega, TComerciales.Nombre AS Comercial, TPresupuestosSubtabla.ID, TFormasDePago.NombreFormaDePago, TPresupuestos.Obra, TPresupuestosSubtabla.Posicion" _
            & " FROM (TFormasDePago INNER JOIN (TComerciales INNER JOIN (TClientes INNER JOIN TPresupuestos ON TClientes.CodigoCliente = TPresupuestos.CodigoCliente) ON TComerciales.CodigoComercial = TPresupuestos.CodigoComercial) ON TFormasDePago.CodigoFormaDePago = TPresupuestos.CodigoFormaDePago) INNER JOIN (TIVA INNER JOIN TPresupuestosSubtabla ON TIVA.CodigoIVA = TPresupuestosSubtabla.CodigoIVA) ON TPresupuestos.CodigoPresupuesto = TPresupuestosSubtabla.CodigoPresupuesto"
        Me.LblPresupuesto.Caption = "Presupuesto"
        Me.CodigoPresupuesto1.ControlSource = "CodigoPresupuesto"
        Me.LineasDeCortesia.Visible = True
        Me.LblFormaDePago.Visible = True
        Me.NombreFormaDePago.ControlSource = "NombreFormaDePago"
        Me.NombreFormaDePago.Visible = True
        Me.LblCuentaBancaria.Visible = True
        Me.CuentaBancaria.Visible = True
        Me.Validez.Visible = True
        Me.PresupuestoSubinforme.LinkMasterFields = "ID; CodigoPresupuesto"
        Me.PresupuestoSubinforme.LinkChildFields = "ID; CodigoPresupuesto"
        Me.GroupLevel(0).ControlSource = "CodigoPresupuesto"
    End If
End Sub

As I go through arguments if it is an invoice (factura) or a budget (presupuesto), I have a conditional for each case, where I change the recordsource to the main report and hide or change the control origin of some fields.

Then in the subreport I have the following code:

Code:
Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SELECT TFacturasSubtabla.CodigoFactura, TFacturasSubtabla.ID, TFacturasSubtabla.Caracteristicas, TFacturasSubtabla.Imagen" _
        & " FROM TFacturasSubtabla WHERE " & Me.Parent.Filter
    Me.GroupLevel(0).ControlSource = "CodigoFactura"
End Sub

I was thinking of passing you the database, but it's in Spanish, and I can't explain how the form to access the report works. Come on, if I have to, I would, I record a video or whatever it takes.

Thank you very much.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 05:03
Joined
Sep 21, 2011
Messages
14,048
Please do not throw a load of code at me, especially when it is not even between code tags. :(

I would put a breakpoint on that line in the report, then try and print, then inspect the Parent variable.? Basic debugging technique for me.?

I could be completely wrong?. I am just suggesting one thing to check.?

I am puzzled though, as you say this technique works on another report. However when others have said pretty much the same thing, one finds that there was a subtle difference?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:03
Joined
May 7, 2009
Messages
19,169
why not create Two separate report, one for budget and one for invoice.
so you don't need to add code.
 

Minty

AWF VIP
Local time
Today, 05:03
Joined
Jul 26, 2013
Messages
10,355
I'm with @Gasman, sub-reports and sub-forms load before their main forms.

So referring to Me.Parent.Filter is unlikely to work. You could try moving your code to the On_Format or load event.
 

zelarra821

Registered User.
Local time
Today, 06:03
Joined
Jan 14, 2019
Messages
803
Sorry. I have already put the code well so that you can see it better.

I have already solved.

@arnelgp: Yes, I had thought about it, but since the design of the invoice and the budget is the same, so as not to have two separate reports, and that if I make any changes, it applies to both.

@Minty and @Gasman: The problem was that I was loading that subreport twice. Let's see, I have that subreport in the Detail section, and it loads it as many times as there are records. Let's see if I can explain, such as an invoice or a quote, with that subreport, what I'm showing are the concepts. Therefore, if there are more than two concepts, open the subreport as many times as there are concepts. You understand me? So what I have done has been putting a conditional like this:

Code:
Private Sub Report_Open(Cancel As Integer)
Dim Sql As String
  
    Select Case Split(Me.Parent.OpenArgs, "#")(1)
        Case "Factura"
            Sql = "SELECT TFacturasSubtabla.CodigoFactura, TFacturasSubtabla.ID, TFacturasSubtabla.Caracteristicas, TFacturasSubtabla.Imagen" _
                & " FROM TFacturasSubtabla WHERE " & Me.Parent.Filter
            If Not Me.RecordSource = Sql Then
                Me.RecordSource = Sql
                Me.GroupLevel(0).ControlSource = "CodigoFactura"
            Else
                Exit Sub
            End If
        Case "Presupuesto"
            Sql = "SELECT TPresupuestosSubtabla.CodigoPresupuesto, TPresupuestosSubtabla.ID, TPresupuestosSubtabla.Caracteristicas, TPresupuestosSubtabla.Imagen" _
                & " FROM TPresupuestosSubtabla WHERE " & Me.Parent.Filter
            If Not Me.RecordSource = Sql Then
                Me.RecordSource = Sql
                Me.GroupLevel(0).ControlSource = "CodigoPresupuesto"
            Else
                Exit Sub
            End If
    End Select
End Sub

Thank you so much.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:03
Joined
Sep 21, 2011
Messages
14,048
That link that I posted mentioned loading sub report more than once and a workaround.
Loading that sub report more than once, was probably that subtle difference I mentioned. :)
 
Last edited:

Users who are viewing this thread

Top Bottom