** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Access y VBA > Access y VBA
  Mensajes nuevos Mensajes nuevos RSS - Resumir Código
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoResumir Código

 Responder Responder
Autor
Mensaje
mfafa Ver desplegable
Asiduo
Asiduo


Unido: 23/Septiembre/2009
Localización: España
Estado: Sin conexión
Puntos: 497
Enlace directo a este mensaje Tema: Resumir Código
    Enviado: 23/Mayo/2020 a las 16:49
Hola!

En el evento al activar el informe tengo el siguiente código para rellenar un calendario (42 Cuadros de Texto), funciona muy bien, pero me gustaría resumirlo con For Next, si alguien me puede echar una mano.

Dim db As Database
Dim rstRecords As Recordset
Dim strText0
Dim strText1
Dim strText2
Dim strText3
Dim strText4
Dim strText5
Dim strText6
Dim strText7
Dim strText8
Dim strText9
Dim strText10
Dim strText11
Dim strText12
Dim strText13
Dim strText14
Dim strText15
Dim strText16
Dim strText17
Dim strText18
Dim strText19
Dim strText20
Dim strText21
Dim strText22
Dim strText23
Dim strText24
Dim strText25
Dim strText26
Dim strText27
Dim strText28
Dim strText29
Dim strText30
Dim strText31
Dim strText32
Dim strText33
Dim strText34
Dim strText35
Dim strText36
Dim strText37
Dim strText38
Dim strText39
Dim strText40
Dim strText41
Dim CritM
Dim CritY
Dim Crit0D
Dim Crit1D
Dim Crit2D
Dim Crit3D
Dim Crit4D
Dim Crit5D
Dim Crit6D
Dim Crit7D
Dim Crit8D
Dim Crit9D
Dim Crit10D
Dim Crit11D
Dim Crit12D
Dim Crit13D
Dim Crit14D
Dim Crit15D
Dim Crit16D
Dim Crit17D
Dim Crit18D
Dim Crit19D
Dim Crit20D
Dim Crit21D
Dim Crit22D
Dim Crit23D
Dim Crit24D
Dim Crit25D
Dim Crit26D
Dim Crit27D
Dim Crit28D
Dim Crit29D
Dim Crit30D
Dim Crit31D
Dim Crit32D
Dim Crit33D
Dim Crit34D
Dim Crit35D
Dim Crit36D
Dim Crit37D
Dim Crit38D
Dim Crit39D
Dim Crit40D
Dim Crit41D
Set db = CurrentDb()
Set rstRecords = db.OpenRecordset("tblData", dbOpenDynaset)
Crit0D = [Forms]![frmCalander]![Command0].[Caption]
Crit1D = [Forms]![frmCalander]![Command1].[Caption]
Crit2D = [Forms]![frmCalander]![Command2].[Caption]
Crit3D = [Forms]![frmCalander]![Command3].[Caption]
Crit4D = [Forms]![frmCalander]![Command4].[Caption]
Crit5D = [Forms]![frmCalander]![Command5].[Caption]
Crit6D = [Forms]![frmCalander]![Command6].[Caption]
Crit7D = [Forms]![frmCalander]![Command7].[Caption]
Crit8D = [Forms]![frmCalander]![Command8].[Caption]
Crit9D = [Forms]![frmCalander]![Command9].[Caption]
Crit10D = [Forms]![frmCalander]![Command10].[Caption]
Crit11D = [Forms]![frmCalander]![Command11].[Caption]
Crit12D = [Forms]![frmCalander]![Command12].[Caption]
Crit13D = [Forms]![frmCalander]![Command13].[Caption]
Crit14D = [Forms]![frmCalander]![Command14].[Caption]
Crit15D = [Forms]![frmCalander]![Command15].[Caption]
Crit16D = [Forms]![frmCalander]![Command16].[Caption]
Crit17D = [Forms]![frmCalander]![Command17].[Caption]
Crit18D = [Forms]![frmCalander]![Command18].[Caption]
Crit19D = [Forms]![frmCalander]![Command19].[Caption]
Crit20D = [Forms]![frmCalander]![Command20].[Caption]
Crit21D = [Forms]![frmCalander]![Command21].[Caption]
Crit22D = [Forms]![frmCalander]![Command22].[Caption]
Crit23D = [Forms]![frmCalander]![Command23].[Caption]
Crit24D = [Forms]![frmCalander]![Command24].[Caption]
Crit25D = [Forms]![frmCalander]![Command25].[Caption]
Crit26D = [Forms]![frmCalander]![Command26].[Caption]
Crit27D = [Forms]![frmCalander]![Command27].[Caption]
Crit28D = [Forms]![frmCalander]![Command28].[Caption]
Crit29D = [Forms]![frmCalander]![Command29].[Caption]
Crit30D = [Forms]![frmCalander]![Command30].[Caption]
Crit31D = [Forms]![frmCalander]![Command31].[Caption]
Crit32D = [Forms]![frmCalander]![Command32].[Caption]
Crit33D = [Forms]![frmCalander]![Command33].[Caption]
Crit34D = [Forms]![frmCalander]![Command34].[Caption]
Crit35D = [Forms]![frmCalander]![Command35].[Caption]
Crit36D = [Forms]![frmCalander]![Command36].[Caption]
Crit37D = [Forms]![frmCalander]![Command37].[Caption]
Crit38D = [Forms]![frmCalander]![Command38].[Caption]
Crit39D = [Forms]![frmCalander]![Command39].[Caption]
Crit40D = [Forms]![frmCalander]![Command40].[Caption]
Crit41D = [Forms]![frmCalander]![Command41].[Caption]
CritM = [Forms]![frmCalander]![cboMonth]
CritY = [Forms]![frmCalander]![cboYear]

If [Forms]![frmCalander]![Command0].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit0D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText0 = IIf(IsNull(![Event]), strText0 & " " & ![Name] & Chr(13) + Chr(10), strText0 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit0D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command1].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit1D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText1 = IIf(IsNull(![Event]), strText1 & " " & ![Name] & Chr(13) + Chr(10), strText1 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit1D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command2].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit2D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText2 = IIf(IsNull(![Event]), strText2 & " " & ![Name] & Chr(13) + Chr(10), strText2 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit2D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command3].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit3D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText3 = IIf(IsNull(![Event]), strText3 & " " & ![Name] & Chr(13) + Chr(10), strText3 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit3D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command4].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit4D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText4 = IIf(IsNull(![Event]), strText4 & " " & ![Name] & Chr(13) + Chr(10), strText4 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit4D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command5].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit5D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText5 = IIf(IsNull(![Event]), strText5 & " " & ![Name] & Chr(13) + Chr(10), strText5 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit5D & "And [Month] =" & "'" & CritM & "'"
    Loop
    strText5 = vbCrLf & strText5
End With
Else
End If
If [Forms]![frmCalander]![Command6].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit6D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText6 = IIf(IsNull(![Event]), strText6 & " " & ![Name] & Chr(13) + Chr(10), strText6 & ![Name] & " (" & ![Event] & ")" & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit6D & "And [Month] =" & "'" & CritM & "'"
    Loop
    strText6 = vbCrLf & strText6
End With
Else
End If
With rstRecords
    .FindFirst "[Day] =" & Crit7D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText7 = IIf(IsNull(![Event]), strText7 & " " & ![Name] & Chr(13) + Chr(10), strText7 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit7D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit8D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText8 = IIf(IsNull(![Event]), strText8 & " " & ![Name] & Chr(13) + Chr(10), strText8 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit8D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit9D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText9 = IIf(IsNull(![Event]), strText9 & " " & ![Name] & Chr(13) + Chr(10), strText9 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit9D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit10D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText10 = IIf(IsNull(![Event]), strText10 & " " & ![Name] & Chr(13) + Chr(10), strText10 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit10D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit11D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText11 = IIf(IsNull(![Event]), strText11 & " " & ![Name] & Chr(13) + Chr(10), strText11 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit11D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit12D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText12 = IIf(IsNull(![Event]), strText12 & " " & ![Name] & Chr(13) + Chr(10), strText12 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit12D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit13D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText13 = IIf(IsNull(![Event]), strText13 & " " & ![Name] & Chr(13) + Chr(10), strText13 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit13D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit14D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText14 = IIf(IsNull(![Event]), strText14 & " " & ![Name] & Chr(13) + Chr(10), strText14 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit14D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit15D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText15 = IIf(IsNull(![Event]), strText15 & " " & ![Name] & Chr(13) + Chr(10), strText15 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit15D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit16D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText16 = IIf(IsNull(![Event]), strText16 & " " & ![Name] & Chr(13) + Chr(10), strText16 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit16D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit17D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText17 = IIf(IsNull(![Event]), strText17 & " " & ![Name] & Chr(13) + Chr(10), strText17 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit17D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit18D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText18 = IIf(IsNull(![Event]), strText18 & " " & ![Name] & Chr(13) + Chr(10), strText18 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit18D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit19D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText19 = IIf(IsNull(![Event]), strText19 & " " & ![Name] & Chr(13) + Chr(10), strText19 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit19D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit20D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText20 = IIf(IsNull(![Event]), strText20 & " " & ![Name] & Chr(13) + Chr(10), strText20 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit20D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit21D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText21 = IIf(IsNull(![Event]), strText21 & " " & ![Name] & Chr(13) + Chr(10), strText21 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit21D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit22D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText22 = IIf(IsNull(![Event]), strText22 & " " & ![Name] & Chr(13) + Chr(10), strText22 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit22D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit23D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText23 = IIf(IsNull(![Event]), strText23 & " " & ![Name] & Chr(13) + Chr(10), strText23 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit23D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit24D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText24 = IIf(IsNull(![Event]), strText24 & " " & ![Name] & Chr(13) + Chr(10), strText24 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit24D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit25D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText25 = IIf(IsNull(![Event]), strText25 & " " & ![Name] & Chr(13) + Chr(10), strText25 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit25D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit26D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText26 = IIf(IsNull(![Event]), strText26 & " " & ![Name] & Chr(13) + Chr(10), strText26 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit26D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
With rstRecords
    .FindFirst "[Day] =" & Crit27D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText27 = IIf(IsNull(![Event]), strText27 & " " & ![Name] & Chr(13) + Chr(10), strText27 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit27D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
If [Forms]![frmCalander]![Command28].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit28D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText28 = IIf(IsNull(![Event]), strText28 & " " & ![Name] & Chr(13) + Chr(10), strText28 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit28D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command29].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit29D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText29 = IIf(IsNull(![Event]), strText29 & " " & ![Name] & Chr(13) + Chr(10), strText29 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit29D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command30].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit30D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText30 = IIf(IsNull(![Event]), strText30 & " " & ![Name] & Chr(13) + Chr(10), strText30 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit30D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command31].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit31D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText31 = IIf(IsNull(![Event]), strText31 & " " & ![Name] & Chr(13) + Chr(10), strText31 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit31D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command32].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit32D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText32 = IIf(IsNull(![Event]), strText32 & " " & ![Name] & Chr(13) + Chr(10), strText32 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit32D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command33].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit33D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText33 = IIf(IsNull(![Event]), strText33 & " " & ![Name] & Chr(13) + Chr(10), strText33 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit33D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command34].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit34D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText34 = IIf(IsNull(![Event]), strText34 & " " & ![Name] & Chr(13) + Chr(10), strText34 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit34D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command35].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit35D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText35 = IIf(IsNull(![Event]), strText35 & " " & ![Name] & Chr(13) + Chr(10), strText35 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit35D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command36].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit36D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText36 = IIf(IsNull(![Event]), strText36 & " " & ![Name] & Chr(13) + Chr(10), strText36 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit36D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command37].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit37D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText37 = IIf(IsNull(![Event]), strText37 & " " & ![Name] & Chr(13) + Chr(10), strText37 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit37D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command38].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit38D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText38 = IIf(IsNull(![Event]), strText38 & " " & ![Name] & Chr(13) + Chr(10), strText38 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit38D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command39].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit39D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText39 = IIf(IsNull(![Event]), strText39 & " " & ![Name] & Chr(13) + Chr(10), strText39 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit39D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command40].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit40D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText40 = IIf(IsNull(![Event]), strText40 & " " & ![Name] & Chr(13) + Chr(10), strText40 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit40D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If
If [Forms]![frmCalander]![Command41].Visible = True Then
With rstRecords
    .FindFirst "[Day] =" & Crit41D & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText41 = IIf(IsNull(![Event]), strText41 & " " & ![Name] & Chr(13) + Chr(10), strText41 & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit41D & "And [Month] =" & "'" & CritM & "'"
    Loop
End With
Else
End If


Muchas gracias.

Un saludo.
Arriba
01loko Ver desplegable
Colaborador
Colaborador


Unido: 17/Agosto/2017
Localización: Santander
Estado: Sin conexión
Puntos: 807
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 17:26
no se si estara perfecto, pero de comienzo creo que asi puede funcionar

voy a suponer que:

CritXb es un label;strtextX es una textbox;comandX es un textbox

dim etiqueta as label; texto as textbox, comando as textbox
n=0
Set db = CurrentDb()
Set rstRecords = db.OpenRecordset("tblData", dbOpenDynaset)

set etiqueta=controls("crit" & n & "b")
set texto=controls("srttext" & n)
set comando= controls("comand" & n)
With rstRecords
If [Forms]![frmCalander]![Commando].Visible = True Then

    .FindFirst "[Day] =" & etiqueta & "And [Month] =" & "'" & CritM & "'"
    Do While Not .NoMatch
        strText0 = IIf(IsNull(![Event]), Texto & " " & ![Name] & Chr(13) + Chr(10), Texto & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & etiqueta & "And [Month] =" & "'" & CritM & "'"
n=n+1
if n>41 then goto final

    Loop
final:
End With


Editado por 01loko - 23/Mayo/2020 a las 17:31
Recordar de que soy nuevo y estoy aprendiendo.
Arriba
mfafa Ver desplegable
Asiduo
Asiduo


Unido: 23/Septiembre/2009
Localización: España
Estado: Sin conexión
Puntos: 497
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 19:58
Hola!

No soy capaz de adaptar el código, me salta error  en estas tres líneas:
set etiqueta=controls("crit" & n & "b") '(No encuentra el campo crit0 al que se hace referencia en la expresion)
set texto=controls("strtext" & n) '(No encuentra el campo StrText0 al que se hace referencia en la expresion)
set comando= controls("comand" & n) '(No encuentra el campo Command0 al que se hace referencia en la expresion)

Subo la base de datos a ver si alguien encuentra el fallo.

http://www.filebig.net/files/EnDh6B8Sy2


Muchas gracias!



Editado por mfafa - 23/Mayo/2020 a las 22:44
Arriba
xavi Ver desplegable
Administrador
Administrador
Avatar
Terrassa-BCN

Unido: 10/Mayo/2005
Localización: Catalunya ||||
Estado: Sin conexión
Puntos: 14720
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 20:09
Hola,

Yo utilizaría un array

Dim rstRecords As DAO.Recordset
Dim arrTexto(0 To 41)  as String
Dim i As Integer

Set rstRecords = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)

For i = 0 To 41
  If Forms("frmCalander").Controls("Command" & i).Visible = True Then
    With rstRecords
      .FindFirst "[Day] =" & Forms("frmCalander").Controls("Command" & i).Caption & "And [Month] =" & "'" & Forms("frmCalander").Controls("cboMonth") & "'"
      Do While Not .NoMatch
        arrTexto(i) = IIf(IsNull(![Event]), arrTexto(i) & " " & ![Name] & Chr(13) + Chr(10), arrTexto & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
        .FindNext "[Day] =" & Crit41D & "And [Month] =" & "'" & Forms("frmCalander").Controls("cboMonth") & "'"
      Loop
    End With
  Else
  End If
Next

rstRecords.Close
Set rstRecords = Nothing
Xavi, un minyó de Terrassa

Mi web
Arriba
mfafa Ver desplegable
Asiduo
Asiduo


Unido: 23/Septiembre/2009
Localización: España
Estado: Sin conexión
Puntos: 497
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 21:37
Hola!

Gracias por contestar,

Pero no me muestra nada. Sale en blanco.
Arriba
Dabellaso Ver desplegable
Asiduo
Asiduo


Unido: 18/Noviembre/2012
Localización: España
Estado: Sin conexión
Puntos: 338
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 22:42
Hola
Ocurre que xavi usa un array para almacenar todos los valores que necesitas, pero en tu código original no indicas que haces después con esos valores. 

Viendo la aplicación del link, he visto que mas adelante usas esos valores para meterlos en los textbox. Lo que debes hacer es usar el array de xavi para meter esos valores en su lugar.

No lo he probado, pero algo así:
i=0
For i = 0 To 41

        Me.Controls("[Text" & i & "]").Visible = Forms("frmCalander").Controls("Command" & i).Visible
        Me.Controls("[Text" & i & "]").Value = " " & Forms("frmCalander").Controls("Command" & i).Caption & arrTexto(i)
    

Next i



Editado por Dabellaso - 23/Mayo/2020 a las 22:43
El saber no ocupa lugar, sólo tiempo
Arriba
mfafa Ver desplegable
Asiduo
Asiduo


Unido: 23/Septiembre/2009
Localización: España
Estado: Sin conexión
Puntos: 497
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 23:15
Hola!

Gracias por contestar!

Me da error de compilación, No se ha definido la variable y me marca esta línea:-

.FindNext "[Day] =" & Crit41 & "And [Month] =" & "'" & Forms("frmCalander").Controls("cboMonth") & "'"


Editado por mfafa - 23/Mayo/2020 a las 23:16
Arriba
Dabellaso Ver desplegable
Asiduo
Asiduo


Unido: 18/Noviembre/2012
Localización: España
Estado: Sin conexión
Puntos: 338
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 23:24
 

La idea de usar un array, es la de evitar esas variables. Cambia:
  Crit41 
por :
   Forms("frmCalander").Controls("Command" & i).Caption



Editado por Dabellaso - 23/Mayo/2020 a las 23:24
El saber no ocupa lugar, sólo tiempo
Arriba
mfafa Ver desplegable
Asiduo
Asiduo


Unido: 23/Septiembre/2009
Localización: España
Estado: Sin conexión
Puntos: 497
Enlace directo a este mensaje Enviado: 23/Mayo/2020 a las 23:45
TongueTongueTongue eso era!!!!

Doy las gracias a 01loko, Xavi y Dabellaso.

Dejo el link por si interesa a alguien Big smile
 


Hasta pronto.

Se puede cerrar este hilo



Editado por mfafa - 23/Mayo/2020 a las 23:53
Arriba
Dabellaso Ver desplegable
Asiduo
Asiduo


Unido: 18/Noviembre/2012
Localización: España
Estado: Sin conexión
Puntos: 338
Enlace directo a este mensaje Enviado: 24/Mayo/2020 a las 00:13
Perdón por escribir estando el hilo resuelto, pero si a alguien le puede ayudar, expongo la solución abordando el problema desde el punto de vista de 01loko, incluyendo la parte del código omitida al inicio (la asignación de los valores a los Textbox el informe).

La intención es que puedan analizarlo y utilizarlo en casos similares

Dim db                      As Database
Dim rstRecords              As Recordset
Dim ctlFormulario           As Access.CommandButton
Dim ctlInforme              As Access.TextBox
Dim strContenido            As String
Dim i                       As Long

Dim CritM:                  CritM = [Forms]![frmCalander]![cboMonth]
Dim CritY:                  CritY = [Forms]![frmCalander]![cboYear]

Set db = CurrentDb()
Set rstRecords = db.OpenRecordset("tblData", dbOpenDynaset)


For i = 0 To 41

    Set ctlFormulario = Forms("frmCalander").Controls("Command" & i)
    Set ctlInforme = Me.Controls("[Text" & i & "]")
    
    ctlInforme.Visible = ctlFormulario.Visible
    
    If ctlFormulario.Visible = True Then
    
        With rstRecords
        
            .FindFirst "[Day] =" & ctlFormulario.Caption & "And [Month] =" & "'" & CritM & "'"
            strContenido = ""
            Do While Not .NoMatch
                strContenido = IIf(IsNull(![Event]), strContenido & " " & ![Name] & Chr(13) + Chr(10), strContenido & " (" & ![Event] & ") " & ![Name] & Chr(13) + Chr(10))
                .FindNext "[Day] =" & ctlFormulario.Caption & "And [Month] =" & "'" & CritM & "'"
            Loop
            
            ctlInforme.Value = " " & ctlFormulario.Caption & strContenido

        End With
        
    End If
    
Next i

Set ctlFormulario = Nothing
Set ctlInforme = Nothing
rstRecords.Close
Set rstRecords = Nothing
El saber no ocupa lugar, sólo tiempo
Arriba
mfafa Ver desplegable
Asiduo
Asiduo


Unido: 23/Septiembre/2009
Localización: España
Estado: Sin conexión
Puntos: 497
Enlace directo a este mensaje Enviado: 24/Mayo/2020 a las 00:20
ClapClapClap Impresionante.

Otra forma de hacer. Tengo que estudiarlo con calma Wink
Muchas gracias.
Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable