0

I have a button on a form with the code below sitting in the form. It used to execute without a problem. Suddenly when clicking the button I now get this run time error:

Run-time error 91: Object Variable or With block variable not set in procedure cmdImportEDD_Click, line 0

I have tried commenting out individual lines in the code to find the problem. I ended up determening that the FileDialog part seems to be a problem somehow. However, after having added the If.. then part to it last time, the code worked again, but today the error is back.

To be clear, the error appears before the VBA code is executed (hence line 0) and Compile yields no errors either!

What is happening here that I'm not getting?

Private Sub cmdImportEDD_Click()
On Error GoTo cmdImportEDD_Click_Error

Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
fDialog.AllowMultiSelect = False
fDialog.InitialFileName = GetDownloadFolder
fDialog.Show
If fDialog.SelectedItems.Count < 1 Then
    Exit Sub
End If
Debug.Print fDialog.SelectedItems(1)

'Replace the selected file with the current one
Dim sOldFile As String
sOldFile = strTARGET_EDD_SALESFILE
Dim oFSO As FileSystemObject
Set oFSO = New FileSystemObject
oFSO.DeleteFile sOldFile
oFSO.MoveFile Source:=fDialog.SelectedItems(1), Destination:=sOldFile

'Perform the update
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_app_EDD", acViewNormal, acAdd
DoCmd.SetWarnings True
MsgBox "The data has been successfully imported!", vbOKOnly Or vbInformation, "Import Data: EDD"

    On Error GoTo 0
    Exit Sub

cmdImportEDD_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdImportEDD_Click, line " & Erl & "."
End Sub

The code for GetDownloadFolder:

Function GetDownloadFolder() As String
    Dim objShell
    Dim objFolder
    Dim objFolderItem
    Dim temp
    Const DESKTOP = &H10&

    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(DESKTOP)
    Set objFolderItem = objFolder.Self
    temp = objFolderItem.Path
    temp = Left(temp, Len(temp) - 7) & "Downloads" '<--- I believe this is the download folder
    GetDownloadFolder = temp
End Function

Having canceled out the OnError I now get a debug error on the line Set objFolderItem = objFolder.Self

braX
  • 11,506
  • 5
  • 20
  • 33
rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • 1
    For now I only have questions about `strTARGET_EDD_SALESFILE` and `GetDownloadFolder`. Is it set somewhere? – Teamothy Oct 03 '19 at 07:56
  • Strange behavior so far. Did you try to **de**compile/compact the database? – AHeyne Oct 03 '19 at 07:59
  • 4
    Remove `On Error GoTo cmdImportEDD_Click_Error` to find out which line is failing, `Erl` won't work without line numbers. – BrakNicku Oct 03 '19 at 08:02
  • I removed the OnError... I now get the Error located in the GetDownloadFolder function. The problem seems to be that that `objFolder` turns out to be nothing in the Local Window – rohrl77 Oct 03 '19 at 09:22
  • `Self` is not a property of Shell folder object. If you set Shell library and used early binding, then intellisense popup would trigger. However, I don't get compile error on that line. – June7 Oct 03 '19 at 09:29
  • I got run-time error on DESKTOP line. Had to put DESKTOP in quote marks. Now Self line has run-time error. Review https://stackoverflow.com/questions/23070299/get-the-windows-download-folders-path – June7 Oct 03 '19 at 09:51
  • I adjusted to early binding... you are right, there is no `.Self`. Odd thing is that this worked in the past?!? I now also get the run time error on the DESKTOP – rohrl77 Oct 03 '19 at 09:54
  • When was the 'past'? Did you upgrade Access and/or convert from ADP? Seems I've read that Namespace was used with Access 2003 and earlier ADP files and may no longer be supported. – June7 Oct 03 '19 at 10:03
  • It's beginning to look like this is an issue with 64BIT installation. I use two computers. One 32BIT installation of Office and the other 64BIT. I have mainly been using this DB on the 32BIT one... this specific function I would have used on both computers at some point though. – rohrl77 Oct 03 '19 at 10:06
  • I posted an answer. Thanks to BrackNiku and June7 who helped me in pinpointing the problem. If either one of you want to post an answer, I'll accept that. – rohrl77 Oct 03 '19 at 13:21

1 Answers1

0

I managed to get my code working by replacing the somewhat more complicated Shell call in the GetDownloadFolder function with a simplerEnviron("USERPROFILE") & "\Downloads".

This has taken care of the run time error.

rohrl77
  • 3,277
  • 11
  • 47
  • 73