I need my form to open report after excel import complete (1 Viewer)

gojets1721

Registered User.
Local time
Today, 09:47
Joined
Jun 11, 2019
Messages
429
Hi I've got the below code which is a form that imports in an excel file and then exports some files to excel based on that import. It also feeds into a report in access. I want to add in code that basically says "Import Complete! Would you like to open up the summary report? Yes/No" and then if no is selected, it closes the form. And if yes is selected, it opens the report within Access.

I have next to no idea how to write that. I inherited this database and while I understand all it's current workings, I don't know how to write new code. I was hoping someone might have a suggestion. Cheers

Code:
Public Sub bttnProcessIt_Click()

Dim wdShell As Object

' On Error GoTo ImportIt_Err

    MsgBox "Remember to export the report as a .CSV file and then convert to a .XLS file in Excel", vbOKOnly
    

    ' Prompt user for file path for the Raw Data spreadsheet
    Application.FileDialog(msoFileDialogOpen).Title = "Please select the PHI file for processing"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "F:\Complaints"
    Application.FileDialog(msoFileDialogOpen).Filters.Add "Excel Spreadsheets", "*.xlsx", 1
    Application.FileDialog(msoFileDialogOpen).FilterIndex = 1
    Application.FileDialog(msoFileDialogOpen).Show
    strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
            
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "qryClear_RawAudit"
    DoCmd.RunSavedImportExport "Import-Data"
    DoCmd.RunSavedImportExport "Export-Last Name Match Report"
    DoCmd.RunSavedImportExport "Export-First and Last Name Match Report"
    DoCmd.RunSavedImportExport "Export-Address Match Report"
    DoCmd.RunSavedImportExport "Export-Phone Number Match Report"
    DoCmd.RunSavedImportExport "Export-High Profile Staff Match Report"

    StrResponse = MsgBox("Process Complete!")

ImportIt_Exit:
    Exit Sub

ImportIt_Err:
    MsgBox Error$
    Resume ImportIt_Exit

End Sub
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
Code:
if msgbox("Question",vbyesno,"  ")=vbyes then
   'do something
end if
 

gojets1721

Registered User.
Local time
Today, 09:47
Joined
Jun 11, 2019
Messages
429
What would I put in the 'do something line? The report name?
 

gojets1721

Registered User.
Local time
Today, 09:47
Joined
Jun 11, 2019
Messages
429
okay...how does this look?
Code:
if msgbox("Question",vbyesno,"  ")=vbyes then
   DoCmd.OpenReport "Summary Report", acViewNormal
end if
 

isladogs

MVP / VIP
Local time
Today, 16:47
Joined
Jan 14, 2017
Messages
18,186
Code:
If MsgBox ("Your question", vbQuestion+vbYesNo, "Open Report?")=vbYes Then
   DoCmd.OpenReport "YourReportName", acViewPreview
Else
   DoCmd.Close acForm, Me.Name
End If
 

gojets1721

Registered User.
Local time
Today, 09:47
Joined
Jun 11, 2019
Messages
429
Code:
If MsgBox ("Your question", vbQuestion+vbYesNo, "Open Report?")=vbYes Then
   DoCmd.OpenReport "YourReportName", acViewPreview
Else
   DoCmd.Close acForm, Me.Name
End If
Sweet! That worked. Any idea how to get the form to close no matter which choice is selected? How's this look:

Code:
If MsgBox ("Your question", vbQuestion+vbYesNo, "Open Report?")=vbYes Then
   DoCmd.Close acForm, Me.Name
   DoCmd.OpenReport "YourReportName", acViewPreview
Else
   DoCmd.Close acForm, Me.Name
End If
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
okay...how does this look?
Code:
if msgbox("Question",vbyesno,"  ")=vbyes then
   DoCmd.OpenReport "Summary Report", acViewNormal
end if
It looks pretty darn good, as does your later code. Good job!

Give the command to close the form AFTER the End If block has completed.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
If you always want the form to close, move the close action out of the If statement.

PS, if you use object names with embedded spaces or special characters (neither is recommended), you must surround the offending names with square brackets.

[Summary Report]
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
PS, if you use object names with embedded spaces or special characters (neither is recommended), you must surround the offending names with square brackets.
In this case you actually don't need to.

(But don't worry! - I'm NOT disagreeing with your statement on 'neither is recommended'). Just that it will work without brackets
 

gojets1721

Registered User.
Local time
Today, 09:47
Joined
Jun 11, 2019
Messages
429
Give the command to close the form AFTER the End If block has completed.
That worked!! Thanks everyone. Learning everyday; I appreciate it
 

isladogs

MVP / VIP
Local time
Today, 16:47
Joined
Jan 14, 2017
Messages
18,186
Nothing new there.
Its always been like that in code to open forms/reports because the name is in quotes so cannot be misinterpreted due to a space
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
I'm not going to argue with either of you but I'm pretty sure that there are places where the names in quotes still require the square brackets. Given that I don't ever create names like that, I don't have an example and I'm not going to try to create one so I defer to your opinion.
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
Definitely there are, you may be thinking of for example the criteria portion of a dlookup (where what is required is analogous to the literal Where clause of a query), I believe brackets are required to refer to a field name. Or if you are doing a dcount, and the 'domain' is an object with a space, then i believe brackets are required.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
Thank you. I'm feeling sane again. As I said, I don't create objects with names that VBA doesn't like so I never have the problem with my own apps. Just other peoples.

My list of places where Access is inconsistent grows.
1. Union queries don't show the "text" for table level lookups. They show the ID value.
2. Cross tab queries require that arguments be declared. Other query types do not.
3. Some parts of Access require square brackets for offending names even when they are enclosed in quotes, others do not.
4. Some parts of Access will allow you to use file names with multiple dots, others will not. I had to rename the text files sent to me by the Unix people before I could import them because they loved their dots!!!
5. Some parts of Access will allow you to use files with "dangerous" extensions like ".dat", others will not. I actually remember when this broke. A client was importing files from a scanner and it worked one day but not the next. Access stopped allowing imports of text files with .dat extensions.
 

Users who are viewing this thread

Top Bottom