Solved

Why is navigation pane displayed after code runs

Posted on 2013-06-10
3
340 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 84
Comment Utility
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
Comment Utility
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now