Testing for a record

[Russie]

Hi.

I'm using the below code to test for the presence of a record in tblControlledRiskAssessment:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblControlledRiskAssessment")

If rs.EOF = True Then
MsgBox ("empty")
Else
rs.FindFirst "[RiskID] = " & Me.txtRiskID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
End If
End If

db.Close
Set rs = Nothing

The error I get is 'Operation not supported by this type of object'. Runtime error 3251.

Is this a references issue?

If anyone knows why this is happening or of a simpler method for testing for a record in another table then I'd be grateful to hear it.

Thanks.

Russie

 

[KenReay]

I assume you are using a version of Access after Access97?

Fully qualify your DIM's as below

Your code is using DAO, be sure you have a reference to DAO 3.6 library (version may vary, depending on what you have installed on your PC)

If problem persists, please state the line on which the error occurs, it would help

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblControlledRiskAssessment")

If rs.EOF = True Then
MsgBox ("empty")
Else
rs.FindFirst "[RiskID] = " & Me.txtRiskID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
End If
End If

db.Close
Set rs = Nothing


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Russie]

Hi Ken.

I have referenced DAO 3.6.

I've changed the code to DAO and I still get the same error.

The error happens on the row below:

rs.FindFirst "[RiskID] = " & Me.txtRiskID

Could it be the data type of Me.txtRiskID that's the problem? txtRiskId is a text field.

Regards.

Russie

 

[PHV]

What about this ?
rs.FindFirst "[RiskID] = '" & Me.txtRiskID & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

Hi

If its a text field you need

rs.FindFirst "[RiskID] = '" & Me.txtRiskID & "'"


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK