Closing Form Issue

[Paul Cooke]

Hi guys I have a select case statement to send a report via print or email from the active form "AccidentOnly". I am having problems closing the form within it and would be very grateful for some advice on how to resolve this.

I need to place these two lines somewhere after the 'sendobject' command as if they close before it the report cannot pull the information of the form

DoCmd.Close acForm, "AccidentOnly", acSaveYes
DoCmd.Close acForm, "FindPatientAccident"

code as follows

   Case 3  'Email
        
        DoCmd.Close acForm, "ReportOptionsSinglePatientAccidentOnly"
        DoCmd.OpenForm "Navigation"
        
        On Error GoTo ErrorRoutine1
         
        Const errUserCanceledAction As Long = 2501
        
            DoCmd.SendObject acSendReport, "ARFOnSetAO", acFormatPDF, , , , "Patient Accident Form" _
                , "Accident form is attached. Please treat this docuument as confidential." _
            & vbCrLf & vbCrLf & "If you require further information or advice, please do not hesitate to contact us." & vbCrLf & vbCrLf & vbCrLf & "On Set Medical Support Services" _   
            
RoutineExit1:
        
         Exit Sub
         
ErrorRoutine1:

         If Err.Number = errUserCanceledAction Then
         
         'Do nothing

         Else
         
         MsgBox "Error number: " & Err.Number & vbCrLf & vbCrLf & _
         Err.Description, vbCritical, "Error"
         
         End If
         
        Resume RoutineExit1

    End Select

I have tried placing them in various areas of the statement but they do not close, although the email bit works fine and I am tottally lossed on this now !

Perhap I shouldn't be doing this on a Sunday morning with a hangover !!

Many thanks in advice for any advice offered

 

[vbaInet]

Perhap I shouldn't be doing this on a Sunday morning with a hangover !!

Perhaps it isn't a good idea Paul

I need to place these two lines somewhere after the 'sendobject' command as if they close before it the report cannot pull the information of the form

So how does it not work? You get an error message?

 

[Paul Cooke]

Thanks for the reply - hangovers gone - thankfully !!

There are no errors at all everything works as it should apart from closing the forms that are open to take the user back to the Navigation form.

The main form "AccidentOnly" has to stay open until the report has been generated so I need the docmd.close to fire up after this - if that makes sense !

Thanks again

Paul

 

[vbaInet]

How about this:

DoCmd.SendObject acSendReport, "ARFOnSetAO", acFormatPDF, , , , "Patient Accident Form" _
    , "Accident form is attached. Please treat this docuument as confidential." _
    & vbCrLf & vbCrLf & "If you require further information or advice, please do not hesitate to contact us." & vbCrLf & vbCrLf & vbCrLf & "On Set Medical Support Services"

SelectObject ... --> select the Navigation form here
Docmd.Close ... --> close the form here

OR

Save the values from the form into some public variables, close the form before sending to e-mail.

 

[Paul Cooke]

Thanks again for the reply..
The SelectObject didn't work

How would / do I save the values into 'public' variables? say for example on value is txtFirstName ?

 

[vbaInet]

It depends on what you are actually using the variables for? Is it being used in the report's record source?

 

[Paul Cooke]

Basically the report values are all "=Forms!AccidentOnly.mycontrol... ect ect [/CODE]

 

[vbaInet]

I will give you the steps:

1. Declare some Public variables in a Module (not a form or report module, a global module)
2. Create a function in your report to retrieve the values from the variables. Here's how it could look like:

Public Function GetFormVars(strVarName As String) As Variant
    Select Case strVarName
        Case "variable1"
            GetFormVars = the variable here
        Case "variable2"
            GetFormVars = the variable here
    End Select
End Function

2. Set the controls in your report to the function, e.g.

=GetFormVars("variable1")

You could even send the name of the control to the function to determine which variable to return
3. Before sending to e-mail, save the values into the variable and close your form.

Before you implement the above, try putting DoEvents just after SendObject and before you close your form. See if that helps.

 

[Paul Cooke]

Sorry for not replying sooner

DoEvents didn't work so I went for the first option which works a treat !!

thanks so much for your help

Regards Paul

 

[vbaInet]

You're welcome!

SendObject seems to be consuming lots resources.

 

[boblarson]

I need to place these two lines somewhere after the 'sendobject' command as if they close before it the report cannot pull the information of the form

DoCmd.Close acForm, "AccidentOnly", acSaveYes
DoCmd.Close acForm, "FindPatientAccident"

Just an FYI for you around this code:
DoCmd.Close acForm, "AccidentOnly", acSaveYes

You may be misunderstanding what acSaveYes actually does. I'm sure you really meant to use acSaveNo instead (the default is acSavePrompt) as this has to do with DESIGN CHANGES to the form and has NOTHING to do with records. You would not want to let users save design changes to the form (which can include filters, which can then cause problems). Also, if used with the Access Runtime it would throw an error because you can't save design changes with the Access Runtime.

 

[Paul Cooke]

Thanks Bob,

To be honest now you mentioned that I was aware of it but copied that line 'as is' as I was doing changes to the form and kept forgetting to save it and losing some code changes (long story!) however I did not take it out of the finished bit so it's a good reminder for me and a good tip for others reading this.

Thanks again