CODEWITHSUNDEEP
sqlvbams-accessms-word
Jackson Jenkins
Jackson Jenkins

Reputation: 1

How to search database records from access using Microsoft Word using VBA

I'm looking to fill Text Form fields in Word with table entries from the company's Access Database.

So far I have a user form that populates the combo boxes with the company's Project Numbers. I would like to have that when the user submits the client information from the selected project is put into text form fields in the Word document.

My problem is searching the table for the Project Number and accessing the record. When I check the Recordset value after the 'Find First' function it returns the first record in the table.

Here is my Code so far:

'Access Database
Dim db As Database

Dim rst As Recordset

Dim strPath As String

Dim doc As Document

Set doc = ThisDocument

strPath = "string path name"

Set db = OpenDatabase(strPath)

Set rst = db.OpenRecordset("Word Report Query")

rst.FindFirst "Project Number = " & ProjectBox.Value

Upvotes: 0

Views: 1826

Answers (2)

Jackson Jenkins
Jackson Jenkins

Reputation: 1

I ended up using a while loop to search through the database. Probably not the best solution but the only way I could reliably find the recordset.

'Access Database
Dim db As Database

Dim rst As Recordset

Dim strPath As String

Dim doc As Document

Set doc = ThisDocument

strPath = "path name"

Set db = OpenDatabase(strPath)

Set rst = db.OpenRecordset("Word Report Query")

'Find Selected Record
Do While rst![Project Number] <> ProjectBox.Value

rst.MoveNext

Loop

ActiveDocument.FormFields("Company").Result = rst![Client]
ActiveDocument.FormFields("Email").Result = EmployeeBox.Value
ActiveDocument.FormFields("Date").Result = DateBox.Value

If rst![Consultant] <> Null Then
    ActiveDocument.FormFields("Addressing").Result = rst![Consultant]
End If

There are no error checks for the project number because I use the database to populate the dropdown box options when the userform is initialized

'Populate Project Box Dropdown
Do While Not rst1.EOF

ProjectBox.AddItem rst1(0)

rst1.MoveNext

Loop

Upvotes: 0

Cahaba Data
Cahaba Data

Reputation: 622

Using an Access form to select the correct record - should be set up using a combo box and following the wizard.

Word Merge is the feature set of Office where working with Word - one can set up a template that links to an Access table or query or excel sheet - - and that data source is used to insert the data into the Word template/doc.

Alternatively Access can be used to create a Report, and then export that as an rtf Word file.

Upvotes: 0

Related Questions