Solved

Is there a way to open and access form from a link?

Posted on 2011-02-24
9
345 Views
Last Modified: 2012-06-22
Hey guys,

Is there a way that I can open a specific form and find a specific record in my Access application from a link that can be embeded in an email?

Here's the application: We send quotes to our customers to follow up on orders.  When the customer replys, we must manually look up the customer's quote number in our Access database to locate the quote in our database.

I'm hoping there is a way that I can click on a link in the email that will open up our quote form and locate the proper quote.

If this is not possible, I'm open to other ideas.

Just for reference, lets say the name of the form is frmQuote and the recordsource would have to be "Select * from Quotes WHERE QuoteNumber=12345"

Thanks
0
Comment
Question by:pcalabria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 

Expert Comment

by:jameslaws
ID: 34976174
This would entail writing VBA code in Outlook (I am assuming that you are using Outlook for your email, however if you are using Notes you can develop code also). The biggest major problem is that you need to have a place where the Quote number is required. Requiring customers to reference quote numbers in the subject would be great, but then you will still probably have to perform the manual process also for those customer that just can't follow directions (they exist, believe me). Anyway you could write code to extract the number from the subject line place it in a txt files and then open an Access database has links to the real Access database with an Autoexec Macro that pulls in the text file and uses that value to run an query to produce the result you want. ^_^
0
 
LVL 85
ID: 34976326
You could use Outlook VBA to automate this. You can fire off Access through automation and open your form to the Quote defined in your email. This would require you to know the location of Access on our machine (although you could get that through the Registry, of course).
0
 

Author Comment

by:pcalabria
ID: 34976470
LSM,

That sounds promising, but I don't understand.  Can you explain.

What does "fire off access through automation" mean?

Also, what does "know the loaction of Access on our machine" mean?

Do you mean the physical location of access?  If so, the mdb file is in the c:\database directory and the program file is wherever the standard installation directory is.

I've never used Outlook VBA but I'm pretty familar with Access VBA.  Do I need to set a reference to something?

Sorry if my questions are lower level.

Thanks
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 85
ID: 34976506
Outlook VBA would be similar enough to Access VBA That you could probably muddle through it.

Basically, you setup an automation object to your Access database, and then you can work with it from there:

Dim objAccess As Object

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "Full path to your database"

'/open a form in that database

objAccess.DoCmd.OpenForm "frmCustomers", , , "CustomerID=" & YourCustomerIDValue

objAccess.Visible = True

I'm not sure how you'll get the value from your email. You might consider enclosing it in some "special" characters - for example, you might do this:

||<YourIdValueHere>||

And you could then search the email body for that specific and unique string of "||<" to signify the start of your ID value, and the string of ">||" would signify the end of the ID value. I'm not familiar enough with Outlook VBA to do this, but it would seem that you'd get a handle on the specific email message, at which point you should easily be able to get to the Body of that email.

Note this uses Late Binding, so you don't need a reference to Access in the Outlook app.
0
 

Author Comment

by:pcalabria
ID: 34976601
Okay, this is starting to make sense, but where does the code go and how does it get executed?
Do I put this code somewhere in my outlook application?
0
 
LVL 85
ID: 34976631
The code would go somewhere in your Outlook app. As to how it gets executed - that's your call. You can build userforms in Outlook that can automate these sorts of processes, but as I said I'm no expert in those sorts of things.
0
 

Author Comment

by:pcalabria
ID: 34978918
Wow.  I'm feeling like I'm learning to walk again.
Are you saying that I can create an application in Outlook like I can in Access with forms and event procedures?  I didn't know this although I have suspected such, but I don't know where to begin.  I'll search for beginner tutorials.

More important thought, are you saying that I can not access the outlook inbox from Access and read messages as you can with vb?

If I can do so in Access, I can create a routine which cycles through the inbox looking for specific messages.  If I can't open an access form from a link, that would be the next best way to proceed, without venturing into Outlook VBA.

Thanks again
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34978951
<Are you saying that I can create an application in Outlook like I can in Access with forms and event procedures?>

Not really. You can create "userforms", I believe, which are somewhat similar to an Excel userform. They are nowhere near as feature-rich as Access forms.

<are you saying that I can not access the outlook inbox from Access and read messages as you can with vb>

No, that's not what I'm saying. You indicate that you wish this process to be launched based on user actions in Outlook, not Access. In that case, you'd have to initiate that process in Outlook, not Access. I'm not sure exactly how you'd do that, since it would largely depend on your needs.

However, you certainly can use Access to open and examine Outlook email messages. I've seen some solutions setup where email was shuttled to a specific Folder in Outlook (using simple Outlook Rules), and then Access periodically looped through that Outlook folder and processed the messages there.

In your case, if you wish to "launch" Access based on a hyperlink click in an Email message, you'd have to code that in Outlook, and as I said earlier, I'm certainly no Expert in that area.
0
 

Author Comment

by:pcalabria
ID: 34992753
Okay, then if I can't simply embed a link in email messages that opens an MSAccess form, the best way I can think of to handle this project would be to create a Access routine which scan the subject line or body of messages in the Outook inbox, and when it finds a message of interest to perform some action.

Can you help me with any Access VBA code that can read email subject and body text, and perhaps a method to cycle through all messages in a folder?  In that case, when I find a message of interest I can open the quote form and dump the email txt into a memo field on an Access form.

Since I can't do what I was hoping to do, I'm not sure if I should award you points and then open a new question, or just keep working from here.

Thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question