Open an MS Access form from Outlook 1

[neemi]

outlook/access 2002


I need to firstly check to see if the db is open which I have done.
Then I need to be able to check to see if an particular form in access is open. If not then open it.

Does any one know the code so that I can open a ms access form from within outlook?

Also is it possible to search a particuar record from outlook and go straight to it from within outlook?

Help will be appreciated. code help will be loved.

Cheers,
Nims

 

[CautionMP]

neemi,
The easiest way is to create a macro in Access that does what you want ([tt]mcrAutomationTest[/tt] in this example) then call the macro from Outlook using automation.

Here is some sample code that you could use in Outlook to do this:

Sub TestAutomation()
'Dealing with external objects, use inline error trapping
On Error Resume Next
Dim appAccess As Object
'Grab the database if open
Set appAccess = GetObject("C:\Documents and Settings\Brett\My Documents\Access\BinaryTest.mdb", "Access.Application")
If Err.Number <> 0 Then
  'The database was not open so open it
  Set appAccess = CreateObject("Access.Application")
  Err.Clear
End If
'This is the macro in your database that should open the form
appAccess.DoCmd.RunMacro ("mcrAutomationTest")
'Make Access visible
If Err.Number <> 0 Then
  MsgBox "There was an error"
  Set appAccess = Nothing
Else
  appAccess.Visible = True
End If
End Sub

You could also declare all the objects (database, forms...) and then use the properties/methods of those objects just as you would in Access. The drawback is that everytime you make a change you will need to re-deploy your code for Outlook.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[neemi]

Hi CMP,

Thanks for responding.

Could you help me with code to declare the objects so this way I can open the form from outlook and go tstraigh to the required record from outlook.#

Also how could I check if a form is already open in access from outlook?
Can I check form properties from outlook ie. is it set to allow additions etc?

your help appreciated.
cheers,
Neemi

 

[neemi]

I have used the code as below

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\CLM\ClmXp_v4.mdb"

appAccess.DoCmd.openform "FrmClient"

However it tries to always opens a new db.

If I already have the db open how can I the form in that db?
Cheers,
Neemi

 

[neemi]

Hi CMP

the line of code

Set appAccess = GetObject("C:\Documents and Settings\Brett\My Documents\Access\BinaryTest.mdb", "Access.Application
[\code]

keeps opening a new instance of the db.

How do I change this so that if the db is already open then it doesn't open a new instance and references the open db.

Cheers,
Neemi

 

[CautionMP]

neemi,
My bad, call [tt]GetObject()[/tt] without the Class to keep Access from opening another instance of the database.
[tt]Set appAccess = GetObject("C:\TekTips.mdb")[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[neemi]

sorry CMP...
I Copy the code you have provided above and the only thing I am changing is the location of the db. But even if I have a copy of the db open when I run the code again it opens another instance of it!!!

I am running access/outlook 2002.

Not sure what is going on?

arrrggghhhh!!!

 

[CautionMP]

neemi,
I don't have Office '02 but I have tested it on both Office 2k and Office '03 and on both platforms the macro grabbed the currently running instance of the Access database.

At this point I'm not sure what to tell you.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[neemi]

Hi CMP

It is working, I was being really dum! even though you had told me I still left the access.application part in GetObject!!

It works now fine without opening up a new instance of the db each time.

I shall give you a star for your help!!

One more thing If the database is already open I have been trying to switch focus to this database straight away, but have been unsuccessful in my attempt!

Any idea's how I can achieve ths without using the appActivate command.

If it is opening the database focus switchs fine. But not when it is alreay open. So if I have some msgboxes on the access db untill the user physically selects the db they do not see them.

Is there a way without using appactivate?

Cheers,
Neemi

 

[JerryKlmns]

appAccess.Visible=True

 

[CautionMP]

JerryKlmns,
The visibility issue is covered in the 17 Aug 06 11:44 post.

neemi,
I'm guessing that the issue with [tt]AppActivate()[/tt] is knowing the Access Application Title and having multiple instances of Access running?

The quick way around this is to use an API call:

[b][green]'General Declarations[/green]
Declare Function SetFocusAPI Lib "user32" Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long[/b]

Sub TestAutomation()
...
'This is the macro in your database that should open the form
appAccess.DoCmd.OpenForm ("frmErrorTest")

'Make Access visible
If Err.Number <> 0 Then
  MsgBox "There was an error"
  Set appAccess = Nothing
Else
  appAccess.Visible = True
  [b]SetFocusAPI appAccess.Application.hWndAccessApp[/b]
End If
...
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[neemi]

Thanks CMP you have been a great help.

 

[neemi]

Hi CMP.

One last question... What is the best way I can learn to programme using api calls. Is there any good books you are aware of or any good web sites that i can use as a reference etc.

I have used API calls in the past but through advice of others, and would learn about them myself.

Your advice appreciated

Cheers,
Neemi

 

[TranquilStorm]

Try this site for API

http://www.allapi.net/

Auf Wiedersehen,

Storm