Count number of records in form A and display result in Form B

[rodqntr]

Hey there,

I was reading around and I found this forum. It looks like the replie to inquiries is very fast.

I would appreciate a little help. My knowledge in access is limited but I am very good at reverse engineering. I am using the MS Projects databased template. I modified it to my use. Anyway, the main form display a list of active projecs and when you open it you get the project details within this form you can see tasks.

On the Projects Details form, you see the project form and a second tab with a subform (task details)

I was able to display a count of records in the project details form like this:

=[Tasks subform]![Counter]

where Counter is just a field with =Count(*)

this works just how I want it. Now I would like to show the same results (=[Tasks subform]![Counter]) in another form Project List but can't seem to get it right

I was trying like:

=[Forms]![Project Details]![Total tasks]

where

Project Details is a form with a field named "Total tasks" with the function =[Tasks subform]![Counter])


any help is greatly appreciated

 

[hk1]

There are many ways to do this but here's what I'd probably do. Create a public variable on the main form and then reference it from your subform.

Public plTaskCount As Long

On your main form's On_Current event you'll need code like this:
plTaskCount = Me!TaskCount 'Whatever your field name is

Put this in your subform's On_Current event.
Me.txtTotalTasks = Me.Parent.plTaskCount

 

[rodqntr]

thank you for the prompt reply ... as I stated before, I know little about access. I am using the Microsoft Project Databased template so I did not created it.

I will try to be as detail as posible


Form A (Project List): list projects that can be added using Form B

Form B (Project Details): it contains two tabs the main project data and a subform (Tasks Details) where user can input data

Form C (Tasks Details subform): it contains all the tasks added to the Form B

therefore;

1. On Form B tab 2 (tasks subform) I added a text box named "Counter" with function: =Count(*) - This will count the number of recods on the subform

2. On Form B tab 1 (project details) I added a text box named "Total Tasks" with function: =[tasks subform]![Counter] - This will display the results from the "Counter" text box

what I need is

On Form A; the function to use on a text box that will display the same results as the "Total Tasks" text box from Form B

i was trying to do so with:=[Forms]![project Details]![Total Tasks] but does not seem to work

Again, I appreciate the reply but I can not understand half of it. the simply the response the better.

 

[hk1]

Apparently you're trying to do this all on a form/field level. My recommendation was to use VBA code instead. Is that something you're not familiar with and not comfortable doing? I do see an error in code where I was expecting you to get the task count in your subform's SQL statement.

So basically you're trying to figure out how to fill in a field on the main form with a value contained in a calculated field on a subform. Here's a handy reference that might help you: http://www.mvps.org/access/forms/frm0031.htm

So it should look something like this:
Me!Subform1.Form!ControlName

or

=Me![Tasks subform].Form![Total Tasks]

By the way, I don't care what Microsoft does but it's bad to not use any naming convention or to use spaces in your forms. You should change the form names to frmProjectDetails and fsubTasks and then change your textbox control names to be like this: txtTotalTasks Then you will be able to use code like this:
=Me!fsubTasks.Form!txtTotalTasks

The brackets are only necessary when your form names, control names, or field names contain spaces.