Solved

Why is navigation pane displayed after code runs

Posted on 2013-06-10
3
342 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
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 84

Accepted Solution

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 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

14 Experts available now in Live!

Get 1:1 Help Now