Unknown VBscript error reason 1

[monoDeveloper]

Hi everybody

I write this script to insert then check insertion done or not
But I get error message " "Data type mismatch in criteria Expression" on line [highlight #FCE94F]Set rs = db.OpenRecordset (strSQL)
[/highlight]
When I try to run the select statement as you will see in coming code:

Private Sub cmd_Create_Request_Click()

'Run Insert new suppy_request query
DoCmd.RunSQL (" INSERT INTO SupplyRequest( supplierID, supp_Req_Date, Creator_U_ID) " & _
" Values ( " & _
"[Forms]![frm_Add_Supply_Request]![cboSupplier], " & _
"[Forms]![frm_Add_Supply_Request]![txtDate], " & _
"[Forms]![frm_Add_Supply_Request]![txtEmpNumber] )")

'================
'Run SQL statement to confirm Insertion process done
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intResult As Integer

'Open a snapshot-type Recordset based on an SQL statement
inResult = txtRequestNumber
strSQL = (" SELECT supp_Req_SN FROM SupplyRequest WHERE( supp_Req_SN = '" & inResult & "')")

Set db = CurrentDb
Set rs = db.OpenRecordset (strSQL)

intResultConfirm = rs.RecordCount


rs.Close
Set rs = Nothing
Set db = Nothing

If (inResultConfirm > 0) Then
MsgBox "Insertion Process done... Record # " + intResultConfirm

inResult = DMax("supp_Req_SN", "SupplyRequest") + 1
Me.txtRequestNumber = inResult
Else
MsgBox "Data process failed", vbInformation
End If

End Sub

----
Can anybody find the incorrect point & tell me how to correct it.

Will be waiting

 

[Andrzejek]

[green]
'Open a snapshot-type Recordset based on an SQL statement[/green]
inResult = txtRequestNumber
strSQL = " SELECT supp_Req_SN FROM SupplyRequest WHERE( supp_Req_SN = '" & inResult & "')"
[red]
Debug.Print strSQL
[/red]
Set db = CurrentDb
Set rs = db.OpenRecordset (strSQL)

What do you get from RED piece of code?
How is supp_Req_SN defined? As number? Text?

Have fun.

---- Andy

 

[monoDeveloper]

I define "supp_Req_SN" as number in Database

 

[monoDeveloper]

Debug.Print strSQL , gives me nothing

 

[Andrzejek]

You get nothing in the Immediate Window from Debug line?

And if supp_Req_SN is a number, you don't need quotes around it in your SQL:
[tt]
strSQL = "SELECT supp_Req_SN FROM SupplyRequest WHERE supp_Req_SN = " & inResult
[/tt]



Have fun.

---- Andy

 

[monoDeveloper]

Thanks, It works now .

 

[dhookom]

monoDeveloper,
I noticed you are new to Tek-Tips so welcome and keep coming back.

When someone provides the answer to your questions it is appropriate to identify the answer by clicking the "Like this post? Star it!" link.

This both recognizes Andy for taking the time to provide a correct reply and marks the thread as answered.

Duane
Hook'D on Access
MS Access MVP