Check if Record exists in Access Table

[SKM]

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

[DKenny]

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.

SELECT Count(*) FROM RecordInUse WHERE OurRef = ValueToSearchFor

[Hack]

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.

[DKenny]

You say tomato, I say...

[Al42]

You say tomato, I say...

...faster.

[SKM]

Hi,

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

Thanks,
SKM

[nagasrikanth]

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

[dee-u]

Something like...

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