Placing Wingding in Word 2007 form based on query results to access db

[jbarbara11]

Hi,

I am pulling data out of an Access db and using Word as a customized report format. I want put a Wingding character in a field on the form based on the query result. As an example of what I am trying to do, I have form with just a few fields. I have a dropdown box that gets populated from the database. I hit a Refresh button that then populates the fields. What i want is a field that updates with a wingding character, like a smiley or whatever wingding I chose. For example in the query, if the country is USA, then I put a smiley face (Wingding 74). If it is UK, then I put a star (Wingding 182). I am not sure what kind of form field to put it in, as you can see I tried a macrobutton. I cannot get this insert symbol part to work.

Here is what I have thus far:


Option Explicit'Define Variables


Private Sub UserForm_Initialize()


Dim dbDatabase As Database
Dim rsNorthwind As Recordset
Dim i As Integer
Dim aResults()


' This code activates the Database connection.
Set dbDatabase = OpenDatabase("C:\Users\jbarbara\Downloads\sharkins_hdi_dynamically_fill_Word_fields\Dynam icFormFill.mdb")


' This code opens the Employees table.


Set rsNorthwind = dbDatabase.OpenRecordset("Employees", dbOpenSnapshot)

i = 0

With rsNorthwind
' This code populates the combo box with the values



Do Until .EOF
wfLastName.AddItem (i)
wfLastName.Column(0, i) = .Fields("LastName")
.MoveNext
i = i + 1
Loop


End With
End Sub


Sub FillDependentFields()
'Fill form fields based on selected employee
'in wfEmployeeDropdown.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Document
Dim strSQL As String
Dim strPath As String
Dim f As Field
Set doc = ThisDocument
strSQL = "SELECT TitleOfCourtesy, FirstName, Country FROM Employees " _
& "WHERE LastName = '" _
& wfLastName.Value _
& "'"
strPath = "C:\Users\jbarbara\Downloads\sharkins_hdi_dynamically_fill_Word_fields\Dynam icFormFill.mdb"
Set db = OpenDatabase(strPath)
Set rst = db.OpenRecordset(strSQL)
'Ignore Null values from Access data.
On Error Resume Next
wfTitleOfCourtesy.Text = rst(0).Value
wfFirstName.Text = rst(1).Value
doc.FormFields("wfCountry").Result = rst(2).Value


f.Code.Font.Name = "WingDings"


If rst(2).Value = "USA" Then
f.Code.Text = "MACROBUTTON " + Chr(74)
End If
If rst(2).Value = "UK" Then
f.Code.Text = "MACROBUTTON " + Chr(182)

End If


'If rst(2).Value = "USA" Then doc.FormFields("Check1").CheckBox.Value = True
'If rst(2).Value <> "USA" Then doc.FormFields("Check1").CheckBox.Value = False
'doc.FormFields("wfTitleOfCourtesy").Result = rst(0).Value
Set db = Nothing
Set rst = Nothing
End Sub


Private Sub CommandButton1_Click()
FillDependentFields
End Sub


Private Sub Document_Close()
'Clear fields.
Dim doc As Document
Set doc = ThisDocument
doc.FormFields("wfFirstName").TextInput.Clear
doc.FormFields("wfTitleOfCourtesy").TextInput.Clear
'Close without saving or prompting.
ActiveDocument.Saved = True
End Sub

Private Sub wfLastName_Click()
End Sub

Private Sub wfTitleOfCourtesy_Click()
End Sub

Private Sub wfTitleOfCourtesy_Change()
End Sub



Looking forward to some help...Thanks in Advance!

[SamT]

Barbara,

Welcome to VBAExpress.

I am not real familiar with VBA for Word, but I noticed that you are coding for Fields and FormFields as if they were the same thing.

I see that in your code you have not set the Variable "f" to any object.

Instead of using an Object Variable for only one or two operations, it might be easier to access the Object through it's Collection.

Fields(index).Code.Font.Name = "WingDings"

'or

FormFields(name).Range.Text = Chr(74)

[jbarbara11]

Figured it out...



Sub FillDependentFields()
'Fill form fields based on selected employee
'in wfEmployeeDropdown.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Document
Dim strSQL As String
Dim strPath As String
Dim lng As Long
Dim lng2 As Long
Set doc = ThisDocument
strSQL = "SELECT TitleOfCourtesy, FirstName, Country FROM Employees " _
& "WHERE LastName = '" _
& wfLastName.Value _
& "'"
strPath = "C:\Users\jbarbara\Downloads\sharkins_hdi_dynamically_fill_Word_fields\Dynam icFormFill.mdb"
Set db = OpenDatabase(strPath)
Set rst = db.OpenRecordset(strSQL)
'Ignore Null values from Access data.
On Error Resume Next
wfTitleOfCourtesy.Text = rst(0).Value
wfFirstName.Text = rst(1).Value
wfCountry.Text = rst(2).Value

If rst(2).Value = "UK" Then
Selection.GoTo What:=wdGoToBookmark, Name:="bmIsUSA"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.InsertSymbol Font:="Wingdings", CharacterNumber:=-3928, Unicode _
:=True
Selection.GoTo What:=wdGoToBookmark, Name:="bmIsUK"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.InsertSymbol Font:="Wingdings", CharacterNumber:=-3843, Unicode _
:=True
Selection.TypeText Text:=""


ElseIf rst(2).Value = "USA" Then
Selection.GoTo What:=wdGoToBookmark, Name:="bmIsUSA"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.InsertSymbol Font:="Wingdings", CharacterNumber:=-3843, Unicode _
:=True
Selection.GoTo What:=wdGoToBookmark, Name:="bmIsUK"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.InsertSymbol Font:="Wingdings", CharacterNumber:=-3928, Unicode _
:=True
Selection.TypeText Text:=""
End If




'If rst(2).Value = "USA" Then For Each f In Selection.Fields
'f.Code.Font.Name = "WingDings"
'If rst(2).Value = "USA" Then
' f.Code.Text = "MACROBUTTON FillDependentFields " + Chr(254)
' End If
'Next
'If rst(2).Value = "USA" Then doc.FormFields("Check1").CheckBox.Value = True
'If rst(2).Value <> "USA" Then doc.FormFields("Check1").CheckBox.Value = False
doc.FormFields("wfTitleOfCourtesy").Result = rst(0).Value
Set db = Nothing
Set rst = Nothing
End Sub

[SamT]

Great! And thanks for publishing the solution. I am sure that someone will use it.

:beerchug: