datatype mismatch error, can't figure out

[aksesvibiey]

Hi,
When I click this buton I get an error on one of the following lines.. I can't figure out why and how to fix it.
the error I get is
run-time error '3464':
Data type mismatch in criteria expression.
I made the part where the error occurs in bold and italics

'submit for approval
Private Sub Command22_Click()
Dim rs As Object
Dim rs2 As Recordset
Dim db As Database
Dim name As String
Dim x As Integer 'will be used as flag for do while loop
Dim cnt As Integer 'this will contain the number of records in the recordset

'MsgBox (Me.projno)
Set db = CurrentDb

Answer = MsgBox("Are you sure you want to submit this timesheet?", vbYesNo)
'if cancelled
If Answer = vbNo Then
Else
x = 0 'initialize flag
Set rs = Me.Recordset.Clone

rs.MoveLast
cnt = rs.RecordCount
rs.MoveFirst

Do While x < cnt
If rs!statusPM = "pending" Then
MsgBox "This timesheet has already been submitted. You can't submit this again."
x = cnt
End If
If rs!statusPM = "approved" Then
MsgBox "This timesheet has already been approved by your supervisor. You can't submit this again."
x = cnt
Else
MsgBox (rs!projno)
Set rs2 = db.OpenRecordset("SELECT projmanager FROM Projects WHERE projno =" & rs!projno)
Do While Not rs2.EOF
name = rs2!projmanager
MsgBox (name)
rs2.MoveNext
Loop
rs.Edit
rs!statusPM = "pending"
rs!status = "pending"
rs.Update
x = x + 1
'MsgBox (rs!projno)
rs.MoveNext
End If
Loop
'clear variables
Set db = Nothing
Set rs2 = Nothing
End If
End Sub

Thanks in advance,

 

[Bat17]

my guess would be that projno is text so you need to wrap it in quotes

Set rs2 = db.OpenRecordset("SELECT projmanager FROM Projects WHERE projno ='" & rs!projno &"'")

Spaced out that is - =' " & rs!projno & " ' ")

HTH

Peter

 

[aksesvibiey]

Hi peter,

my guess would be that projno is text so you need to wrap it in quotes

Set rs2 = db.OpenRecordset("SELECT projmanager FROM Projects WHERE projno ='" & rs!projno &"'")

Spaced out that is - =' " & rs!projno & " ' ")

HTH

Peter

I tried it but now I get this

Run-time error '13':
Type mismatch

any idea? I really don't know why.. loads of thanks

 

[Bat17]

what data type is projno?

peter

 

[namliam]

Projno sounds like a number field

Set rs2 = db.OpenRecordset
is a DAO recordset open command

Dim rs2 As Recordset
Dim db As Database
While you are not "disambiguating" your declerations, thus rs2 could very well be an ADO recordset. Doublecheck you references that you are using DAO indeed.

 

[boblarson]

Spaced out that is - =' " & rs!projno & " ' ")

Peter: actually in the case of this you DON'T want it spaced out.

it SHOULD be:
='" & rs!projno & "'")

Otherwise you are asking it to include the space on either side of the text for purposes of comparing the text with the other text which doesn't include a space on either side.

aksesvibiey: I echo Peter's question about the actual datatype of projno in your table.

 

[aksesvibiey]

it is of type text..

 

[boblarson]

So have you tried Peter's suggestion with my change (no spaces between the single quotes and the item)?

Set rs2 = db.OpenRecordset("SELECT projmanager FROM Projects WHERE projno ='" & rs!projno &"'")

Another thing to try might be to assign rs!projno to a string variable before using it in the code:

strProjNo = rs!projno
Set rs2 = db.OpenRecordset("SELECT projmanager FROM Projects WHERE projno ='" & strProjNo &"'")

 

[Bat17]

I just provided the spaced version to make it easier to see, but I should have made it clear that it was the other version to use

Peter

 

[boblarson]

I just provided the spaced version to make it easier to see, but I should have made it clear that it was the other version to use

I know, sometimes what seems logical at the time, to yourself, winds up being confusing to someone else but there's not much you can do about that except to be as explicit about what you are saying all the times, which isn't necessarily either feasible or good.

 

[aksesvibiey]

hello to all of you..

don't be sad Peter, I understood what you meant with your post, I didn't do it with spaces but I appreciate that you showed it with spaces, I understood it easily, thanks
but unfortunately it didn't fix the problem..

Hi boblarson, thanks for the help too
yep I already tried that before, I assigned it to a variable but still no success

namliam, I got confused with what you said so I did a little research on the net.. then I changed some of my codes..
rs2 is now
Dim rs2 As DAO.Recordset, and voila! it worked thanks!

thanks to all of you guys, I never would have fixed this without all your help.

Take care!
michelle

 

[namliam]

I did a little research on the net..

rs2 is now
Dim rs2 As DAO.Recordset, and voila! it worked thanks!

A little research will make you remember

Make sure you do the same for Currentdb to prevent problems and try a search on the forum for DAO vs ADO and/or Disambiguate you will find (eventually) a very worthwhile thread to read on both subjects.

 

[Ritchie]

I got the same error (3464) today and Googled for a while until I gave up and had to find the answer myself! The database was written in the dark ages by someone else and I discovered that one field in the offending table was configured as Required=No, Allow Zero Length=No Indexed = Yes(No duplicates). One record had no entry in this field and when I deleted the record everything worked fine.

Regards,
Ritchie