Need Help with Recordsets

[NightZEN]

I am using some code converted from: faq703-760 to print a "report" to MS Word. I set it up on an offline copy of my database and it works great. Once I copied my Code and Queries over to the online version, I get a 'Run Time Error 3464 / Data Type Mismatch in Criterial Expression'.

The cause is this: the table i query has a key field of type TEXT which I had changed to AUTONUMBER in the online version. I made a copy of the online version, changed the TYPE back to TEXT, and the Code worked fine. The variable "Public strProdNum As String" declared in the Module pulls data from this field durring the ONCLICK proceedure like this: "strProdNum = Me.PartsID". When i go to debug after the error occurs it highlights this line in my code: "Set recSubmittal = db.OpenRecordset(strSQL"


I want to keep the Autonumber Feature, but I Also need this code to work. How can I fix this? Sorry to bounce you back and forth to the above code - Hope this makes sence!!

Thanks

 

[Rick39]

You might try something like:

"strProdNum = CStr(Me!PartsID)

 

[NightZEN]

I get the same error...

 

[Rick39]

Can you post your strSQL statement?

 

[NightZEN]

Here is the Code for the Command Button ONCLICK, and from my Module. Note that I have changed some names from FAQ703-760. Thanks!


Private Sub Command206_Click()

Dim db As DAO.Database
Dim recSubmittal As DAO.Recordset
Dim recSubmittal2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String


' Capture the field whose value will narrow your recordset down
strECNNum = Me.[ECN #]


strSQL = "SELECT * FROM qryTestWord WHERE [ECN NUMBER]= '" & strECNNum & "';"
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)

strSQL2 = "SELECT * FROM qryTestWordSub WHERE [ECN NUMBER]= '" & strECNNum & "';"
Set db = CurrentDb()
Set recSubmittal2 = db.OpenRecordset(strSQL2)


' This CreateSubmittal sub is created in the module
CreateSubmittal recSubmittal, recSubmittal2


End Sub

Option Compare Database 'Use database order for string comparisons

Option Explicit

' location of the template for ECN BOARD REPORT Email -
Public Const m_strDIR As String = "R:\Engineering\Databases\Engineering Database\"
Public Const m_strTEMPLATE As String = "ECNBoard.dot"

' sets up objects for use and Public variables to be shared in database -
Private m_objWord As Word.Application
Private m_objDoc As Word.Document
Public strECNNum As Variant
Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As DAO.Recordset)

Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)

m_objWord.Visible = True

InsertTextAtBookmark "ECNNo", recSubmit("ECN NUMBER&quot
InsertTextAtBookmark "PART", recSubmit("PART NUMBER&quot
InsertTextAtBookmark "DATE", recSubmit("RECEIVED DATE&quot
InsertTextAtBookmark "REQBY", recSubmit("REQUESTOR&quot
InsertTextAtBookmark "Engineer", recSubmit("ENGINEER&quot
InsertTextAtBookmark "TYPE", recSubmit("ECN TYPE&quot
InsertTextAtBookmark "Desc", recSubmit("DESCRIPTION&quot
InsertTextAtBookmark "Reason", recSubmit("REASON&quot
InsertTextAtBookmark "Actual", recSubmit("ACTUAL CHANGE&quot



'Generate the table data
'InsertSummaryTable recSubmit2


Set m_objDoc = Nothing
Set m_objWord = Nothing


End Sub

Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
' This finds the bookmarks in the Word template to place the data.
m_objDoc.Bookmarks(strBkmk).Select
m_objWord.Selection.Text = varText & ""

End Sub

Private Sub InsertSummaryTable(recR As DAO.Recordset)
' This pulls in the data for a table then highlights the data
' and creates a table in the Word document at a bookmark location
' for each field you want in the column of the table, have tabs
' surround it. Items in quotes are field names from the query/recordset
' If you need to have a blank column, just place vbTab in twice
On Error GoTo No_Record_Err
Dim strTable As String
Dim objTable As Word.Table

recR.MoveFirst
strTable = ""
While Not recR.EOF
strTable = strTable & vbTab & recR("discontinuedpart&quot & vbTab & vbTab & recR("5x5No&quot & vbCr
recR.MoveNext
Wend

InsertTextAtBookmark "DiscPart", strTable
Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

objTable.Select
objTable.Columns(1).Width = InchesToPoints(1.51)
objTable.Columns(2).Width = InchesToPoints(2.56)
objTable.Columns(3).Width = InchesToPoints(1.44)
objTable.Columns(4).Width = InchesToPoints(2.14)

Set objTable = Nothing
No_Record_Err:
Exit Sub

End Sub

 

[Rick39]

If the ECN NUMBER is actually a number, then you don't need the single quotes in the criteria:

strSQL = "SELECT * FROM qryTestWord WHERE [ECN NUMBER]= " & strECNNum & ";"
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)

 

[NightZEN]

KICK ASS! Thanks!