Call MakeDir Failing on certain systems

[jazzy]

for some reason there are 2 computers out of 15 that are giving me an Error 75 Path/File not found. When i replace %USERNAME% with their actual username it works.



UserVenuePath is being pulled from a separate database. the path is c:\Users\%USERNAME%\OneDrive - Caterer\Caterer Name. the reason i am using %USERNAME% is users share computers and their one drive path changes. stepped it through and it fails on the 'Call MakeDir(strFilePath)'. like i said only happening on 2 systems. If i cut and paste the path in windows explorer, c:\Users\%USERNAME%\OneDrive - Caterer\Caterer Name it resolves fine.

Any help would be appreciated.

Private Sub Command276_Click()DoCmd.RunCommand acCmdSaveRecord


Dim UserVenuePath As String
Dim strFilePath As String
Dim strReport As String


UserVenuePath = DLookup("[FilePath]", "tbl_SystemInfo", "[SystemInfoID] = 1")
strFilePath = UserVenuePath & "\Contracts\" & Format(Form_frm_Contract.DateofFunction, "MM-DD-yyyy") & "\" & Form_frm_Contract.DayTimeFunction & "\"
strFileName = Form_frm_Contract.NameonContract & ".pdf"


If FolderExists(strFilePath) = False Then
          Call MakeDir(strFilePath)
Else
          MsgBox "This folder already exists.", vbInformation, "Folder Exists"
End If


'First let's open the report
strReport = "rpt_Contract"
DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = [Forms]![frm_Contract]![ContractsID]", acWindowNormal


'Now let's save the open report to .PDF
DoCmd.SelectObject acReport, strReport
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFilePath & strFileName, False, , , acExportQualityPrint
DoCmd.Close acReport, strReport


'Display Message
MsgBox "New file has created successfully in the new folder!", vbInformation, "VBAF1"


End Sub

[Minty]

You are much better of using a UNC/URL path for one drive locations.

https://yourfirms-my.sharepoint.com/personal/myfolder...

This should work from anywhere.

If not, what is the operating system version on the PC's that don't work?

[Welshgasman]

Hmm,
I would have thought you would need to use Environ("Username") in VBA
Not sure VBA would have any idea of system variables without it?

[jazzy]

Windows 10

[Minty]

Windows 10

And does it work on other that are on windows 10?
What other difference are there between those machines Office versions, mapped drive letters, etc?

[jazzy]

they have windows 10 as well. cant figure it out at all. some work some dont. we are not using mapped drives. use One-Drive from the c:\. only thing i can thing of is the username is different then the User folder. but they resolve when i cut and past the c:\users\%username%\etc.... in windows explorer

[Gicu]

On the machines where it fails what does Environ("OneDrive") return?

Cheers,

[jazzy]

too risky if they have multiple one drive folders, don't know how to choose correct one.

[jazzy]

made some changes and are using Environ("username") seems to be working. so confused why %USERNAME% worked for so long and on all machines until recently. Thanks

[moke123]

Call MakeDir(strFilePath)

Is that a typo for MkDir() or a seperate procedure?
Whats your MakeDir() code look like?

[Gicu]

Glad to hear you got it working, not sure what you meant by multiple OneDrive folders, I thought the current (logged in) user can only have one.

Cheers,

[jazzy]

Users can have multiple one drive folders. One is set to their desktop, etc…

[ssanfu]

I am guessing you are looking up "UserVenuePath" in a table (tbl_SystemInfo) in the current dB (which is supposed to evaluate to a user's name?).
The path points to One Drive; somehow you get the Caterer Name (is there more than one Caterer?).


Looking at the posted code:

Private Sub Command276_Click()  Terrible name for a button (I am guessing it is a button).

You are referencing 3 controls on form "frm_Contract" using syntax like

Form_frm_Contract.DateofFunction

"Form_frm_Contract" is how Access keeps track of form/report object names. It is not a good idea, but you can/could have a FORM named "Test1" AND a REPORT named "Test1"
In the IDE, the prefix "Form_" and "Report_" is how Access allows for both objects to have the same name.

You should be using Me.DateofFunction or [Forms]![frm_Contract]![controlname] (<<-- change controlname to the actual control names)


In the OpenReport command, you need to concatenate the value to the field name.

if ContractsID is numeric, use

  DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = " & [Forms]![frm_Contract]![ContractsID], acWindowNormal

if ContractsID is text, then use

  DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = '" & [Forms]![frm_Contract]![ContractsID] & "'", acWindowNormal

To export the report to PDF, you don't need to open the report first.
From help:
ObjectName (Optional Variant)

A string expression that's the valid name of an object of the type selected by the ObjectType argument. If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank.

Because you have "strReport" as the ObjectName parameter, the command is not using the selected object - it is using the saved report. Hopefully the report record source has a WHERE clause.


Consider this code (rename the sub - maybe "btnExportRptToPDF):

Private Sub Command276_Click()

    Dim UserVenuePath As String
    Dim strFilePath As String
    Dim strReport As String

    '    DoCmd.RunCommand acCmdSaveRecord
    Me.Dirty = False

    UserVenuePath = DLookup("[FilePath]", "tbl_SystemInfo", "[SystemInfoID] = 1")
    strFilePath = UserVenuePath & "\Contracts\" & Format(Forms!frm_Contract.DateofFunction, "MM-DD-yyyy") & "\" & Forms!frm_Contract.DayTimeFunction & "\"
    strFileName = Forms!frm_Contract.NameonContract & ".pdf"

    If FolderExists(strFilePath) = False Then
        Call MakeDir(strFilePath)
    Else
        MsgBox "This folder already exists.", vbInformation, "Folder Exists"
    End If

    strReport = "rpt_Contract"

    'Save the report to .PDF
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFilePath & strFileName, False

    'Display Message
    MsgBox "New file has created successfully in the new folder!", vbInformation, "VBAF1"

End Sub

[jazzy]

this code wants to print every contract in the system. need to open and set criteria DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = [Forms]![frm_Contract]![ContractsID]", acWindowNormal

[Gicu]

DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = [Forms]![frm_Contract]![ContractsID]", acWindowNormal

DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = " & [Forms]![frm_Contract]![ContractsID], acWindowNormal