[truthlover]

I need some help. I need a button on an Access form that allows the user to browse for a file on our network, and insert it in a field (rather than having to type a path). I dont need any filters because the files can be any number of formats.

I've come across a couple of so-called "simple" solutions, but for the life of me, I either cant understand them or I cant get them to work. I know nothing about VB so if someone can provide a truly simple answer (preferably something I could cut and paste) with clear instructions on how to actually get it to work, I'd be VERY grateful.

Oh, I am coding this in Access 2000, but if I need to, I can code it in Access 2002-2003.

Thanks!
Barb

[ADezii]

Originally Posted by truthlover

I need some help. I need a button on an Access form that allows the user to browse for a file on our network, and insert it in a field (rather than having to type a path). I dont need any filters because the files can be any number of formats.

I've come across a couple of so-called "simple" solutions, but for the life of me, I either cant understand them or I cant get them to work. I know nothing about VB so if someone can provide a truly simple answer (preferably something I could cut and paste) with clear instructions on how to actually get it to work, I'd be VERY grateful.

Oh, I am coding this in Access 2000, but if I need to, I can code it in Access 2002-2003.

Thanks!
Barb

1. Set a Reference to Microsoft Office XX.X Object Library.
2. Copy and Paste the following code wherever appropriate, changing the name of the Text Box
   
   Expand|Select|Wrap|Line Numbers

   1. Dim fdg As FileDialog, vrtSelectedItem As Variant
   2. Dim strSelectedFile As String
   3.  
   4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
   5.  
   6. With fdg
   7.   .AllowMultiSelect = False
   8.   .InitialView = msoFileDialogViewDetails
   9.     If .Show = -1 Then
   10.       For Each vrtSelectedItem In .SelectedItems    'onby be 1
   11.         strSelectedFile = vrtSelectedItem
   12.       Next vrtSelectedItem
   13.       Me![txtSelectedFile] = strSelectedFile
   14.     Else     'The user pressed Cancel.
   15.     End If
   16. End With
   17.  
   18. Set fd = Nothing
3. Let me know if you have any problems.

[truthlover]

Set a Reference to Microsoft Office XX.X Object Library.

How do I do that?

Copy and Paste the following code wherever appropriate,

I'm afraid I couldnt begin to know where it's appropriate. Can you be more specific?

changing the name of the Text Box

Do you mean I have to change the name of the actual Text Box (what do I change it to?) or do you mean I have to rename the Text Box reference in the below code?

Sorry, I warned you I knew nothing about this

Thanks!
Barb

Originally Posted by ADezii

1. Set a Reference to Microsoft Office XX.X Object Library.
2. Copy and Paste the following code wherever appropriate, changing the name of the Text Box
   
   Expand|Select|Wrap|Line Numbers

   1. Dim fdg As FileDialog, vrtSelectedItem As Variant
   2. Dim strSelectedFile As String
   3.  
   4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
   5.  
   6. With fdg
   7. .AllowMultiSelect = False
   8. .InitialView = msoFileDialogViewDetails
   9. If .Show = -1 Then
   10. For Each vrtSelectedItem In .SelectedItems 'onby be 1
   11. strSelectedFile = vrtSelectedItem
   12. Next vrtSelectedItem
   13. Me![txtSelectedFile] = strSelectedFile
   14. Else 'The user pressed Cancel.
   15. End If
   16. End With
   17.  
   18. Set fd = Nothing
3. Let me know if you have any problems.

[ADezii]

Originally Posted by truthlover

How do I do that?



I'm afraid I couldnt begin to know where it's appropriate. Can you be more specific?



Do you mean I have to change the name of the actual Text Box (what do I change it to?) or do you mean I have to rename the Text Box reference in the below code?

Sorry, I warned you I knew nothing about this

Thanks!
Barb

1. To set a Reference to the Microsoft Office Object Library
   • In Form Design View
   • Select View from the Menu Bar ==> Code
   • Select Tools from the Menu Bar ==> References
   • Scroll down to the Microsoft Office XX.X Object Library
   • Select the Check Box next to this Option
   • Click on OK
2. Select the Text Box on your Form (Design View) that will show the Path to the selected File
   • Right Click on Text Box ==> Properties ==> All Tab ==> Name.
   • Rename the Text Box to txtSelectedFile
   • Close Properties Dialog Box

[truthlover]

Ok, I got that. Now where does the code go? And is there anything I have to do to connect the code to the text box?

Originally Posted by ADezii

1. To set a Reference to the Microsoft Office Object Library
   • In Form Design View
   • Select View from the Menu Bar ==> Code
   • Select Tools from the Menu Bar ==> References
   • Scroll down to the Microsoft Office XX.X Object Library
   • Select the Check Box next to this Option
   • Click on OK
2. Select the Text Box on your Form (Design View) that will show the Path to the selected File
   
   • Right Click on Text Box ==> Properties ==> All Tab ==> Name.
   • Rename the Text Box to txtSelectedFile
   • Close Properties Dialog Box

[ADezii]

Originally Posted by truthlover

Ok, I got that. Now where does the code go? And is there anything I have to do to connect the code to the text box?

Now where does the code go?

The most obvious location would be the Click() Event of a Command Button.

And is there anything I have to do to connect the code to the text box?

Line #13 connects the code (selected File) to the Text Box as long as it is named txtSelectedFile.

[truthlover]

Thanks, that worked great!

One question, though, is that even though I have it set up as a hyperlink, when it's clicked on, nothing happens. Is there some extra coding it needs?

Also, is there any way of also getting an image of the file selected to display?

Thanks so much!
Barb

Originally Posted by ADezii

The most obvious location would be the Click() Event of a Command Button.

Line #13 connects the code (selected File) to the Text Box as long as it is named txtSelectedFile.

[ADezii]

Originally Posted by truthlover

Thanks, that worked great!

One question, though, is that even though I have it set up as a hyperlink, when it's clicked on, nothing happens. Is there some extra coding it needs?

Also, is there any way of also getting an image of the file selected to display?

Thanks so much!
Barb

You definately should have mentioned these 'little details' up front! Is the Control Source for txtSelectedFile a Hyperlink Data Type?

[truthlover]

Originally Posted by ADezii

You definately should have mentioned these 'little details' up front! Is the Control Source for txtSelectedFile a Hyperlink Data Type?

Sorry. I didint mention it because it was working with the "edit hyperlink" code I tried first.

Anyway, yes, I currently have the control source as a Hyperlink. I also had some trouble initially getting the hyperlink to not use http:// to target the link. I may have done it wrong (i set the format to "file://") so if that's wrong, please tell me the correct method.

Thanks for all your patience!!

[ADezii]

Originally Posted by truthlover

Sorry. I didint mention it because it was working with the "edit hyperlink" code I tried first.

Anyway, yes, I currently have the control source as a Hyperlink. I also had some trouble initially getting the hyperlink to not use http:// to target the link. I may have done it wrong (i set the format to "file://") so if that's wrong, please tell me the correct method.

Thanks for all your patience!!

You are quite welcome. Now, let's work on the Hyperlink part first. Make a slight change to the code in Line #11, as listed below. Select a Local File first, see if you can Navigate to it via the Hyperlink on the Form Field (txtSelectedFile), then a File on the Network. Let me know how you make out:

Expand|Select|Wrap|Line Numbers

1. Dim fdg As FileDialog, vrtSelectedItem As Variant
2. Dim strSelectedFile As String
3.  
4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
5.  
6. With fdg
7.   .AllowMultiSelect = False
8.   .InitialView = msoFileDialogViewDetails
9.     If .Show = -1 Then
10.       For Each vrtSelectedItem In .SelectedItems 'onby be 1
11.         strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem
12.       Next vrtSelectedItem
13.       Me![txtSelectedFile] = strSelectedFile
14.     Else 'The user pressed Cancel.
15.     End If
16. End With
17.  
18. Set fd = Nothing

[truthlover]

That worked perfectly, thanks!!

What about getting an image to display? Is it possible to have that function also display as an image? PDF would be the preferred format, since that's the output of our scanners, but if that's not possible, a .tiff or a .jpg would work.

Access does have a method of doing it, but to be honest, it's clunky and difficult and the people using it are not going to get it so if i can get this one function to work, it would be fabulous.

Even if I need a separate button to get the image to display, it would be better than not having the ability to display the image.

Thanks again
-Barb

Originally Posted by ADezii

You are quite welcome. Now, let's work on the Hyperlink part first. Make a slight change to the code in Line #11, as listed below. Select a Local File first, see if you can Navigate to it via the Hyperlink on the Form Field (txtSelectedFile), then a File on the Network. Let me know how you make out:

Expand|Select|Wrap|Line Numbers

1. Dim fdg As FileDialog, vrtSelectedItem As Variant
2. Dim strSelectedFile As String
3.  
4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
5.  
6. With fdg
7. .AllowMultiSelect = False
8. .InitialView = msoFileDialogViewDetails
9. If .Show = -1 Then
10. For Each vrtSelectedItem In .SelectedItems 'onby be 1
11. strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem
12. Next vrtSelectedItem
13. Me![txtSelectedFile] = strSelectedFile
14. Else 'The user pressed Cancel.
15. End If
16. End With
17.  
18. Set fd = Nothing

[ADezii]

Originally Posted by truthlover

That worked perfectly, thanks!!

What about getting an image to display? Is it possible to have that function also display as an image? PDF would be the preferred format, since that's the output of our scanners, but if that's not possible, a .tiff or a .jpg would work.

Access does have a method of doing it, but to be honest, it's clunky and difficult and the people using it are not going to get it so if i can get this one function to work, it would be fabulous.

Even if I need a separate button to get the image to display, it would be better than not having the ability to display the image.

Thanks again
-Barb

I could incorporate code which would check the Selected File's Extension, and if it is one of the standard Graphic File Formats (*.bmp, *.gif, *.wmf, *.jpg) dynamically load the Image into an Iamge Control. This Control would have to have practical dimensions to be effective. Is this what you are looking for?

[truthlover]

Yes, that's exactly what I need.

Thanks so much for your help and your patience.

Have a wonderful weekend
-Barb

Originally Posted by ADezii

I could incorporate code which would check the Selected File's Extension, and if it is one of the standard Graphic File Formats (*.bmp, *.gif, *.wmf, *.jpg) dynamically load the Image into an Iamge Control. This Control would have to have practical dimensions to be effective. Is this what you are looking for?

[ADezii]

Originally Posted by truthlover

Yes, that's exactly what I need.

Thanks so much for your help and your patience.

Have a wonderful weekend
-Barb

You have a nice weekend, also. I'll try to have a look at it during this weekend and see what I can come up with.

[ADezii]

Originally Posted by truthlover

Yes, that's exactly what I need.

Thanks so much for your help and your patience.

Have a wonderful weekend
-Barb

I'm posting some revised code based on an Image Control on your Form named imgSelectedFile, but please download the Attached Test Database on this Thread to get a much clearer picture of what's going on. Let me know how you make out.

Expand|Select|Wrap|Line Numbers

1. Dim fdg As FileDialog, vrtSelectedItem As Variant
2. Dim strSelectedFile As String, strImagePath As String
3.  
4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
5.  
6. With fdg
7.   .AllowMultiSelect = False
8.   .InitialView = msoFileDialogViewDetails
9.     If .Show = -1 Then
10.       For Each vrtSelectedItem In .SelectedItems 'onby be 1
11.         strImagePath = vrtSelectedItem
12.         strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem
13.       Next vrtSelectedItem
14.       Me![txtSelectedFile] = strSelectedFile
15.         Select Case Right$(strImagePath, 4)
16.           Case ".wmf", ".emf", ".dib", ".bmp", ".ico", ".gif", ".jpg"
17.             Me![imgSelectedFile].Picture = strImagePath
18.           Case Else
19.             Me![imgSelectedFile].Picture = ""
20.         End Select
21.     Else 'The user pressed Cancel.
22.     End If
23. End With
24.  
25. Set fdg = Nothing

[truthlover]

It worked, but when i closed and reopened the form, the link was there, but the image didnt display. In fact, the attached file is the same way. How do I get the image to remain displayed?

Oh, one more thing. Is there a way to get this image to display in a report?

Thanks!
-Barb

Originally Posted by ADezii

I'm posting some revised code based on an Image Control on your Form named imgSelectedFile, but please download the Attached Test Database on this Thread to get a much clearer picture of what's going on. Let me know how you make out.

Expand|Select|Wrap|Line Numbers

1. Dim fdg As FileDialog, vrtSelectedItem As Variant
2. Dim strSelectedFile As String, strImagePath As String
3.  
4. Set fdg = Application.FileDialog(msoFileDialogFilePicker)
5.  
6. With fdg
7. .AllowMultiSelect = False
8. .InitialView = msoFileDialogViewDetails
9. If .Show = -1 Then
10. For Each vrtSelectedItem In .SelectedItems 'onby be 1
11. strImagePath = vrtSelectedItem
12. strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem
13. Next vrtSelectedItem
14. Me![txtSelectedFile] = strSelectedFile
15. Select Case Right$(strImagePath, 4)
16. Case ".wmf", ".emf", ".dib", ".bmp", ".ico", ".gif", ".jpg"
17. Me![imgSelectedFile].Picture = strImagePath
18. Case Else
19. Me![imgSelectedFile].Picture = ""
20. End Select
21. Else 'The user pressed Cancel.
22. End If
23. End With
24.  
25. Set fdg = Nothing

[ADezii]

Originally Posted by truthlover

It worked, but when i closed and reopened the form, the link was there, but the image didnt display. In fact, the attached file is the same way. How do I get the image to remain displayed?

Oh, one more thing. Is there a way to get this image to display in a report?

Thanks!
-Barb

The following code will dynamically load specific 'Graphic' Image Types into the Image Control on your Form while viewing (based on the displayed Link). Place it in the Current() Event of your Form as indicated below. It is pretty self-explanatory, but if you need to know how it works, just let me know:

Expand|Select|Wrap|Line Numbers

1. Private Sub Form_Current()
2. If Not IsNull(Me![txtSelectedFile]) Then
3.   Select Case Right$(Me![txtSelectedFile], 4)
4.     Case ".wmf", ".emf", ".dib", ".bmp", ".ico", ".gif", ".jpg"
5.       'Extract the Address Section from the Hyperlink
6.       Me![imgSelectedFile].Picture = Left$(Me![txtSelectedFile], InStr(Me![txtSelectedFile], "#") - 1)
7.     Case Else
8.       Me![imgSelectedFile].Picture = ""
9.   End Select
10. Else
11.   Me![imgSelectedFile].Picture = ""
12. End If
13. End Sub

[truthlover]

There was no Current() option in the events. I am probably using the wrong type of image control. In fact, when I tried to put an image control in, it kept making me chose a source, so I ended up copy/pasting the image control from the sample you attached.

Can you tell me how to insert the proper Image control?

Thanks

Originally Posted by ADezii

The following code will dynamically load specific 'Graphic' Image Types into the Image Control on your Form while viewing (based on the displayed Link). Place it in the Current() Event of your Form as indicated below. It is pretty self-explanatory, but if you need to know how it works, just let me know:

Expand|Select|Wrap|Line Numbers

1. Private Sub Form_Current()
2. If Not IsNull(Me![txtSelectedFile]) Then
3. Select Case Right$(Me![txtSelectedFile], 4)
4. Case ".wmf", ".emf", ".dib", ".bmp", ".ico", ".gif", ".jpg"
5. 'Extract the Address Section from the Hyperlink
6. Me![imgSelectedFile].Picture = Left$(Me![txtSelectedFile], InStr(Me![txtSelectedFile], "#") - 1)
7. Case Else
8. Me![imgSelectedFile].Picture = ""
9. End Select
10. Else
11. Me![imgSelectedFile].Picture = ""
12. End If
13. End Sub

[ADezii]

Originally Posted by truthlover

There was no Current() option in the events. I am probably using the wrong type of image control. In fact, when I tried to put an image control in, it kept making me chose a source, so I ended up copy/pasting the image control from the sample you attached.

Can you tell me how to insert the proper Image control?

Thanks

There was no Current() option in the events.

1. Form Design View
2. Properties
3. Event Tab
4. On Current
5. Click on ... to advance to the Current() Event

Can you tell me how to insert the proper Image control?

1. Form Design View
2. View ==> Toolbox
3. Select Image Control then draw on Form
4. Select any Graphic File then OK
5. Select Image Control
6. Properties
7. Format Tab
8. In Picture Property Delete the Path to the Graphic File
9. Answer Yes to the Do you want to remove the Picture from Form prompt
10. Rename the Image Control to imgSelectedFile

[truthlover]

That did the trick. Turned out the reason I couldnt find the Current () event is because I was looking for it in the image control properties and not the Form properties. Thank you for beign so patient with me.

One last question (I think). Is there a way to get the link and image to show up in a report too?

Thanks
-Barb

Originally Posted by ADezii

1. Form Design View
2. Properties
3. Event Tab
4. On Current
5. Click on ... to advance to the Current() Event

1. Form Design View
2. View ==> Toolbox
3. Select Image Control then draw on Form
4. Select any Graphic File then OK
5. Select Image Control
6. Properties
7. Format Tab
8. In Picture Property Delete the Path to the Graphic File
9. Answer Yes to the Do you want to remove the Picture from Form prompt
10. Rename the Image Control to imgSelectedFile

[ADezii]

Originally Posted by truthlover

That did the trick. Turned out the reason I couldnt find the Current () event is because I was looking for it in the image control properties and not the Form properties. Thank you for beign so patient with me.

One last question (I think). Is there a way to get the link and image to show up in a report too?

Thanks
-Barb

Not time right now, will return later.

[ADezii]

Originally Posted by truthlover

That did the trick. Turned out the reason I couldnt find the Current () event is because I was looking for it in the image control properties and not the Form properties. Thank you for beign so patient with me.

One last question (I think). Is there a way to get the link and image to show up in a report too?

Thanks
-Barb

One last question (I think). Is there a way to get the link and image to show up in a report too?

Yes there is. It may be confusing to explain the entire process in the Post, but it is exactly the same code in the Form's Current() Event posted to the Format() Event of the Detail Section of a Report. I've kept the naming conventions the same to make it easy for you, simply create a Directory (Folder) in the Root Directory of Drive C: named PFDBMPS, namely C:\PFDBMPS. Download the Test Database for this Thread and copy the *.bmps to that Directory. Click on the View Report Button on the Main Menu to see how it is done.

Expand|Select|Wrap|Line Numbers

1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
2. If Not IsNull(Me![txtSelectedFile]) Then
3.   Select Case Right$(Me![txtSelectedFile], 4)
4.     Case ".wmf", ".emf", ".dib", ".bmp", ".ico", ".gif", ".jpg"
5.       Me![imgSelectedFile].Picture = Left$(Me![txtSelectedFile], InStr(Me![txtSelectedFile], "#") - 1)
6.     Case Else
7.       Me![imgSelectedFile].Picture = ""
8.   End Select
9. Else
10.   Me![imgSelectedFile].Picture = ""
11. End If
12. End Sub

[truthlover]

It worked great!

Now everything's doing what I wanted it to do.

Thank you sooooo much for all your help! Not only did you give me the information I needed, you did it in a way I could really make sense of.

If you dont already teach for a living, you should give it some serious thought.

-Barb

Originally Posted by ADezii

Yes there is. It may be confusing to explain the entire process in the Post, but it is exactly the same code in the Form's Current() Event posted to the Format() Event of the Detail Section of a Report. I've kept the naming conventions the same to make it easy for you, simply create a Directory (Folder) in the Root Directory of Drive C: named PFDBMPS, namely C:\PFDBMPS. Download the Test Database for this Thread and copy the *.bmps to that Directory. Click on the View Report Button on the Main Menu to see how it is done.

Expand|Select|Wrap|Line Numbers

1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
2. If Not IsNull(Me![txtSelectedFile]) Then
3. Select Case Right$(Me![txtSelectedFile], 4)
4. Case ".wmf", ".emf", ".dib", ".bmp", ".ico", ".gif", ".jpg"
5. Me![imgSelectedFile].Picture = Left$(Me![txtSelectedFile], InStr(Me![txtSelectedFile], "#") - 1)
6. Case Else
7. Me![imgSelectedFile].Picture = ""
8. End Select
9. Else
10. Me![imgSelectedFile].Picture = ""
11. End If
12. End Sub

[ADezii]

Originally Posted by truthlover

It worked great!

Now everything's doing what I wanted it to do.

Thank you sooooo much for all your help! Not only did you give me the information I needed, you did it in a way I could really make sense of.

If you dont already teach for a living, you should give it some serious thought.

-Barb

I'm glad it all worked out for you, truthlover. BTW, give yourself a pat on the back also for persisting until a solution was arrived at. As far as teaching, you are not gonna believe this one - I'm a retired Philadelphia Fireman after 32+ years who now works Security for a Performing Arts building (Kimmel Center).

[beemomo]

Hi , ADezii!

you are great! i try to search and google around to find a solution to make a browse dialog button for almost a day but i still cannot get what i want.

But your code does make what i want works in just a short time!! i can't imagine that you are a retired fireman and still willing to share your knowledge !

Thanks again!

[ADezii]

Originally Posted by beemomo

Hi , ADezii!

you are great! i try to search and google around to find a solution to make a browse dialog button for almost a day but i still cannot get what i want.

But your code does make what i want works in just a short time!! i can't imagine that you are a retired fireman and still willing to share your knowledge !

Thanks again!

Thanks for the cudos, like everyone else here at TheScripts, we're always glad to help ya!

[truthlover]

Originally Posted by ADezii

I'm glad it all worked out for you, truthlover. BTW, give yourself a pat on the back also for persisting until a solution was arrived at.

Sorry to bother you again, but I'm afraid I'm having a problem with the reports. The image is showing up, but the same image is showing up for all of the records instead of the proper record showing up for each report.

Am I doing something wrong?

Thanks

[rudeman76]

hi,

Is there a way to put the image onto a Word document or Excel spreadsheet?

Andrew

[ADezii]

Originally Posted by truthlover

Sorry to bother you again, but I'm afraid I'm having a problem with the reports. The image is showing up, but the same image is showing up for all of the records instead of the proper record showing up for each report.

Am I doing something wrong?

Thanks

Is [txtSelectedFile] a Bount Text Box in the Detail Section of the Report, since that is what the code is reading?

[ADezii]

Originally Posted by rudeman76

hi,

Is there a way to put the image onto a Word document or Excel spreadsheet?

Andrew

Kindly create a seperate Thread concerning this Topic.

[truthlover]

Originally Posted by ADezii

Is [txtSelectedFile] a Bount Text Box in the Detail Section of the Report, since that is what the code is reading?

I'm not sure. I did it the way you told me, so if that would create a Bound Text Box, then that's what it is. (I went back to look at the properties, but didnt see any clear indication if it is or not.)

One thing I found out, that might make a difference, is where the problem is occurring is when I run a report that includes all records.

The application is for Work Orders, most that will have corresponding maps. If I open the report for a specific Work Order #, I get the correct image displayed, but if I run a report of all open Work Orders, the image for the first open Work Order displays on all the Work Orders.

Thanks!

[ADezii]

Originally Posted by truthlover

I'm not sure. I did it the way you told me, so if that would create a Bound Text Box, then that's what it is. (I went back to look at the properties, but didnt see any clear indication if it is or not.)

One thing I found out, that might make a difference, is where the problem is occurring is when I run a report that includes all records.

The application is for Work Orders, most that will have corresponding maps. If I open the report for a specific Work Order #, I get the correct image displayed, but if I run a report of all open Work Orders, the image for the first open Work Order displays on all the Work Orders.

Thanks!

The code must reside in the Format() Event of the Detail Section, is that where you placed it?