creating ole object using "on focus" event

I have created a field in a table and set the field's data type to ole object.

I then included this ole object on a form.  Access created a bound object frame that's bound to the ole object field.

Now, the help files say I need to click on the ole object in form view or datasheet view then click on the insert menu, click object and from there choose the object I want to use (I want to use excel in all cases).

I don't want the user to go through this every time for this field.  I always want it to be an excel object.

I thought I could automatically insert the excel object into my ole object field on my table using the on focus event.

Does anyone have a clue as to how I can do this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wow!  This was a challenge!

OK.  I created a one field table, 'Source'.  That one field is an OLE Object field, named 'oleobj'.

Then I opened the table, and inserted a spreadsheet into that field, using Insert|Object, and then selecting Create New Microsoft Exccel WorkSheet.

Then I made a form and table combination as you described.

I put a button on the form, (just for testing, with this code:

Private Sub InsertSpreadsheet_Click()

    Dim d As Database:  Set d = CurrentDb
    Dim r As Recordset: Set r = d.OpenRecordset("Source")

    Me!oleobj = r!oleobj

    Set r = Nothing
    Set d = Nothing
End Sub

When I go to a new record, and click the button, the bound object frame instantly has a worksheet in it.

Now what you should do, is using the Current event, if Me.NewRecord, then insert a copy of the object in the 1-field, 1-record table.


martinreAuthor Commented:

I'm getting a type mismatch on the "set r" statement.

Any clue?  I'm using access 2000.  The code seems right.  Could be something with the table "source"?

thanks for the help
Can you open the table 'Source' directly?
Can you substitute the name of a different table and have that one line run without error?

Have a look in the help files under 'OpenRecordset', and see the example that's bound to be there to see if there is something apparent.  (I don't have 2000...)

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!


Brian is by far the expert when it concerns Access however, try this

Dim r As DAO.Recordset: Set r = d.OpenRecordset("Source")

What might be happening is that there
is a library conflict with ADO and DAO
and Access 2000 does not know what
recordset you are referring to.

Also, you can create one unbound OLE
on your form and click insert Excel
sheet and make this OLE invisible.

You can use in code after a certain


and then in code use

 me!oleBound1.Action = acOLEPaste

be aware of the enabled and locked
properties of these OLE objects for
the wrong setting will not allow you
to use the acOLEPaste action.

Also, if you right click the OLE
bound or unbound object and select
the properties and go to the other
tab, set the verb property to -2 if
you want your OLE to maximize.  Set the
verb property to 0 if you want to
keep the OLE within the confines of
your access form.

Hope this helps.......

Sincerely, Jeffrey Schnepf

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martinreAuthor Commented:
Brian was great in coming up with that idea!!!  I liked your alternate method though.

It was quick and easy!!  

Also, you were right on with the DAO problem that Brian was trying to do.  I made the change and it worked.

I am using the copy/paste technique you suggested.

Thanks for help Brian/Jeffrey
You are very welcome:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.