Can't Count Records in Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paputxi - 09 Mar 2007 17:34 GMT

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.

Reply to this Message

Jeff Boyce - 09 Mar 2007 20:12 GMT

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
[quoted text clipped - 15 lines]
>
> Any suggestions? Thanks in advance.

Reply to this Message

Paputxi - 09 Mar 2007 21:15 GMT

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.

> 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
[quoted text clipped - 24 lines]
> >
> > Any suggestions? Thanks in advance.

Reply to this Message

Jeff Boyce - 09 Mar 2007 23:45 GMT

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

> Perhaps this will explain it better. I have two tables, Table1 and
> Table2.
[quoted text clipped - 56 lines]
>> >
>> > Any suggestions? Thanks in advance.

Reply to this Message

Paputxi - 11 Mar 2007 23:40 GMT

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.

> Please re-read my response.
>
[quoted text clipped - 74 lines]
> >> >
> >> > Any suggestions? Thanks in advance.

Reply to this Message