I need my form to open report after excel import complete

[gojets1721]

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

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]

if msgbox("Question",vbyesno,"  ")=vbyes then
   'do something
end if

 

[gojets1721]

What would I put in the 'do something line? The report name?

 

[Isaac]

DoCmd.OpenReport method (Access) | Microsoft Docs

 

[gojets1721]

okay...how does this look?

if msgbox("Question",vbyesno,"  ")=vbyes then
   DoCmd.OpenReport "Summary Report", acViewNormal
end if

 

[isladogs]

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

 

[gojets1721]

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:

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]

okay...how does this look?

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]

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]

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]

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

That worked!! Thanks everyone. Learning everyday; I appreciate it

 

[Pat Hartman]

In this case you actually don't need to.

Thanks Isaac. That's even worse. Now they're adding inconsistencies

 

[isladogs]

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]

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]

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]

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.