Display Count of records from subform on Main form

[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.

 

[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

 

[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

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.

 

[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

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.

 

[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

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.

 

[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

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.

 

[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

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

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.

 

[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

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

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

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.