Extract fields from a database dependant on a user inputted key va

[DonC]

I would like to have a Word template that allows a user to enter an ID and
have a macro obtain address details and other information from a database and
populate the document with that information.

Anyone have a example of this type of thing?

Don C

 

[Karl E. Peterson]

I would like to have a Word template that allows a user to enter an ID and
have a macro obtain address details and other information from a database and
populate the document with that information.

Anyone have a example of this type of thing?

Any example would be highly specific to the task at hand, as there are several
distinct steps involved. Which of those steps are you having difficulty with:

- getting the user input
- querying the database
- populating your document

?

 

[DonC]

Any example would be highly specific to the task at hand, as there are several
distinct steps involved. Which of those steps are you having difficulty with:

- getting the user input
- querying the database
- populating your document

Being totally new to VBA, everything. I have worked out how to get VB forms
to take an ID and display all the data, so I am getting to grips with DB
connection and VB scripts there, but I have never used a "user form" or a
macro in any word document, although I have been using Word since it first
appeared.

So I need to know:
How to put up a form to get the ID
Pass it to a macro, that performs a select of the data
Populate the fields contained in the document with the retrieved data.

 

[Doug Robbins - Word MVP]

Better to just load all of the data into a combobox or listbox on a user
form and then insert the data for the selected record

To start with creating a userform, See the article "How to create a
Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Then to load a ListBox on the userform with data from and Access table, use

Private Sub UserForm_Initialize()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim NoOfRecords As Long

' Open the database

Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")

' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT * FROM Owners")

' Determine the number of retrieved records

With rs

.MoveLast

NoOfRecords = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records

ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

End Sub



Then, here's how you get the data from the selected record into the document



Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[DonC]

Hi Doug

Thanks, looks good so far.

Do you have an example of pull data from an ODBC source or a MySQL DB?

Don C

 

[Doug Robbins - Word MVP]

I don't know anything about MySQL and whether or not it exposes itself to
VBA. However, here is how you would load a list box with data from an
SQLExpress database

Private Sub UserForm_Activate()

For i = lstSites.ListCount To 1 Step -1
lstSites.RemoveItem (i - 1)
Next i
For i = lstProducts.ListCount To 1 Step -1
lstProducts.RemoveItem (i - 1)
Next i
constring = "Provider=SQLOLEDB.1;" _
& "Server=GLOBAL-C66780E6\SQLEXPRESS;" _
& "Database=ChangedsoIdonotgetintostrife;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;"
con.ConnectionString = constring
con.Open
Set rst.ActiveConnection = con
' Load the UserListBox with the retrieved records
With rst
.Source = "SELECT * from UserName"
For i = 1 To .Fields.Count
lstUserFields.AddItem .Fields(i - 1).Name
Next i
lstUser.ColumnCount = .Fields.Count
.Open
i = .RecordCount
lstUser.Column = .GetRows(i)
End With
Set rst = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP