Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Why is navigation pane displayed after code runs

Posted on 2013-06-10
3
Medium Priority
?
355 Views
Last Modified: 2013-06-11
I am using the following code (thanks to several experts last week to import an Excel file and do some magic with the end results in the table.

But for some reason after the code has finished the navigation pane displays even though I have it "turned off" in the settings... File > Options > Current Database.

Can someone tell me why this is happening?

Code:

Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Click

    If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
        MsgBox "please select excel file"
        Me.cmdSelect.SetFocus
        Exit Sub
    End If

    If MsgBox("This function will import the Excel file you have selected and clean the data. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    DoCmd.Hourglass True

    'Import Excel file into tblTempImport
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.txtFileName, True
   
    'Code to delete any import error tables
    Dim tblDef As TableDef
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.Name, "ImportError") > 0 Then
        DoCmd.SelectObject acTable, tblDef.Name, True
        DoCmd.PrintOut
        DoCmd.DeleteObject acTable, tblDef.Name
        Beep
        MsgBox "There was an error importing all of your records." & vbCrLf & vbLf & "An error report was sent to your default printer." & vbCrLf & vbLf & "The error report will detail the error reason for each field and row number for each record that was not successfully imported from your file." & vbCrLf & vbLf & "Please correct all errors and import your data again.", vbInformation, "Import Errors"
    End If
    Next tblDef

    'Use tblTempImport to add records to table named tblImportRecords
    Dim db As Database
    Set db = CurrentDb

    db.Execute "apndtblqryImportRecords", dbFailOnError
   
    'Run query to delete records from tblTempImport
    db.Execute "delqryDeleteRecordsFromtblTempImport", dbFailOnError
   
    'Data cleaning process
    db.Execute "qryCleanTable1", dbFailOnError
    db.Execute "qryCleanTable2", dbFailOnError
    db.Execute "qryCleanTable3", dbFailOnError
    MsgBox "Data Import and Cleaning Finished."
    'End of data cleaning process
   
    Else
   
    MsgBox "Import/Clean Function Cancelled"

    DoCmd.Close , "frmBrowse", acSaveNo
   
    End If

    DoCmd.Close , "frmBrowse", acSaveNo
    DoCmd.Hourglass False
 
Exit_cmdImportAndClean_Click:
    Exit Sub
   
Err_cmdImportAndClean_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportAndClean_Click
 
End Sub
0
Comment
Question by:SteveL13
  • 2
3 Comments
 
LVL 85
ID: 39234940
Commands like this one will show the Nav Pane:

        DoCmd.SelectObject acTable, tblDef.Name, True

Is there some reason you need to Select that object? I'm not sure what your goal is with that block of code ... but that's what is causing the Nav Pane to show.
0
 

Author Comment

by:SteveL13
ID: 39235024
The import Excel file part of this is leaving behind a import error table which I don't need because we'll never need, nor are we importing, the field that has the issue in the import.  So I'm just trying to automatically delete the import error table.

The code for the import is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.txtFileName, True

Out of all the fields in the Excel file I am only bringing about 1/2 of them into the table.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39235156
Looks like you're also printing the table. That's going to be troublesome, since the only way to "print" a table is to select it (like you're doing), and in doing that (selecting it) you'll always show the Nav Pane.

You could output that table to a Textfile instead, something like this:

Dim fld As DAO.Field
Dim rst As DAO.Recordset
Set rst = "SELECT * FROM " & tblDef.Name

Dim sPath As String
sPath = "C:\YourFileSavePathHere\

Dim iFile As Integer
iFile = FreeFile

Open sPath & "\ImportErrors.txt" For Output AS iFile

Dim s As String

Do Until rst.EOF
  s = ""
  For each fld In rst.Fields
    s = fld.Value & "," & s
  Next fld
  '/ print the data
  Print iFile, s

  rs.MoveNext
Loop

Close iFile
rst.Close
Set rst = Nothing

AFTER doing that, you can then delete the table:
With this:

Currentproject.Connection.Execute "DROP TABLE " & tblDef.Name

You'd do away with the SelectObject, DeleteObject lines (and the Printout line).
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question