Continuous form subrecord count

[rustersen]

Hi all,
I am struggling with access 2007 estimate program for my company.

I use a continuous form, to select tasks to add to an estimate, which works great.

Now I want to be able to add parts to these tasks, so I have created a PartsTbl linked to the TaskId.

My question is: Is it possible to add a field to the TaskSelectFrm, displaying the number of parts associated with each task?

I have tried putting a DCount field in the form query, but it displays the total number of parts in all lines of the continuous form... :banghead:

Any help is much appreciated!

 

[John Big Booty]

You should be able to use the Criteria of the DCount() function to count only the records specific to your current Task.

 

[rustersen]

Thanks John,
The strange thing is, if I add:

MsgBox DCount("*", "PartTbl", "AircraftModelID =" & [Forms]![Estimate]![AircraftModelID] & " And [TaskId] =" & Me!TaskId)

..to a onlick event on one of the fields in the forms, clicking that field returns the correct number of subrecords for that record.

However, if I put the same dcount code in the controlsource of a field, it returns a #name error.

=DCount("*"; "PartTbl"; "AircraftModelID =" & [Forms]![Estimate]![AircraftModelID] & " And [TaskId] =" & Me!TaskId)

Any idea why?

 

[rustersen]

...never mind, figured it out. Appearently I had to remove the Me! in front of TaskId.

=DCount("*"; "PartTbl"; "AircraftModelID =" & [Forms]![Estimate]![AircraftModelID] & " And [TaskId] =" & [TaskId])

 

[John Big Booty]

Glad you got it sorted

 

[boblarson]

Appearently I had to remove the Me! in front of TaskId.

Yep, as an FYI - ME is only good in VBA, not in control sources or queries.