type-declaration character error

[kermit]

this one has me baffled.

I have created stuff like this many times before, but this one comes up with an error: "Type-Declaration character does not match declared data type.

all i am doing is creating a recordset and i want to get the values out of the record that it finds.

any ideas???
it errors out on the last line on the "rs!".
here is my code:


Dim db As Database
Dim rs As Recordset
Dim strsql As String
Dim number_of_uses As String

scan = Me.ScanID
firstletter = Left(scan, 1)

If firstletter = "t" Then
scan = Mid(scan, 2, Len([scan]))
End If

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT distinctrow [Master File].* FROM [Master File] WHERE ((([Master File].[Tool Serial Number])=""" & scan & """) AND (([Master File].Status)=""Active""));", dbOpenDynaset)

If rs.RecordCount = 0 Then
MsgBox "Incorrect Tool Serial Number"
Exit Sub
End If

number_of_uses = rs!([Number of Uses(if by usage)])



thanks for your help!
-kermit

 

[Calvin]

Not that it is generating an error but changing the double quotes inside the string in the SQL statement to single quotes would be a more correct SQL syntax. But you still need the double quote at the the ends of the string and where your concatinating the SCAN variable name into the string.

Set rs = db.OpenRecordset("SELECT distinctrow [Master File].* FROM [Master File] WHERE ((([Master File].[Tool Serial Number])='" & scan & "') AND (([Master File].Status)='Active'));", dbOpenDynaset)

After this you need a couple of things before you can do the record count.
First you need to check to see if the recordset is empty, it if is empy and you try to use RecordCount you will probably get the error you mentioned above. so insert the following

If rs.BOF and rs.EOF then  'the recordset is empty
     MsgBox "Incorrect Tool Serial Number"
     exit sub
end if

'there was matching data
rs.movelast    'you have to move to the last record in the recordset to get an accurate recordcount.
dim intRecCount as Integer
intRecCount = rs.RecordCount
rs.movefirst
number_of_uses = rs!([Number of Uses(if by usage)])

if you want to know more about the idiosyncrasies of the RecordCount start with reading my last entry in this thread.

 

[kermit]

Calvin-
thanks for the help, but i still get the same error.
here is what my code looks like now:

Dim db As Database
Dim rs As Recordset
Dim strsql As String
Dim number_of_uses As String

scan = Me.ScanID
firstletter = Left(scan, 1)

If firstletter = "t" Then
scan = Mid(scan, 2, Len([scan]))
End If

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT distinctrow [Master File].* FROM [Master File] WHERE ((([Master File].[Tool Serial Number])='" & scan & "') AND (([Master File].Status)='Active'));", dbOpenDynaset)

If rs.BOF And rs.EOF Then
MsgBox "Incorrect Tool Serial Number"
Exit Sub
End If

rs.MoveFirst

number_of_uses = rs!([Number of Uses(if by usage)])




it still gives me the type-declaration error right at the "rs!(...)"

the record count was not the important part, i just needed it to see if there were any records.

the part i need is to get the data out of the recordset. it just does not want to assign it.

any other ideas?
-kermit

 

[kermit]

Calvin and anyone else who gets this stinking error-

i found the darn problem with this!

if you put it as:
rs!([variable])

you will get the type-declaration error.

but for some reason if you put it as:
rs![variable]

it works fine!

just get rid of the darn parens.

thanks again calvin-
-kermit