CMD Button to add contacts from Outlook

[vanlanjl]

I noticed in one of the templates from Access that it has the option to click a cmdButton and it opens the search window in Outlook to add contacts to the form and table in outlook. I have tried to replicate this but to no avail. Any Ideas?
Thanks

 

[PaulBricker]

Welcome to Tek-Tips vanlanjl. It would be helpful if you could post the code you have tried so that we can see where your thought processes are in this. From there we can make appropriate suggestions.

Thanks

Paul

 

[vanlanjl]

There is no code. There wasn't any code on the Access asset template for it either.

 

[PaulBricker]

Where did you get this template? Can you see this template(form) in design view? There has to be either some code or a macro that is run by the click event of the button. Do you get an error message when you click your button?
The more info you can give us the better.


Paul

 

[vanlanjl]

Okay Let me start over. My boss has asked me to design a db that list all of our computer assets and user info along with that asset. It will alos automatically create two reports that we use daily. I noticed that Access 2007 has a Asset template but it goes above and beyond what I need I am only tracking one type of asset - the computer. So I don't want to use MS template but create my own. But i want a feature that they have.


If you own Access 2007 open a new db and run the Asset Template. Open the Contact List form and click the "Add From Outlook" cmd button. This opens the contact search window from outlook. You type the user you are looking for then push ok and it populates the information into the Access Contact list form.
There is a macro listed
Action Arguments
On Error Next,
RunCommand AddFromOutlook

As far as the code goes Microsoft has it locked so it cannot be viewed.
I have images saved but do not have a place to post them. (company has it blocked) so if you would like to see them please let me know.

Any help with this would be great, and I appreciate the support I have gotten and apologize for not being clearer. I wish i had a code to look at, i think I would be able to figure it out from there but we all know how Microsoft is.
Thanks

 

[PaulBricker]

OK, to make this happen here is what you do.

1. Create your macro.
2. On the Create tab in your database, click on the dropdown arrow on the Table Templates button and click on Contacts
3. Save this table as Contacts.
4. Highlight the Contacts table name in the list of tables, and then on the menu, click the Form button. This automatically builds a form from your Contacts table.
5. Go into form design view, add a command button and in the OnClick event, select the name of your macro.

That will do what you are looking for.
Post back with specific problems after you've tried it. Apparently, the structure of your table has everything to do with Access being able to map a connection with Outlook.
Anyway, give a go and see how you make out.

Paul

 

[vanlanjl]

Sweet it worked!!
I cant it to work in my existing form but it works in the new one. Thank you so much!!

 

[PaulBricker]

Glad you got it. If you google the term

acCmdAddToOutlook

you will find some info that might help explain it, but there isn't a lot of data from MS about it.

Paul

 

[vanlanjl]

Okay last question about this issue. Everything works fine except it will let add the same contact multiple times. Is there a way to keep that from happening? I imagine i will have to make a smaller form that might say " user is already in system" or something like that.

 

[PaulBricker]

If you open the Contacts table in design view, you will see that the primary key for the table is set to the auto number. You could change that primary key to the First and Last Name fields (to do that, you would highlight both the first and last name fields and click the primary key button on the menu). That would exclude any duplicate names from being entered in the table but you will also have to get rid of any duplicate contacts before you attempt to change the primary key.
If you try to enter a duplicate contact, the error message will just tell you the operation failed and nothing else. If you want some more descriptive error messages, then we would have add some error handling to the macro or, and probably a better way to do it, would be to write some code to open the recordset, check to see if the name is already in the contacts table and then produce the appropriate action from there.
If you want to go the second method, you need to let me know how familiar you are with writing code. It's not hard to do, but it would help to know what your experience level is.

Paul

 

[vanlanjl]

lol i have very little experience - but am excited to learn

 

[vanlanjl]

Okay i tried the first way just to see it. It will not give me an error and but it will not recreate the record. Also how do i make it populate the form?

 

[PaulBricker]

Also how do i make it populate the form?

Sorry, I'm not quite sure what you are asking. I assume you tried changing the primary keys and it would not put in a duplicate contact record but I don't understand the last queston.
I may have spoken a little quickly when I said this wouldn't be very hard. The whole operation doesn't allow you anyplace to break into the process but given a little time, we can probably figure out something.
Anyway, let me know about the populate form question.

Paul

 

[vanlanjl]

Okay - the actual form is named "frmContact"
It contains a "cmdAddNewUser" command button
a "cmdSaveUser" command Button
a "cmdAddFromOutlook" command button

COnatins 16 txt boxes such as last name and first name company, e-mail etc..

When i run the macro for the addfromoutlook button it will add it to the tblcontacts but will not populate the actual txt boxes in the form frmcontact.

also i have combo box called "ContactSelect" that will show the contacts but will only populate the txtLastName field when selected and will not fill the other txtx boxes with the correct information

my code for that is:
SELECT [tblContacts].[Last Name], [tblContacts].[First Name] FROM tblContacts ORDER BY [tblContacts].[Last Name];

and it is located in the "Row source" of the combo box
Again many thanks for your help.

 

[PaulBricker]

This may be some of the issue with mapping the fields to the Outlook Contacts list.
I have not worked in A2007 much so this is all new to me as well so I will have to figure out solutions as you run into problems. That will require me to ask lots of questions.

1. Open your form in design view, and tell me the Record Source for the form. It should be set to your Contacts table.

2. When I had you create the form, I told you to highlight the Contact table in the table list and then click the button on the Create menu tab labeled Form so that the form created was from the Contacts table. Did you do it this way. It sounds like you may have built the form by hand and I don't know if that's an issue or not.

3. Where is this combobox and what are you trying to do with it? Any name in that combobox is going to be a name in the tblContact so I don't see the use for it. You can't create a new record from it so I'm not getting what to use it for. To populate other fields (like the first name textbox on the form, you need to set the Control Source for the textbox to the appropriate column property of the combobox). Something like =ComboName.Column(1) or run some code in the afterupdate event for the combobox.

Let me know.

Paul

 

[vanlanjl]

1. The record source is set to tblContacts
2. Yes i did do it this way
3. The combobox is in the form header and named ContactSelect
I tried this code for the after update and it didn't work (I dont know a whole lot of code - more of a network cisco guy)

Private Sub ContactSelect_AfterUpdate()

Me![ID] = ContactSelect.Column(1)
Me![Company] = ContactSelect.Column(2)
Me![Last Name] = ContactSelect.Column(3)
Me![First Name] = ContactSelect.Column(4)
Me![E-mail Address] = ContactSelect.Column(5)
Me![Job Title] = ContactSelect.Column(6)
Me![Buisness Phone] = ContactSelect.Column(7)
Me![Home Phone] = ContactSelect.Column(8)
Me![Mobile Phone] = ContactSelect.Column(9)
Me![Fax Number] = ContactSelect.Column(10)
Me![Address] = ContactSelect.Column(11)
Me![City] = ContactSelect.Column(12)
Me![State/Province] = ContactSelect.Column(13)
Me![ZIP/Postal Code] = ContactSelect.Column(14)
Me![Country/Region] = ContactSelect.Column(15)
Me![Web Page] = ContactSelect.Column(16)

End Sub

But this code is wrong and doesn't work

 

[PaulBricker]

I'm not sure why the data is not populating the form. My copy populates the fields in the form and table. You've got the Record Source set correctly, you might try opening the form in design view and check the Control Source for the textboxes to make sure they are mapped to the right fields in your table. A long shot because you say you created the form using the method I told you to so all the fields should be mapped properly.

So you are using the combo box to find a particular record on your form. The code you tried is not correct for that. You need to use something like this, where Combo37 is the name of your combo box.

Private Sub Combo37_AfterUpdate()

Me.RecordsetClone.FindFirst "[Last Name] = '" & Me![Combo37] & "'"
If RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

If all else fails, you could delete the form and try creating it again but that would mean recreating everything. If the table is populating properly, than I don't understand why a form that has the record source set to the table isn't. There's got to be some simple reason.

Paul

 

[vanlanjl]

I checked the control source for all my text boxes and they just say for example "Fax Number"

I also inserted the code you had with adjustment to name for the combo box to the After Update event.

I also have the following code in the row source for the combo box:

SELECT tblContacts.ID, tblContacts.[Last Name], tblContacts.[First Name], tblContacts.[E-mail Address] FROM tblContacts WHERE (((tblContacts.ID)<>Nz([Form]![ID],0)));

and the row/source type is table/query
bound column: 1
limit to list:yes
allow value list:yes
inherit value list:yes
show only row: no
enabled:yes
locked:no
auto expand:yes

Under Event:
After Update the code you sent

Also for the cmdAddFromOutlook command button
Data
Enabled: yes

Event
On click: Embedded Macro (macro follows)
Action Arguments
RunMacro AddFromOutlook,

Also for the actual form named "frmContact"
Event
On Load: Embedded Macro (macro follows)

Data
Record source:tblContacts
Record Setynaset
Fetch Defaults: Yes
Filter: 1=0
Filter on load: yes
Order by on load: yes
Data entry:no
allow editions: yes
allow deletions: yes
allow edits:yes
allow filters: yes
record locks:no locks

Let me know if you want to see that.
Again Thanks for all your help

 

[vanlanjl]

Oh I also have to queries set up

qryUserName

SELECT tblUserName.Username
FROM tblUserName
ORDER BY tblUserName.tblUserName.[LastName], tblUserName.Username;

qrytblContacts

SELECT tblContacts.ID, tblContacts.Company, tblContacts.[Last Name], tblContacts.[First Name], tblContacts.[E-mail Address], tblContacts.[Job Title], tblContacts.[Business Phone], tblContacts.[Home Phone], tblContacts.[Mobile Phone], tblContacts.[Fax Number], tblContacts.Address, tblContacts.City, tblContacts.[State/Province], tblContacts.[ZIP/Postal Code], tblContacts.[Country/Region], tblContacts.[Web Page]
FROM tblContacts;

maybe that will help some
thanks for all you help!!

 

[vanlanjl]

Okay I tried something new. I created a new combo box named:cmbSelectUser

i followed the wizard and chose the "find a record on my form based on the value i selected in my combo box".

And it works.... but...

When i open the form it opens already populated, i then can choose from the combo box a name and it will populate the entire form but... if change the filtering on the actual form to : 1=0

it will load the form blank (txtboxes) and choose the combo box selection it will not populate the txtboxes.

I guess what i am saying is that i want the form to load as blank, then have the option to either use the combobox to find a current user or use the "addfromOutlook" button to grab a user from outlook then (which it will do now) have it populate the form.