rmbonds
asked on
VBA event procedures to improve ATTACHMENTS control on MS Access 2007 form
Greetings. I have an Access 2007 database with several different forms, each of which has an "attachments"-type control that the user can double-click to browse for and select a file to be associated with the current record. (In this case the files are always JPGs, to display an image for the record on the form.)
If possible, I would like to improve how this control works by writing one or more VBA event procedures. Two questions:
1. When the user double-clicks on the control, the "Attachments" dialog box pops up, with the title "Attachments." Is there some way to change this title to something friendlier or more self-explanatory, such as "Click ADD to select an image" or something like that?
2. Then, in the "Attachments" dialog box, the user clicks on the "Add..." button, which opens a Windows-Explorer-style "Choose File" dialog. Is there some way to set the default folder that opens at this point, so that the user doesn't need to click again and again to navigate to it?
Thanks,
Robert Bonds
If possible, I would like to improve how this control works by writing one or more VBA event procedures. Two questions:
1. When the user double-clicks on the control, the "Attachments" dialog box pops up, with the title "Attachments." Is there some way to change this title to something friendlier or more self-explanatory, such as "Click ADD to select an image" or something like that?
2. Then, in the "Attachments" dialog box, the user clicks on the "Add..." button, which opens a Windows-Explorer-style "Choose File" dialog. Is there some way to set the default folder that opens at this point, so that the user doesn't need to click again and again to navigate to it?
Thanks,
Robert Bonds
Rey Obrero (Capricorn1)
afaik, that "Attachment form" is like the MsgBox form, and there is no direct way to change those properties.
ASKER
Thanks, capricorn1, but in VBA you can change both the title and the content of the MsgBox form. So I'm hoping there's a way to do something similar with the "Attachments" dialog box. -- Robert Bonds
I too find that this dialog box is a bit "unwelcoming", ...hopefully they will improve it in future versions...
Do the images *have* to be stored in the DB?
In some cases using "Linked" Images might be a better option.
Most code to do this allows for customization of the "Save As" dialog box.
See here for more info:
http://www.mvps.org/access/api/api0001.htm
attached is a sample file that illustrates one technique for linking images with a custom dialog box.
Also worth a look is this program:
http://www.ammara.com/dbpix/access.html
;-)
JeffCoachman
Access-BasicLinkFormReportImages.mdb
untitled.JPG
Do the images *have* to be stored in the DB?
In some cases using "Linked" Images might be a better option.
Most code to do this allows for customization of the "Save As" dialog box.
See here for more info:
http://www.mvps.org/access/api/api0001.htm
attached is a sample file that illustrates one technique for linking images with a custom dialog box.
Also worth a look is this program:
http://www.ammara.com/dbpix/access.html
;-)
JeffCoachman
Access-BasicLinkFormReportImages.mdb
untitled.JPG
ok, sorry about the direct comparison to MsgBox,
i should have said,
"where, certain properties like Font Style can not be changed."
the title and contents of the MsgBox was exposed as options in creating the message, and this is not available in the "Attachment" dialog form.
i should have said,
"where, certain properties like Font Style can not be changed."
the title and contents of the MsgBox was exposed as options in creating the message, and this is not available in the "Attachment" dialog form.
Yes, cap, thanks for the clarification.
You are correct, many people incorrectly think that just because you can invoke the msgbox object and customize it, they think that all dialog boxes can be manipulated to the same extent.
So while you can customize the message box to a certain extent, you need API code to Invoke/modify common dialog boxes like "Save As".
And still others, like the "attachment" dialog box, may not be able to be changed at all.
Jeff
You are correct, many people incorrectly think that just because you can invoke the msgbox object and customize it, they think that all dialog boxes can be manipulated to the same extent.
So while you can customize the message box to a certain extent, you need API code to Invoke/modify common dialog boxes like "Save As".
And still others, like the "attachment" dialog box, may not be able to be changed at all.
Jeff
ASKER
Jeff, thanks for the clever database file. Much appreciated. Yes, that's certainly one way to do it!
Your solution is similar to many I've used in the past, where a specific image appears on the screen when the form displays that record, based on the value of a field in the record that stores the location of the image. Alas, I can't use that approach this time because I'm developing this database for multiple users who will be passing it around, and the relative location of the stored images will be different from user to user. That's why the "attachments" control option in Access 2007 is attractive: once the image has been located, it's stored in the database with relatively little bloat to the database file. And the image stays put even if the original image file is deleted, moved or renamed.
You mention that the attachment dialog box "may not be able to be changed at all." Yes, that's what I'm trying to find out: whether, through one or more VBA event procedures, I can modify the way it displays. Actually, while changing the title of the dialog box would be nice, it's not essential... but directing the user to the correct folder as the default option IS important, since some of my users won't be comfortable with navigating to folders that may be four or five clicks away.
Anyway, thanks for your ideas. Will continue to stand by in hopes that someone can either suggest an approach or confirm that it just isn't possible.
Robert Bonds
Your solution is similar to many I've used in the past, where a specific image appears on the screen when the form displays that record, based on the value of a field in the record that stores the location of the image. Alas, I can't use that approach this time because I'm developing this database for multiple users who will be passing it around, and the relative location of the stored images will be different from user to user. That's why the "attachments" control option in Access 2007 is attractive: once the image has been located, it's stored in the database with relatively little bloat to the database file. And the image stays put even if the original image file is deleted, moved or renamed.
You mention that the attachment dialog box "may not be able to be changed at all." Yes, that's what I'm trying to find out: whether, through one or more VBA event procedures, I can modify the way it displays. Actually, while changing the title of the dialog box would be nice, it's not essential... but directing the user to the correct folder as the default option IS important, since some of my users won't be comfortable with navigating to folders that may be four or five clicks away.
Anyway, thanks for your ideas. Will continue to stand by in hopes that someone can either suggest an approach or confirm that it just isn't possible.
Robert Bonds
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Capricorn1, thanks, you might be onto something here... if it's possible to set/change the default database folder through VBA.
I mentioned in my original post that I have this attachments-type control on several different forms. The image files for each form are stored in a different folder. Therefore, if the user double-clicks on the attachments control in (say) Form1, you want the default folder to which he's automatically directed to be Folder1; if he's double-clicking from Form2, he'll be automatically taken to Folder2; etc. (Again, I know it's possible to do something along these lines using FileSystemObjects, but if at all possible I'd really prefer to use Access 2007's built-in attachment dialog box.)
So... IS it possible to set/change the default database folder in VBA, achieving the same thing as if you changed it via the "Office button > access options > popular > Browse" approach you mentioned?
(Jeff, thanks for the recommendation to the intriguing "DBPix" utility. Will definitely check it out for my own possible future use, even though it won't be feasible in this situation given the number of users involved.)
Thanks,
Robert Bonds
I mentioned in my original post that I have this attachments-type control on several different forms. The image files for each form are stored in a different folder. Therefore, if the user double-clicks on the attachments control in (say) Form1, you want the default folder to which he's automatically directed to be Folder1; if he's double-clicking from Form2, he'll be automatically taken to Folder2; etc. (Again, I know it's possible to do something along these lines using FileSystemObjects, but if at all possible I'd really prefer to use Access 2007's built-in attachment dialog box.)
So... IS it possible to set/change the default database folder in VBA, achieving the same thing as if you changed it via the "Office button > access options > popular > Browse" approach you mentioned?
(Jeff, thanks for the recommendation to the intriguing "DBPix" utility. Will definitely check it out for my own possible future use, even though it won't be feasible in this situation given the number of users involved.)
Thanks,
Robert Bonds
"(Again, I know it's possible to do something along these lines using FileSystemObjects, but if at all possible I'd really prefer to use Access 2007's built-in attachment dialog box.)"
As is being stated, this is not possible to do directly with the Attachment dialog box.
Is this a deal breaker?
As is being stated, this is not possible to do directly with the Attachment dialog box.
Is this a deal breaker?
ASKER
Actually, thanks to Capricorn1's tip, I've been able to answer my own question about using VBA to change the default database folder. Adding the following code to the attachment control's "On Enter" event does the trick:
Dim strDefaultFolder As String
strDefaultFolder = [The folder you want]
Application.SetOption "Default Database Directory", strDefaultFolder
Thus, once the user double-clicks in the control to open the "Attachments" dialog box, the dialog box isn't any friendlier or self-explanatory than before (thanks to Jeff for clarifying that this simply isn't possible), but at least it then opens up to the correct folder without the user needing to navigate to it.
Thanks very much to both of you for your help.
Robert Bonds
Dim strDefaultFolder As String
strDefaultFolder = [The folder you want]
Application.SetOption "Default Database Directory", strDefaultFolder
Thus, once the user double-clicks in the control to open the "Attachments" dialog box, the dialog box isn't any friendlier or self-explanatory than before (thanks to Jeff for clarifying that this simply isn't possible), but at least it then opens up to the correct folder without the user needing to navigate to it.
Thanks very much to both of you for your help.
Robert Bonds