3 problems, Email, Pictures and URLs

Ok, in my access database, i have a user table , with name, email, address etc (and a ID (auto Incriment) as primary key)

I then have a form with that data displayed / available for edit / andd new etc..
Now, under "Postcode" i wish to have a hyperlink / button that when you click it takes you to a multimap page.

here is the URL:

http://www.multimap.com/map/browse....lang=&pc=SP13BQ

change the postcode on the end to get different areas (UK). The issue is that i need access to replace that in the URL with what is in the postcode box (field name PostCode)

I am really stuck here.

A similar issue is with the email field, i think you know what i want to do here...(click button, outlook starts with that address in the To field) I cant get it to do much on that area either...

Thirs issue is that i have another table, called media (this is a school project - a Video Liabary media record system) which is going to have all the videos, dvds etc in it, but i want to have the form include a image of the cover art. I have the images but am having difficulty applying them so they change with the differernt records.. Any Ideas?

thanks in advance to anyone who helps!
Al,

Hi Al,

Got a lot on your plate do ya... Okay, here is somethings to get you started.

1) Create your hyperlink on your form. Then in the AfterUpdate of your field PostCode, enter the following code:

Dim varPostCode As Variant
varPostCode = [PostCode]

HyperlinkControlName.HyperlinkAddress = "http://www.multimap.com/map/browse....t=none&lang=&pc=" & varPostCode

This will change the postcode portion in the hyperlink when you change the postcode in your field.

2) For your command button for the email we will apply the some of the same principle by declaring the To: in a variable. In the OnClick Event for your command button

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim strAddress As String
strAddress = [EmailAddress]
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(strAddress)
objOutlookRecip.Type = olTo
.Subject 'Whatever you want in the subject line
.Body 'Whatever you want in the body of the email
.Display 'Display the email before sending
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Make sure you have the references set to the Outlook Object Library

3) Need to know where you are storing your pictures. Are you storing them in a table or in separate files and what is the format of these pictures?

lwells

Thanks for that lwells, really useful, i would on never got that sorted out, the images are stored in a subfolder to the DB called "covers" then one folder for "DVD" and one for "VHS". The files in DVD are called 1 (x).jpg x incrimenting 1 number for every cover, in alphabetical order.
The same for VHS but the first number is 2.

Thanks alot for the help, it is most appreciated, i will be dong this first thing tonight when i get home!

Add a field for each record call it something like ImagePath. In datasheet view of the table add the path to each of the images for each of the records. (A bit tedious, but has to be done)

On your form add an image control and give it the name ImageFrame. Insert whatever picture you want when prompted.

Then in the OnCurrent event for the form add the following code.
Private Sub Form_Current()
On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]
End Sub

And in the AfterUpdate event for the text box ImagePath add this code
Private Sub ImagePath_AfterUpdate()
On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]
End Sub

lwells

Thanks for the help lwells, I will be trying this tonight, by the way, will that outlook code work with Outlook Express? No probs if it doesnt.. I will let you know of any issues i encounter, but that should be pretty straighforward...

No, you can not call the Outlook Express Application from Access.

Ok, Something has gone horriably wron with all of that, The first bit, what do you put in the hyperlink, i put:

Dim varPostCode As Variant
varPostCode = [PostCode]

HyperlinkControlName.HyperlinkAddress = "http://www.multimap.com/map/browse....t=none&lang=&pc=" & varPostCode

(replacing the dotted bits) in the control "AfterUpdate" (in a VB type thing)
than placed:

"http://www.multimap.com/map/browse....t=none&lang=&pc=" & varPostCode (replacing dots) in my hyperlink

No Work

I then tried the Email thing, I placed all that code in the VB type thing in an OnClick even and Wehn i clicked it VB sprung up and said a debug error had occured...I have attached the database for you to have a look at if you have time, i thank you for your help and ability to help a muppit such as myself..

I will keep trying.

EDIT: where is the image control located and which text box...

Attachment
----------
DB1.ZIP (577kb)

Okay, lets take one thing at a time.

1) You didn't put in a hyperlink on your form to begin with. Go to tools insert hyperlink. Follow the prompts to add the hyperlink address using the same address for the one you posted here. Switch back to form view and click on the hyperlink to make sure it works.

2) In the AfterUpdate of the PostCode field, put the code I posted for you. YOU must change the HyperlinkControlName to the name of the hyperlink control on your form. Usually it will be Label followed by a number. You can use that name or you can put your own name in for the control.

Now when you change or add text to the PostCode field with a new valid postal code and press enter, the hyperlink address for the hyperlink control will change. Click on the hyperlink control after changing your post code and the new map will be displayed for that postal code.

3) For your mail item, you must add the reference to the MicroSoft Outlook Object Library. When in the code window - tools - references and put a check mark next to this library. In my version it looks like this Microsoft Outlook 11.0 Object Library. Depending on your version of access the number maybe 10.0. After selecting the library, you will need to compile the application (Debug - Compile)

Then you must use the name of your controls in the code where I have put brackets[].
For example I showed strAddress = [EmailAddress]. In your application you have the control named EMail. So change it to strAddress = [EMail]

You will never be able to cut and paste VBA Code directly from a forum into your application without going through the code and inserting the control names that you use in your application. Sample code offered in any forum is generic and you will have to modify to suit your application and needs.

4) In your table media, you have a text field named Picture. This is where you will put the path to your image (example only! C:\Documents and Settings\user\My Documents\mypicture.jpg) Obviously you will need a path typed in for each picture or image that you have for each record in the table. In the code I gave you, the control name was [ImagePath]...in your application the control name is [Picture]...starting to get the idea here. You will also need to add an image control to your form. Insert whatever picture you want at the prompts. Just remember I showed the image control name as [ImageFrame] in the code. So you will have to use the name of your image control inside the brackets.

lwells

ok, i sort of get all that, will try tomorrow when it is not nearly midnight and i have my brain in the right way up..

Ok, i have the post code thing under control, but it only works if you edit the field then click the button, not if you just hit the button... Any ways to sort that or not?

The Email thing throws up a debug error on: Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

(bit in bold)

And i have added the control.

Havent really tried the image thing yet, will let you know..
Thanks alot for all the help you have given me so far, could not be down without you!

EDIT

Put " before and after that and the other thing in brackets and the debug passes that now but fails on the .Subject thing...
What do i replace that with?

EDIT2:
Just tried the pic, tif i add a image using the image button, it has no after update or oncurrent fields... What do i do?

I took the database you sent to me and added the hyperlink the way it was suppose to be added. I also corrected your email button so it will work. I added the image frame to the form, but you will have to add your own path to your directory where you have your images or photos stored. I left the path in the picture text box to a photo I had on my computer to give you an example of how to make the path for your own pictures.

Look at the code to see how this was all done. Just a couple of things,

1) You will need a valid PostCode in the PostCode field. If the post code is blank, the hyperlink will be disabled. If an invalid PostCode is entered, the Internet Explorer will give you an error message.

2) You will not be able to send an email if the EMail address is blank or invalid. If you have all the service packs installed for Office, you will get a security message telling you that a program (Access) is trying to send an email and will prompt you to answer Yes or No if it is okay. Answer the prompt with a Yes. The security window may appear twice before opening Outlook to view your email. This is a normal security window and cannot be bypassed.

3) If you correctly set the path to your images in the table field Picture, the images will automatically display in the image box as you scroll through each record you created.

Enjoy,
lwells

Well the file size is too large. Send me your email address and I will send it to you by email.

wow, thanks alot, My email address is admin@hellzbellz.net

Thanks for that!
Alex.

Dude that is absoloutly perfect, just what i wanted down to the last button knob and wistle!
Thanks alot!

Much appreciated!

Just one little addition, how do i make it so when the picture is clicked, it opens up the file in the normal program?
I have tried using the code for the URL and modifying it to work with the "Picture" field, but it doesnt work... any ideas?

^bump^

Hi Al,

Probably the easiest way would be to store your image into a bound field as an OLE Object in your table. Then add this field to your form. Then when you double-click on the image, the program that created the image will open with that image or photo. You could use the current image frame to double-click and with using the shell.exe to open the application that created the photo or image, but then you will also have to use the file browse to locate the specific image or write additional vb code to automatically locate the specific image. Either way it can be done, the first method would be the easiest, however depending on the number of photos/image