How to export to an external drive, selecting it from a combobox

[neelsfer]

I use this code to export a query to a folder on the desktop and it works fine with Access 2007.
I have to many issues with this in windows XP and office 2010 to get it to work.
I would rather select an external drive name, ie e or f on the parent form, and then the query must export to the selected drive. Is this possible?

The unbound combobox name on main form: "drive"

   DoCmd.OpenReport "rptStocktake1", acViewPreview, "", "", acNormal
   DoCmd.Minimize
Dim strUserName As String, strPath As String
Dim strReportname As String
DoCmd.Minimize
 strUserName = Environ("username")
 strReportname = [Forms]![home]![facility3] + "-" & Format(Date, "dd-mm-yyyy") + ".pdf"
strPath = "C:\Users\" & strUserName & "\desktop\Stocktakefiles\" & strReportname  ' current path to desktop
'''strPath = "[Forms]![home]![drive]"  ' path to combobox
 DoCmd.OutputTo acOutputReport, "rptStocktake1", acFormatPDF, strPath, False
 MsgBox "The File have been succesfully exported to your desktop folder called stocktakefiles "
DoCmd.close acReport, "rptStocktake1"
End If

I imagine it has to do with the strPath . My link to the "drive" combobox on main form is:

[Forms]![home]![drive]

Any suggestions please

[neelsfer]

Yippeee - got it working. So simple actually, I added another field to the tbldrive, where i added the path ie E:\

strReportname = [Forms]![home]![facility3] + "-" & Format(Date, "dd-mm-yyyy") + ".pdf"
  strPath = [Forms]![home]![drive1] & strReportname
  DoCmd.OutputTo acOutputReport, "rptStocktake1", acFormatPDF, strPath, False

It would have been nice if a validation rule can tell me that maybe drive "E" does not exist, try another one!

[ADezii]

It would have been nice if a validation rule can tell me that maybe drive "E" does not exist, try another one!

You can easily check for, and probably should check, the existence of any Drive before executing any Code that may access it. This can easily be done by:

Private Function fDoesDriveExist(strDrive As String) As Boolean
'Set a Reference to the Microsoft Scripting Runtime
Dim fso As FileSystemObject
Dim drv As Drive
Set fso = New Scripting.FileSystemObject

For Each drv In fso.Drives
  If Replace(Replace(strDrive, "\", ""), ":", "") = drv.DriveLetter Then fDoesDriveExist = True
    Exit For
Next
End Function

'Can pass either: "E", "E:", or "E:\" to Function
If fDoesDriveExist("E:\") Then
  MsgBox "Drive Exists"
Else
  MsgBox "Drive Does Not Exist"
End If

[neelsfer]

thx Adezi i will defnitely try that.
My above code is for a pdf export to an external drive.