Yet another counting problem

[bbwolff]

i use this as expression in a continues form and it returns a number of times an event apperas and it works ok, gives me the right value for each entry

DCount("*";"Oper";"[oOper] = '" & [txtOper] & "' and [ostatus]='done'")

counts the number of times a person that's displayed in txtOper box is in ooper field of the table, different number for each person

now if i use it as vba, like this

Me.tTest = DCount("*", "Oper", "[oOper] = '" & [txtOper] & "' and [ostatus]='done'")

it always uses the first person and the result is the same for each entry in a continues form (or if i do it in single form format, it doesn't get recalculated)

I'd like to do it through vba, cause it allows me for users to build their own queries adding different parameters.

Any ideas

 

[Isskint]

The code is correct, the problem is when the data is calculated. In a continuous form each record line would show the same data because it is created for the active record. What you need to do is create a query (using the same table as the form already uses) and add the Dcount() to the query. Then use that query as the forms RecordSource

 

[bbwolff]

problem is, i use a lot of calculations on this form, i use it as an overview for all activities
i might be really bad at queries, that's one thing, but i never managed to get counts across the coloumns with queries.
is there a way to force the form to use the right data
maybe something through recordsets?

if i use on current, then the form recalculates the value for entry i clicked in, but displays same value in all forms

 

[bbwolff]

i'll probably just use vba to create a temp table with all my calculations, sounds the easiest thing to do

Dim tTest, tcount
DoCmd.RunSQL "delete * from test"
CurrentDb.Execute "ALTER TABLE test ALTER COLUMN ID COUNTER(1,1)"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select Name from Users")
    Do Until rs.EOF
    tTest = rs!Name
    tcount = DCount("*", "Oper, "[oOper] = '" & tTest & "' and [ostatus]='done'")
    CurrentDb.Execute "INSERT INTO test (Name, count1) Values('" & tTest & "','" & tcount & "')"
    rs.MoveNext
    Loop

then i can easily throw as many calculations i want to and display them anyway i like

 

[Isskint]

the temp table is one solution, but no more difficult than the query. Try using queries, they are the heart (or another very significant organ) of a database.

Problem is, i use a lot of calculations on this form, i use it as an overview for all activities

Thats no issue. Best practice suggests you should NEVER link a table and a form but always create a query. So you create your query and add all the calculations you use on the form. The BIG advantage is the values are record specific, current and available from elsewhere.

However, if the approach you take works for you, then that is the 'right' approach.

Good Luck