Display Count of records from subform on Main form

T

TotallyConfused

I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 
M

Maurice

Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
 
K

Klatuu

DLookup will not return a number of records. As written, it will return the
first value it finds in the ID field in the query.
The correct method would be

=DCount("*", "Query Name","[ID] = " & Me.txtID)
--
Dave Hargis, Microsoft Access MVP


Maurice said:
Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
--
Maurice Ausum


TotallyConfused said:
I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 
M

Maurice

I know what you mean but if you create a query as stated you would only get
one field with a total count in it. Then when you use a dlookup you would
only get one value returned being the total count of the records as created
by the query...
--
Maurice Ausum


Klatuu said:
DLookup will not return a number of records. As written, it will return the
first value it finds in the ID field in the query.
The correct method would be

=DCount("*", "Query Name","[ID] = " & Me.txtID)
--
Dave Hargis, Microsoft Access MVP


Maurice said:
Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
--
Maurice Ausum


TotallyConfused said:
I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 
K

Klatuu

That is not correct. DLook does not return a number of records, it returns
one value from the field specified.

First sentence of DLookup in VBA Help:

You can use the DLookup function to get the value of a particular field from
a specified set of records (a domain).

And further on:
If more than one field meets criteria, the DLookup function returns the
first occurrence. You should specify criteria that will ensure that the field
value returned by the DLookup function is unique. You may want to use a
primary key value for your criteria, such as [EmployeeID] in the following
example, to ensure that the DLookup function returns a unique value

So how do you get a count from DLookup?
--
Dave Hargis, Microsoft Access MVP


Maurice said:
I know what you mean but if you create a query as stated you would only get
one field with a total count in it. Then when you use a dlookup you would
only get one value returned being the total count of the records as created
by the query...
--
Maurice Ausum


Klatuu said:
DLookup will not return a number of records. As written, it will return the
first value it finds in the ID field in the query.
The correct method would be

=DCount("*", "Query Name","[ID] = " & Me.txtID)
--
Dave Hargis, Microsoft Access MVP


Maurice said:
Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
--
Maurice Ausum


:

I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 
M

Maurice

Ok, let me try to explain what I meant.

You can create a query from let's say table1 which contains 10 records with
several fields.
When you drop the first field on the grid and then use a group by Count you
would get one result in the query from just one field.

You can now perform a dlookup as follows:

dlookup ("field";"created query")

this will return the only field you have designated in your query. The field
contains the total count of records thereby giving you a number (actually a
fieldvalue).

Does that make sense?
--
Maurice Ausum


Klatuu said:
That is not correct. DLook does not return a number of records, it returns
one value from the field specified.

First sentence of DLookup in VBA Help:

You can use the DLookup function to get the value of a particular field from
a specified set of records (a domain).

And further on:
If more than one field meets criteria, the DLookup function returns the
first occurrence. You should specify criteria that will ensure that the field
value returned by the DLookup function is unique. You may want to use a
primary key value for your criteria, such as [EmployeeID] in the following
example, to ensure that the DLookup function returns a unique value

So how do you get a count from DLookup?
--
Dave Hargis, Microsoft Access MVP


Maurice said:
I know what you mean but if you create a query as stated you would only get
one field with a total count in it. Then when you use a dlookup you would
only get one value returned being the total count of the records as created
by the query...
--
Maurice Ausum


Klatuu said:
DLookup will not return a number of records. As written, it will return the
first value it finds in the ID field in the query.
The correct method would be

=DCount("*", "Query Name","[ID] = " & Me.txtID)
--
Dave Hargis, Microsoft Access MVP


:

Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
--
Maurice Ausum


:

I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 
K

Klatuu

I understand what you are saying, now, but does it make sense?

I don't think so. It would add additional overhead. The DCount, IMHO, is a
better solution.
--
Dave Hargis, Microsoft Access MVP


Maurice said:
Ok, let me try to explain what I meant.

You can create a query from let's say table1 which contains 10 records with
several fields.
When you drop the first field on the grid and then use a group by Count you
would get one result in the query from just one field.

You can now perform a dlookup as follows:

dlookup ("field";"created query")

this will return the only field you have designated in your query. The field
contains the total count of records thereby giving you a number (actually a
fieldvalue).

Does that make sense?
--
Maurice Ausum


Klatuu said:
That is not correct. DLook does not return a number of records, it returns
one value from the field specified.

First sentence of DLookup in VBA Help:

You can use the DLookup function to get the value of a particular field from
a specified set of records (a domain).

And further on:
If more than one field meets criteria, the DLookup function returns the
first occurrence. You should specify criteria that will ensure that the field
value returned by the DLookup function is unique. You may want to use a
primary key value for your criteria, such as [EmployeeID] in the following
example, to ensure that the DLookup function returns a unique value

So how do you get a count from DLookup?
--
Dave Hargis, Microsoft Access MVP


Maurice said:
I know what you mean but if you create a query as stated you would only get
one field with a total count in it. Then when you use a dlookup you would
only get one value returned being the total count of the records as created
by the query...
--
Maurice Ausum


:

DLookup will not return a number of records. As written, it will return the
first value it finds in the ID field in the query.
The correct method would be

=DCount("*", "Query Name","[ID] = " & Me.txtID)
--
Dave Hargis, Microsoft Access MVP


:

Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
--
Maurice Ausum


:

I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 
M

Maurice

Ok, fair enough I think the additional overhead would consist of one query.
However the dcount as it was originally meant for does the trick also..
--
Maurice Ausum


Klatuu said:
I understand what you are saying, now, but does it make sense?

I don't think so. It would add additional overhead. The DCount, IMHO, is a
better solution.
--
Dave Hargis, Microsoft Access MVP


Maurice said:
Ok, let me try to explain what I meant.

You can create a query from let's say table1 which contains 10 records with
several fields.
When you drop the first field on the grid and then use a group by Count you
would get one result in the query from just one field.

You can now perform a dlookup as follows:

dlookup ("field";"created query")

this will return the only field you have designated in your query. The field
contains the total count of records thereby giving you a number (actually a
fieldvalue).

Does that make sense?
--
Maurice Ausum


Klatuu said:
That is not correct. DLook does not return a number of records, it returns
one value from the field specified.

First sentence of DLookup in VBA Help:

You can use the DLookup function to get the value of a particular field from
a specified set of records (a domain).

And further on:
If more than one field meets criteria, the DLookup function returns the
first occurrence. You should specify criteria that will ensure that the field
value returned by the DLookup function is unique. You may want to use a
primary key value for your criteria, such as [EmployeeID] in the following
example, to ensure that the DLookup function returns a unique value

So how do you get a count from DLookup?
--
Dave Hargis, Microsoft Access MVP


:

I know what you mean but if you create a query as stated you would only get
one field with a total count in it. Then when you use a dlookup you would
only get one value returned being the total count of the records as created
by the query...
--
Maurice Ausum


:

DLookup will not return a number of records. As written, it will return the
first value it finds in the ID field in the query.
The correct method would be

=DCount("*", "Query Name","[ID] = " & Me.txtID)
--
Dave Hargis, Microsoft Access MVP


:

Try it this way:

Create a query with the needed table. Add the id-field to the grid. Total it
by Count. Save the query and on the form you place an unbound field.

In VBA you can do a Dlookup for the one field in the query like

Dlookup("id","name of your query")
This will display the amount of records.

hth
--
Maurice Ausum


:

I need to show a count of records for doc on main form. Doc info is on main
form and list of pts is on subform. I have entered an unbound text box on
the main form and have tried DCount, ABSSum and Count all I get is 0. This
is what I have entered: =DCount([ID]) or ABS(Sum([myfrom].[ID]) and
=Count([ID]). Thank you in advance for any help you can provide. Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top