Adding a file / adding an attachment to a record

[Compressor]

Add a file/add an attachment to a record and be able to open it from within your form

Ok... this post went from a question to an answer:
So for all the people who should struggle with this problem, take some time (just 5 minutes) and try it. It should work like a charm.

After fiddling around with some code from the microsoft website:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303066

1. Open the sample database Northwind.mdb.
2. Create a new form. Name the form Form1. Open the new form in Design view.
3. Add a command button to Form1. Set both the Name property and the Caption property to "Command1."
4. Add a text box to Form1. Set the Name property to "Text1."
5. Right-click Command1, click Properties, and then click the Event tab.
6. In the On Click event procedure, click [Event Procedure] from the drop-down list, and then click the ellipsis to start the Visual Basic Editor.
7. Modify the Command1_Click procedure to the following:

Private Sub Command1_Click()
Me!Text1 = LaunchCD(Me)
End Sub


8. On the Insert menu, click Module.
9. Copy and paste the following code sample in the new module.

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Function LaunchCD(strform As Form) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = strform.hwnd
sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
"JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "A file was not selected!", vbInformation, _
"Select a file using the Common Dialog DLL"
Else
LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function


10. On the Debug menu, click Compile and Save All Modules if you are using Access 97. If you are using Access 2000 or Access 2002, click Compile Northwind, and then close the Code window.
11. On the View menu, click Form View.
12. Click Command1. Click a file from the browse window. Put the path of that file in the Text1 box.

Ok, so that works. Now back to design view. Select the Text1 textbox and press F4 to open up the properties settings, on the tab Format, all the way at the bottom, set Is Hyperlink to Yes. (edit: well, or not, just found out it doesn't really matter. I like the blue underlining though )
Now, click the event tab (with the textbox Text1 still selected) and click on the [...] next to On Click to open up the code builder again and paste, so you will have this:

Private Sub Text1_Click()
On Error GoTo Text1_Err

Application.FollowHyperlink Text1

Text1_Exit:
Exit Sub

Text1_Err:
If Error$ = "Invalid use of Null" Then MsgBox "It's only usefull to click here once you have added a file.", vbOKOnly
Resume Text1_Exit
If Error$ <> "Invalid use of Null" Then MsgBox Error$
Resume Text1_Exit

End Sub

Et voila! When clicking on the command button, you can select a file from anywhere on your drive, the path will be put in the textbox and when you double click the textbox the file will magically open in the application that's associated with the filetype used. Just make sure you have set the Field Size in the table design to a value large enough to hold all the characters of the path to the file. Also, it might be smarter to put the files you're going to attach into a directory on your c:\ drive instead of the \Documents and Settings\etc. location.

The code comes from microsoft kb and from a user on this forum called Ziggy1. (hey, I didn't come up with this stuff, I just put the pieces together, so credit to whom credit is due )

And for all you buffs: this might be "old cake" but to me.... I feel like I've just invented the wheel . Well, not really.... but you get the picture. I just hope that someone else then me will also find a use for it.

 

[Compressor]

I do have one question about this though which I can't seem to figure out.
When the textbox is empty and you click it, you will get the error message that's been typed in.
When you click the button to add a file and you click on Cancel, you get the message that no file was selected. So far so good. But after you've done that once, and if you click on the textbox then, you will not get the error message anymore, just a blinking cursor in the textbox.

It's no biggie but I would like to have it display the Text1_Err message also at that moment.
I've tried somethings along the line of
If Text1 = "" Then Goto Text1_Err

But that doesn't work... Also using Null seems not to be an option since the Invalid use of Null error is handled already. How can this be done?

 

[gemma-the-husky]

i've not looked at, but the trouble is after you have clicked the text box, and handled messages you are still in the text box. One way is to remove the tab stop from the text box, and overlay the text box with a transparent button (visible, but transparent), same size. Then users are clicking the command button, not the text box, and can't enter the text box.

 

[Compressor]

i've not looked at, but the trouble is after you have clicked the text box, and handled messages you are still in the text box. One way is to remove the tab stop from the text box, and overlay the text box with a transparent button (visible, but transparent), same size. Then users are clicking the command button, not the text box, and can't enter the text box.

Hello again you
I've also tried changing the exit procedure to change the focus so the cursor will be out of the textbox.

Text1_Exit:
Me.SomeObjectsName.SetFocus
Exit Sub

Which works, I can see the focus has changed to the other object. But still, even though the cursor is not in the textbox anymore, when you click it, the "error" message doesn't pop up. So there must be some value that changes once you have clicked the textbox which makes it think it's got contents. Since the contents isn't really there, I thought it would have to be an empty string, and that's stated as: "" right? But no cigar.....

I'm gonna try your idea and then set the command button property to disabled once a string is present in the textbox, so they still can click the textbox to open up the file. I will try that.

 

[gemma-the-husky]

disabled won't be any good, the command button will just not respond to a click. you will need to set visble = false, to get at the text box

 

[Compressor]

Hmmz... Too bad, still no cigar....

Private Sub AddCaseAttachment1CmdBtn_Click()
    Me!CaseAttachment1 = LaunchCD(Me)
End Sub

Private Sub CaseAttachment1_Click()
    On Error GoTo CaseAttachment1_Err
            
    Application.FollowHyperlink CaseAttachment1
        
CaseAttachment1_Exit:
    Me.AttachmentsTabPage.SetFocus
    Exit Sub

CaseAttachment1_Err:
    
    If Error$ = "Invalid use of Null" Then MsgBox "No file has been selected yet", vbOKOnly + vbInformation, _
    "Nog geen bestand opgegeven"
    Resume CaseAttachment1_Exit
    If Error$ <> "Invalid use of Null" Then MsgBox Error$
    Resume CaseAttachment1_Exit
    
        
End Sub

Private Sub Command74_Click()
    If CaseAttachment1 <> "" Then GoTo Sequence_1
Sequence_1:
    Me.AttachmentsTabPage.SetFocus
    Me.Command74.Visible = False
    ' If CaseAttachment1 = "" Then Me.Command74.Visible = True
    MsgBox "No file has been selected yet", vbOKOnly + vbInformation, _
    "Nog geen bestand opgegeven"
End Sub

When the open file dialog has opened, and cancel is pressed, I am still able to just enter the textbox without getting the "error" message.

 

[Compressor]

I'm gonna take a break and get back to it later. I'm not seeing things straight anymore.

 

[gemma-the-husky]

if you set the textbox tabstop to false, the enabled to false, and the locked to true, you definitely will not be able to get to click on the text box. However if you set the button to not visible also, you won't be able to react with that area of the form at all.

the other thing it might be is your error handling

If Error$ = "Invalid use of Null" Then MsgBox "No file has been selected yet", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Resume CaseAttachment1_Exit
'need an end if here

If Error$ <> "Invalid use of Null" Then MsgBox Error$
Resume CaseAttachment1_Exit
'need an end if here

don't you need an "end if" after each resume statement. Otherwise you never see the second error$ test.

 

[Compressor]

That's what I had in the very beginning, but got an error message, so I got confused since that's what I've learned to do by now: If, End If.

I've set the End Ifs again now, but again, when I then click the empty textbox I get a Compile Error: End If without block if.

 

[gemma-the-husky]

I see - if you have text on the same line you dont need the end if.
the syntax is

IF a THEN do something

or
IF a THEN
do something
do something else
END IF

so you are

If Error$ = "Invalid use of Null" Then MsgBox "No file has been selected yet", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Resume CaseAttachment1_Exit - and THIS STATEMENT ALWAYS GETS PROCESSED

you need

IF Error$ = "Invalid use of Null" Then
MsgBox "No file has been selected yet", vbOKOnly + vbInformation, "Nog geen bestand opgegeven"
Resume CaseAttachment1_Exit
END IF

 

[Compressor]

Ok, I changed that to the correct IF End IF order. So that should be sorted now. But still... after adding a command button and adding the following code (and a lot of variations of it):

Private Sub Command75_Click()

    If CaseAttachment1.Value <> Null Then
    Me.Command75.Visible = False
    'ElseIf CaseAttachment1.Value = Not Null Then
    'Me.Command75.Visible = True
    End If
    MsgBox "No file has been selected yet", vbOKOnly + vbInformation, _
    "Nog geen bestand opgegeven"
    'GoTo Sequence_1
    'If CaseAttachment1 = "" Then Me.Command75.Visible = True
'Sequence_1:
 '   Me.AttachmentsTabPage.SetFocus
  '  Me.Command75.Visible = False
   ' MsgBox "No file has been selected yet", vbOKOnly + vbInformation, _
   ' "Nog geen bestand opgegeven"

End Sub

The button works, every time. Also when I have added a file (so a string) to the CaseAttachment1, the commandbutton75 stays visible.

Strange thing is, that when I use the sequence_1 part of the code, the focus does get set to another object. So why won't it make the command75 button invisible then OR come up with a message? It is the next statement so.... And if I do not set the focus to another object, an error comes up stating that an object can't be made invisible when it has the focus.

Maybe.... I'm just going to have to learn to live with the fact that when cancel on the open file dialog is pressed, and then the textbox is clicked again it doesn't come up with a message if it's empty.

I don't know what the problem is, at first I thought that "something" happens to the status of the content of the textbox which makes the value not Null but "" or... well, I'm about to give up. Its just.... I can't stand this small thing won't work when the rest of the thing is working so nice.

Edit: I'm starting to mix things up. The way the code is now, the focus doesn't change as it did before. So forget that part for now.
The "value of nothingness" does get stored somehow since when I reopen the "case" and goto that field, it also won't respond to clicking the empty field. But apart from that... do I check correctly for a string or no string in the value of the textbox? And what is the difference between Null, Is Null and "" ? If I place the right check on the On_Current property of the form... shouldn't that take care of it? If Text1 holds a string its ok to click on it so open the link thats in it. If there isn't a string in it, show messagebox . ?

 

[gemma-the-husky]

have another go at the file open save dialog. if the user cancels, then it might returns an empty string not null

In any event, as someone reminded me recently, you cant test for equality to null in that way

you either have to say

if isnull(caseattachment1) or
if nz(caseattachment1, vbnullstring) = vbnullstring

 

[Compressor]

Thanks, When i'm somewhat less tired, i will have another crack at it. Since there are so many questions about this, but no easy answers, I would really like to have a result from this thread. For myself ofcourse, but also for other people do not have the time or just want an easy to use step by step plan for attaching and opening a file to a case. Well... maybe there are such plans or instructions, but I couldn't find them.

NZ statement? Well... i''ll go and find out some more about that.

Once again, thank you so much for all your effort. I really, really appreciate it. I wish I could give you something in return. Tnx.

I'll try this again tomorrow. Now.... I must start to find out how to calculate some things to determine how much money the customer has to pay for labourtime spent on his/her case. As I said before, my timewindow is limited and I have spend almost a day on this little thingy. Well, I am glad it's gotten as far as it has gotten now. (or isn't that good english?) But this one... sooner or later, I want it to be as perfect as I can get it. Just for fun.

 

[gemma-the-husky]

if you haven't came across the nz ... its a vital little bit of code. converts any null into whatever you want, to make the rest of yur function work.

eg in reports, where nulls would propogate through to give a null total, wrapping each value as nz([myfield],0) solves that. With text nz([mytextfld,"") converts a null string to a zero length string. (vbnullstring is just a vb constant for a zero length string).

 

[gemma-the-husky]

I've had a look and you will kick yourself over this. Set the textbox as no tab stop, enabled= false, locked= true. You don't want your users to get in this text box at all - unless you want them to be able to edit the contents.

put a transparent button over the textbox, and on the onclick event of the button put the following. That should do everything you are trying to do, without having to hide anything.

Private Sub CommandTrans_Click()
If Nz(Text1, vbNullString) = vbNullString Then
MsgBox ("No File Selected Yet") 'if you want a message
Exit Sub
End If
Application.FollowHyperlink Text1
End Sub


A further point is that the text box is unimportant. You could actually just return the file string in a variable, and open it directly without using the text box, as below

Private Sub Command1_Click()
Dim text2 As String
text2 = Nz(LaunchCD(Me), vbNullString)

If Nz(text2, vbNullString) = vbNullString Then
MsgBox ("No File Selected Yet")
Exit Sub
End If
Application.FollowHyperlink text2
End Sub

 

[Compressor]

Ok, I get the idea. But I've been fiddling with my code and now I'm really lost.

The idea:
Have a command button with which the user can add a file.
Make the path and filename visible to the user so they know what file they are going to open, since this can be anything.
When they click the text in the textbox, the file should open.

The rest of the story you know already (about the empty textbox, the invalid use of null error etc.)

How am I supposed to order the pieces of code now? The transparent button must disappear for the user to be able to view the file contents and edit it.

So now I have a textbox called CaseAttachment1 to hold and display and make clickable the path to the file,
A commandbutton called AddCaseAttachment1CmdBtn to make it possible for the user to get to the open file common dialog,
A transparent commandbutton called CommandTrans

The code for CommandTrans:

Private Sub CommandTrans_Click()

If Nz(CaseAttachment1, vbNullString) = vbNullString Then
MsgBox "No File selected yet", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Else
If CaseAttachment1 <> vbNullString Then
Me.CommandTrans.Visible = False
Exit Sub
End If
' hoping I did the check right: if there is a string in the caseattachment box, so it is not vbNullString then make the CommandTrans not visible so interaction with the CaseAttachment1 textbox becomes possible.

Application.FollowHyperlink CaseAttachment1

End Sub

The code for the commandbutton (AddCaseAttachment1CmdBtn) to add the path of a file as a string to the textbox CaseAttachment1:

Private Sub AddCaseAttachment1CmdBtn_Click()
Me!CaseAttachment1 = LaunchCD(Me)
End Sub

The code for the textbox CaseAttachment1:

Private Sub CaseAttachment1_Click()
On Error GoTo CaseAttachment1_Err

Application.FollowHyperlink CaseAttachment1


CaseAttachment1_Exit:
Exit Sub

CaseAttachment1_Err:

If Error$ = "Invalid use of Null" Then
MsgBox "No file selected yet.", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Resume CaseAttachment1_Exit
End If
If Error$ <> "Invalid use of Null" Then
MsgBox Error$
Resume CaseAttachment1_Exit
End If

End Sub

So... the result is still the same. Everything goes as planned except when the cancel button is pressed on the open file common dialog. Then clicking the textbox just does nothing except give a blinking cursor in it.
Even the check <> vbNullString seems to work, since I can click on the file to open and edit it once it has been added.

 

[Compressor]

Private Sub CommandTrans1_Click()

If Nz(CaseAttachment1, vbNullString) = vbNullString Then
MsgBox "No file selected yet.", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Exit Sub
End If

End Sub

And

Private Sub CaseAttachment1_Click()
On Error GoTo CaseAttachment1_Err

If Nz(CaseAttachment1, vbNullString) <> vbNullString Then
Me.CaseAttachment1.FontUnderline = True
ElseIf CaseAttachment1 = vbNullString Then
MsgBox "No file selected yet.", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Me.CommandTrans1.Visible = False
Exit Sub
End If
Application.FollowHyperlink CaseAttachment1


CaseAttachment1_Exit:
Exit Sub

CaseAttachment1_Err:

If Error$ = "Invalid use of Null" Then
MsgBox "No file selected yet.", vbOKOnly + vbInformation, _
"Nog geen bestand opgegeven"
Resume CaseAttachment1_Exit
End If
If Error$ <> "Invalid use of Null" Then
MsgBox Error$
Resume CaseAttachment1_Exit
End If

End Sub

Made it work!
Thank you very much! (F)

P.S. never mind the font.underline = true. Tomorrow morning I will probably understand why it works, but I can't see it now, too tired again. I've tried so many different combinations of things, I can't remember either But at one moment I noticed that it worked, but it had to have something else to do at that point, otherwise the "Application.FollowHyperlink CaseAttachment1" would open up the file two times. So... probably not being able to re-write the code so that it works in the same way without causing some new error(s) I sought to find another thing for it to do that wouldn't have too much impact or change. Probably it can be rewritten so that it doesn't need that line. But for now: it finally works AND the way it is supposed to. (not counting the underlining command).

 

[humph]

Storing the file name

Help please am I missing something really stupid everything is working great I can select the file and open word etc but the file name gets lost when I come back to it.

where is the instruction o save the file attachment address to the table? Do I need to set up an istruction to transpose the address into it?

 

[Mayakovski]

Help please am I missing something really stupid everything is working great I can select the file and open word etc but the file name gets lost when I come back to it.

where is the instruction o save the file attachment address to the table? Do I need to set up an istruction to transpose the address into it?

Hey;

I ran into this as well and the solution is very simple, on Form1 set the Control Source of Text1, to a text field in a table that you have created or are going to use. That is all. Make sure to to set the length of the filed to the longest string you might encounter.

If that does not work, let me know and I''ll give better instructions.

 

[GaryPanic]

have just come in on the end of this - if you check samples - I think Bob - has a really smart version of this which opens up a folder to view,which all the files in it to view as a seperate file
this is very smart

I cannot quite get it to do what I want which is look at a different folder on each record set - but thats a job for later on - this is a very smart piece of work and I recommend that you look at this as it is sh*t hot