I have a form with subforms that reference other tables. I have a
button which uses DoCmd.SendObject to create an email message and
includes these values into a text field as part of the email message.
It works fine for the fields in one table but not two others. For
example
Group table: contains the group information - group contact, address,
phone, etc.
Position table: contains positions available for each group - related
to the group table.
Individual table: contains information on individuals filling
positions the group has open.

While debugging, I am able to verify that my Position fields are
collecting the correct values but when I reference either the
Individual or Group Table field I get an error indicating the field
cannot be found.

Here is some code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Set dbs = CurrentDb
............. and so on to the value capturing

*** REFERENCES POSITION TABLE FIELDS CORRECTLY
strStartDate = Nz([PStartDate], "")
strEndDate = Nz([PEndDate], "")

** REFERENCE TO INDIVIDUAL TABLE FIELD VALUE FAILS HERE ***

strIndFirst = Nz([IFirstName], "Vacant or No Entry")
strIndLast = Nz([Individual].[ILastName], "")
strIndHomeCity = Nz([IHomeCity], "")

What should I do to change the pointer to the correct record in each
table?

Bluegrass,

This code will reference controls based on where they are in relation to
the command button that the code runs from.

I would assume from your descriptuion that you have PStartDate and
PEndDate controls on the same form as the command button. Is that correct?

And I would assume the other controls IFirstName etc are not. Correct?
So where are they? On a subform? Or the command button is on a
subform and the other contros are on the parent form? Nowhere? If on a
subform, what is its name?

By the way, as an aside, if I can assume that PStartDate and PEndDate
are dates, then youir expressions such as Nz([PStartDate], "") are not
correct.

--
Steve Schapel, Microsoft Access MVP

bluegrassstateworker wrote:
> I have a form with subforms that reference other tables. I have a
> button which uses DoCmd.SendObject to create an email message and
> includes these values into a text field as part of the email message.
> It works fine for the fields in one table but not two others. For
> example
> Group table: contains the group information - group contact, address,
> phone, etc.
> Position table: contains positions available for each group - related
> to the group table.
> Individual table: contains information on individuals filling
> positions the group has open.
>
> While debugging, I am able to verify that my Position fields are
> collecting the correct values but when I reference either the
> Individual or Group Table field I get an error indicating the field
> cannot be found.
>
> Here is some code:
> Dim dbs As DAO.Database
> Dim rs As DAO.Recordset
> Set dbs = CurrentDb
> ............ and so on to the value capturing
>
> *** REFERENCES POSITION TABLE FIELDS CORRECTLY
> strStartDate = Nz([PStartDate], "")
> strEndDate = Nz([PEndDate], "")
>
> ** REFERENCE TO INDIVIDUAL TABLE FIELD VALUE FAILS HERE ***
>
> strIndFirst = Nz([IFirstName], "Vacant or No Entry")
> strIndLast = Nz([Individual].[ILastName], "")
> strIndHomeCity = Nz([IHomeCity], "")
>
> What should I do to change the pointer to the correct record in each
> table?

On Oct 29, 2:52 pm, Steve Schapel <scha...@mvps.org.ns> wrote:
> Bluegrass,
>
> This code will reference controls based on where they are in relation to
> the command button that the code runs from.
>
> I would assume from your descriptuion that you have PStartDate and
> PEndDate controls on the same form as the command button. Is that correct?
>
> And I would assume the other controls IFirstName etc are not. Correct?
> So where are they? On a subform? Or the command button is on a
> subform and the other contros are on the parent form? Nowhere? If on a
> subform, what is its name?
>
> By the way, as an aside, if I can assume that PStartDate and PEndDate
> are dates, then youir expressions such as Nz([PStartDate], "") are not
> correct.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> bluegrassstateworker wrote:
> > I have a form with subforms that reference other tables. I have a
> > button which uses DoCmd.SendObject to create an email message and
> > includes these values into a text field as part of the email message.
> > It works fine for the fields in one table but not two others. For
> > example
> > Group table: contains the group information - group contact, address,
> > phone, etc.
> > Position table: contains positions available for each group - related
> > to the group table.
> > Individual table: contains information on individuals filling
> > positions the group has open.
>
> > While debugging, I am able to verify that my Position fields are
> > collecting the correct values but when I reference either the
> > Individual or Group Table field I get an error indicating the field
> > cannot be found.
>
> > Here is some code:
> > Dim dbs As DAO.Database
> > Dim rs As DAO.Recordset
> > Set dbs = CurrentDb
> > ............ and so on to the value capturing
>
> > *** REFERENCES POSITION TABLE FIELDS CORRECTLY
> > strStartDate = Nz([PStartDate], "")
> > strEndDate = Nz([PEndDate], "")
>
> > ** REFERENCE TO INDIVIDUAL TABLE FIELD VALUE FAILS HERE ***
>
> > strIndFirst = Nz([IFirstName], "Vacant or No Entry")
> > strIndLast = Nz([Individual].[ILastName], "")
> > strIndHomeCity = Nz([IHomeCity], "")
>
> > What should I do to change the pointer to the correct record in each
> > table?

You are correct, the button does rest on a form in the POSITION
table. This form has tabs: The INDIVIDUAL subform rests within the
Position Form on one tab. The GROUP FORM is on another tab by itself
and serves at the main tab. Most of my time has been spent in Excel
VBA and Access VBA is a different enough. So would it be easier to
create a new tab, bound to a query then pull from that?

Bluegrass,

Tabs on forms provide only for convenience, so whether your subforms are
on tabs or not is not relevant.

No, it just means you need to correctly reference the controls. And a
control on a subform is sort of a control within a control. So if I
understand you correctly, that the IFirstName control is on the
Individual subform, then that line of code would look like this:

strIndFirst = Nz(Me.Individual.Form.IFirstName, "Vacant or No Entry")

--
Steve Schapel, Microsoft Access MVP

bluegrassstateworker wrote:
> You are correct, the button does rest on a form in the POSITION
> table. This form has tabs: The INDIVIDUAL subform rests within the
> Position Form on one tab. The GROUP FORM is on another tab by itself
> and serves at the main tab. Most of my time has been spent in Excel
> VBA and Access VBA is a different enough. So would it be easier to
> create a new tab, bound to a query then pull from that?

On Oct 29, 3:58 pm, Steve Schapel <scha...@mvps.org.ns> wrote:
> Bluegrass,
>
> Tabs on forms provide only for convenience, so whether your subforms are
> on tabs or not is not relevant.
>
> No, it just means you need to correctly reference the controls. And a
> control on a subform is sort of a control within a control. So if I
> understand you correctly, that the IFirstName control is on the
> Individual subform, then that line of code would look like this:
>
> strIndFirst = Nz(Me.Individual.Form.IFirstName, "Vacant or No Entry")
>
> --
> Steve Schapel, Microsoft Access MVP
>
> bluegrassstateworker wrote:
> > You are correct, the button does rest on a form in the POSITION
> > table. This form has tabs: The INDIVIDUAL subform rests within the
> > Position Form on one tab. The GROUP FORM is on another tab by itself
> > and serves at the main tab. Most of my time has been spent in Excel
> > VBA and Access VBA is a different enough. So would it be easier to
> > create a new tab, bound to a query then pull from that?

Thanks Steve! This appears to be what I need.

On Oct 30, 8:15 am, bluegrassstateworker <andy.crumbac...@gmail.com>
wrote:
> On Oct 29, 3:58 pm, Steve Schapel <scha...@mvps.org.ns> wrote:
>
>
>
> > Bluegrass,
>
> > Tabs on forms provide only for convenience, so whether your subforms are
> > on tabs or not is not relevant.
>
> > No, it just means you need to correctly reference the controls. And a
> > control on a subform is sort of a control within a control. So if I
> > understand you correctly, that the IFirstName control is on the
> > Individual subform, then that line of code would look like this:
>
> > strIndFirst = Nz(Me.Individual.Form.IFirstName, "Vacant or No Entry")
>
> > --
> > Steve Schapel, Microsoft Access MVP
>
> > bluegrassstateworker wrote:
> > > You are correct, the button does rest on a form in the POSITION
> > > table. This form has tabs: The INDIVIDUAL subform rests within the
> > > Position Form on one tab. The GROUP FORM is on another tab by itself
> > > and serves at the main tab. Most of my time has been spent in Excel
> > > VBA and Access VBA is a different enough. So would it be easier to
> > > create a new tab, bound to a query then pull from that?
>
> Thanks Steve! This appears to be what I need.

I spoke too soon. I am getting values but they are not correct. Are
coming from the wrong records from each table (on the screen and
navigationally they are correct). I thought I could establish the
recordset to point to the proper table then obtain the correct value.
My logic might be way off too. Any ideas/suggestions appreciated.

Sub SendEmail3()
'declarations
Dim MyDB As DAO.Database
Dim TblGroup As DAO.Recordset
Dim TblPosition As DAO.Recordset
Dim TblIndividual As DAO.Recordset

' Strings of fields defined here
Dim strGroupName As String
Dim strPositionDescription As String
Dim strIndividualName As String
.......

, Defines the tables to pull the information from
Set MyDB = CurrentDb
Set TblGroup = MyDB.OpenRecordset("Group")
Set TblPosition = MyDB.OpenRecordset("Position")
Set TblIndividual = MyDB.OpenRecordset("Individual")

'Captures Variables for the Text within the email and a TO: recipient
strGroupName = TblGroup.Fields("GName")
strPositionDescription = =TblPosition.Fields("PName")
strIndividualName = TblIndividual.Fields("IFullName")


'Formats the email message using the above data
'
strSubject = "Email Subject Text"
strText = "The position below will be expiring and will need to be
addressed. " & _
"If you need additional information, please feel free to contact me. "
& Chr(13) & Chr(13) & _
"Group: " & strGroupName & Chr(13) & _
"Position :" & strPositionDescription & Chr(13) & _
"Name of Current Member: " & strIndividualName

DoCmd.SendObject acSendNoObject, " ", "HTML", strTO, , , strSubject,
strText, True
TblGroup.Close
TblIndividual.Close
TblPosition.Close


End Sub

Bluegrass,

It appears that you are reading the values from one field in the first
record of each of 3 separate tables.

I obviously misunderstood you earlier - I thought we were talking about
the value of controls on your form/subform.

Now I don't have any idea at all what you are trying to achieve.

Just in ordinary English... How many emails are supposed to be going
out? To whom? How do we identify the intended recipient(s)?

--
Steve Schapel, Microsoft Access MVP

bluegrassstateworker wrote:
> I spoke too soon. I am getting values but they are not correct. Are
> coming from the wrong records from each table (on the screen and
> navigationally they are correct). I thought I could establish the
> recordset to point to the proper table then obtain the correct value.
> My logic might be way off too. Any ideas/suggestions appreciated.
>

On Oct 31, 2:44 pm, Steve Schapel <scha...@mvps.org.ns> wrote:
> Bluegrass,
>
> It appears that you are reading the values from one field in the first
> record of each of 3 separate tables.
>
> I obviously misunderstood you earlier - I thought we were talking about
> the value of controls on your form/subform.
>
> Now I don't have any idea at all what you are trying to achieve.
>
> Just in ordinary English... How many emails are supposed to be going
> out? To whom? How do we identify the intended recipient(s)?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> bluegrassstateworker wrote:
> > I spoke too soon. I am getting values but they are not correct. Are
> > coming from the wrong records from each table (on the screen and
> > navigationally they are correct). I thought I could establish the
> > recordset to point to the proper table then obtain the correct value.
> > My logic might be way off too. Any ideas/suggestions appreciated.

The button is intended to send just one email to notify a contact that
a term will be expiring. The TO: value is defined (in the full code
not included here) from a field in the GROUP table. I am trying to
pull together values I am visually able to see on the screen into the
email message.
-
I have searched online for some articles on working with record
pointers and subforms without any real success. Have not seen any
code to demonstrate the logic of this principle either so I am
bewildered at this point how far off in either programming or logic
(and/or both) I am.

Bluegrass,

I showed you already the syntax for referencing a control on a subform.

I have gathered that you have a form named Position, and on this form
there are two subforms named Group and Individual. And the command
button to run the emailing code is on the Position form. Am I
approximately right?

So here's an example of something like the code you will need:

----------
strGroupName = Me.Group.Form.GName
strPositionDescription = Me.PName
strIndividualName = Me.Individual.Form.IFullName

strSubject = "Email Subject Text"
strText = "The position below will be expiring and will need to be
addressed. " & _
"If you need additional information, please feel free to contact
me." & vbCrLf & _
"Group: " & strGroupName & vbCrLf & _
"Position :" & strPositionDescription & vbCrLf & _
"Name of Current Member: " & strIndividualName

DoCmd.SendObject ...
----------

Forget recordsets and fields, they are not applicable to your purpose.
Although an alternative approach would be to make a query which includes
all 3 tables, with appropriate criteria to isolate the record you
require for your email, and then reference the data from this query into
your code... but that's unnecessarily complicated.

--
Steve Schapel, Microsoft Access MVP

bluegrassstateworker wrote:
> The button is intended to send just one email to notify a contact that
> a term will be expiring. The TO: value is defined (in the full code
> not included here) from a field in the GROUP table. I am trying to
> pull together values I am visually able to see on the screen into the
> email message.
> -
> I have searched online for some articles on working with record
> pointers and subforms without any real success. Have not seen any
> code to demonstrate the logic of this principle either so I am
> bewildered at this point how far off in either programming or logic
> (and/or both) I am.

"Steve Schapel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bluegrass,
>
> It appears that you are reading the values from one field in the first
> record of each of 3 separate tables.
>
> I obviously misunderstood you earlier - I thought we were talking about
> the value of controls on your form/subform.
>
> Now I don't have any idea at all what you are trying to achieve.
>
> Just in ordinary English... How many emails are supposed to be going out?
> To whom? How do we identify the intended recipient(s)?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> bluegrassstateworker wrote:
>> I spoke too soon. I am getting values but they are not correct. Are
>> coming from the wrong records from each table (on the screen and
>> navigationally they are correct). I thought I could establish the
>> recordset to point to the proper table then obtain the correct value.
>> My logic might be way off too. Any ideas/suggestions appreciated.
>>