Crazy about Recordset.Filter!!! (1 Viewer)

[muratas2]

Hi,
I've written below sub.

Public Sub Coun()
Dim cnn As Connection
Dim rs As New ADODB.Recordset
Dim r As Integer

r = Me!txtRef1

Set cnn = CurrentProject.Connection

rs.Open "tblItems", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
cnn.BeginTrans

rs.Filter = "RefNo=r"
If rs.RecordCount = 0 Then GoTo line1
rs.Delete
rs.Update

line1:
cnn.CommitTrans

End Sub

unfortunately, when I run it I receive 'RUN TIME ERROR 3001' on the statement
rs.Filter = "RefNo=r"

Do I have to use only values on the criteria string? Is there a way out to use variables as well? What's the best way to filter a recordset and determine Recordset.Recordcount?

Thanks in advance

murat

 

[BarkerD]

You can use variables in your filter criteria.
rs.Filter = "RefNo = " & r & ""
Make sure that you provide the spaces between the ampersands.

You don't need to use the Filter method unless you have more than one criteria.

In this instance it may be easier to use the Find method.

I would use an SQL string in the Source item and test its recordcount property.

Dim SQL as string
SQL = "SELECT * FROM tblitems WHERE RefNo = " & me![txtRef1] & ";"

when you use an SQL string, be sure to use the acCmdText option instead of acCmdTableDirect.

Hope this helps. I've only just been learning this myself.
Is there any special reason why you are using the ADO model?
I used it at first because I couldn't get the DAO methods to work.(Found out I just needed to add a Reference. Doh! )

I find the DAO object methods work much better.

[This message has been edited by BarkerD (edited 01-19-2000).]

 

[vicsar]

Here are my two cents, anyone facing this problem can use this code.

First I created a text box named txtRecordCount (format it to your liking)

Then I used this code, which can be assigned to a form event or to a button by making it a Sub, your choice.

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
Me.txtRecordCount.Value = rst.RecordCount & " records were retrieved"

I hope someone finds it useful