recordset is returning 0

[samantha72]

I am trying to get back a recordset which contains a number
My database looks roughly like this

IdNum Name
8 Sam
9 John
10 Sara

I am getting back sam and john when they are selected but when
it comes to sara, i am getting back a 0
The same thing is happening for record no 20, 30 etc., infact anything
with a trailing 0, I get back a 0 in my recordset. If it is 21 then I get back 21
The idnum field in the database is a long integer.
I have tried defining my variable in vb as a long and it still does not work


Public Sub Getidnumber(intidnum As Integer)

Dim rsrecord As ADODB.Recordset


Set rsrecord = New ADODB.Recordset
rsrecord.Open "Select idnum, name from Table1", gCnn, adOpenDynamic, adLockOptimistic

rsrecord.MoveFirst
Do Until rsrecord.EOF


'check for the exact match
If rsrecord("name") = frmFirst.txtName.text Then
intidnum = rsrecord("idnum")
End If
rsrecord.MoveNext
Loop
End If

Please help. Thankssss

[wbeetge]

Hi Samantha

Try replacing your sub with a function
You are trying to return a variable from a sub and it is not intended for that.
Alternatively, you can declare the variable global. It should work fine then.

Please check the revised code fo find the exact record - much faster and cleaner (Let SQL do the hard work for you).

Public Function Getidnumber(intidnum As Integer)

Dim rsrecord As ADODB.Recordset

Set rsrecord = New ADODB.Recordset
rsrecord.Open "Select idnum, name from Table1 where name = '" & frmFirst.txtName & "'", gCnn, adOpenDynamic, adLockOptimistic

if NOT rsrecord.BOF then
intidnum = rsrecord("idnum")
else
msgbox "No match found!"
intidnum = 0
End If

End Function

Having set the query to find exactly what your loop was trying to achieve, the result will tell you immediately if it found a match or not.
BOF - no match found
Not BOF - Match Found (Use .movelast and .recordcount to establish how many matches has been found)

[Cimperiali]

...Last line of function Wbeetge suggested should be
Getidnumber= intidnum
to return the value.
I cannot test it now, but it seems to me you could add a .value statement:
'check for the exact match
If rsrecord("name") = frmFirst.txtName.text Then
intidnum = rsrecord("idnum").value 'this should return the value stored
End If

However, if you get 1 to 21 but miss 10 and 20, I am afraid bug is not in code: it should be in data. You sure you do not have doubled records (=records with all values the same)? - to discover it out, make a select * from your table without any where clauses.
Hope it helped
Cesare Inmperiali

Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.