• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1096
  • Last Modified:

VBA Code Help

This code works fine except it does not pull the data for the current record.

Please help!

Public Function MergetoWord()
' This method creates a new document in MS Word 2002 using Automation.
On Error Resume Next
Dim rsEE As Recordset, iTemp As Integer
Dim WordObj As Word.Application

Set rsEE = DBEngine(0).Databases(0).OpenRecordset("tblEOI_Import", dbOpenTable)
rsEE.Index = "PrimaryKey"
rsEE.Seek "=", Forms!frmMember![txtSSN]
    If rsEE.NoMatch Then
        MsgBox "Invalid Record", vbOKOnly
        Exit Function
    End If

DoCmd.Hourglass True

Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
    Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True

' Warning
' Specify the correct drive and path to the
' file named thanks.dot in the line below.
WordObj.Documents.Add Template:="C:\DEV\EOI.dot", NewTemplate:=False
' The above drive and path must be fixed.

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Date"
    WordObj.Selection.TypeText Date
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="EE_FirstName"
    WordObj.Selection.TypeText rsEE![EE_FirstName]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="EE_LastName"
    WordObj.Selection.TypeText rsEE![EE_LastName]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="StreetAddress_1"
    WordObj.Selection.TypeText rsEE![EE_StreetAddress_1]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="StreetAddress_2"
    If IsNull(rsEE![EE_StreetAddress_2]) Then
        WordObj.Selection.TypeText ""
    Else
        WordObj.Selection.TypeText rsEE![StreetAddress_2]
    End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="StreetAddress_3"
    If IsNull(rsEE![EE_StreetAddress_3]) Then
        WordObj.Selection.TypeText ""
    Else
        WordObj.Selection.TypeText rsEE![StreetAddress_3]
    End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="City"
    WordObj.Selection.TypeText rsEE![EE_City]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="State"
    WordObj.Selection.TypeText rsEE![EE_State]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Zip"
    WordObj.Selection.TypeText rsEE![EE_Zip_Code]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="PhoneNumber"
    WordObj.Selection.TypeText rsCust![PhoneNumber]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="NumOrdered"
    WordObj.Selection.TypeText Forms!Orders![Quantity]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="ProductOrdered"
    If Forms!Orders![Quantity] > 1 Then
        WordObj.Selection.TypeText Forms!Orders![Item] & "s"
    Else
        WordObj.Selection.TypeText Forms!Orders![Item]
    End If

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="FName"
    iTemp = InStr(rsCust![ContactName], " ")
    If iTemp > 0 Then
        WordObj.Selection.TypeText Left$(rsCust![ContactName], iTemp - 1)
    End If

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="LetterName"
    WordObj.Selection.TypeText rsCust![ContactName]

DoEvents
WordObj.Activate
WordObj.Selection.MoveUp wdLine, 6

' Set the Word Object to nothing to free resources
Set WordObj = Nothing

DoCmd.Hourglass False

Exit Function

TemplateError:
   Set WordObj = Nothing
   Exit Function

End Function

0
Sariff
Asked:
Sariff
  • 3
  • 3
  • 2
  • +1
1 Solution
 
jefftwilleyCommented:
What exactly defines a current record?
0
 
SariffAuthor Commented:
It should be the "txtSSN" field.

-Sariff
0
 
jefftwilleyCommented:
revise this part

Public Function MergetoWord()
' This method creates a new document in MS Word 2002 using Automation.
On Error Resume Next
Dim rsEE As dao.Recordset, iTemp As Integer
Dim WordObj As Word.Application
dim strSQL as string

'Take out the single tics below if ssn is numeric
strSQL = "Select * from tblEOI_Import where ssn = '" & Forms!frmMember![txtSSN] & "';"
Set rsEE = DBEngine(0).Databases(0).OpenRecordset(strSQL)
if rsEE.eof then
        MsgBox "Invalid Record", vbOKOnly
        Exit Function
    End If

DoCmd.Hourglass True

Give that a go
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
p912sCommented:
>>MsgBox "Invalid Record", vbOKOnly

Does it ever say it didn't find a match on the seek?

0
 
SariffAuthor Commented:
Jeff - It did not work...

-Sariff
0
 
SariffAuthor Commented:
It is grabbing the first record in the table. It is not actually search for a match.

-Sariff
0
 
stevbeCommented:
you could open the recordset to the correct record directly ...

Set rsEE = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblEOI_Import WHERE SSN='" & Forms!frmMember![txtSSN] & "'")

    If rsEE.EOF Then
        MsgBox "Invalid Record", vbOKOnly
        Exit Function
    End If

Steve
0
 
jefftwilleyCommented:
Is there actually a value for Forms!frmMember![txtSSN]?

by using the SSN from your form as criteria for the SQL, it would open to the first, and possibly the only record that matches. That's not an indication of failure. Do the SSNs match?
J
0
 
p912sCommented:
Sariff,

Do you ever get the "Invalid Record" message? Or does it go right past that and then write the wrong record?

Scot
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now