Surely you can get a recordcount??

[BNPMike]

I'm doing a telephone directory. You put in bits of surname and/or christian name. If there are zero records I want a message 'No matching records'. If it is one record I want to go straight to the details screen. If it is two or more records then I put up a subform to allow the user to select the person they want.

You'd think there's no reason why I can't do this with macros, but I have looked everywhere and can't find how you access the return codes from an SQL query.

Worse still, in VBA the .recordcount seems to give you numbers based on your star sign as much as the actual number of records.

Am I missing something? mike.stephens@bnpparibas.com

 

[shaddow]

If u need to use .RecordCount u need to set the right settings to the RecordSet object
I use this code even in VB with his ADODB.Recordset object cuz this always worked..

Set Con = Server.CreateObject( "ADODB.Connection" )
'Con.Open 
' open your connection to the dadabase here
Set Rs = Server.CreateObject ("ADODB.Recordset")

Const adOpenStatic = 3
Const adUseClient = 3
Const adLockPessimistic = 2

sub Exec(byval cmd)
'State
'0-Closed
'1-Open
 if rs.State=1 then rs.Close
 Rs.CursorType = adOpenStatic
 Rs.CursorLocation = adUseClient
 Rs.LockType = adLockPessimistic
 Rs.Source = cmd
 Rs.ActiveConnection = Con   
 Rs.Open 
end sub

And use Exec to call an querry and then use rs object to check rs.Recordcount ________
George, M