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!