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

[pacctono]

I am trying to make a form using control tabs. I thought it was ideal for my requirements.

I need a form that using the same query gives me 13 prints, one for each month (even month 0).
I am using "on change" event. I change to any tab and gives me the same result, corresponding to the last time I used the form. If I close and open the form again, it gives me the last result that it should have given me when it was opened and this situation, repeats again every time I open and close the form.

Does any body hace a working example with Control tabs using the same query, with different result, for more than one tab???

 

[June7]

What do you mean by "control tabs" - a Tab Control or Navigation Form? What code?

 

[pacctono]

What do you mean by "control tabs" - a Tab Control or Navigation Form? What code?

Sorry! Tab Control

 

[June7]

You need to provide code for analysis. Or if you want to provide database, follow instructions at bottom of my post.

 

[theDBguy]

Hi @pacctono. Are you talking about using a subform in each tab?

 

[MajP]

There are probably many ways to do this. Here is one.

 

[pacctono]

I would like to have 3 subforms (left, right and bottom on each tab. It is the same subformswith the same query, for each tab. The only thing that change is the condition in the query. Now, I am testing with the left subform only. Here is my code:

NOTE: I could not work with 'on click' event, so I am working with the ón change' event. 'TabMeses' is the Tab Control.

Option Compare Database
Dim ejercicio As Integer, periodo As Integer, pagina As Integer
Dim selSql As String
Const qryIzq As String = "co_catalogo_cta_sf_izq"
Const izqSql As String = "SELECT Mid(c.cuenta,1,5) AS cta_izq, c.nombre AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "GROUP BY Mid(c.cuenta,1,5), c.nombre" & vbCrLf _
& "HAVING Count(*) > 0" & vbCrLf _
& "UNION" & vbCrLf _
& "SELECT '' AS cta_izq, 'T O T A L' AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "ORDER BY 1;"

Private Sub enero_Click()

' Call consulta_izquierda(1)
'
' Me.co_catalogo_cta_sf_izq.Requery
End Sub

Private Sub febrero_Click()

' Call consulta_izquierda(2)
'
' Me.co_catalogo_cta_sf_izq.Requery
End Sub

Private Sub Form_Load()
Dim selSql As String

Call CS_Scale(Me)
DoCmd.Maximize

DoCmd.SetWarnings False

ejercicio = DLookup("ejercicio", co_control)
Me.cboAno = ejercicio
periodo = DLookup("periodo", co_control)
Me.cboAno.enabled = False ' Comentar al trabajar con varios años.

TabMeses.Pages(periodo).SetFocus

End Sub

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings True
End Sub

Sub consulta_izquierda(periodo_sel As Integer)
periodo = periodo_sel
selSql = Replace(izqSql, "ejercicio_actual", ejercicio) ' Here I want to use the table of the year (ejercicio).
selSql = Replace(selSql, "periodo_actual", periodo) ' Changing the right month (periodo).
CurrentDb.QueryDefs(qryIzq).sql = selSql
End Sub

Private Sub TabMeses_Change()
pagina = TabMeses.Value
Call consulta_izquierda(pagina)
TabMeses.co_catalogo_cta_sf_izq.Requery ' I think this is not working I tried: TabMeses.Pages(pagina).co_catalogo_cta_izq.Requery but gave me an error.
' DoCmd.Requery (TabMeses.Pages(pagina).Name)
End Sub

 

[pacctono]

Hi @pacctono. Are you talking about using a subform in each tab?

Yes!

I would like to have 3 subforms (left, right and bottom on each tab. It is the same subforms with the same query, for each tab. The only thing that change is the condition in the query. Now, I am testing with the left subform only.

 

[Uncle Gizmo]

This blog may provide some inspiration:-

Split a Large MS Access Datasheet in to 3 Smaller Datasheets - Nifty Access

Split a Large MS Access Datasheet in to 3 Smaller Datasheets You've got a large "VERTICAL" List of Data in datasheet form, you might prefer to have it spread out across the screen instead of constantly having to page up and down the list. In this set of videos I demonstrate two ways of implementing

www.niftyaccess.com

 

[Gasman]

I would like to have 3 subforms (left, right and bottom on each tab. It is the same subformswith the same query, for each tab. The only thing that change is the condition in the query. Now, I am testing with the left subform only. Here is my code:

NOTE: I could not work with 'on click' event, so I am working with the ón change' event. 'TabMeses' is the Tab Control.

Option Compare Database
Dim ejercicio As Integer, periodo As Integer, pagina As Integer
Dim selSql As String
Const qryIzq As String = "co_catalogo_cta_sf_izq"
Const izqSql As String = "SELECT Mid(c.cuenta,1,5) AS cta_izq, c.nombre AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "GROUP BY Mid(c.cuenta,1,5), c.nombre" & vbCrLf _
& "HAVING Count(*) > 0" & vbCrLf _
& "UNION" & vbCrLf _
& "SELECT '' AS cta_izq, 'T O T A L' AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "ORDER BY 1;"

Private Sub enero_Click()

' Call consulta_izquierda(1)
'
' Me.co_catalogo_cta_sf_izq.Requery
End Sub

Private Sub febrero_Click()

' Call consulta_izquierda(2)
'
' Me.co_catalogo_cta_sf_izq.Requery
End Sub

Private Sub Form_Load()
Dim selSql As String

Call CS_Scale(Me)
DoCmd.Maximize

DoCmd.SetWarnings False

ejercicio = DLookup("ejercicio", co_control)
Me.cboAno = ejercicio
periodo = DLookup("periodo", co_control)
Me.cboAno.enabled = False ' Comentar al trabajar con varios años.

TabMeses.Pages(periodo).SetFocus

End Sub

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings True
End Sub

Sub consulta_izquierda(periodo_sel As Integer)
periodo = periodo_sel
selSql = Replace(izqSql, "ejercicio_actual", ejercicio) ' Here I want to use the table of the year (ejercicio).
selSql = Replace(selSql, "periodo_actual", periodo) ' Changing the right month (periodo).
CurrentDb.QueryDefs(qryIzq).sql = selSql
End Sub

Private Sub TabMeses_Change()
pagina = TabMeses.Value
Call consulta_izquierda(pagina)
TabMeses.co_catalogo_cta_sf_izq.Requery ' I think this is not working I tried: TabMeses.Pages(pagina).co_catalogo_cta_izq.Requery but gave me an error.
' DoCmd.Requery (TabMeses.Pages(pagina).Name)
End Sub

Any code more than a few lines, please put within code tags.

 

[MajP]

Can you post a copy of the database or at least a screen shot?

I would like to have 3 subforms (left, right and bottom on each tab. It is the same subforms with the same query, for each tab.

If on every tab you have the same three subforms, you do this by making only a single set of three subforms. Instead of embedding the subform into each page, you "float" the subforms in front of the tab control. You can do this be pasting the subforms on to the form and then dragging over the tab control. This gives the effect that the subforms are on each page, but actually they are in front of the tab control. So you only need 3 subforms not 13 X 3.

I could not work with 'on click' event, so I am working with the ón change' event. 'TabMeses' is the Tab Control.

You do not do this on a page click event. You do this on a tab control Change event. The value of the tab control is the index of the selected page. See my demo.

 

[pacctono]

Sorry!

I do not know how you get the same control (subFrmOrdersCtl) and the form on each tab.

 

[MajP]

You can select the entire tab control or an individual page depending on where you click. In the first case I selected page January

In this case I selected the whole tab

If you paste on the whole tab and not a page the subform appears as if it is on all pages. To demonstrate I slid the subform over to show that it is really just in front of the tab control

 

[MajP]

In truth you do not need the pages. You can shrink down the tabs to just tabs and no pages. You are really only using the tab to control the subform.

 

[pacctono]

That's true. I'll try that.

Thanks



In truth you do not need the pages. You can shrink down the tabs to just tabs and no pages. You are really only using the tab to control the subform.

View attachment 101452

 

[pacctono]

Excuse me! Instead of using filter. I would like to use Requery.

I have this code:

<code>
Option Compare Database
Dim ejercicio As Integer, periodo As Integer, pagina As Integer
Dim selSql As String
Const qryIzq As String = "co_qry_catalogo_cta_sf_izq"
Const izqSql As String = "SELECT Mid(c.cuenta,1,5) AS cta_izq, c.nombre AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "GROUP BY Mid(c.cuenta,1,5), c.nombre" & vbCrLf _
& "HAVING Count(*) > 0" & vbCrLf _
& "UNION" & vbCrLf _
& "SELECT '' AS cta_izq, 'T O T A L - ' & periodo_actual AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "ORDER BY 1;"

Private Sub Form_Load()
Dim selSql As String

Call CS_Scale(Me)
DoCmd.Maximize

DoCmd.SetWarnings False

ejercicio = DLookup("ejercicio", co_control)
Me.cboAno = ejercicio
periodo = DLookup("periodo", co_control)
Me.cboAno.enabled = False ' Comentar al trabajar con varios años.

TabMeses.Pages(periodo).SetFocus

End Sub

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings True
End Sub

Private Sub consulta_izquierda()
selSql = Replace(izqSql, "ejercicio_actual", ejercicio)
selSql = Replace(selSql, "periodo_actual", periodo)
CurrentDb.QueryDefs(qryIzq).sql = selSql
Me.co_catalogo_cta_sf_izqCtrl.Form.Requery ' Looks like it is not doing the requery. It chages de query, but keeps giving me the same result, no matter what tab I click.
End Sub

Private Sub TabMeses_Change()
pagina = TabMeses.Value
periodo = pagina
consulta_izquierda
End Sub
</code>

In truth you do not need the pages. You can shrink down the tabs to just tabs and no pages. You are really only using the tab to control the subform.

 

[MajP]

Also for what you are doing (controlling the same subform), you could use an option group.

I do not think you are using the code tags correctly

Option Compare Database

Dim ejercicio As Integer, periodo As Integer, pagina As Integer
Dim selSql As String
Const qryIzq As String = "co_qry_catalogo_cta_sf_izq"
Const izqSql As String = "SELECT Mid(c.cuenta,1,5) AS cta_izq, c.nombre AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
 & "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
 & "WHERE d.periodo = periodo_actual" & vbCrLf _
 & "GROUP BY Mid(c.cuenta,1,5), c.nombre" & vbCrLf _
 & "HAVING Count(*) > 0" & vbCrLf _
 & "UNION" & vbCrLf _
 & "SELECT '' AS cta_izq, 'T O T A L - ' & periodo_actual AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
 & "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
 & "WHERE d.periodo = periodo_actual" & vbCrLf _
 & "ORDER BY 1;

 

[MajP]

Need to learn how to debug your own code.

Private Sub consulta_izquierda()
  selSql = Replace(izqSql, "ejercicio_actual", ejercicio)
  selSql = Replace(selSql, "periodo_actual", periodo)
  CurrentDb.QueryDefs(qryIzq).sql = selSql
  Debug.print currentDb.querydefs(qryIzq).sql
  'Post the SQL here and verify it is correct
  Me.co_catalogo_cta_sf_izqCtrl.Form.Requery ' Looks like it is not doing the requery. It chages de query, but keeps giving me the same result, no matter what tab I click.
End Sub

 

[pacctono]

Need to learn how to debug your own code.

Private Sub consulta_izquierda()
  selSql = Replace(izqSql, "ejercicio_actual", ejercicio)
  selSql = Replace(selSql, "periodo_actual", periodo)
  CurrentDb.QueryDefs(qryIzq).sql = selSql
  Debug.print currentDb.querydefs(qryIzq).sql
  'Post the SQL here and verify it is correct
  Me.co_catalogo_cta_sf_izqCtrl.Form.Requery ' Looks like it is not doing the requery. It chages de query, but keeps giving me the same result, no matter what tab I click.
End Sub

I am using break point and checking the sql and it is correct. Baside that if I close the form and start it again, shows me the last query (qryIzq) that was create. I think I am missing a property that permits me to "Requery".

 

[pacctono]

Yes, I tried like image, but still problem with the Requery.

View attachment 101453 Also for what you are doing (controlling the same subform), you could use an option group.

I do not think you are using the code tags correctly

Option Compare Database

Dim ejercicio As Integer, periodo As Integer, pagina As Integer
Dim selSql As String
Const qryIzq As String = "co_qry_catalogo_cta_sf_izq"
Const izqSql As String = "SELECT Mid(c.cuenta,1,5) AS cta_izq, c.nombre AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "GROUP BY Mid(c.cuenta,1,5), c.nombre" & vbCrLf _
& "HAVING Count(*) > 0" & vbCrLf _
& "UNION" & vbCrLf _
& "SELECT '' AS cta_izq, 'T O T A L - ' & periodo_actual AS ncta_izq, Count(*) AS cantidad" & vbCrLf _
& "FROM cuentas_ejercicio_actual AS c INNER JOIN diarios_det_ejercicio_actual AS d ON c.cuenta=MID(d.cuenta,1,5) & '00'" & vbCrLf _
& "WHERE d.periodo = periodo_actual" & vbCrLf _
& "ORDER BY 1;