Solved

VBA Code Help

Posted on 2006-11-27
9
1,091 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

Industry Leaders: 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!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 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