Diificult to find answers to the easy questions

[marmitedog]

Hi everyone,

I am a newbie to VB and making great use of various web resources to find answers. However, the simple topics don't seem to be covered anywhere.

Here goes. I have some code that opens the windows open file dialog box and prompts the user to locate the file. The file string is then stored in the variable varFileName. So far so good. I then want to use the file name in a DoCmd.TransferText command to get the text file into my database.

How do I pass the variable (which is stored as K:etcetcetcetc/etc.csv) into the TransferText command?

Heres the code, thanks in advance, Stuart

Private Sub cmdImport3_Click()

Call OpenTextFile

DoCmd.TransferText acImportFixed, "Daily Rental Analysis Import Specification", _
"CM Daily Rental Report", "How do I get the bold bit below in here???"

MsgBox ("The file was imported")

End Sub



Public Function OpenTextFile(Optional varDirectory As Variant) As Variant
Dim strFilter As String, lngFlags As Long, varFileName As Variant

lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If

strFilter = ahtAddFilterItem(strFilter, "csv files (*.csv)", "*.csv")
strFilter = ahtAddFilterItem(strFilter, "Any files (*.*)", "*.*")
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:="Open a csv file ...")
If Not IsNull(varFileName) Then
varFileName = varFileName
End If
OpenTextFile = varFileName

End Function

[jeffstl]

You need to make your varFileName variable be a Public Shared variable at the top of your module.

This will make it accessible across multiple functions in the same module. In other words, once you set it to a value....as long as the function you want to read that value is called AFTER it has been set...you can get it in there

'at the very top somewhere
 
Public Shared varFileName as String
 
'Run this only 
Sub MainExample
  SetFileName()
  PrintFileName()
End Sub
 
 
Function PrintFileName
'stuff
msgbox varFileName
End Function
 
Public Function SetFileName
'stuff
varFileName = "Something"
End Funciton
 
 

[marmitedog]

Thanks Jeff,

I tried to declare the varaible at module level as Public Shared, however, VB just changes it to Public (module code below). The variable holds the correct value until it steps out of the module and back into the function that called it. I'm using Access 2000 unfortunately, would this be causing the issue?

Thanks for your help.

Public Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant

Dim strFilter As String
Dim lngFlags As Long
Public varFileName As Variant

lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

[jeffstl]

Hmm. Could be. I am not that familiar with Access but I know it should work similar to VB6.0.

So it sounds like the variable is needed not only across functions but across modules?

Regardless it looks like in your post you still have the variable declared inside of a function. It has to be declared outside the function either at the top of the module or possibly in a different module all together (which at that point it might need to be declared as Global).

Either way your solution is tied to making sure you declared it outside of any function or sub and its declared in a place that all functions and subs can access it. You might want to search like Global variable in access or sharing variable with functions in access or something like that ......there are subtle differences I know in Access syntax and VB6

[marmitedog]

OK I'm getting there! I've now got the function in the same place as the sub, so it can see the variable.

The code

MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="K:Finance\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Please locate the Connect file you previously saved")

This produces a message string "You selected K:Finance\Haynes etc etc etc .csv"

How do I then pass just the K:Finance etc etc etc to the TransferText command. Thanks again for your help.