I have a form with a record source (lets call it A) on the form I want
to have a field (can be text box or whatever you think appropriate).
This field is based upon a query. The query returns a number as a
result. It counts the number of records. I would like this number
showing in the field. (The record source for this is not A). How can I
accomplish this?

Thanks.

---

Clarification of Question by cheaptrinkets-ga on 01 Jan 2003 17:54 PST

I have a form, lets call it Main.  I have another form called for
Phone Messages (based on the table Phone Messages) On the main form, I
want to put one boxes. One I will call "Phone Messages to Return".

I would like to query the tables for Phone Messages. (I have a query
with the following sql "SELECT Count(ID) AS CountOfID FROM Phone
Messages;) The query counts the number of records in this table
(actually the number of unreturned messages) and returns a number.
Call this result COUNT.

I would like to put the result (COUNT) into the text box on the Main
form. This way I could look at my form and see how many messages I
need to return.

Hello cheaptrinkets,

Thank you for your question.

Unless I am misunderstanding what you wish to do, this can be done
very easily.

Create a text box and assign that data source for the text box as 

=GetRecordCount([Form])

This will display the total number of records. If you run a query
against this database that will produce a subset of records, the
number in this text box will change to reflect how many records have
been selected as a result of running the query.

If you really desire to insert the number of records from a query, I
located the following message for you by searching [query result in
text box] :

Tek Tips Forums
http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/702/qid/404093

Hello. I'm using access 2000.
My problem should be easy to solve but I don't seem to get how.

I have a form with a text box which I want to autofill with a result
that I get from a query.

The query works just fine.

I'm trying to do that with the expression builder, but it doesn't
work, I always get the error "#name?" in the text box.
How should I do it?

Answer:

You can use DLookUp:

=DLookUp("YourField", "YourQuery", "YourCriteria")

Access help shows quite a bit of information for DLookup.

Additionally, searching [DLookup +query], I found:

MSDN
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acfctDLookup.asp

"You can use the DLookup function to get the value of a particular
field from a specified set of records (a domain). Use the DLookup
function in Visual Basic, a macro, a query expression, or a calculated
control on a form or report..."

There is a wealth of information about DLookup in this article.

I believe my original suggestion should accomplish what you desire (
and I use it successfully in many of my databases), however if you
require using the query to obtain the number of records, the DLookup
function should provide your solution.

I trust my research has answered your question, however if a link
above should fail to work or my research require further explanation,
please do post a Request for Clarification.


Best Regards,

-=clouseau=-

---

Request for Answer Clarification by cheaptrinkets-ga on 01 Jan 2003 19:33 PST

I am not able to get it to work. I have posted a this problem online.
www.partos.com/access1  the file is askgoogle.mdb

Again, thanks for the help.

---

Clarification of Answer by clouseau-ga on 01 Jan 2003 23:46 PST

Hello again,

I will look at your database in the morning and see what I can see,
cheaptrinkets.

Can you tell me what exactly did not work? Did you try both of my
suggestions? Can you provide me with any details?

I'll post again tomorrow when I have new information for you.

-=clouseau=-

---

Clarification of Answer by clouseau-ga on 01 Jan 2003 23:55 PST

cheaptrinkets,

I just downloaded your database, but it fails to open for me. It may
be damaged, but more likely it was written with a newer version of
Access than I have (97) and is not compatible.

Can you save this as an older version of Access?  If not, please
provide details about what you have tried and did not work and I will
investigate further.

-=clouseau=-

---

Clarification of Answer by clouseau-ga on 02 Jan 2003 01:17 PST

Hello again cheaptrinkets,

I played with this a bit more tonight and have a working example for
you to see at http://www.lucidmatrix.com/uploads/googletest.mdb

Here I used the following for a text box controlsource on my form:
=DCount("[Name]","Messages")

This is counting the number of records in the Messages table based on
the Name field. Substitute your field name and table name and it
should work for you, even without the query.

Do let me know your results.

-=clouseau=-

Thanks. Works great.

Try restating your question more clearly.