If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keeping Access Open
Hi,
I have this sub on ms outlook. I saves an attachement then opens access. Why MS Access open then it closes. Public Sub import_supplypro() On Error Resume Next 'Declare Varibles Dim myItems, myItem, myAttachments, myAttachment As Object Dim myOrt As String Dim myOlApp As New Outlook.Application Dim myOlExp As Outlook.Explorer Dim myOlSel As Outlook.Selection Dim file_name As String Responce = MsgBox("This Macro will Modify EvoERP Data." & Chr(10) & " " & Chr(10) & "Would you like to Continue? ", vbCritical + vbYesNo, " UMCORP") If Responce = vbNo Then Exit Sub End If file_name = "SUPPLYPROCR.TXT" myOrt = "M:\Reorder\toolcript\" Set myOlExp = myOlApp.ActiveExplorer Set myOlSel = myOlExp.Selection For Each myItem In myOlSel Set myAttachments = myItem.Attachments If myAttachments.Count 0 Then For i = 1 To myAttachments.Count myAttachments(i).SaveAsFile myOrt & file_name Next i myItem.Save End If Next 'Open MS Access Dim myaccess As Object Set myaccess = CreateObject("Access.Application") myaccess.Visible = True myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb") 'Free variables Set myItems = Nothing Set myItem = Nothing Set myAttachments = Nothing Set myAttachment = Nothing Set myOlApp = Nothing Set myOlExp = Nothing Set myOlSel = Nothing Set myaccess = Nothing End Sub |
#2
|
|||
|
|||
Keeping Access Open
"Bre-x" wrote in message
... Hi, I have this sub on ms outlook. I saves an attachement then opens access. Why MS Access open then it closes. Public Sub import_supplypro() On Error Resume Next 'Declare Varibles Dim myItems, myItem, myAttachments, myAttachment As Object Dim myOrt As String Dim myOlApp As New Outlook.Application Dim myOlExp As Outlook.Explorer Dim myOlSel As Outlook.Selection Dim file_name As String Responce = MsgBox("This Macro will Modify EvoERP Data." & Chr(10) & " " & Chr(10) & "Would you like to Continue? ", vbCritical + vbYesNo, " UMCORP") If Responce = vbNo Then Exit Sub End If file_name = "SUPPLYPROCR.TXT" myOrt = "M:\Reorder\toolcript\" Set myOlExp = myOlApp.ActiveExplorer Set myOlSel = myOlExp.Selection For Each myItem In myOlSel Set myAttachments = myItem.Attachments If myAttachments.Count 0 Then For i = 1 To myAttachments.Count myAttachments(i).SaveAsFile myOrt & file_name Next i myItem.Save End If Next 'Open MS Access Dim myaccess As Object Set myaccess = CreateObject("Access.Application") myaccess.Visible = True myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb") 'Free variables Set myItems = Nothing Set myItem = Nothing Set myAttachments = Nothing Set myAttachment = Nothing Set myOlApp = Nothing Set myOlExp = Nothing Set myOlSel = Nothing Set myaccess = Nothing End Sub You need to hand off the application to user control: myaccess.Visible = True myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb") myaccess.UserControl = True -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
Keeping Access Open
Thanks!!!!
"Dirk Goldgar" wrote in message ... "Bre-x" wrote in message ... Hi, I have this sub on ms outlook. I saves an attachement then opens access. Why MS Access open then it closes. Public Sub import_supplypro() On Error Resume Next 'Declare Varibles Dim myItems, myItem, myAttachments, myAttachment As Object Dim myOrt As String Dim myOlApp As New Outlook.Application Dim myOlExp As Outlook.Explorer Dim myOlSel As Outlook.Selection Dim file_name As String Responce = MsgBox("This Macro will Modify EvoERP Data." & Chr(10) & " " & Chr(10) & "Would you like to Continue? ", vbCritical + vbYesNo, " UMCORP") If Responce = vbNo Then Exit Sub End If file_name = "SUPPLYPROCR.TXT" myOrt = "M:\Reorder\toolcript\" Set myOlExp = myOlApp.ActiveExplorer Set myOlSel = myOlExp.Selection For Each myItem In myOlSel Set myAttachments = myItem.Attachments If myAttachments.Count 0 Then For i = 1 To myAttachments.Count myAttachments(i).SaveAsFile myOrt & file_name Next i myItem.Save End If Next 'Open MS Access Dim myaccess As Object Set myaccess = CreateObject("Access.Application") myaccess.Visible = True myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb") 'Free variables Set myItems = Nothing Set myItem = Nothing Set myAttachments = Nothing Set myAttachment = Nothing Set myOlApp = Nothing Set myOlExp = Nothing Set myOlSel = Nothing Set myaccess = Nothing End Sub You need to hand off the application to user control: myaccess.Visible = True myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb") myaccess.UserControl = True -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#4
|
|||
|
|||
Keeping Access Open
Does that cause VBA code to stop execution at that point until Access is
closed, then execute the next statement once it is closed? I noticed he's setting the myAccess variable to Nothing right after it's opened - didn't know if that would cause a problem or not. Dirk Goldgar wrote: Hi, I have this sub on ms outlook. [quoted text clipped - 51 lines] Set myaccess = Nothing End Sub You need to hand off the application to user control: myaccess.Visible = True myaccess.OpenCurrentDatabase ("M:\Reorder\toolcript\toolcrib.mdb") myaccess.UserControl = True -- Jim Burke Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Keeping Access Open
"JimBurke via AccessMonster.com" u49905@uwe wrote in message
news:99e881060f82a@uwe... Does that cause VBA code to stop execution at that point until Access is closed, then execute the next statement once it is closed? I noticed he's setting the myAccess variable to Nothing right after it's opened - didn't know if that would cause a problem or not. No, the VBA in the application that created the Access application object will continue after that object is set to UserControl. But setting the object variable to Nothing will no longer terminate the Access application, because it has been turned over to user control. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|