Using data entered in prompt to search database table

**BluRay** · 03-25-2011, 05:35 AM

Hey, I will try to explain what I want to do:

When I open a document, there will be prompt to enter the EmployeeID. The id entered will be used to search the data source, in this case a database table, and data like name and phone will be retrieved and displayed on the word document like shown:

EmployeeID: (from the prompt)
Name: (from database table)
Phone: (from database table)

I am able to do the first part. I did it by using a fill-in box. I recorded a macro which selects the fill-in from Quick Parts from Insert. But I have no idea on know to do the rest of the part.

Some help will be greatly appreciated.

**pike** · 03-26-2011, 02:19 AM

hi BluRay
Roy has a very good downloadable example
"J & R Database Form..."
http://www.excel-it.com/vba_examples.htm

**BluRay** · 03-26-2011, 04:48 AM

Thanks pike. That is a impressive example. But I was looking for some simpler way involving word and macro. I don't know much about the VB codes.

Oh, one thing, about the problem I asked, is it possible to accomplish this task using only word and macro?

**pike** · 03-26-2011, 05:05 AM

Hi BluRay
macro VBA are/mean the code

stripped royUK code from userform example

Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
    Dim f      As Integer

    strFind = Me.TextBox1.Value    'what to look for

    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
           
            FirstAddress = c.Address
     c.Offset(0, 1).Value = Me.TextBox2.Value
    c.Offset(0, 2).Value = Me.TextBox3.Value
    c.Offset(0, 3).Value = Me.TextBox4.Value
            
            
            
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
End Sub

worksheet functions "lookup"s or combine "Index" and "Match"
from the thread
http://www.excelforum.com/excel-work...orksheets.html

=VLOOKUP(D2,Sheet2!A:K,11,False)

=INDEX(Sheet2!K:K,MATCH(D4,Sheet2!A:A,0),1)