Solved

search MS Access table and return field contents

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Outlook Free & Paid Tools
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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