I changed to ("[checkboxFIeld] = -1") but I am still getting the same error
message. To try to narrow down the error I ran the code with only one
criteria at a time, and individually both worked as expected. When I put
them back together with AND I get the same error with them in either order.
Is there something I am not understanding correctly about using multiple
criteria or is there some way I would be able to narrow down the table by one
of the criteria before I call it in the DCount function? Thanks!
:
("[CheckboxField] = Yes")
s/b
("[checkboxFIeld] = True")
or you can use -1 in place of True (-1 = True, any other number = False)
("[checkboxFIeld] = -1")
and... I'm not sure if this matters or not, but I would put the linkingfield
criteria before the checkbox criteria in the string. I'm thinking maybe the
way you have it Jet would try to pull all the checkbox fields first, then
further filter them based on ID? You would rather have it the other way
around for efficiency reasons (if it makes a difference)
Can anyone advise if that's correct or not? Does the order of criterion
effect the sequence of lookup?
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
Yes, you are right about what I am trying to do. I was successful at
determining the number of records in the subform, but am having problems with
counting the number of check boxes out of those records. I have tested my
criteria by including one at a time, but when I put in both criteria I am
getting a type mismatch error. This is the code I am trying to use (with
descriptions instead of my actual names of tables and fields). Do you know
what I am doing wrong? Thanks so much for your help!
countofchecks = DCount("[checkboxfield]", "TableForSubform",
("[CheckboxField] = Yes") And ("[Linkingidfield] = " & idOfMainForm))
:
One further note:
You may want to use DCount to get the base record count instead of using the
recordcount property of the subs recordset... reason being is that for
certain types of recordsets, sometimes they don't always return the entire
count until the cursor has been moved to the end of the recordset (I forget
the details regarding this, but as a point I always make sure if I'm using a
recordset object to explicitly .MoveLast before getting the count... I think
maybe as part of a form's recordset, this might not be an issue, but because
I'm not sure I thought I'd post anyway... better safe than sorry).
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
So, you want to fill a box on the mainform based on whether all, some or none
of the records in the subform have this particular box checked?
I think you can return that infomation by doing some minor analysis on the
subform's recordset. First get the total count of the recordset for the
subform,
Me.SubControlName.Form.Recordset.Recordcount
then use the DCount function with a where clause that pulls from the table a
list filter by the linked field (to give the same recordset you have in the
sub), along with criteria for the checkbox.
Then you should be able to compare the value returned by DCount to the value
returned by the subform recordset recordcount, and set the main field
accordingly...
Did I get that right?
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
Thank you that is working just how I was hoping. I don't know if I should be
starting a new post for this, but I had one more question related to this.
One of my fields in the subform I am pulling is a check box. I would like to
be able to set a field on the main form to Complete, Partially Complete, or
Incomplete based on whether all, some, or none of these boxes are checked. I
know I can check the first value with
Me![Subformname].Form.checkboxfieldname
but how do I check all the subform records associated with the main form
record? Thanks!
:
I *think* that if you use DoCmd.OpenReport in Hidden/Preview mode, and then
use SendObject, and then DoCmd.Close acReport, the SendObject method should
take the open copy of the report (that you will open with a where clause) and
send it. So your code (add appropiate args, I forget their placement...)
would resemble this:
DoCmd.OpenReport ....
DoCmd.SelectObject .... 'for good measure
DoCmd.SendObject ....
DoCmd.Close ....
hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
Thanks for the input. If I need to send it as a report, how do I make the
report only call the subform records associated with that particular main
form record. They both have ID fields to match them up, but I don't know
where to put that condition when sending the report. When I am opening the
the report there is a parameter for a where condition, but I don't see this
when sending. Thanks!
:
I think you would need to make a report of the data in the subform (not too
big of a deal) and send that as an attatchment. This isn't a strong point of
mine, but I believe SendObject can attach the report in a few different
formats (none of which I've found useful, personally).
hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
I have visual basic code written in the after update event of a field on the
main form. The form has a subform that has 0-10 records & 3 different
visible fields. I have the code below written to send an email, which runs
successfully. In addition to the current text I would like to be able to
include the entire contents of the subform associated with the record in the
body of the email. Is this possible to do, or if it is not, would it be
possible to include it as an attachment? Thanks!
DoCmd.SendObject , , , recep, , , "Parts Shippment", stemailtext & stwn &
stemailtext2 & sttn
recep: list of email addresses that come from a series of "if" statements
based on main form fields
stemailtext & stwn & stemailtext2 & sttn: current body of the email
comprised of two strings that are standard text saved in earlier code and two
strings that come from fields in the main form.
