Email subform contents from main form


T

toby131

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.
 
Ad

Advertisements

J

Jack Leach

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)
 
T

toby131

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!
 
J

Jack Leach

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)
 
T

toby131

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!
 
J

Jack Leach

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)



toby131 said:
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!



Jack Leach said:
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)
 
Ad

Advertisements

J

Jack Leach

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)



Jack Leach said:
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)



toby131 said:
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!



Jack Leach said:
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.
 
T

toby131

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))


Jack Leach said:
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)



Jack Leach said:
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)



toby131 said:
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.
 
J

Jack Leach

("[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)



toby131 said:
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))


Jack Leach said:
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)



Jack Leach said:
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.
 
T

toby131

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!

Jack Leach said:
("[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)



toby131 said:
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))


Jack Leach said:
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.
 
J

Jack Leach

countofchecks = DCount("[checkboxfield]", "TableForSubform",
("[CheckboxField] = Yes") And ("[Linkingidfield] = " & idOfMainForm))


oops... sorry, didn't catch the quote/parenthese syntax error. You have the
parentheses of the where clause (and the AND) outside the quotes... they need
to be part of the string.

countofchecks = DCount("[checkboxfield]", "TableForSubform",
"([CheckboxField] = True) AND ([Linkingidfield] = " & ifofmainform & ")")

that should do...
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



toby131 said:
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!

Jack Leach said:
("[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)



toby131 said:
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.
 
Ad

Advertisements

T

toby131

Thank you so much for all of your help Jack. The new functionality is
working great!

Jack Leach said:
countofchecks = DCount("[checkboxfield]", "TableForSubform",
("[CheckboxField] = Yes") And ("[Linkingidfield] = " & idOfMainForm))


oops... sorry, didn't catch the quote/parenthese syntax error. You have the
parentheses of the where clause (and the AND) outside the quotes... they need
to be part of the string.

countofchecks = DCount("[checkboxfield]", "TableForSubform",
"([CheckboxField] = True) AND ([Linkingidfield] = " & ifofmainform & ")")

that should do...
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



toby131 said:
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!

Jack Leach said:
("[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.
 
Ad

Advertisements

J

Jack Leach

glad to help

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



toby131 said:
Thank you so much for all of your help Jack. The new functionality is
working great!

Jack Leach said:
countofchecks = DCount("[checkboxfield]", "TableForSubform",
("[CheckboxField] = Yes") And ("[Linkingidfield] = " & idOfMainForm))


oops... sorry, didn't catch the quote/parenthese syntax error. You have the
parentheses of the where clause (and the AND) outside the quotes... they need
to be part of the string.

countofchecks = DCount("[checkboxfield]", "TableForSubform",
"([CheckboxField] = True) AND ([Linkingidfield] = " & ifofmainform & ")")

that should do...
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



toby131 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top