Putting a browse button on a form in access 2010

Im looking to put a browse button into a form in access so i can browse for a file and then import that file into my 'scanner' table.  How would I go about doing this?

Thanks
NatspapAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can use this function:
Public Function GetFile() As Variant
Dim dialog As Object
Dim pickedfile As Boolean
Set dialog = Application.FileDialog(3)
GetFile = Null
With dialog
    .AllowMultiSelect = False
    .Title = "Please select file for import"
    .Filters.Clear
    .Filters.Add "Text Files", "*.TXT" 'Mask for text files. Remove it if you like to browse for all files
    pickedfile = False
    pickedfile = .Show
    If pickedfile Then
        GetFile = .SelectedItems.Item(1)
    End If
End With
End Function

Open in new window

and use it in button's code like:
If IsNull(GetFile) Then
    MsgBox "Nothing was selected", vbOKOnly
Else
    MsgBox GetFile(), vbOKOnly
End If

Open in new window

0
AccessGuy1763Commented:
As the code comment states, this is from a MS KB article:

Public Function fcnLaunchCD(strform As Form) As String

    'This public function is used to enable the common dialog.
    'The code comes from http://support.microsoft.com/default.aspx?scid=kb;en-us;303066
    
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
      "JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file using the Common Dialog DLL"
         Else
            fcnLaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

Open in new window


OpenFileName declaration:

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Open in new window


Once you have the file name, you can import it but we need to know what the file is to provide further advice.  For example, if the file is Excel you could utilize TransferSpreadsheet:
http://msdn.microsoft.com/en-us/library/office/ff844793.aspx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can do it two ways:

1) Use the FileDialog method. Essentially you do this:

Dim oDialog As Office.FileDialog
Set oDialog = Application.FileDialog(msoFileDialogFilePicker)

oDialog.Show

The .SelectedItems collection would then hold the file(s) selected by the user.

See this MSDN article for more information: http://support.microsoft.com/kb/824272

One caveat to this - you must have the full version of Office installed on the machine. If you have users running only Access (the Full or Runtime version), then you can't use this method.

2) The API Method. It's complicated, and is described in detail here: http://access.mvps.org/access/api/api0001.htm. Essentially you copy the files you'll find at that location to a new Standard Module, and then call it like this:

Dim strFilter As String
Dim strInputFileName as string
'/ Filer to Excel files
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
                Flags:=ahtOFN_HIDEREADONLY)

Once you do that, the variable "strInputFileName" would contain the file selected by the user.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NatspapAuthor Commented:
Thanks everyone for the help so far.  I am trying to transfer in a notepad (.txt) file
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Once you've found the file, you can then use TransferText:

DoCmd.TransferText acImportFixed, , "YourTable", "YourFile"

Where "YourFile" is the fully formatted path to your file.

See here for more info on TransferText:
http://office.microsoft.com/en-us/access-help/HV080751325.aspx
0
NatspapAuthor Commented:
I am new to this and cant get any of these methods to work.  Can you go into a little bit more depth with the answers?

Thanks for all the help
0
AccessGuy1763Commented:
Without an error message or even which of the two suggested methods you've chosen to try, trying to figure out where you've gone wrong will be a bit tough.

For the solution I suggested, this is what the form module would look like if I were to call the common dialog from a command button and display what was selected through a message box:

Option compare database
Option explicit

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Sub cmdMyTestButton_Click()

   dim strFileName as string

   strFileName = fcnLaunchCD(me)

   If strFileName<>"" then

      Call msgbox("File selected:" & vbcrlf & strFileName,vbokonly)

   End If

End Sub

Private Function fcnLaunchCD(strform As Form) As String

    'This public function is used to enable the common dialog.
    'The code comes from http://support.microsoft.com/default.aspx?scid=kb;en-us;303066
    
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
      "JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file using the Common Dialog DLL"
         Else
            fcnLaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

Open in new window

0
NatspapAuthor Commented:
Maybe I'm putting the code in the wrong spot.  I should put the code on the 'on click' property of a 'command button' correct?
0
AccessGuy1763Commented:
The code should be in the form's module.  Command button events are also found in the form module for the form on which they reside.  In my example, the code lines "Private Sub cmdMyTestButton_Click()" and the following "End Sub" would be automatically generated when you go to the Event tab of the command button's property sheet, click the ellipses next to "On Click" and choose "Code Builder".  

The declaration for OpenFileName should be pasted into the same module, but not inside any subroutine.  The declaration should be the first thing in the module after your "Option Compare database" and "Option Explicit" lines.
0
NatspapAuthor Commented:
I am getting this error when i run it...

Compile Error:

Sub or function not defined

Option Compare Database
Option Explicit

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Sub cmdMyTestButton_Click()

   Dim strFileName As String

   strFileName = fcnLaunchCD(Me)

   If strFileName <> "" Then

      Call MsgBox("File selected:" & vbCrLf & strFileName, vbOKOnly)

   End If

End Sub

Private Function fcnLaunchCD(strform As Form) As String

    'This public function is used to enable the common dialog.
    'The code comes from http://support.microsoft.com/default.aspx?scid=kb;en-us;303066
    
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.Hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
      "JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file using the Common Dialog DLL"
         Else
            fcnLaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

Open in new window

0
AccessGuy1763Commented:
Without being able to see your actual database, my guess is that your command button is not named "cmdMyTestButton".  I sometimes have trouble getting an Event tied to a control when I'm manually typing them in.

My suggestion would be to right click on your command button and select "Build Event", then "Code Buillder".  This should automatically generate an empty routine tied to that button.  Next, copy everything inside "Private Sub cmdMyTestButton_Click()" and the following "End Sub" and paste it inside your new command button click routine.  Once that's done, delete the WHOLE cmdMyTestButton_Click() routine.
0
NatspapAuthor Commented:
Thanks for the help so far!

Im still getting the same error.  Its highlighting the row...

Private Function fcnLaunchCD(strform As Form) As String

Open in new window


and

lReturn = GetOpenFileName(OpenFile)

Open in new window

0
AccessGuy1763Commented:
You indeed are missing that function.  Add this just above your declaration for OPENFILENAME:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Open in new window

0
als315Commented:
Look at sample
DBOpenFile.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AccessGuy1763Commented:
I just tested it and it worked... is that what you're showing me?

Did you make sure to "Enable Content"?
0
NatspapAuthor Commented:
I feel like im getting really close!  I get this error now when i run that...

The expression On Click you entered as the event property setting produced the following error: The code in this project must be updated for use on 64 bit systems.  Please review and update declare statements and then mark them with the PtrSafe attribute.

This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.


Any ideas on how I go about fixing this error?
0
AccessGuy1763Commented:
I think you just need to add "PTRSafe" to the function declaration to get it to work for 64 bit office:

Private Declare PTRSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Open in new window


Reference:
http://www.access-programmers.co.uk/forums/showthread.php?t=225860
0
NatspapAuthor Commented:
I finally got it working!  I used als315's sample to help make some changes.  Thanks for the help everyone!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.