Link to home
Start Free TrialLog in
Avatar of diek_nf
diek_nfFlag for Canada

asked on

Wanted Helpful ADO Problem Solver - Part 3

Hi,
I am trying to figure out how to determine if a query has no match. I know with dao it was by way of the .nomatch. I am still getting my head wrapped around ado. I have an ado data control. Here is my failed attempt:
=======================================
'dat_Fin_Frm1 is the ado control    

dat_Fin_Frm1.RecordSource = ("SELECT * " & _
                                "FROM Financial " & _
                                "WHERE [BBINumber_Fin] = " & CInt(i_BBINumber) & "")

       
    If dat_Fin_Frm1.RecordSource.NoMatch Then 'NEW ENTRY
========================================
If someone could tell me how to sort this out I would be grateful, and give you my last 10 points.

diek
Avatar of Ashron
Ashron

Which type of ADO control is it ? Recordset, Connection ?

Anyway, your data will eventually be returned in a Recordset. Just use the EOF method to know if there is a result. You can then iterate in your recordset by using MoveNext, like this

dim oRec as Recorset

set oRec = MyConn.Execute "SQL Statement"

if oRec.EOF then
   ' There is no result
else
  do until oRec.EOF
    ' Do something
    oRec.MoveNext
  loop
end if


Hope this helps
Avatar of diek_nf

ASKER

Ashron,
It is a ADO Data Control, Version6.0(SP3).

diek
The recordsource will have no records and the EOF property will be true if there are no matches.

Use this....
         
    If dat_Fin_Frm1.Recordset.EOF Then 'NEW ENTRY
Better yet, check the following:
-  Make sure your recordset is not at either EOF AND BOF.  If both are true, then you have an empty recordset...
Avatar of diek_nf

ASKER

Paul,
Please do not get annoyed,because I truly appreciate your help. I just do not want to have my question locked. I am going to give that a try, and the bof. However I am wondering won't no record generate an error? Anyways, I will get back shortly because I have to solve this, my boss is loosing patience.

diek
ASKER CERTIFIED SOLUTION
Avatar of pauljk
pauljk

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of diek_nf

ASKER

Adjusted points from 10 to 20
Avatar of diek_nf

ASKER

Adjusted points from 20 to 30
Avatar of diek_nf

ASKER

Comment accepted as answer
Avatar of diek_nf

ASKER

Paul,
Thank you. I did a combo of yours and some other code. It appears to work fine. It may be redundant, I might adjust it later. So :

dat_Fin_Frm1.RecordSource = ("SELECT * " & _
                                          "FROM Financial " & _
                                          "WHERE [BBINumber_Fin] = " & CInt(i_BBINumber) & "")

dat_Fin_Frm1.Recordset.Filter = "[BBINumber_Fin] = " & CInt(i_BBINumber) & ""

If CStr(dat_Fin_Frm1.Recordset.AbsolutePosition) = -1 Then  'NEW ENTRY