Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why is navigation pane displayed after code runs

Posted on 2013-06-10
3
Medium Priority
?
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

722 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