Use information entered by user in Form in Macro

Question

(This is in Access 2003.) I am writing a macro that opens up a form to allow users enter the path and name of the file they wish to import. Then it closes the form, and imports the data in the specified file. I have the code that opens the form and user input properly. I also have the code that does the import/manipulation of data once I get the path and file name.

My question is: how do I use the information entered in the form in the macro/other subs after the form is closed?

Here's the simple version of my form's code ("Enter Import Info"):

Option Compare Database
Option Explicit

Sub RunButton_Click()

    FilePathTextBox.SetFocus
    DataPath = FilePathTextBox.Text
    FileNameTextBox.SetFocus
    DataName = FileNameTextBox.Text

    DoCmd.Close

End Sub

Here's the simple version of my macro attempt (which failed):

Option Compare Database
Option Explicit
Option Base 1

Public DataPath As String
Public DataName As String
Public CompleteName As String

Function StartImport()

    'Open form to let user enter import file info
    DoCmd.OpenForm "Enter Import Info", acNormal, , , , acDialog

    'Get complete file path/name. I think this is where the problem is.
    CompleteName = DataPath & "\" & DataName

    'Import
    ImportData

    'Data clean up
    DataCleanup
End Function


Function ImportData()

    'Import CompleteName
    'The import code here works fine when CompleteName has the right info

End Function

Sub DataCleanup()

    'cleans up the data as needed (using DoCmd.RunSQL). Works fine.

End Sub

I spent more than 2 hours looking on the Internet for solutions, but couldn't find one. A friend suggests creating a temp table to store the values entered in the form as a string (meaning the table will have only 1 field and 1 record). But I don't know how to retrieve it as a string out of the table.

Sorry for the very long post - I wanted to be as clear as possible. Thank you for your help in advance!!

==============Edit (Additional information)

The original code in the form asks the user to manually type in the path and file name. The entire code was working back then.

However, I wanted to avoid mistakes/errors by using FileDialog to determine the path and name, so I created subs that are called when the textbox is clicked(FileNameTextBox_Click and FilePathTextBox_Click). The code for FileNameTextBox_Click() can be found here: FileDialog doesn't work

Does that affect anything?

I am with Tony on this. It works fine for me. Are you sure you have not declared Datapath and Dataname elsewhere? As an aside, do not use the text property of cntrols and then you do not have to set focus, it will lead to grief at some stage. Just say `DataPath = Me.FilePathTextBox` - Me is important for avoiding name confusion. If you must have a property, use `.value` which is available even when the control does not have focus. — Fionnuala, Feb 28 '12 at 11:01

score 0 · Answer 1 · answered Feb 28 '12 at 10:58

0

I posted a comment saying I could not get your code to fail. I then thought I ought to do everything just as you had. I do not understand why changing some of the names made a difference but it did.

Using your names and your code, I got the path and file name passed back to the module with the following changes:

RunButton_Click

Debug.Print "Form " & DataPath              ) Check values are stored
Debug.Print "Form " & DataName              )

' Close form and nothing else.
DoCmd.Close objecttype:=acForm, objectname:="Enter Import Info"

Module1

After DoCmd.OpenForm "Enter Import Info", acNormal, , , , acDialog

Debug.Print "Module " & DataPath            )  Check have values
Debug.Print "Module " & DataName            )

answered Feb 28 '12 at 10:58

Tony Dallimore

12,335
7
32
61

I ran the code provided with the sole change of using the value rather than the text property, and it worked for me. `CompleteName` contained the full path. – Fionnuala Feb 28 '12 at 12:13
I added some information in the question post, not sure if that affects anything, but would appreciate it if you or @Remou can take a look! Also, I added in those few lines and tried running the macro, but it kept giving a compile error of "Ambiguous name detected: DataPath", and highlighting DataPath in RunButton_Click. – Emily Feb 28 '12 at 15:16
Ambiguous name means that DataPath has been declared more than once. Within the VB Editor, click `Ctrl+F` to access Find. Search for DataPath within the Current Project. You should expect to find two or more declarations that are within scope for `RunButton_Click()`. – Tony Dallimore Feb 28 '12 at 17:31

Use information entered by user in Form in Macro

1 Answers1