Problem changing the DefaultFilePath in Access2000

[emik]

Hi guys, this is my first post. I've been working with MS Access for awhile but I'm not an export, I mostly work with recordsets and basic coding.

What I'm trying to do now is have a textbox in my main form where the user enters a default path, eg C:\DATA then I have the following code:

Dim strFileName As String
Dim xlApplication As Excel.Application
Dim strInitDir As String

Set xlApplication = New Excel.Application

'set the path to the textbox on the main form
strInitDir = Form_frm_Main_Menu.txtDir.Value
If IsNull(strInitDir) Or strInitDir = "" Then
strInitDir = "C:\DATA"
End If

xlApplication.DefaultFilePath = strInitDir
strFileName = xlApplication.GetOpenFilename("Excel Files (*.xls), *.xls")

Now here is the problem I'm having. The first time I run the program, it opens to the right filepath. Now if I change the filepath on the main form and run the program again, it will go to the original file path. If I run it a third time it will go to the file path I had previously entered. It seems the code is always 1 entry behind what I want.

I'd appreciate any help, thanks!

 

[RonRepp]

I'm not an Access wiz...far from it. You may actually get better results from the Access forum if you don't get anything here.

However, does you txtDir.Text change after you code runs?

The reason I ask this is that your variable (strIniDir) remains in memory until the form is closed. If your textbox doesn't change, the same DIR will remain present.

If Access has a Text_Change event, you may want to store your variable in there.

Anyway,check out forum705 if it doesn't pan out here.

Good luck.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[emik]

Thanks for the reply.

The textbox may or may not change but I think I figured out the problem (maybe this will help someone else).

When you create an Excel object it has a default path and doesn't like it to be changed (it wants to be closed to initiate it). So the following code works below but I don't think it's the best way of doing this. I posted this question in the forum you suggested but here's the code that seems to get the job done. Just added the xlApp = Nothing.

Set xlApplication = New Excel.Application

'Set the default path in the initial main menu form

If IsNull(Form_frm_Main_Menu.txtDir) = False Then
strInitDir = Form_frm_Main_Menu.txtDir.Value
If strInitDir = "" Then
strInitDir = "C:\DATA"
End If
Else
strInitDir = "C:\DATA"
End If

'We set the default path to get the file from the textbox on the main form
xlApplication.DefaultFilePath = strInitDir

'Here we reset the Excel object so that it takes the right default path. If we do
'not reset it, it will start with its own default path.
Set xlApplication = Nothing
Set xlApplication = New Excel.Application

strFileName = xlApplication.GetOpenFilename("Excel Files (*.xls), *.xls")

 

[mp9]

A shorter form of your If-then-else loop:

If Len(Form_frm_Main_Menu.txtDir) > 0 Then
    strInitDir = Form_frm_Main_Menu.txtDir.Value
Else
    strInitDir = "C:\DATA"
End If

http://www22.brinkster.com/accessory/