Calling a Procedure on an Access form from an Excel form

[ktwclark]

I'm having a bit of a problem calling an procedure on a
Microsoft Access Form from an Excel Form. I've created the
Access object, opened the database containing the form,
displayed the form, autofilled some textboxes, but I can't seem
to get the correct syntax to call one of the access form's
procedures. This is what I have so far:

I have an Access form called InputData
It has a textbox called txtData

From within an Excel form I have this code:

Dim appAccess As Object 'Microsoft Access database object
'Create an instance of Microsoft Access
Set appAccess = CreateObject("Access.Application&quot
With appAccess
'Open the database "ABC.mdb"
.OpenCurrentDatabase "C:\My Documents\ABC.mdb"
'Open the "InputData" form.
.DoCmd.OpenForm "InputData"
.Forms![InputData]!txtData.SetFocus
.Forms![InputData]!txtData.Text = "Demo Data"
'Call Procedure "StoreData" on Access Form "InputData"
HELP!
End With

 

[ide]

...
Dim frmTmp As Access.Form
...
Set frmTmp = .Forms![InputData]
Call frmTmp.StoreData

ide

 

[ktwclark]

Nice try IDE but your code doesn't work. Remember I'm trying to call a sub procedure on an Access form from an Excel form. The code you suggested produces a compile error or "User-defined type not defined" on the line containing "Dim frmTmp As access.Form". Unless I'm doing something wrong, but I can't understand what. I assume Excel, being part of the Office Suite, already has a refernce to Access. If not, I can't see where to add it to Excel.

 

[KyleS]

ktwClark,

To add the reference to Access (remember, we're working with Microsoft here so NEVER assume) open up the VB Editor in Excel and select "Tools" --> "References" and select "Microsoft Access 8.0 Object Library" the only difference you might see is in the "8.0" depending on what version you are running. I have '97 which is 8.0, if you have 2000 I think it's 9.0, but it doesn't matter because you'll only see one option for Access

Kyle

 

[ktwclark]

OK, I've added the reference to Access 9.0 but I get an error "Application-defined or object defined error"


Sub Access()
Dim appAccess As Access.Application 'Microsoft Access database object
Dim frmTmp As Access.Form

Set appAccess = CreateObject("Access.Application&quot 'Create an instance of Microsoft Access

With appAccess
'Open the Demand Planning Tool database
.OpenCurrentDatabase "C:\My Documents\abc.mdb"
.Visible = True
'Open the Import EDW form.
.docmd.openform "InputData"
.Forms![InputData]!txtLocation.SetFocus
.Forms![InputData]!txtLocation.Text = "abc"
End With
Set frmTmp = .Forms![InputData]
Call frmTmp.CustomProc 'call a procedure CutsomProc on the access form "InputData"
End Sub

That's the code I'm using

Thanks far any help........