Need "Browse for File" button

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.

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

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?

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.

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?

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:

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?

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.

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.

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:

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

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

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.

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.

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).

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!

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

hi,

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

Andrew

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

Kindly create a seperate Thread concerning this Topic.

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?

Yes, it's in the On Format() Event in the Detail section of the report.

Something thing I just noticed is not only is first Work Order is the only one that displays the proper image, it's also the only one that displays the link to the file. The rest of the records it displays nothing (I'm guessing that's why the images arent updating)

Hi ADezii,

Any ideas on the solution yet? The database is going live today so I need to get this resolved soon.

Thanks!!

Were valid Links created for the rest of the Records? The problem can't be that complicated, and I honestly cannot see how the DB can go live while this little bug still exists. Is there any chance that you can E-Mail the DB or a subset of it to me as an Attachment? If so, I'll send you my E-Mail Address in a Private Message, and then you can Upload it to me. II will look at it ASAP.

Yes, I can upload the DB to you. It's about 3mb.

As for going live with it, though the bug definitely needs to be fixed asap, it does not adversely effect creating the work orders and monitoring everyone's workload, which is something we needed up and running two weeks ago. And where a hard copy of a work order is needed, as long as it's printed out as a single record, the image prints properly

I cant tell you how much I appreciate your help. I couldnt have completed it without you.

Thanks so much for the file. Havent had time to post response. Will get to it later this morning.

Ok, I looked at the file, and i see the report you created out of the Images table, but the reports I am having problems with are still doing the same thing.
The report I need fixed is "rpt_SurveyWorkOrder(Current)". Sorry, I should have been clearer about which report was the problem.

I sent a revised Database to you @ 2:15 P.M.

Hi ADezi

Sorry I didnt post sooner, but I was a way from the computer for a while.

Anyway, all the images are displaying properly Thank you so much.

One question, though. The records with no links are properly not showing any image, but it appears that the space for the image is retained. Is there any way of getting rid of that so it doesnt print two pages unnecessarily?

And if you could tell me how to get rid of that process dialog that would be fantastic (will it also speed up the launching of the application?)

Thanks again and enjoy the rest of your weekend.

Not really sure, but when I get a chance, I'll see what I can do.

It will speed up the opening of the opening of the Report and the Application itself when the *.jpgs are loaded into the Image Control, but be very careful since the fix does involve editing of the Registry.

'Reference the following Link, specifically the Post by me (ADezii) dated January 18, 2008 12:57 A.M.
The BLOB (not the Movie)

Your problem with the blank 2nd Page can be easily solved by setting the Top and Bottom Margins of the Report to the absolute minimum (set both to 0, Access will re-adjust and Fix), and removing the Page Footer, and possibly reducing empty spaces between controls. Now, everything will fit on a single Page. I've tried this and it does work.

That works as long as the image stays the size it is, but the size of the image actually should be bigger than what I have there now. I dont imagine there's a way to have the image control expand or contract with the size of the linked image, is there?

Thanks!

Modifying the Clip, Zoom, and Stretch Properties, you can vary how a Graphic Image is displayed within the Image Control. The Image Control, unfortunately, will not adjust to the size of the Image.

I figured as much.

Thanks again for all your help. I know this wasnt anything like fighting a fire, but you're still a hero in my book!

Thanks, truthlover, the pleasure was all mine.

Hi ADezii,

Sorry to bother you again, but is there any way to get this application to allow multiple files to be attached?

Thanks!

The AllowMultiSelect Property of the FileDialog determines if multiple files can be selected and you'll have to readjust your code to place these files into a Variant Array, but I do not see how this is relevant in your specific case.

Actually, you're right. It doesnt directly apply to the control that allows the map to be displayed, but they did ask that multiple related files (of all types) could be "attached" so anyone referring to the work order could access the related files from one place.

So what would I do? Create a separate control that doesnt display anything? Would I store it in the same table as the images, or should i create a separate table?

Thanks!

Here is a code segment which will:

1. Allow you to select Multiple Files.
2. Check every Selected File, and if it is one of the specified Formats, dynamically loads its Path into an Array.
3. The prior contents of the Array are Preserved and it is Re-dimensioned.
4. All the Selected File Paths are now contained in the Array arrsGraphicFiles().
5. List all these Files and their Paths to the Immediate Window.
6. To test the code, I selected hundreds of Files in the Windows Directory, 6 of which were of a Graphic nature (*.bmp).
7. The results are listed below, as well as the code.

OUTPUT: