FIle Open dialog and OLE objects

[Carthesis]

I have a problem that I have been struggling with now for a few weeks, and I've tried the majority of related solutions I seem to be able to find, practically none of which work, so I'm turning to the good folk of the tek-tips forums to help me out!

My quandry is thus:

I'm trying to put together a database that contains all the photos/videos that my company has for operations on one of our sites to make it nice and easy for people to find any relevant media that may be of use to them. Simple so far, right? If only...

I have a database that, basically, is a table [tblMedia] with three fields - an OLE Object field named [Preview], a text field named [Path] and a memo field named [Description]. Pretty obvious what each field does.

Now, the way I'd kill for this to work would be that there is a 'Browse...' button underneath the preview pane in the form [frmInput] that you press, and it brings up the old Open/Save dialog. You then browse for the file you want, and select it as per normal. Once you've selected, this file is placed in the OLE Object field (as a linked file), and a preview is displayed, while also the path is displayed in the text field below.

That way,
a) the system is pretty much idiot-proof
b) you can simply double-click the preview image to invoke the application linked to the program in order to get a full-size view, or to play the movie, or whatever
c) it also tells you where the file can be located.

Now, i can get this to work by using DoCmd to open the 'Insert Object' dialog, and then you have to go to the 'Browse' button, but I'd much prefer it if I could skip this imtermediate stage.

If anyone can explain to me, preferably in nice, simple, easy to understand terms that a poor structural engineer can understand, I'd really, really appreciate it, and would happily give them my first-born if they so requested!

I realise this is now a bit lengthy, so I'm going to end as soon as I've added

PLEASE HELP!!!

 

[CVigil]

Hi there! Open
thread181-396930
in this web site, and look for my (CVigil) post of Nov. 5, 2002. In it I've already posted all the code and explanations that I would post here. It's set up to let you click a button to bring up the Open common dialog, and auto-load the file selected into the field on the form. I used embedded files (not linked), because that's what the powers-that-be here wanted, but the change to using linked files should be a small one -- *probably* just a setting on the OLE field control itself. If you need any help with that, or with other stuff, just let me know here .

As for displaying the path to the linked file, I don't think that you'll even need to store it in your database. Since you are *linking* your files, the OLE field has to keep track of (store) the file location anyway. On your form, use a textbox control to display the file path. Assuming a textbox named txtBox1 and a bound OLE field named olePic1, use the property sheet in Design View to set txtBox1's .ControlSource property:

     txtBox1.ControlSource = olePic1.SourceDoc

The biggest problem I actually had in using the OLE field, was getting different kinds of files to display well in the field. I never really solved the problem; instead, I avoided it by only using file formats that displayed well without my having to do or install anything new. For instance, you'd think that MS-Access (97) would display bitmaps, JPEGs, GIFs, Metafiles, and perhaps other common graphic formats, well and automatically when they appear in an OLE field. Not so. And I never figured out what I'd need to install (and make sure is installed on every computer using the database) to get the database to display such things. Instead, what we wound up doing was storing Office (Word and Excel) files in the OLE field. Access gets Word and Excel to display their files just fine in the field; and Word and Excel, in turn, display many graphic formats well and correctly. This solution actually gave us another bonus: We can add arrows and callouts and textboxes and other nifty things to the drawings in the Office files, from within Access.

However, with regards to the topic of the preceding paragrah ... If you figure out, or know, how to deal with what I described, *please* post it back here for myself and for future readers!
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[Carthesis]

Thanks very much! I shall try this very approach first thing in the morrow, after my beauty sleep, and let you know how it goes. I may have questions.

As to your problem, I've never noticed the problem. We're using the almighty Office 2000 suite here, and it seems to display JPGs fine - I had some system up and working, but it was a bit naff, but the pics displayed ok. The only problem was the scaling detail and jaggies, but even they weren't too bad.

PS - do you have any ideas about how i could convince a movie clip to play in a form, rather than having to invoke a separate application?

 

[CVigil]

I'm using Access 97, and Office 97 (w/o Access). This may cause some differences in what we experience -- just mentioned to cover our bases and keep us aware of what's going on .

Displaying pictures, showing movies, editing Word or Excel files -- it's all part and parcel of the same, in one very real sense, when we're discussing storing them in an OLE field and showing them in an OLE control. In every case, they're actually being displayed (which can be a catch-all term for display/show/open/play/...) by an OLE server. The OLE server is just any application that can handle the files and that is also set up to allow interaction with other programs.

When I have Word docs showing in my OLE control, Access is getting Word to display the document in Access' form. If I let the user edit the document -- inside the boundaries of Access' form, or outside in a separate instance of Word -- either way, *Word* is actually handling the user's interaction with the document, not Access. Same goes for pictures, movies, gewgaws, Things, all OLE objects; the OLE server handles them for Access.

By the way, when an object is being activated within Access' window, the server's menus might be mixed in with Access' menus, so stay aware and look at the menus carefully . Same goes with toolbars.

The trick is having a program that will act as an OLE server for the file type in which you're interested. The OLE server can handle displaying the file, either in a recogniseable form, or as an icon, and should expose an arbitrary number and selection of *Verbs* or *Actions*. These are what you see when you right-click an OLE field and look in the Object's sub-menu -- choices like "Open", "Edit", "Print", and more. The choices change with the type of file, and with the OLE server being used; some servers might expose different choices than others. Read the Access help on the OLE controls for more info on Verbs/Actions.

When you look at my code in the post I referenced earlier, you'll see references to the properties that allow you to manipulate and make use of the objects' Verbs/Actions as well.

Now, with the background set, to actually answer the question in your P.S. You have to invoke a separate application anyway, since Access won't handle playing movie clips natively. BUT ... you are really asking whether we can get it to play inside the Access form, rather than in a stand-alone instance of the other application (the OLE server). In other words, "Can we make it look to the user like Access is playing the movie?" I'll bet the answer is, "Yes," so keep looking for the solution.

I just tested by putting C:\WINNT\clock.avi into my form's OLE field. (Yeah, instead of what I usually put there; the field doesn't care -- it'll hold any file!) And I right-clicked and chose the 'Play' option under the 'Video Clip Object' sub-menu. It played. There were also 'Edit' and 'Open' options. So I already have a player/OLE server installed that handles .AVI files! In my case, Windows Media Player steps in to handle the job.

(Grumble, grumble... .GIF and .JPG and .BMP files display well in my form now, when they didn't before. I tested for the heck of it. The grumble is only because I don't know why <wry grin> . Maybe I got what was needed when I installed Internet Explorer 6 a few weeks ago; this is the first time I've tried in quite a while. Still can't view Windows Metafiles, though! But I don't have a viewer or them *outside* of Access, either. [You'd think that Windows would *come* with a viewer for *windows* Metafiles, wouldn't you?] But then again, I *always* had programs that would view .JPG, .GIF, and .BMP files. What suddenly set something up as an *OLE server* for me, I don't know. Ah well, now you begin to see some more of my mystery...)

So .AVI files will play on my WIN NT 4.0, SP6, system, with Access 97 and IE 6 (and other stuff) installed. Try it on your system! Then try it with other kinds of media files. Maybe QuickTime or RealAudio/RealVideo or some other apps will take care of your various movie formats. Maybe even Windows Media Player itself. As my grandfather used to never say, &quot;It all depends on whatever it depends on, kid.&quot;

Be aware that the results may differ from computer to computer, based on the software and environment installed. In the place you're doing your current work, ask yourself how much you know, or can count on, regarding the users' computer set-ups. You might need to go somewhere to change a program association, or install a new program, depending on the choices you make now. As much as possible, depend more on what you know is likely to be on the largest number of systems already. And if you just want to show or play a file, then which program is used on an individual's machine may not matter as much as if you want users to be able to edit. (Think Word vs. WordPad, vs. Word Viewer. All will show a Word doc decently, but the editing capabilities differ.) But then, maybe it will, too.

For the most part, I think, whichever program is associated with the file type in the Registry -- whichever program automatically starts when you double-click the file's icon in Explorer, for instance -- is the one that will start up to try handling the file. You might have a number of programs that can handle showing bitmaps, for instance, but only one will start as the OLE server for bitmaps.

So, there you go. The place to start really *is* just to drag-and-drop (or use my code and buttons) to get different media files into your OLE field before anything else. Then activate them and see what happens. Go from there.

I hope this helps .
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[Carthesis]

Thanks for that! I have no idea how long it must have taken to type that little lot in, but cheers!! The majority I was already aware of, and I was in fact just asking if I could con the user into thinking Access was playing the clip....possibly through some kind of ActiveX control....hmmm....

As for the rest, AVIs, BMPs, JPGs, GIFs and the rest all work ok. Not tried metafiles, sorry. I tried your code and hit some problems though.

a) I'm assuming the function I actually need to get a file and insert it into an OLE object field is the cmdGetFileForOLEobject_Click() function. However, it won't let me select that in the RunCode option for a macro. Does this need to be changed from a Private Sub to something else?

b) Every time I try to compile the code, I hit the snag that it throws up every use of the 'me' keyword. It doesn't like it.

c) If I paste in the code 'as is' and try to use it, it throws up various errors when I execute it.

Am I missing something obvious?

 

[Carthesis]

And in response to my previous post:

a) Ignore most of this. I'm being stupid. I need the ShowOpenFileDialog() function, and this calls the other one as a sub procedure, right?

b) using Me.[oleObj] - Access does NOT like this at all. Everytime I've tried to use the Me. function, Access complains. So I changed it to [Tables]![tblMedia]![oleObj], and i got that [Tables] was a &quot;Compile error: external name not defined&quot;. I then tried removing the [], and got &quot;Compile error: variable not defined&quot;. Leaving it as Me.[oleObj] gives &quot;Compile error: invalid use of Me keyword&quot;. I also get exactly the same if I leave out 'Tables' altogether - it just complains about the next part.

c) If i just use the code as I cut and pasted it in from the thread ID you gave me, upon running any section of the code, I get &quot;Compile error: Only comments may appear after End Sub, End Function or End Property&quot;. If I then move the code starting from the 'Option Compare Database' to the top of the code and run the 'ShowOpenFileDialog' function, I get a VB action failed message box, which after halting the code, tells me that the 'Expression you entered has a function containing the wrong number of arguments'

What am I doing wrong here? HELP!!! Starting to loathe and despise Access as the spawn of the Anti-christ!

If you can't suss this, is there any chance that I could just get a 'blank' copy of the DB you use that I can just modify?

 

[CVigil]

I can suss it all just fine, and it sounds like your only fault is that you're just beginning to learn about programming in Access with VBA. And that isn't a fault. That's just &quot;starting out&quot;. Read this, and you'll be much more fine; hopefully then, Access will look less like the anti-Christ and more like, ummm, an interesting maze that you're beginning to map?

Sorry, I can't help but chuckle (at our simple humanity) . One of us did miss something obvious, and without going back to check, I actually don't know which of us (heh) . A third of that code goes into the module of a *form*! (All the code that includes the keyword 'Me'.) I haven't gone back to review my other post, so I don't know if it mentioned a form, or if that was part of the context in the thread, but whatever . The other two thirds go into separate modules. These were the key facts you missed. More follows a little further down.

The Me keyword is reserved exclusively for use within Forms and Reports, and is a way to refer specifically to &quot;the particular instance of the form/report running the code containing this Me&quot;. So if you have multiple instances of a &quot;Display Book Summary&quot; form running at the same time, if the user clicks a button on one of the instances of the form, a 'Me' in the code of that button would refer to the form instance on which the button was pressed, not any of the other instances. And if they clicked the &quot;same&quot; button on a different instance of the form, the 'Me' in that button's code would refer to *that* instance of the form. And so on. Does my explanation make sense? If so, then you now know something more about form/report programming.

Those procedures that begin with &quot;cmd&quot; and end in &quot;_Click()&quot; are the Click event handlers for a CommandButton. (Thus, &quot;cmd&quot; as the prefix; this is part of a standard nomenclature for naming VB/VBA controls/objects/variables/things. Look around for information about the &quot;Reddick naming convention&quot;. This convention helps to make code self-documenting, if it is followed consistently, and if the reader knows the convention, of course.)

So &quot;cmdGetFileForOLEobject_Click()&quot; is the event procedure (yes, a sub) for a CommandButton called &quot;cmdGetFileForOLEobject&quot;. (If I just type &quot;button&quot; at some point further along in this post, I probably do mean &quot;CommandButton&quot; rather than &quot;ToggleButton&quot; or some other kind.) &quot;Event handler/procedure&quot; means &quot;the code that runs when the event in question is triggered&quot;. In *this* case, the code that runs when the button is clicked.

Nothing I've posted has anything to do with, or any need for, macros. And in fact, I don't use macros, because VBA is much more powerful and flexible, since I know how to use it well. (No condescension, just information.)

cmdGetFileForOLEobject_Click calls the ShowOpenFileDialog function to display the Open dialog (the Common Dialog control, in 'Open' view). So the user clicks the button, gets a dialog in which they can browse to a file -- you will want to change the default starting directory and the list of filters the dialog uses, for your project -- and the program loads the chosen file into the OLE field.

The 'Edit' button, whatever I called it, works in conjunction with the 'In Place/Outside Access' toggle button immediately next to it. The 'Edit' button opens the file/object contained in the OLE field for editing, or activates it. When it goes to do so, its code checks the state/value of the ToggleButton and uses that to decide whether to do the editing in place, inside Access' window and my form, or outside Access, in a stand-alone instance of the editing application.

So you need a form, and two separate modules. My code has comments in blocks made of equal signs, telling you how to divide up the code, which sections of code go into which modules.

0) First note that

    Option Compare Database
    Option Explicit

really belongs at the start of each of the modules (including the form module). It shows up in my copy of the mdlOpenFileDialog code because I copied that entire module; it doesn't show up in the others, because I only included the necessary relevant portions of those modules instead of a Select All and Copy & Paste. (The other modules also included code that had nothing to do with the OLE field.)

1) The form has to have a bound OLE control, two CommandButtons, and a ToggleButton, for my code to work. Also, the control names in the code and on the form have to match, of course. (If I just missed mentioning a control or something, it will come up unrecognised when you compile, and you should be able to recognise the control name after reading about syntax and exclamation points further down; and I'm happy to answer regarding them, too.)

2) The ShowOpenFileDialog() function and attendant code belongs in its own module, mdlOpenFileDialog.

3) The name of the second module is mdlUtilities, and it contains -- you guessed it -- some code of general utility, my Replace() function.

I *think* I specified these things in the post containing the code, but I'll go back to check. ... Yeah, here's a relevant portion of the post, right near the top: &quot;Here's a bunch of code, separate subs and functions, from a form I built. I also included the code from outside the form module, which is invoked by the form code.&quot; Don't worry too much about missing it. We all get in a hurry and skim or miss stuff from time to time.

Yeah ... read all the intro paragraphs; they explain what the code is doing and how. The code alone isn't quite enough, especially if you take it to be a single piece. But do keep writing back if you have other questions!


As for the Me![object name] syntax:
You typed &quot;Me.[oleObj]&quot;, but I used &quot;Me![oleObj]&quot;. This is an *important* difference when reading the code. The exclamation point is better documentation *and* better code. (You *can* use a dot instead of an exclamation point, but it isn't good. Using the dot while typing the code in the VBA editor gets Access to list all of the properties, methods, and controls of the form for you so that you don't have to type or recall the whole name of whatever you whichever one you want to reference. But the exclamation point specifically references the default collection, instead of adding a level -- referencing the default property, which returns what-happens-to-be-the-default-collection. Clear as mud? Read it again after reading the next two paragraphs, which explain more.)

Okay, so … You now know that Me refers to a specific form (the one running the code containing the Me). The brackets are *required* if the field/control name inside them contains a space or a pound-sign, but I always try to use them whether required or not, for consistency's sake, and as a visual documentation. They are used for control/field names, but not for anything else like property or method names. In this way, the brackets provide good visual documentation; what they enclose is a field or control name.

Detail about the exclamation point:
(Actually, *after* typing what follows, I found a succinct description in the help, looking in the index under &quot;! (operator)&quot; then choosing &quot;Use the ! and . (dot) operators in expressions&quot;. Read that. But after already having typed what follows, I'm leaving it as a supplement to the help.)

What the exclamation point actually signifies is that what precedes it is a collection and that what follows it is a member of the collection named before the exclamation point (i.e. [Collection Name]![Member Name] shows that the thing named Member Name is a member of the collection named Collection Name). In Access, this syntax applies to all collections and their members. Since Access allows default properties/collections, sometimes you see something named before an exclamation point that isn't a collection -- but in these cases, it is implicitly assumed that the default collection is being used, preceding the exclamation point -- in effect, the collection reference is there, even though you can't see it. In the case of Forms, I quote the help for the Form object:
----
Each Form object has a Controls collection, which contains all controls on the form. You can refer to a control on a form either by implicitly or explicitly referring to the Controls collection. Your code will be faster if you refer to the Controls collection implicitly. The following examples show two of the ways you might refer to a control named NewData on the form called OrderForm [Note that the Forms collection is a collection, too, and so also uses the exclamation point. -- CVigil]:

' Implicit reference.
Forms!OrderForm!NewData

' Explicit reference.
Forms!OrderForm.Controls!NewData
----
The default property of a form is the .Controls property, which returns the Controls collection, as I mentioned earlier. So Me.[controlname] works, as it implies the .Controls property which returns the Controls collection of which controlname is a member -- but Me![controlname] is actually preferable, and better. And when you see the brackets, you can be sure that what's inside is not a property or method name, but a member of a collection; in the context of the form reference (Me), it's a field/control name. So you see, even if the brackets aren't necessary (I avoid spaces and pound signs in names at all cost), they are useful and brief visual documentation.

That's all I can think of for now... I think I've provided what you need to try again and move forward. And don't worry; from my side, it's clear you'll get there.
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[Carthesis]

Good news!! I got there. Got the thing working perfectly!

I have now but one problem.....
I set everything to use linked OLE objects rather than embedded ones, and tried inserting a picture. Database size went from 160Kb to 1.94Mb (image size was 161Kb). Inserted another picture (image size 182Kb), and size went up to 3.92Mb. Cleared database, set back to embed, and tried again. After first picture went in, file size was a nice tasteful 7.64Mb.

Tried banging in a Word document. Database size went from 160Kb to about 165Kb. This is all very odd. How come the file size of the database increases so much even when the files are linked?

Even stranger, the file size went up by the same (roughly) amount, no matter what size file you inserted. Inserting a 20Mb movie clip put database to same size as 161Kb JPEG. Any ideas? Anyone?

With files this big, I'm going to have to cheat and not be able to use OLE Objects after all, which will make me sad. Unless someon has another way to invoke a separate application to open the file (like auto activate, but not using the OLE field.)

Or, alternatively, can I use the 'Browse...' code to write the path to a text field, and then use an unbound OLE object to view the picture? I actually tried this, but couldn't get the Unbound OLE Object to accept the SourceDoc as [FilePath].

 

[CVigil]

I don't really know much about the file size issue. I do know that you can compact the database size, which does not actually fix the problem. The fact that -- using linked objects (rather than embedded) -- the file size jumps roughly the same amount for all objects ... this doesn't surprise me too much. A reference to one thing shouldn't take up a very different amount of space than a reference to a different thing; memory/disk addresses are memory/disk addresses, after all. The field may store some info about the file itself which could vary from one type to another, but I'm not too surprised that the space used is fairly consistent. I am (somewhat) surprised (perhaps dismayed is a better word) that it takes so *much* room.

I *did* find this web site a couple of days ago, as a result of reading another post here:

http://www.unsoftwareag.com/

Look specifically at:

http://www.unsoftwareag.com/Faq_core.asp?FAQSTRID=G11

which relates to your problem/ This company claims to have products that eliminate the size bloating problems associated with storing objects in Access OLE fields. I haven't tested their products, looked at them, or found myself too interested (no real need for me); there were a few things in the web site that did not impress me, and I didn't bookmark them for future use (good thing for you they were still in my history, though .) But they claim to have working products that you can use to eliminate the problem, so you might want to at least read and decide for yourself. Browse their site's advertisements/articles, and see what they have to say. And, given the idea, you might want to see if anyone else has come up with solutions, maybe even *free* ones [greedy] !

You wrote: &quot;Or, alternatively, can I use the 'Browse...' code to write the path to a text field, and then use an unbound OLE object to view the picture? I actually tried this, but couldn't get the Unbound OLE Object to accept the SourceDoc as [FilePath].&quot;

The 'Browse...' function returns a string (which is the path and filename of the chosen file). That's it. You can do anything you want with that string. You can even use the code line

Me![txtPath] = ShowOpenFileDialog("*.*")

to enter the path and filename directly into a TextBox named txtPath. Follow that line of code with *code* that sets the properties of the unbound OLE control, and you're golden!
***Taking your word for the correct property to use***:

Me![olePic1].SourceDoc = Me![txtPath]
'...whatever else you need to set and do...

In other words, if both pieces of code are in the same Sub, then the TextBox and the unbound OLE control are filled at the same time. The other thing to do is to add to your Form_Current event handler; add the same code that uses the TextBox's value to set the unbound OLE control's properties. Since it's the same code, encapsulate (put) it into a Sub of its own, which would be called from Form_Current and from the procedure that sets the TextBox's value from the Open dialog.

The advantage to this method is that, since you're keeping your files outside of the database *anyway*, all you need to store in the database is a string! No harsh bloating at all.
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[Carthesis]

Doesn't matter. I changed my mind and did it a different way. I set the Open File Dialog to save the path to a text field called [FilePath], and then used an image frame to display the picture which is loaded through the forms OnCurrent() event. Means that the database is tiny and just loads the pictures when you access them....perfect! I even got a button written which uses the file path to open a specific application (Photo Editor), and then load the file listed. It's great!

However, my one last question is thus...how do I modify the open file dialog code so that it defaults to the last directory opened in the session? For example, the default directory is O:\Photographs, so I hit 'Browse...', and it displays the contents of O:\Photographs. I then browse to O:\Photographs\FolderXYZ and select 'abc.jpg'. This is then displayed and all the info for that file is displayed. Then I put in another entry, and the open file dialog opens to O:\Photographs\FolderXYZ as opposed to O:\Photographs.

I hope that's clear.

 

[CVigil]

That's clear . And the solution is simple. The Open File dialog (the common dialog) remembers the last chosen path for you. As long as you do *not* specify an initial directory, the common dialog &quot;stays where it was left&quot;, and displays the last directory chosen when it is opened again.

So the first time it is opened, use O:\Photographs as the initial directory, and thereafter use an empty string or nothing.

lpstrInitialDir As String

is the relevant parameter in the structure that gets passed to the API that opens the common dialog. It is set in the function we use to access the API.

Function ShowOpenFileDialog(ByVal sFilter As String, Optional ByVal sDefExt As _
    String, Optional ByVal sInitDir As String, Optional ByVal lFlags As Long, _
    Optional ByVal hParent As Long) As String

shows us that the 3rd parameter in the function call is the one that sets the initial path for the dialog. All you need to do is invoke the function each time, specifying the path only the first time.

SO (ignoring other parameters for these examples) ... you call the function once:

Me![FilePath] = ShowOpenFileDialog(,,"O:\Photographs")

And say the user chooses O:\Photographs\FolderXYZ\abc.jpg. That is what becomes the value of strPathFile.

So your all subsequent calls should (work out to) be:

Me![FilePath] = ShowOpenFileDialog(,,"")

This essentially *lets* the common dialog open to the last chosen directory each time. The second call to the function would then open to O:\Photographs\FolderXYZ\ . And so on.

So your button code just looks something *like* this:

Private Sub cmdButton_Click()
    'Static variables' values are not lost between calls to this function.
    Static fDialogUsed as Boolean 'Has common dialog been used yet?
    Dim strPathOnly as String

    If (fDialogUsed = False) then
    	  strPathOnly = "O:\Photographs\"
        fDialogUsed = True 'Now, it's been used, and always will have been.
    Else
        StrPathOnly = ""
    End if

    'MAKE SURE to fill in other parameters, too!
    '   this is just an example regarding initial path!
    Me![FilePath] = ShowOpenFileDialog(,,strPathOnly)
    '* You could also eliminate the variable strPathOnly by putting its assigned value
    '* directly into the ShowOpenFileDialog call's 3rd parameter.  You could.
    
    'DO STUFF TO LOAD YOUR IMAGE CONTROL USING THE FILE AND PATH JUST COLLECTED.
    'DO THAT HERE.
    '[...]
End Sub

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[Carthesis]

Ok...
I had a think about this, and realised that the same thing could be accomplished by simply removing the InitialDir = O:\Photographs line. This now just initially opens the File Dialog to the folder where you were last looking, which is great!

However, this isn't what I was wanting, and there's no way you could have known that, due to me not actually SAYING it in my last post! D'oh!

What I'm wanting is for any particular instance of the database to initally open to O:\Photographs, you then select a sub-directory, FolderXYX, and then select a file, abc.jpg. You then, for example, close the database, and then re-open it, and when you go to 'Browse...' again, the file dialog opens the O:\Photographs folder again...

So what I need is for any particular instance of the database to remember where it was, but then for that to be wiped and start again the next time the database is opened....

I'm always convinced that these explanations I give aren't very good, so I hope thats clear enough for people to muddle through! I'm going to include the code I'm using at the minute, so that we aren't talking at cross purposes..

Private Sub cmdGetFileForOLEobject_Click()

'Lets user select a file with the Common Dialog (Open), and
'puts a file path for that file into the [FilePath] field,
'and then uses the form OnCurrent property to update the
'[Image] field.Dim strFilePathAndName As String
    
    Dim strSaveCaption As String
    Dim strPartNoAsterisk As String

    strFilePathAndName = _
        ShowOpenFileDialog("JPEG Graphics Files (*.jpg)|*.JPG", _
                           "", "", _
                           OFN_FILEMUSTEXIST + OFN_HIDEREADONLY)
    
    If (strFilePathAndName <> &quot;&quot;) Then
        strSaveCaption = cmdGetFileForOLEobject.Caption
        Me![FilePath] = strFilePathAndName
    End If

    If (Me![FilePath] <> &quot;&quot;) Then
        Me![Image].Picture = Me![FilePath]
    Else
        Me![Image].Picture = &quot;&quot;
    End If

End Sub

Thanks in advance for tyhe great help I know you're going to give me!

 

[CVigil]

Okay, let's just start ticking through points without more preamble...

strSaveCaption = cmdGetFileForOLEobject.Caption

seems to have no purpose in your code. The variable is declared as local to the Sub, non-static, and isn't used again in the context of the Sub, anyway. It's wasted code and time, even though not much.

If the user presses Cancel on the common dialog, it returns an empty string, &quot;&quot;. What do you want your app to do when the user presses Cancel on the common dialog? Right now, it does *not* change the value displayed in Me![FilePath], but it *does* clear the picture. That seems inconsistent to me. I don't know if you are intending the form to be used in a record by record &quot;visual scan&quot; fashion (are you storing the file names anywhere?), or if the form is static and the user should just keep choosing different files to preview, or if something else (always have to allow for that possibility). So do you want a Cancel on the common dialog to do *nothing*? If so, put all processing into the &quot;if the common dialog's return is *NOT* an empty string&quot; block. Or do you want it to clear both the Me![FilePath] TextBox and the picture? If so, put code that clears both controls into the &quot;if the common dialog's return *IS* an empty string&quot; block.

In fact, it simplifies more than that, if you want a Cancel to clear both. In that case, all you need is two lines and no Ifs:

    'whether the user chose something, or nothing...
    Me![FilePath] = strFilePathAndName
    Me![Image].picture = strFilePathAndName

And if you want to make a change if the user chooses a file, but want to do nothing if the user presses Cancel, all you want is a single If block:

    If (strFilePathAndName <> &quot;&quot;) Then
        Me![FilePath] = strFilePathAndName
        Me![Image].picture = strFilePathAndName
    End If

This leaves the previous choice intact if the user presses Cancel.

Now. Back on to your main request :
&quot;What I'm wanting is for any particular instance of the database to initially open to O:\Photographs, you then select a sub-directory, FolderXYX, and then select a file, abc.jpg. You then, for example, close the database, and then re-open it, and when you go to 'Browse...' again, the file dialog opens the O:\Photographs folder again...&quot;

I ask if you want the form to reset each time the *database* is opened, or if you want the form to reset each time the *form* is opened.

If you want the reset to happen each time the *form* is opened, then I've already given you the code in my last post. That static boolean variable fDialogUsed keeps track of whether the dialog has been used since the form was last opened (during the current form session). Note that if the form is the only form in the database and if it is always open, then &quot;each time the database opens&quot; *is* &quot;each time the form opens&quot;.

Now, if you were right next to me and I was teaching as if in school, I'd ask you, &quot;Now how could we keep track of the same thing handled by fDialogUsed, but for the whole time that the database stays open?&quot; and wait for you to start to think of the answer -- I'd try more to help you come to it. (Granted, I don't know how much you've learned already, what's fair to expect, so I account for that, too.)

The answer I'd then give:

Okay, what the &quot;static&quot; keyword achieves is to keep the variable around as long as the form is open, as if it had been declared globally within the form's module -- in fact, the only tangible difference is that as-is, fDialogUsed can only be used by the command button's procedure; whereas if it were declared globally within the module, it *could* be used by any procedure within the module, whether you chose to or not.

You declare something globally by declaring it in a module, but before any procedures (this is called the &quot;Declarations section of a module&quot. Use either 'Dim' or 'Private' if you want it to be global only to the containing module, or (within a regular module) 'Public' if you want it to be global to the entire database. Note that 'Public' in this context *only* has effect in a regular module; if used in a class module (for instance, in a form module), it does not cause a compile error, but still only allows the variable to be used within the class module itself.

If you declare a variable with the 'Public' keyword in a regular module, it is available to every other module in the database, regular or class. (This means in particular, that you can use it in your procedure on your form; I trust you know the ones I mean . You don't have to use it anywhere else.) And it stays around for the entire time the database is used.

So remember the question we started with, way back a long time ago, five paragraphs previous: &quot;Now how could we keep track of the same thing handled by fDialogUsed, but for the whole time that the database stays open?&quot; If we can answer that question, you can use the same code I gave you already, with only slight modification.

We can answer the question now. Use a *global* boolean variable, *instead of* the local one. Call it the same thing, fDialogUsed, so that you don't have to change the references to it. Specifically, change Static to Public in the declaration of fDialogUsed, and then move (don't copy) the line to a normal module. It can even be the only thing in the module. When the database opens, the global variable is available, initialised to False. Thus when the database opens, it tells us, &quot;Dialog has been used yet/already: FALSE!&quot; so that our button knows to force the initial path to O:\Photographs . Your form's button's code is the only code that deals with the variable; again, go back to the code *I* gave you, changing whatever variable names, etc., are necessary to make it compatible with your project. The code I gave you already deals with checking the variable's value to see if &quot;this is the first time the common dialog has been used&quot;.

When fDialogUsed is declared locally, that's &quot;the first time since the form was opened&quot;. When fDialogUsed is declared globally to the database, that's &quot;the first time since the database was opened&quot;. Both of these statements are true because the value is only changed *if it has not been changed yet* (knowing/relying on the fact that the initial value of a boolean variable is false; if we needed a different initial value, we would somehow set it immediately after declaration.)

So there ya go. Not just the answer, but education and understanding to go with it, for you and future readers. I can't teach you to fish, but I know something about Access and Visual BASIC .

Have a good one. -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot