Current error message: Run Time Error 3464: Data Type Mismatch in criteria expression. My questions are below Module1 code
A value is entered in the Textbox1 (on a subform) and a button run this code and fails due to Run Time Error 3464: Data Type Mismatch in criteria expression
Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop,'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"
fExecuteQuery strSQL, dbFailOnError ' data type mismatch fExecuteQuery is Module1. See code below
' reset textbox to blank
Me.Textbox1 = Null
End Sub
==============Module1=================================
Function fExecuteQuery(strQuery As String, _
Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
Optional blnReturnAuto As Boolean = False, _
Optional pdb As DAO.Database) As Long
Dim db As Database
Dim prm As DAO.Parameter
Dim qdf As QueryDef
Dim rst As DAO.Recordset
If Not pdb Is Nothing Then
Set db = pdb
Else
Set db = CurrentDb
End If
Select Case Left(strQuery, 7)
Case "INSERT ", "UPDATE ", "DELETE "
Set qdf = db.CreateQueryDef("", strQuery)
Case Else
Set qdf = db.QueryDefs(strQuery)
End Select
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
qdf.Execute intOptions debugger stops here
If blnReturnAuto Then
Set rst = db.OpenRecordset("SELECT @@Identity")
fExecuteQuery = rst(0)
rst.Close
End If
Set prm = Nothing
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Function
================questions===============================
Which code contains the mismatch datatype criteria expression? Is it the Private sub cmdUpdate or in Module1?
A value is entered in the Textbox1 (on a subform) and a button run this code and fails due to Run Time Error 3464: Data Type Mismatch in criteria expression
Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop,'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"
fExecuteQuery strSQL, dbFailOnError ' data type mismatch fExecuteQuery is Module1. See code below
' reset textbox to blank
Me.Textbox1 = Null
End Sub
==============Module1=================================
Function fExecuteQuery(strQuery As String, _
Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
Optional blnReturnAuto As Boolean = False, _
Optional pdb As DAO.Database) As Long
Dim db As Database
Dim prm As DAO.Parameter
Dim qdf As QueryDef
Dim rst As DAO.Recordset
If Not pdb Is Nothing Then
Set db = pdb
Else
Set db = CurrentDb
End If
Select Case Left(strQuery, 7)
Case "INSERT ", "UPDATE ", "DELETE "
Set qdf = db.CreateQueryDef("", strQuery)
Case Else
Set qdf = db.QueryDefs(strQuery)
End Select
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
qdf.Execute intOptions debugger stops here
If blnReturnAuto Then
Set rst = db.OpenRecordset("SELECT @@Identity")
fExecuteQuery = rst(0)
rst.Close
End If
Set prm = Nothing
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Function
================questions===============================
Which code contains the mismatch datatype criteria expression? Is it the Private sub cmdUpdate or in Module1?