count records from table maching criteria

[Diavolo]

Hello, there is 2 tables related one to may. relation field is bylos_nr

In general form I need to display how many records in table 2 maching criteria bylos_nr=me!bylos_nr

I have try use dcount, but have error messege building expresion.
Help

sorry for my english...

 

[Al Borges]

Hi Diavolo:

Using a Parent/Child form situation, the way that I've done it is:

Dim rst As Recordset
Set rst = Forms![sample1]![samplembed].Form.RecordsetClone
rst.MoveLast
rst.MoveFirst
MsgBox rst.recordcount

(You simply set the child form to be filtered on bylos_nr=me)

Alternatively, you can do:

Dim dbs as Database, rst As Recordset
Set dbs = CurrentDb()
SQLStmt = "SELECT YourTable.* FROM YourTable Where YourField = " & chr(34) &
"bylos_nr=me" & chr(34) & ";"
Set rst = dbs.OpenRecordset(SQLStmt, dbOpenDynaset)
With rst
..MoveLast
..MoveFirst
MsgBox .recordcount
End With

Regards,
AL

 

[Al Borges]

Hi Diavolo:

You can use the "DCount()" function to get a count of the records in a
table, but do it like this:

DCount("*","MyTableName","[YourField] = " & chr(34) & "bylos_nr=me!bylos_nr"
& chr(34))

Regards,
Al