Can't Count Records in Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that I use in a subform. I am having problems calculating the
number of records that are displayed on a form as the result of executing
that query in that subform, which has LinkChildFields and LinkMasterFields.
For this example, let's same that the field is named Field1.

If I execute the query by itself (i.e., not using the form), it displays all
the records, say 10 records, which is correct. So, if I use
=DCOUNT("*","Query1") in another part of the form (i.e., not within the
subform), I get a value of 10. However, using my example, say that there are
three records displayed in the subform based on the value in Field1.
However, if I say =DCOUNT("[Field1]","Query1"), I get #Error. I'm looking
for a value of 3.

Any suggestions? Thanks in advance.
 
I suspect that this is failing because you are referring to data that is NOT
part of the main form, nor related to Query1. Doesn't your subform have a
different source?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Perhaps this will explain it better. I have two tables, Table1 and Table2.
I have a one to many relationship between Table1 and Table2. In both tables,
Field1 is the common link. Per my example earlier, Table 2 has 10 records.
Let's say that if Field1 = "xyz" on the current record in Table1, there are
three accociated records in Table 2. In my main form, I display each record
in Table1 sequentially and in a subform I display the records from Table2
associated with the current record from Table1. So that part it working okay.
Now I want to calculate the number of rows being displayed and display that
value in the main form as well.

Perhaps you may know of a better way of approaching this? For example, is
it possible to calculate the number of rows and include that as part of the
query itself? Actually, I tried this approach as well using DCOUNT, but ran
into major problems. So I dropped that approach.

I know that I'm just doing something stupid, but I can't seem to figure it
out. Any suggestions would be greatly appreciated. Thanks.

Jeff Boyce said:
I suspect that this is failing because you are referring to data that is NOT
part of the main form, nor related to Query1. Doesn't your subform have a
different source?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paputxi said:
I have a query that I use in a subform. I am having problems calculating
the
number of records that are displayed on a form as the result of executing
that query in that subform, which has LinkChildFields and
LinkMasterFields.
For this example, let's same that the field is named Field1.

If I execute the query by itself (i.e., not using the form), it displays
all
the records, say 10 records, which is correct. So, if I use
=DCOUNT("*","Query1") in another part of the form (i.e., not within the
subform), I get a value of 10. However, using my example, say that there
are
three records displayed in the subform based on the value in Field1.
However, if I say =DCOUNT("[Field1]","Query1"), I get #Error. I'm
looking
for a value of 3.

Any suggestions? Thanks in advance.
Click to expand...
Click to expand...
 
Please re-read my response.

Your DCOUNT() expression works with Query1, which you described as being the
source for your main form (i.e., showing table1 records). There's no reason
Query1 would show you table2 records also.

Do you have a Query2 that can pull records from table2? If not, create one.
Include in the query a selection criterion something like:

=Forms!YourMainForm!YourIDField

Now retry building a DCOUNT() expression, but using Query2.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Paputxi said:
Perhaps this will explain it better. I have two tables, Table1 and
Table2.
I have a one to many relationship between Table1 and Table2. In both
tables,
Field1 is the common link. Per my example earlier, Table 2 has 10 records.
Let's say that if Field1 = "xyz" on the current record in Table1, there
are
three accociated records in Table 2. In my main form, I display each
record
in Table1 sequentially and in a subform I display the records from Table2
associated with the current record from Table1. So that part it working
okay.
Now I want to calculate the number of rows being displayed and display
that
value in the main form as well.

Perhaps you may know of a better way of approaching this? For example, is
it possible to calculate the number of rows and include that as part of
the
query itself? Actually, I tried this approach as well using DCOUNT, but
ran
into major problems. So I dropped that approach.

I know that I'm just doing something stupid, but I can't seem to figure it
out. Any suggestions would be greatly appreciated. Thanks.

Jeff Boyce said:
I suspect that this is failing because you are referring to data that is
NOT
part of the main form, nor related to Query1. Doesn't your subform have
a
different source?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paputxi said:
I have a query that I use in a subform. I am having problems
calculating
the
number of records that are displayed on a form as the result of
executing
that query in that subform, which has LinkChildFields and
LinkMasterFields.
For this example, let's same that the field is named Field1.

If I execute the query by itself (i.e., not using the form), it
displays
all
the records, say 10 records, which is correct. So, if I use
=DCOUNT("*","Query1") in another part of the form (i.e., not within the
subform), I get a value of 10. However, using my example, say that
there
are
three records displayed in the subform based on the value in Field1.
However, if I say =DCOUNT("[Field1]","Query1"), I get #Error. I'm
looking
for a value of 3.

Any suggestions? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I'll go reread and study your earlier response; I must have missed something.
(Sorry.) BTW, I was able to get the information via RecordSet.RecordCount,
which I saw in examples from some other threads. In any case, because of
your responses, I am now much more informed and prefer your approach. Thanks
again, Jeff.

Jeff Boyce said:
Please re-read my response.

Your DCOUNT() expression works with Query1, which you described as being the
source for your main form (i.e., showing table1 records). There's no reason
Query1 would show you table2 records also.

Do you have a Query2 that can pull records from table2? If not, create one.
Include in the query a selection criterion something like:

=Forms!YourMainForm!YourIDField

Now retry building a DCOUNT() expression, but using Query2.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Paputxi said:
Perhaps this will explain it better. I have two tables, Table1 and
Table2.
I have a one to many relationship between Table1 and Table2. In both
tables,
Field1 is the common link. Per my example earlier, Table 2 has 10 records.
Let's say that if Field1 = "xyz" on the current record in Table1, there
are
three accociated records in Table 2. In my main form, I display each
record
in Table1 sequentially and in a subform I display the records from Table2
associated with the current record from Table1. So that part it working
okay.
Now I want to calculate the number of rows being displayed and display
that
value in the main form as well.

Perhaps you may know of a better way of approaching this? For example, is
it possible to calculate the number of rows and include that as part of
the
query itself? Actually, I tried this approach as well using DCOUNT, but
ran
into major problems. So I dropped that approach.

I know that I'm just doing something stupid, but I can't seem to figure it
out. Any suggestions would be greatly appreciated. Thanks.

Jeff Boyce said:
I suspect that this is failing because you are referring to data that is
NOT
part of the main form, nor related to Query1. Doesn't your subform have
a
different source?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query that I use in a subform. I am having problems
calculating
the
number of records that are displayed on a form as the result of
executing
that query in that subform, which has LinkChildFields and
LinkMasterFields.
For this example, let's same that the field is named Field1.

If I execute the query by itself (i.e., not using the form), it
displays
all
the records, say 10 records, which is correct. So, if I use
=DCOUNT("*","Query1") in another part of the form (i.e., not within the
subform), I get a value of 10. However, using my example, say that
there
are
three records displayed in the subform based on the value in Field1.
However, if I say =DCOUNT("[Field1]","Query1"), I get #Error. I'm
looking
for a value of 3.

Any suggestions? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top