To pause macro until function is finish

[mercapto]

This has to be not difficult but I couldn't found it.

I have a procedure to change a value in a textbox and print a pdf using that value. Then it restores the original value.

The problem is that the restoring is faster than the pdf formatting.

I need the restoring line to "wait" the function.



Any other ideas are welcome.

Thanks in advance.

Private Sub cmdPDFInformeNuevo_Click()

Dim intPerfil As Integer
intPerfil = Me.TipoAnalisis 'Stores original value
Me.TipoAnalisis = 78 

CrearPDF
'I tried DoEvents here and didn't work.

Me.TipoAnalisis = intPerfil 'Restores original value

End Sub

[John_G]

It's hard to tell without seeing CrearPDF, but can you modify it to accept the variable as a parameter, rather than using a form field?

So you would have CrearPDF 78

The sub itself would be

Sub CrearPDF ( MyParameter as Integer)

...
...
...

End Sub

and then in the Sub refer to MyParameter instead of Me.TipoAnalisis.

HTH

John

[mercapto]

Thanks, John.

I didn't want to change CrearPDF (actually is a sub that defines a recordset and runs CrearPDF(strSQL)).

I was wondering if there is way just to pause, because if I change that function I have to change a lot of forms...

[ipisors]

First, the last post is definitely something I'd strongly agree with and not just for this situation, but because of this situation you can see what a bad practice it is to have a bunch of different CreatePDF## functions when you should instead be doing CreatePDF(param). It's a bad practice that's not scalable and you should take the time to fix it now instead of incurring more 'technology debt'.......

But..you can do this:

Do Until Dir("file path of the created PDF file you are expecting")<>""
  'Do Nothing
Loop

[mercapto]

First, the last post is definitely something I'd strongly agree with and not just for this situation, but because of this situation you can see what a bad practice it is to have a bunch of different CreatePDF## functions when you should instead be doing CreatePDF(param). It's a bad practice that's not scalable and you should take the time to fix it now instead of incurring more 'technology debt'.......

But..you can do this:

Do Until Dir("file path of the created PDF file you are expecting")<>""
  'Do Nothing
Loop

Thanks Isaac. I placed that lines after CrearPDF and it crushes. I don't know where I could fit it.

Okay, here is my "Create PDF" function. I think I did my homework. So far, each record has it's own report, but now laws in my sector have changed and now I need to print 2 report types for some records.

Public Sub CreaInformePDF(ByVal strSQL As String, Optional ByVal blnNoPreguntar As Boolean)
'Crear el informe PDF de un recordset definido por la SQL enviada
'Necesita NumAnalisis, FechaRecogida, NumFactura, Nombre (del cliente), Origen de la Muestra, IdTipoAnalisis(Id AS...), TipoAnalisis (descripción), Administración.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCuentaRegistros As Integer
Dim strMensaje As String
Dim lngRespuesta As Long

Set db = CurrentDb()

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount < 1 Then 'Evita que se ejecute el proceso si no hay registros
    MsgBox "No hay registros seleccionados", vbExclamation
Else
    rs.MoveLast 'es necesario este paso para que cuente bien los registros
    intCuentaRegistros = rs.RecordCount
    rs.MoveFirst

strMensaje = "Atención, va a crear " & intCuentaRegistros & " informes analíticos en PDF." & vbCrLf & "¿Está seguro?"

'Este bloque permite que se ejecute el procedimiento sin pedir confirmación al usuario.
If blnNoPreguntar = True Then
    lngRespuesta = vbOK
Else
    lngRespuesta = MsgBox(strMensaje, vbExclamation + vbOKCancel + vbDefaultButton2, "Crear PDFs")
End If

Select Case lngRespuesta
    Case vbOK
        Do While Not rs.EOF

                Dim strDocName As String
                Dim strFileName As String
                Dim datfechaReco As Date
                Dim strMesReco As String
                Dim strAnioReco As String
                Dim strAdmon As String
                Dim strDirActual As String
                Dim strDirInforme As String

                'Informe que se abre
                 strDocName = NombreInformeAnalisis(rs!IdTipoAnalisis)

                'Directorios
                strDirActual = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
                If Not IsNull(rs!Admon) Then
                    strAdmon = rs!Admon
                Else
                    strAdmon = ""
                End If

                datfechaReco = rs!FechaRecogida
                strMesReco = Format(datfechaReco, "mmmm")
                strMesReco = UCase(strMesReco)
                strAnioReco = Format(datfechaReco, "YYYY")
                                    
                'Probando si el directorio existe.
                'Probando si existe el directorio "informes"
                strDirInforme = strDirActual & "DOCS LABORATORIO" & "\"
                If Len(Dir(strDirInforme, vbDirectory)) = 0 Then
                'Crear si no existe.
                    MkDir strDirInforme
                End If
                
                'Probando los siguientes.
                strDirInforme = strDirInforme & strMesReco & " " & strAnioReco & "\"
                If Len(Dir(strDirInforme, vbDirectory)) = 0 Then
                    MkDir strDirInforme
                End If
                
                If strAdmon <> "" Then 'Se crea solo si hay admon.
                    strDirInforme = strDirInforme & strAdmon & "\"
                    If Len(Dir(strDirInforme, vbDirectory)) = 0 Then
                        MkDir strDirInforme
                    End If
                End If
               
                'Nombre del archivo
                strFileName = strDirInforme & "Informe nº" & rs!NumAnalisis & " " & rs!Nombre & "-" & rs![Origen de la Muestra] & " - " & rs!TipoAnalisis & " - " & strMesReco & " " & strAnioReco & ".pdf"
                Application.Echo False
                DoCmd.OpenReport strDocName, acPreview, , "NumAnalisis = " & Nz(rs!NumAnalisis, 0)
                DoCmd.OutputTo acOutputReport, strDocName, acFormatPDF, strFileName, , , , acExportQualityPrint
                DoCmd.Close acReport, strDocName
                Application.Echo True
                'Memoriza el nombre del archivo para enviar por email
                strArchivosAdjuntos = strArchivosAdjuntos & strFileName & ";"

                rs.MoveNext
        Loop
    strArchivosAdjuntos = Left(strArchivosAdjuntos, Len(strArchivosAdjuntos) - 1)

    Case vbCancel
        MsgBox "Ha interrumpido el proceso"
    Case Else
        MsgBox lngRespuesta
End Select
End If



Set rs = Nothing
Set db = Nothing


End Sub

[mercapto]

Yo can't imagine how simple it is...

Private Sub cmdPDFInformeNuevo_Click()

Dim intPerfil As Integer
intPerfil = Me.TipoAnalisis 'Stores original value
Me.TipoAnalisis = 78 
ME.REFRESH

CrearPDF

Me.TipoAnalisis = intPerfil 'Restores original value
ME.REFRESH

End Sub

P.S.: When we were very novices "Me.Refresh" was the solution 90% of the times, wasn't it??

[ipisors]

Thanks Isaac. I placed that lines after CrearPDF and it crushes. I don't know where I could fit it

I don't understand what that means, sorry.

As for your last post, I'm not making sense out of that either. First, the form Refresh method is very, very rarely needed in Access. Requery yes, Repaint sometimes, Refresh almost never.

I must have completely misunderstood your initial problem, or maybe your problem "changed" over the course of this thread.

I thought you needed to have something WAIT until a PDF file was created (either on your computer or a network file share). The normal method for doing that would be to set up a dummy loop and check until Dir(filepath) is not "".

The fact that you somehow "solved" this problem by Refreshing a form is beyond me - but OK.

[mercapto]

I mean that I tried your code and got an error. For sure I didn't place it on the correct line.

When I started using Access, just making very simple forms and using the wizard to add buttons, sometimes I couldn't manage to print updated reports. The solution was always "me.refresh". Google said it, not me.

At last, my problem wasnt that I needed "something to wait". That was only a probable solution. My problem was that I needed to print a certain report and I finally could. Somehow "me.refresh" gives the pause that the procedure needs.

Thanks for your help.