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?
 
als315Connect With a Mentor Commented:
Look at sample
DBOpenFile.accdb
0
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
 
AccessGuy1763Connect With a Mentor Commented:
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
 
AccessGuy1763Connect With a Mentor Commented:
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
 
AccessGuy1763Commented:
I just tested it and it worked... is that what you're showing me?

Did you make sure to "Enable Content"?
0
 
NatspapConnect With a Mentor Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.