Solved

search MS Access table and return field contents

Posted on 2013-01-22
4
241 Views
Last Modified: 2013-01-27
In MS Word, I am trying to search a MS Access table ("Contacts") for a first and last name (fields: "First_Name", "Last_Name") and return the fax number (field: "FaxNumber").  I got this code so far:
Function GetFaxNumber(fName As String, lName As String) As String
Dim wshShell, strDatabaseName As String

With ActiveDocument.MailMerge
    If Len(.DataSource.ConnectString & "") = 0 Then
        Set wshShell = CreateObject("WScript.Shell")
        strDatabaseName = wshShell.SpecialFolders("MyDocuments") & "\JANE'S FILES\Databases\Patient information_be.mdb"
        .OpenDataSource strDatabaseName, wdOpenFormatText, False, True, True, , , , , , , , "SELECT * FROM `Contacts`"
    End If
    .DataSource.ActiveRecord = wdFirstRecord
    .DataSource.FindRecord
    GetFaxNumber = 
End With

End Function

Open in new window

My problem is with the two lines:
    .DataSource.FindRecord
    GetFaxNumber =
MS Help states the format for FindRecord is FindRecord(FindText:="Joe",  _
    Field:="First_Name")
How do I search for a record using two fields?
How do I return the FaxNumber once I find the field?

TIA
0
Comment
Question by:thenelson
  • 3
4 Comments
 
LVL 17

Expert Comment

by:wobbled
ID: 38806476
why don't you modify the select statement

SELECT * FROM `Contacts

to something like

Dim strFirstName as string
Dim strSurname as string

strFirstName  = "myfirstname"
strSurname = "mysurname"

SELECT * FROM `Contacts WHERE First_Name = strFirstName   AND Surname = strSurname

that way you will only get a record set for that person
0
 
LVL 39

Author Comment

by:thenelson
ID: 38806633
Yes, I did that. Here is my line with the select string (I hard coded the FirstName and LastName where developing the code):
.OpenDataSource strDatabaseName, wdOpenFormatText, False, True, True, , , , , , , , "SELECT DISTINCT Contacts.FaxNumber FROM Contacts WHERE FirstName='John' AND LastName='Doe'"

Open in new window

But now how do I retrieve the FaxNumber?
0
 
LVL 39

Accepted Solution

by:
thenelson earned 0 total points
ID: 38806830
I found the answer:
ActiveDocument.MailMerge.DataSource.DataFields("FaxNumber").Value
0
 
LVL 39

Author Closing Comment

by:thenelson
ID: 38823807
I found the answer myself
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This article will show you how to use shortcut menus in the Access run-time environment.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

830 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