Working with Control tabs with subforms in each tab in Access (1 Viewer)

pacctono

Member
Local time
Today, 07:11
Joined
Jun 13, 2022
Messages
64
Yes, I tried like image, but still problem with the Requery.

View attachment 101454
I solved my Requery problem. Instead of changing the sql, I did just one sql changing the where condition: "periodo = [number]" joining a control table that has a 'periodo' field, update the periodo value in the control table and do the Requery. Like this:

<code>
Dim sql2 As String


sql2 = "update lo_diarios_control set periodo = " & periodo
DoCmd.RunSQL sql2

Me.co_catalogo_cta_sf_izqCtrl.Requery
Me.co_catalogo_cta_sf_derCtrl.Requery
</code>

I would still like to know how to change my query dinamically, while running the form, and do something like a Requery.
 

pacctono

Member
Local time
Today, 07:11
Joined
Jun 13, 2022
Messages
64
Well! I got a solution. I do not like it but it is a solution.

Instead of changing the query and then do a Requery. Looks like does not work because the Requery works, only, with que query that was used when opening the form. I used a control table (lo_diarios_control) with a periodo field and join it with my original query.

Now, the query looks like:

<code>
SELECT Mid(c.cuenta,1,5) AS cta_izq, c.nombre AS ncta_izq, Count(*) AS cantidad
FROM (cuentas_2020 AS c INNER JOIN diarios_det_2020 AS d ON c.cuenta = MID(d.cuenta, 1, 5) & '00') INNER JOIN lo_diarios_control l ON d.periodo = l.periodo
GROUP BY Mid(c.cuenta,1,5), c.nombre
HAVING Count(*) > 0
UNION SELECT 0 AS cta_izq, 'T O T A L - ' & d.periodo AS ncta_izq, Count(*) AS cantidad
FROM (cuentas_2020 AS c INNER JOIN diarios_det_2020 AS d ON c.cuenta = MID(d.cuenta, 1, 5) & '00') INNER JOIN lo_diarios_control l ON d.periodo = l.periodo
GROUP BY d.periodo
ORDER BY 1;
</code>

And in vba:

<code>
Dim sql2 As String

sql2 = "update lo_diarios_control set l.periodo = " & periodo
DoCmd.RunSQL sql2


Me.co_catalogo_cta_sf_izqCtrl.Requery ' Now the Requery works.
</code>

As I said I do not like the solution I would like to change the query:

<code>
selSql = Replace(izqSql, "ejercicio_actual", ejercicio)
selSql = Replace(selSql, "periodo_actual", periodo)
CurrentDb.QueryDefs(qryIzq).sql = selSql
Me.co_catalogo_cta_sf_izqCtrl.Requery ' The Requery does not work.
</code>

and do a Requery or something like that!
 

Users who are viewing this thread

Top Bottom