OpenRecordSet help needed.

[PuddinPie]

Hello,

I seem to be a little crazy right now and cant figure out what is going on in my code that is messing everything up.

I'm trying to open a recordset and pull a value in a text column based on the value in a numeric column and I'm getting "error#3464 Data type mismatch in criteria expression."

Here's my code:

Private Sub xProductTreeview_Click()
'On Error GoTo Error_Handler
Dim db As dao.Database
Dim rst As dao.Recordset
Dim nodSelected As MSComctlLib.Node
Dim stOpenForm As String

Set db = CurrentDb
Set nodSelected = Me.xProductTreeview.SelectedItem

If Mid(nodSelected.Key, 1, 1) = "C" Then
Set rst = db.OpenRecordset("SELECT MenuItemForm FROM tblMainMenu WHERE [MenuItemID] = '1'")
'rst.MoveLast
MsgBox rst
Else
MsgBox Mid(nodSelected.Key, 6, 7)
End If

'Error_Handler:
'MsgBox "An error has occured. Please record the following error number and message" & vbNewLine & _
'"so that it can be provided to technical support." & vbNewLine & _
'"Error # " & Err.Number & vbNewLine & _
'"Error Message " & Err.Description

End Sub

Thank you in advance.

 

[pbaldy]

If MenuItemID is numeric you don't want the single quotes around the value.

 

[PuddinPie]

If I remove the "single quotes" I get error#13 Type Mismatch.

 

[vbaInet]

You are probably getting the type mismatch in the MsgBox rs line. Run the code as it was in your first post and tell us exactly what line it fails on.

 

[PuddinPie]

It fails on this line.

Set rst = db.OpenRecordset("SELECT MenuItemForm FROM tblMainMenu WHERE [MenuItemID] = '1'")

 

[vbaInet]

Did you try what pbaldy mentioned:

Set rst = db.OpenRecordset("SELECT MenuItemForm FROM tblMainMenu WHERE [MenuItemID] = 1")

Try the above.

Also, what Data Type is MenuItemID?

 

[PuddinPie]

Yes and I get error 13. Type mismatch.
MenuItemID is number and MenuItemForm is text.
I want it to find the text that is associated to the number in then table.

 

[pbaldy]

On the same line? Because this would need to be:


MsgBox rst!MenuItemForm

 

[PuddinPie]

That worked. Thank's.

 

[vbaInet]

That worked. Thank's.

Now I don't see why it should have errored in the Set line if the error was in the Msgbox?