Solved

search MS Access table and return field contents

Posted on 2013-01-22
4
235 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
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…

932 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

11 Experts available now in Live!

Get 1:1 Help Now