Re: recordset is returning 0
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)
Re: recordset is returning 0
...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.