Microsoft Access Calculated Expressions

[Guest]

I'm trying to get the correct expression for a calculated control on a Main
Form which has a Subform. The Main Form is called "InsMain" and has the
primary key of [Part_ID]. The subform is InsTransactionsTest and is linked
to the Main Form by [Part_ID].

On the Main Form I want the control to sum all the values for the field
[Amount] on the subform for THAT PARTICULAR [Part_ID]. Here's an example of
what I've tried the last time, which doesn't work.

=DSum("Forms![dbo.InsMain]![dbo_InsTransactionsTEST].Form![Amount]
","[Part_ID] = Forms![dbo_InsTransactionsTEST]![Part_ID]")

Can someone give me an idea of what I'm doing wrong?

 

[George Nicholson]

You might want to review the Help entry for Dsum. It has 3 arguments, the
last of which is optional.
DSum(FieldName, Domain,[Criteria])

You seem to be missing Domain: "A string expression identifying the set of
records that constitutes the domain. It can be a table name or a query name
for a query that does not require a parameter."

Note that it doesn't say you can reference the recordset of a form (I'm not
saying you can't, it just doesn't say you can and i've never tried to work
around that).

Also, I'm not sure what "Forms![dbo_InsTransactionsTEST]" refers to, but
since you say the forms are linked on Part_ID, you can probably remove that
and simply refer to that field regardless of which form you are on to get
its current value.

So, you'll probably end up with something like this:
=DSum("[Amount]", "MyTableName", "[Part_ID] = " & [Part_ID])

If PartID is a text value rather than a number you'd need to include single
quotes accordingly in the criteria:
"[Part_ID] = '" & [Part_ID] & "'")