Newbie needs help fixing her code - Outlook & Access

[klandreth]

I had this thing working! But I did "something", and now it doesn't work.

A newbie to VBA, but not to programming; I "borrowed" some code I found here & other places on the web. I surprised myself when I got it work!

In cleaning up the code, I must have deleted something because now it doesn't work...

Scenario...I want to read the current Outlook contact record, grab the customer id, and pass it to Access to open the form (record) associated with that customer id.

Alas, I KNOW the Contact record has data in it, but the code is failing as though it doesn't. Second problem, while the database is opening, the form is not. I know that part of my code is sketchy at best, but I had the Outlook portion working! The other thing I don't understand is I put 'MsgBox' stmts in there to try & trap the error, but those didn't work either.

Please point me in the right direction. I don't know enough about VBA to debug it at this point. Also, the attached file is not an editable document; the system it is being developed on has its USB&CD drives (write functions) locked down. The best I can do is print & scan, but I think you'll get the gist of what I'm trying to do.

Thank you,

Karen.

[JP2112]

Can you post the code you have so far?

[klandreth]

I'm sorry; I attached a zip file that was an image of the code. I can't upload the actual code as it is on a system that is locked down. No USB, No CD-writer, no Internet. :-( However, when I tried to upload the file again, your system said it was already attached to this thread.

[JP2112]

Wow. It's a zip file, containing a Word doc, which has a screenshot of the code

How did you get the screenshot off the computer?

I'm sorry to be stuck on this step, but it's going to be difficult to get help if you need someone to re-type the code just to help you.

[klandreth]

I printed it; it's the only "export" method I have; however, I can retype it if necessary.

[klandreth]

I was able to extract it from a PDF using OCR technology. Here you go:

Sub GetAcessRecordO
On Error Resume Next
Dim objOL As Outlook. Application
Dim objNS As Outlook.NameSpace
Dim objContactsFolder As Outlook.MAPIFolder
Dim objltems As Outlook. Items
Dim objContact As Outlook. Contactltem
Dim objAccess As Object
Dim accessRecld As Integer
Dim access Search As String
Dim bContinue As Boolean

Set objOL = GetObject("Outlook.Application")
Set objNS = Application.GetNamespace("MAPI")
Set objContactsFolder = objNS.GetDefaultFolder( olFolderContacts)
Set objltems = objContactsFolder.ltems

Const accPath = "C:\Users\Me\Documents\WIP\Databases\Contact Mgmt Database (New).accdb"
Const accForm = "Contact Details"

Select Case TypeName(Outlook.Application.ActiveWindow)
Case "Explorer"
If Application .ActiveExplorer.Selection.Count <> 1 Then
MsgBox ("One (and Only One) Record Must Be Selected")
Exit Sub
Else
objContact = Application .ActiveExplorer. Selection.ltem( 1)
End If
Case "Inspector"
objContact = Outlook.Application.Activelnspector.Currentitem
End Select
If objContact Class = olContact Then
bContinue = True
Else
bContinue = False
MsgBox ("Must Select a Contact Record")
End If
If bContinue Then
MsgBox ("you are here" & objContactCustomerlD)
accessRecld = objContactCustomerlD ' THIS IS WHERE IT SEEMS TO BE FAILING.
If accessRecld > 0 Then
Set objAccess = GetObject(accPath)
If Err.Number <> 0 Then' database not open
Set objAccess = CreateObject("Access.Application")
Err.Clear
End If
accessSearch = "[ID] = " & accessRecld
objAccessVisible = True
objAccess.DoCmd.OpenForm accForm, acNormal, , accessSearch
Else
MsgBox ("No Customer Id Present")
End If
End If
Set objOL = Nothing
Set objNS = Nothing
Set objContact = Nothing
Set objAccess = Nothing
End Sub

[BrianMH]

objContactCustomerlD doesn't seem to be getting a value anywhere.

[klandreth]

I agree, and that's what I don't understand....the 'active' record DOES have a value -- objContactCustomerId is the 'Customer Id' field of the Contact record.

[BrianMH]

you need to use set when assigning objects.

so instead of

[VBA]If Application .ActiveExplorer.Selection.Count <> 1 Then
MsgBox ("One (and Only One) Record Must Be Selected")
Exit Sub
Else
objContact = Application .ActiveExplorer. Selection.ltem( 1)
End If
Case "Inspector"
objContact = Outlook.Application.Activelnspector.Currentitem
End Select [/VBA]

you need

[VBA]If Application .ActiveExplorer.Selection.Count <> 1 Then
MsgBox ("One (and Only One) Record Must Be Selected")
Exit Sub
Else
set objContact = Application .ActiveExplorer. Selection.ltem( 1)
End If
Case "Inspector"
set objContact = Outlook.Application.Activelnspector.Currentitem
End Select[/VBA]

[BrianMH]

and make sure you have it as objContact.CustomerId not objContactCustomerId

I'm pretty sure your code does but the OCR didn't pick it up...just make sure.

Also in future please include whatever error message you are getting.

[klandreth]

Thank you....that worked...still learning VBA ... when do you use Set & not use it? I was getting an error (1004), but that seems to have stopped...the code was just falling through. Now I know why.

Next problem, the remainder of the code is opening the db (if closed), and it looks like the form for the specified rec# is TRYING to be displayed but I think its closing --- I can see some activity (the screen flashes), but no form.

Do I need to add some code to my Access DB? (I stole the database from MS -- it's their standard 'Contacts Management Db' template).

Once again, thank you for your help.

[BrianMH]

You use set when you want to assign an object. You don't use it when you want to set a value.

I can't see what is causing it to not show. Any errors?