?
Solved

search MS Access table and return field contents

Posted on 2013-01-22
4
Medium Priority
?
253 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
[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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

771 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