Solved

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

Posted on 2011-02-24
9
302 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
  • 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 84
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
 
LVL 84
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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 84
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 84

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now