Hi guys good day.

Sorry for the delayed response to my previous post.We currently just change our internet connection.

I just want to ask on how to get the data of this sql statement because it only returns the total records found.

rs.open "select count(*) as idnumber from tblstudent where idnumber='" & idnumvar & "'",conn,adopenstatic, adlockoptimistic,adcmdtext

totalcount=rs.fields("idnumber")

can i get the data that this sql query returns in order for me to avoid requerying.?

Thank you for giving time.

God bless us all.

Recommended Answers

All 3 Replies

hi Blocker,
i hope the following will be of help.

Private Sub CmdRecordcount_Click()
  'in the following example i have selected all the fields in the table
  'this will help in referencing all the fields in the table

  rs.Open "select * from tbl_studentdetails where studentid='" & stuid & "'", cn, adOpenStatic, adLockOptimistic, adCmdText
  noofrecords = rs.RecordCount

  Print "Number of records = " & noofrecords 'this returns number of records
  Print rs.Fields("studentid") & vbTab & rs.Fields("FirstName") & vbTab & rs.Fields("LastName") 'this returns fields in the record set

End Sub

Just a note of warning, doing a select * and then using variable = rs.recordcount may return undesirable results. To be more specific, it may return a -1, which means it returned records (not zero (0)), but the amount of records are larger than the page size. So to fix this possible result, I suggest that you do...

Rs.MoveLast
NoOfRecords = Rs.RecordCount
Rs.MoveFirst

Good Luck

thank you guys for all your help. Ive just found a good solution yesternight. I made it like this. It does not return the -1 value even if it has more data.

while not rs.eof=true
    var1=rs.fields("lastname").value
    rs.movenext
wend

This thread was solve..

Thanks for all the help.God bless us all.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.