Glad you got it to work Bernie.
BTW, the reason the [CoID] inside the quotes doesn't need
to be prefaced with the table name is that it is
referring to the [CoID] inside tblAddresses which was
defined as the domain in the second criteria.
The second instance of [CoID] in the criteria is
referring to a field value in your query itself, and
normally wouldn't need a table preface either except that
you have more than one instance of the field name in the
queries data source (the joined tables).
In other words, you are telling the function to count all
records in tblAddresses where the [CoID] (in
tblAddresses) is equal to the [CoID] of the current query
record. But, again, since there are two [CoID]'s in the
current query record, it was necessary to further define
which table it was coming from.
Access doesn't see the first instance of the [CoID] field
as a field name because it is inside the quotes. It
concatenates that string with the value of the second
instance of [CoID] to produce a result of something
like "[CoID] = 1234", which is then used to limit the
domain of records (in tblAddresses) counted by the
function.
As far as why you did not need to reference Forms!, you
would only need to do that if you were pulling the value
from a control on the form. But, since the value that
you are using in your function is available within the
query, there is no need to pull it from a form's
control.
You can reference form control values in queries, but
then the queries only work if the form is opened, and the
control can only have one value to pass along. This is
often used for unbound combo box values to define the
records that your query will return. For instance, if
you had a combo box with all the letters of the alphabet,
you could set your query to only return the Names
starting with the selected letter.
In your case, you could not use the form's [CoID] field
in your DCount() function because the form control only
has the value of the current record. If you used that in
the query source DCount() function all of the records in
the form's data source would return the count for the
current record on the form. Also, you would not be able
to view or export the query without the form open.
Regarding the ! vs the . I'm not sure what the official
definition is (one of those things where I've used it for
a long time but not thought about the definition), but I
would say that a . is generally used to preface a
property or collection that would be listed afterward,
whereas the ! is used to denote that a property value
(such as a field name) is passed along. But, each item
has a default collection that it is not necessary to
explicitly define, so it isn't often very obvious. For
example, from the help section on the Controls
Collection, Me![New Data] is the same as Me.Controls!
NewData because the controls collection is the default.
the ![New Data] provides the control name that you want
to access.
Hopefully this will help somewhat. It does take a while
to get used to the syntax, but once you do things really
get a lot easier.
-Ted Allen
-----Original Message-----
Thanks Ted...
It's now Perfect!!!
Your explanation didn't relieve my frustration much,
even though everything is working
ok, and there is NO delay in having to wait for the
calculation each time I go to a new
record. As I said, Perfect!
Frustration: I'm still confused as to why things are
the way they are. The reference to
[CoID] within the quotes doesn't distinguish between
tables? And the reference to [CoID]
not in quotes is not preceeded by "Forms!"??
I'm going to use this to learn more about the Expression
Builder, and to try to understand
all the permutations that may be exceptable here (for
example, using "." instead of "!",
etc.
Lots of people have provided me with assistance in the
past, and it has been very much
appreciated. But your assistance deserves special
attention as the explanations and
your display of patience were especially helpful to me.
My deepest appreciation...
Bernie
Hi Bernie,
The error message that you are getting is probably a
result of the fact that the field name shows up in at
least two of the tables in your query data source, so it
isn't sure which you want to use. All you need to do is
to preface the [CoID] field with the table name. Try the
following(but subsitute your table name):
AddressCount: DCount("[AddrID]","tblAddresses","[CoID]
= " & [YourTableName]![CoID])
Couple of other notes, I changed the Expr1 to
AddressCount to give a more meaningful label. Whatever
label you choose to use will be the control source for
the control on your form.
Also, I believe that the []'s around the table name and
the ! after are the correct syntax, but if you get an
error you can try changing the ! to a . and also dropping
the []'s (The []'s should be optional unless your table
name has a space in which case they would be required).
One way to always make sure that you get that part of an
expression right is to use the expression builder, browse
to the appropriate table and field and double-click, it
will add the table and field reference for you at the
current cursor position.
Hopefully that will help. Post back and let me know how
it goes.
-Ted Allen
-----Original Message-----
Oh, the frustration.
I'm trying to modify the code now to use your first suggestion.
Using the exact code as before:
=DCount("[AddrID]","tblAddresses","[CoID] = " & [CoID])
becomes
Expr1: DCount("[AddrID]","tblAddresses","[CoID] = " & [CoID])
gives me the following message when entering into a
new
field in the Forms Record
Source SQL Statement: Query Builder: "The specified
field '[CoID]' Could refer to more
than one table listed in the FROM clause of your SQL Statement."
So I modify the code as shown here:
Expr1: DCount("[AddrID]","tblAddresses","Forms!
[tblAddresses]![CoID] = " & [Forms]!
[tblAddresses]![CoID])
Now when I try to view this, I get the "Enter
Parameter
Value" for the expression:
Forms!tblAddresses!CoID
Do you have any idea what I'm doing wrong?
Thanks again,
Bernie
On Fri, 27 Feb 2004 07:38:10 -0800, "Ted Allen"
Hi Bernie,
Glad you got it to work. Sorry I mislead you with
the "Me" portion of the criteria. I'm so used to working
in the VB code environment I included it out of habit.
To answer your question about the speed, I can
think
of
two other alternatives that you could try.
You could try adding the same DCount() funtion to your
form's control source query as a calculated expression.
Then, label the expression and use that fieldname
as
the
control source for the text box. This may make your form
a little slower to load initially (since the DCount ()
would be calculated at that time), but the value would
load along with all of the others when looking at a
record on the form.
Another alternative would be to go back to using a
summary query to do the counts, and place a subform on
your form that uses the summary query as the data
source. This will move the summary query out of your
main form's control source and allow you to edit as
before.
-Ted Allen
-----Original Message-----
Hi Ted!
I got it to work with the following expression:
=DCount("[AddrID]","tblAddresses","[CoID] = " & [CoID])
and I think I understand this. My only complaint
now
is
that the value is slow to appear
on the Form, much slower than using the duplicate
queries I was using that didn't allow
me to add new records.
If you have an idea as to how to get this to "process
faster" I'm all ears, otherwise, my
sincere thanks for taking the time to help me.
Bernie
On Thu, 26 Feb 2004 16:02:21 -0800, "Ted Allen"
Hi Bernie,
I have inserted responses to your message below:
-----Original Message-----
Thanks for your help, Ted.
Your assumptions were ON the mark, but I still need
help.
It sounds like the DCount() function is the way to
go=20
here, but I don't know how to use it,
and this may be an excellent chance at
understanding=20
lots more than just DCount().
From the Expression Builder, I get: DCount=20
(=ABexpr=BB, =ABdomain=BB, =ABcriteria=BB) .
1. Am I suppose to use Expression Builder?
You can use the expression builder if you wish, but
it's=20
not required. You can enter the statement directly
in=20
the control source property for the control. The=20
expression builder is just a tool to save some typing.
2. If so, is this the Control Source for the
Unbound=20
Control? Or...
Yes, that's the way I would go in your case.
3. If so is this an expression in the Record Source
of=20
the Form?
No, you don't have to have the expression in the
record=20
source for your form, but you can (in which case the=20
control source for your control would then be the
field=20
name from your query expression). I'm not
really
an=20
expert on efficiencies in Access, so I'm not really
sure=20
which would be faster.
4. Still If so, please explain where to get the=20
values "expr" and "domain"
expr is the name of the field (column) that you want
to=20
count in your lookup table (that will meet the
criteria=20
specified later), such as "[AddressID]". Null (blank)
=20
entries will not be counted in the specified field.
If=20
you want to also count blank entries (all records
meeting=20
your criteria) you can enter "*" as your expression.
domain is the name of the table that you want to
count=20
the records in, surrounded by quotes.
criteria is where things get a little tricky for
most=20
people because it usually requires concatenated
strings=20
(combinations of string and field values to
return
a=20
string). The criteria should evaluate to the where=20
condition of a query, without the word WHERE. In
your=20
case, the criteria would be something like
"[CoID] =3D " & Me.CoID
So, your DCount function in the control source of
the=20
text box would look something like the following (but
you=20
will have to correct the table/field names):
DCount("*","YourTableName","[CoID] =3D " & Me.CoID)
Or,
DCount("[AddressID]","YourTableName","[CoID]
=3D "
&=20
Me.CoID)
Hope this helps. Post back if you have more questions.
-Ted Allen
.. =20
5. If the answer to 1 above is NO, then I'm even
more=20
lost than what you might already
think. Please explain how you intended that I use
DCount
().
FYI: For each CoID (company ID) there may be
multiple=20
AddrID (address ID). So on the
Form, each time I have a company name, I want to
show=20
how many total addresses have
been entered for that company name.
I appreciate your help.
Bernie
p.s. I posted this on another link, as my browser
quit=20
on me. Sorry about the confusion.
On Thu, 26 Feb 2004 12:29:41 -0800, "Ted Allen"=20
Hi Bernie,
=20
When you say that you added the query to the form,
did=20
you add it to the form's control source? If so,
this=20
may=20
have made your form's control source query non-
updateable=20
because the summary queries are not updateable.
=20
If this is the case, you could try just
adding
a=20
subform=20
to the form and use the query as the subform's
data=20
source. This will allow you to remove the query
from=20
the=20
form's conrol source.
=20
Alternatively, it sounds like you could likely
display=20
the same information using the DCount()
function
in
an=20
unbound control on your form without using any=20
subforms=20
or summary queries.
=20
Post back if my assumptions were off the mark and
you=20
still need help.
=20
HTH
=20
-Ted Allen
-----Original Message-----
I have a Form that was working fine, and among
other=20
things, it kept track of multiple=20
addresses for the same company.
So I thought it would be nice to indicate on the=20
Form,=20
how many addresses had been=20
entered for the current company name.
So I built a query to find duplicates, and then=20
another=20
to count the number of duplicates=20
for each company.
I then added this query to my Form, and it does
what=20
I=20
want...except that now I can't add=20
any new records.
I guess I understand how/why this is so, but
surely=20
there must be a way around it.
If you know of a way, I'd appreciate some guidance.
Thanks,
Bernie
.
.
.
.
.
