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
Solved

VBA Code Help

Posted on 2006-11-27
9
1,089 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
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.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

829 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