Vba

[shamas21]

Hi

I was wondering if there is a way that i can add an attatchment to a form field where the field is an OLE field.

for example, i can right click the OLE field and it gives me an option to add and an OLE, but i dont want the user to have to right click the form field to add the OLE every time, instead i want them to click a button i.e. 'Button1' which then opens up the attachment dialog box.

Thanks

 

[LPurvis]

This not been tackled yet?
No probs.
The somewhat manual way you seem to want to tackle this could be with just

    Me.YourOLEControl.SetFocus
    DoCmd.RunCommand acCmdInsertObject

Or if you want to take a bit more control over the insertion

Dim strFile As String
    
    With Application.FileDialog(3)
        .Show
        With .SelectedItems
            If .Count > 0 Then
                strFile = .Item(1)
            End If
        End With
    End With
    
    If Len(strFile) > 0 Then
        With Me.YourOLEControl
            .OLETypeAllowed = acOLEEmbedded
            .SourceDoc = strFile
            .Action = acOLECreateEmbed
        End With
    End If

Be advised that there's no error handling there - it's just example methods.
(You'd definitely need to handle the cancelling of the Insert method in the first example - On Error Resume Next alone would probably do you).

 

[LPurvis]

Bah - I should have mentioned (as inevitaby is the case with OLE field questions)...
You are aware, I imagine, of the downsides of using OLE fields in this manner?
While Acc2007's attachment datatype (which you might be confusing with an OLE field from the wording of the question) is an efficient means of inserting a file to the database - OLE fields are anything but.
It's very likely you'd see considerable growth (/bloat) in your database size.

And, by comparison to inserting BLOBs via code, the format of the inserted file is of very limited external accessibility.

Acc2007's attachment datatype fields at least have the ammended DAO's methods to work with the data for extraction etc via data access code only.

And BLOBs can be extracted by pretty much anything (though have the downside of not being trivially viewable from within Access).

Cheers.

 

[shamas21]

I must say that your a star!!!!! Works Perfect.

Though, do you think its better if i was to link the onject rather than uploading it into my database - this would make my database much smaller.

However, if someone was to move the files around then i suppose the link would become broken and useless.

 

[LPurvis]

Indeed - and even linking to files in an OLE field carries with it unnecessary overhead (and bloat).
If you're going to link - then do it the standard way and just store the path string in a text field. (Or just the part of a path and have all your linked images in a single core location - which you can move en masse if the situation requires).

The other options are, as I mentioned, Acc2007's attachement datatype.
And BLOBS (there's an example of that on my examples page - i.e. my "homepage" listed here). BLOBS are versatile - but as I mentioned you'd lose the inherent display abilities within Access.

 

[shamas21]

Hi again

Thanks for all your help. I have one final question, how can i assign my button called 'btnDelete' to remove the ole obect which i have embedded into my form?

Thanks

 

[LPurvis]

Either method of insertion - to remove it you'll likely just end up with:

    Me.YourOLEControl.SetFocus
    DoCmd.RunCommand acCmdDelete

 

[shamas21]

Either method of insertion - to remove it you'll likely just end up with:

    Me.YourOLEControl.SetFocus
    DoCmd.RunCommand acCmdDelete

Many thanks. You've gone down in my book of Legends

I did try the following code but it didnt execute. Nevertheless yours works great.

Me.oleAttatachments.SetFocus
Me.oleAttatachments.Action = acOLEDelete