Solved

VBA Code Help

Posted on 2006-11-27
9
1,087 Views
Last Modified: 2012-06-21
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
Comment
Question by:Sariff
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18022478
What exactly defines a current record?
0
 

Author Comment

by:Sariff
ID: 18022494
It should be the "txtSSN" field.

-Sariff
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18023121
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
 
LVL 18

Expert Comment

by:p912s
ID: 18023161
>>MsgBox "Invalid Record", vbOKOnly

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

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Sariff
ID: 18026886
Jeff - It did not work...

-Sariff
0
 

Author Comment

by:Sariff
ID: 18026944
It is grabbing the first record in the table. It is not actually search for a match.

-Sariff
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 18027578
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18027739
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
 
LVL 18

Expert Comment

by:p912s
ID: 18032661
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now