UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Closes At End Of Function, Access 2016    
 
   
dlafko
post May 9 2019, 07:01 AM
Post#1



Posts: 217
Joined: 7-May 14





Basically a user goes from the switchboard to a form called. frmReportsandQueries they then click on the button for BiWeeklyReport which then activates the code to create the report and email it to people.

At the end they get a message box telling them they can start using access again because the report is fairly large and takes several seconds to go thru all the procedures. I was wondering how to add code to that so that when they click OK on the message box the frmReportsandQueries from closes and they are back to the switchboard?

Thanks
Go to the top of the page
 
DanielPineault
post May 9 2019, 07:08 AM
Post#2


UtterAccess VIP
Posts: 6,764
Joined: 30-June 11



can you post your code?

What about
CODE
If vbOk = MsgBox(YourMsgBoxCode) Then
    Me.Close
End if


Then again, if you are using a OkOnly, you don't even need the If Statement and can simply do
CODE
MsgBox ...
Me.Close

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
BruceM
post May 9 2019, 07:22 AM
Post#3


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


Doesn't Me.Close close the application? To close the current form I always use:

DoCmd.Close acForm, Me.Name

To close another form (a parameter form for a report, for instance), I use "NameOfTheParameterForm" instead of Me.Name
Go to the top of the page
 
DanielPineault
post May 9 2019, 08:03 AM
Post#4


UtterAccess VIP
Posts: 6,764
Joined: 30-June 11



Me refers to the current object, so if called within a form, then it closes that form.

I actually typically use a more explicit version like you (but that's because I'm paranoid!)
CODE
DoCmd.Close acForm, Me.Name

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dlafko
post May 9 2019, 08:09 AM
Post#5



Posts: 217
Joined: 7-May 14



here is the code in its entirety.
I was also trying to put in a yes/no so that if someone hit the run button but then for some reason changed there mind they could cancel it but that did not work because IDK what to put in the Else field.

CODE
Private Sub BiWeeklyReport_Click()
On Error GoTo Whoops

'declare variables
  Dim ol As Outlook.Application, msg As MailItem, atts As Attachments, strFilePath As String
  Set ol = CreateObject("Outlook.Application")
   'MsgBox = "This will take a few minutes," & vbCrLf & "Please be patient" & vbCrLf & "You will get a message when you may resume using Access"
    If MsgBox("Type your message here", vbYesNo + vbQuestion) = vbYes Then

'VBA Command to be executed if Yes is selected

Else

DoCmd.OpenForm (Switchboard,[acNormal])



End If

          
  'prepare the attachment
  strFilePath = "W:\Bi-Weekly Reports\Bi-Weekly Report " & Format(Date - 14, "mm-dd-yyyy") & " to " & Format(Date, "mm-dd-yyyy") & ".pdf" ' edit this - it's where you will store the report
                         ' and then retrieve it to attach it to the email.
  
  DoCmd.OutputTo acOutputReport, "rpt2Weeks", acFormatPDF, strFilePath, False
  DoEvents
  
  'prepare the message
  Set msg = ol.CreateItem(olMailItem)
  Set atts = msg.Attachments
  With msg
    .Subject = "Bi-Weekly Report" 'edit this
    .Body = "Please find the attached Bi-Weekly Report for " & Format(Date - 14, "mm-dd-yyyy") & " to " & Format(Date, "mm-dd-yyyy") & vbCrLf & "which is located at W:\Bi-Weekly Reports" 'edit this"
    .To = SimpleCSV("SELECT EmailAddress FROM tblEmail", ";")  'edit this
    .CC = "" 'edit this
    atts.Add strFilePath
    '.save 'will put the email in your drafts folder
    '.Display 'will display the email for editing/saving/scrapping
    .Send 'will send it

        
  End With
  MsgBox "The Report has been sent you may continue working in access"
  
    'clean up
Offramp:
  If Not msg Is Nothing Then Set msg = Nothing
  If Not ol Is Nothing Then Set ol = Nothing
  Exit Sub
  
  Error handler
Whoops:
  Select Case Err
    Case Else
      MsgBox "Error #" & Err & ": " & Err.Description
      Resume Offramp
  End Select

End Sub

This post has been edited by dlafko: May 9 2019, 08:11 AM
Go to the top of the page
 
DanielPineault
post May 9 2019, 08:14 AM
Post#6


UtterAccess VIP
Posts: 6,764
Joined: 30-June 11



Not sure if I understand properly, but is this what you're after
CODE
    If MsgBox("Type your message here", vbYesNo + vbQuestion) = vbYes Then
        DoCmd.Close acForm, "frmReportsandQueries", acSaveNo
        DoCmd.OpenForm "Switchboard", acNormal
        GoTo Offramp
    End If


Also, if you're using Late Binding (ie CreateObject) then you should be declaring your variables as Objects. For instance
CODE
    Dim ol                    As Object    'Outlook.Application
    Dim msg                   As Object    'Outlook.MailItem



--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dlafko
post May 9 2019, 08:27 AM
Post#7



Posts: 217
Joined: 7-May 14



Sort of I think, still trying to figure out VBA my brain seems to have a block with it.

IF the person hits yes, then it should continue down and do the record save and send the email if no then it should close and go to the switch board.

The other question is exactly where to put it.

Ok on the other part. This was code from another person will it affect things If I make those changes?
Go to the top of the page
 
DanielPineault
post May 9 2019, 08:37 AM
Post#8


UtterAccess VIP
Posts: 6,764
Joined: 30-June 11



Your original question was it they click Ok/Yes, but now you say when they click No. If it is the latter then switch the code to

CODE
    If MsgBox("Type your message here", vbYesNo + vbQuestion) = vbNo Then
        DoCmd.Close acForm, "frmReportsandQueries", acSaveNo
        DoCmd.OpenForm "Switchboard", acNormal
        GoTo Offramp
    End If


Leave the code at the top of the procedure. It will run this check and exit if they click No, otherwise will continue with the rest of your code.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dlafko
post May 9 2019, 09:00 AM
Post#9



Posts: 217
Joined: 7-May 14



Perfect thanks for your help and your patients
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 07:47 PM