Determine whether a file exists w/ VBA

[SarahR]

I am trying to write code for an Access database that will create a directory and a MSWord file from an Access form via a command button. The created file needs to be saved in the created directory. The name of the directory and file are dependent on info that is in the current record (form). So far, so good. By clicking on the button the directory and file are created. NOW I need to have the program check to see if the file already exists before creating it. I want the program to check for the file, and if it is present, prompt the user with a message box that asks if the file needs to be re-created. If the user clicks yes, proceed; if the user clicks no, the code should stop. I think my problem lies in that the directory and file names are not static. For Example, THIS WORKS:
Dim FileInQuestion As String
FileInQuestion = Dir("C:\Autoexec.bat")
If FileInQuestion = "" Then
MsgBox "No Such File!"
Else
MsgBox "File Exists!"
End If
BUT When I try it with my "string?" I get the message box "No Such File!" whether the file exists or not. Here:
Dim FileInQuestion As String
FileInQuestion = Dir(CStr(("C:\") + (CStr(Forms!Foreclosure!MJUNumber)) & "\" & (CStr(Forms!Foreclosure!MJUNumber)) + "merge"))
If FileInQuestion = "" Then
MsgBox "No Such File!"
Else
MsgBox "File Exists!"
End If

***In my code, "merge" is actually part of the file name. For example, if the "MJUNumber" was 12345, the resulting directory and file created would be: "C:\12345\12345merge.doc".

[MrGTI]

Is VBA much different that VB? Because in VB you just do it this way:

If Dir(sCompletePathAndFileName) <> "" Then
    'Files exists!
End If

[Matthew Gates]

Faster way to do it:

If Len(Dir$("C:\MyFile.exe") <> 0 Then Msgbox "File exists!"

And VBA has pretty much the same functions that VB has, just lacks a few controls, like the Timer Control.

[jim mcnamara]

The other folks answered the Dir part.

Building your test string might bear a long look.

You should use Trim() on your fields in case there are embedded spaces.

Also, Why all the casting - CStr("c:\") - "c:\" is already a string?

Dim tmp as String
 Tmp = "C:\" & Trim(CStr(Forms!Foreclosure!MJUNumber)) & "\"  _ 
   & Trim(CStr(Forms!Foreclosure!MJUNumber)) & "merge.doc"

 fileInQuestion = Dir(Tmp)

[SarahR]

Thank you for all your help -- it is finally working!!!!!!!!

[MrGTI]

You are welcome SarahR.

And welcome to VB-World forums.