Solved

search MS Access table and return field contents

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

803 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