Solved

VBA Code Help

Posted on 2006-11-27
9
1,088 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can not open the Access Help ? 3 52
Passing variables to a function 6 33
How can I Flash a mandatory field in Access Form? 13 45
Combobox row source 2 19
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

816 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

11 Experts available now in Live!

Get 1:1 Help Now