Check if Record exists in Access Table

Hello,

I'm using Access 2000 and VB6.

I need to do a search to check if a record exists in a table, if it does I then need to display a message saying so.

The record being searched for is a Number in a field named "OurRef" in a table named "RecordInUse".

Any ideas because I think I am missing the obvious sollution.

Thanks,
SKM

Run a query against the table to see how many records have the value you are searcing for. If the answer is zero, then the value doesn't exist.

Code:

---

SELECT Count(*) FROM RecordInUse WHERE OurRef = ValueToSearchFor

---

Do a SELECT OurRef FROM RecordInUse WHERE OurRef = Value_To_Look_For

Create a recordset

If the recordset is empty, it doesn't exist. If the recordset is not empty, it does.

You say tomato, I say...

Quote:

---

Originally Posted by DKenny

You say tomato, I say...

---

...faster. :)

Hi,

How do I find out the value of the Recordset or if the recordset is empty.

Thanks,
SKM

VB Code:

msgbox ObjRS("XYZ")       'Displays the value in the XYZ column(of current row)

Something like...

VB Code:

Dim adoRecordset As ADODB.Recordset
Set adoRecordset = New ADODB.Recordset
With adoRecordset
    .Open
    If Not (.EOF And .BOF) Then
        MsgBox "There is record!"
    Else
        MsgBox "There is no record!"
    End If
    .Close
End With
Set adoRecordset = Nothing