Sariff
asked on
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).O penRecords et("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.Applica tion")
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_StreetAddr ess_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_StreetAddr ess_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
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).O
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.Applica
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"
' The above drive and path must be fixed.
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Date"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="EE_FirstName"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="EE_LastName"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="StreetAddress_1"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="StreetAddress_2"
If IsNull(rsEE![EE_StreetAddr
WordObj.Selection.TypeText
Else
WordObj.Selection.TypeText
End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="StreetAddress_3"
If IsNull(rsEE![EE_StreetAddr
WordObj.Selection.TypeText
Else
WordObj.Selection.TypeText
End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="City"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="State"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Zip"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="PhoneNumber"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="NumOrdered"
WordObj.Selection.TypeText
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="ProductOrdered"
If Forms!Orders![Quantity] > 1 Then
WordObj.Selection.TypeText
Else
WordObj.Selection.TypeText
End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="FName"
iTemp = InStr(rsCust![ContactName]
If iTemp > 0 Then
WordObj.Selection.TypeText
End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="LetterName"
WordObj.Selection.TypeText
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
What exactly defines a current record?
ASKER
It should be the "txtSSN" field.
-Sariff
-Sariff
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).O penRecords et(strSQL)
if rsEE.eof then
MsgBox "Invalid Record", vbOKOnly
Exit Function
End If
DoCmd.Hourglass True
Give that a go
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).O
if rsEE.eof then
MsgBox "Invalid Record", vbOKOnly
Exit Function
End If
DoCmd.Hourglass True
Give that a go
>>MsgBox "Invalid Record", vbOKOnly
Does it ever say it didn't find a match on the seek?
Does it ever say it didn't find a match on the seek?
ASKER
Jeff - It did not work...
-Sariff
-Sariff
ASKER
It is grabbing the first record in the table. It is not actually search for a match.
-Sariff
-Sariff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Sariff,
Do you ever get the "Invalid Record" message? Or does it go right past that and then write the wrong record?
Scot
Do you ever get the "Invalid Record" message? Or does it go right past that and then write the wrong record?
Scot