asked on

StrConv() not working

Hi all,

I'm trying to change CAPITAL fields to normal fields where the first letter of each word is CAP where the rest is lower case. I have these fields NAME, ADDRESS, STATE, CITY.

NAME represents company name and instead of HOME BASE INC it should be Home Base Inc
ADDRESS represents the address and it should look like 12A Barrett Street not (12 A BARRETT STREET)
STATE should be New York, not NEW YORK
CITY same as the STATE field

I added these lines by using the vbProperCase function, but nothing is working. Still need to figure how I can make it work for other fields as well:
x = StrConv(rst![CITY], vbProperCase)
x = StrConv(rst![STATE], vbProperCase)

The whole code starts with a button on a form, loops through a table and exports specified fields into a Word merge document.

Any help would be appreciated.
Thanks.

currentdb

Private Sub Merge_Click()
   Dim Sql As String
   Dim pathFichierWord As String
   Dim Rs As DAO.Recordset
   Dim db As DAO.Database
   Set db = CurrentDb()
   Dim WordObj As Word.Application
   Dim docs As Word.Documents
   Dim doc As Word.Document
   Dim pb As New Form_frmProgBar
   Dim intK As Integer 
   Dim lngRecCount as long
   Dim x as String

   Dim path As String
   path = CurrentProject.path
   pathFichierWord = path + "\" + "IN\" + "letter2.docx"
   Sql = "SELECT * FROM tableM"
   Set Rs = db.OpenRecordset(Sql)

    On Error Resume Next
    Set WordObj = GetObject(, "Word.Application") 'New Word.Application
    If Err.Number <> 0 Then
       Set WordObj = CreateObject("Word.Application")
    End If
    WordObj.Visible = False
    
    rs.movelast
    lngRecCount = rs.RecordCount
    rs.movefirst

    pb.SetMessage "Upload to Word in progress...please wait"
    pb.SetCaption "Upload in progress"
    pb.SetBarVisible True

    While not rs.eof
        pb.SetBarPercent (rs.absoluteposition+1 )/ lngRecCount * 100
        x = StrConv(rst![CITY], vbProperCase)
        x = StrConv(rst![STATE], vbProperCase)

 
        Wait (2)
    
        WordObj.Documents.Add Template:=pathFichierWord, newtemplate:=False
        With WordObj.Selection
            NAME = Rs("NAME")
            ADDRESS = Rs("ADDRESS")
            CITY = Rs("CITY")
            STATE = Rs("STATE")
            ZIP_CODE = Rs("ZIP_CODE")
            NO_FILE = Rs("NO_FILE")
            AGENT = Rs("AGENT")
            NO_PHONE_AGENT = Rs("NO_PHONE_AGENT")
             
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="NAME"
            WordObj.Selection.TypeText NAME
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="ADDRESS"
            WordObj.Selection.TypeText ADDRESS
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="CITY"
            WordObj.Selection.TypeText CITY
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="STATE"
            WordObj.Selection.TypeText STATE
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="ZIP_CODE"
            WordObj.Selection.TypeText ZIP_CODE
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="NO_FILE"
            WordObj.Selection.TypeText NO_FILE
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="AGENT"
            WordObj.Selection.TypeText AGENT
                
            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="NO_PHONE_AGENT"
            WordObj.Selection.TypeText NO_PHONE_AGENT
                
            WordObj.ActiveDocument.SaveAs path + "\" + "Output\" + NO_FILE + ".docx"
            WordObj.ActiveDocument.close
                  
        End With

        Rs.MoveNext
    Wend
          
    Rs.Close
    Set Rs = Nothing
          
    Set WordObj = Nothing
     
    pb.SetBarPercent (100)
    pb.SetMessage "Done!"

    Wait (5)
    Set pb = Nothing
   
End Sub

Open in new window

Patrick Matthews

All that the lines:

x = StrConv(rst![CITY], vbProperCase)
x = StrConv(rst![STATE], vbProperCase)

are doing is assigning a value to the variable x--which you then never actually use. Indeed, the second line overwrites the first :)

Try instead converting:

NAME = Rs("NAME")
ADDRESS = Rs("ADDRESS")
CITY = Rs("CITY")
STATE = Rs("STATE")
ZIP_CODE = Rs("ZIP_CODE")
NO_FILE = Rs("NO_FILE")
AGENT = Rs("AGENT")
NO_PHONE_AGENT = Rs("NO_PHONE_AGENT")

to:

StrConvert(NAME = Rs("NAME"), vbProperCase)
StrConvert(ADDRESS = Rs("ADDRESS"), vbProperCase)

et cetera