[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

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?
  • 2
  • 2
  • 2
1 Solution
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...)

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


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
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:)

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now