collecting variables from multiple tables

[bluegrassstateworker]

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?

 

[Steve Schapel]

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.

 

[bluegrassstateworker]

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

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?

 

[Steve Schapel]

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

 

[bluegrassstateworker]

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

Thanks Steve! This appears to be what I need.

 

[bluegrassstateworker]

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

 

[Steve Schapel]

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

 

[bluegrassstateworker]

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

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]

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.

 

[Edward Yue]

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

 

[bluegrassstateworker]

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

Thanks for your help and patience Steve. For some reason, in my
earlier code I kept getting this illegal use of Me message and in
researching the cause of that, I tried defining literally which table
and field was to be used - a bad turn it appears (from the old COBOL
days). The good news is that even without this button, I am gradually
turning my coworkers away from the "just put it on a spreadsheet"
mentality. Regards, Bluegrass

 

[Steve Schapel]

Good news indeed.

Mind you, spreadsheets are great - so long as it's a spreadsheeting
purpose you are pursuing.

 

[Fawaz Hameed]

Hello,

I am new to Microsoft Acess. I have designed a form that has two corresponding sub forms in it. I can print out this form perfectly. But I would like to add a command button that will allow me to send this form to my email inbox as it appears. It does not matter what format it is sent in. But would prefer PDF or Excel. Would someeone be so kind to give me some advice on how to solve this problem.

Thank you

 

[Gina Whipp]

Fawaz Hameed,

I would suggest you use a report, then you could use
http://www.lebans.com/reporttopdf.htm to send you report in .pdf format
eMail. Forms are meant for entering data/information, reports are meant for
sharing information.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm