Variable Choices VBA

[ServiceEnvoy]

I have setup a button with an event procedure behind it. I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled. The email fields to pick
from would be: serviceremail or serviceremail2


Here is the code I currently have that works for the first email only:

Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler

Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim WaitingFor As String



Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)

DoCmd.RunCommand acCmdSaveRecord
WaitingFor = Me!serviceremail
With objMailItem

.To = WaitingFor
.Subject = "Missing Work Order SO# " & [WorkOrder#]
.body = "We have not received the signed work order for the
call referenced at the bottom of this message. Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the work
order to: (e-mail address removed)" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it. I have just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13) &
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"(e-mail address removed)" & Chr(13) & "www.serviceenvoy.com"
.display
End With

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub

 

[Jeff Boyce]

It all starts with the data. We aren't there -- we can't see your data
structure.

How is it that you can have "more than one email field filled"? Does that
mean your table has multiple (repeating) fields for the same kind of data
(e.g., email address)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ServiceEnvoy]

It all starts with the data. We aren't there -- we can't see your data
structure.

How is it that you can have "more than one email field filled"? Does that
mean your table has multiple (repeating) fields for the same kind of data
(e.g., email address)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have setup a button with an event procedure behind it. I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled. The email fields to pick
from would be: serviceremail or serviceremail2

Here is the code I currently have that works for the first email only:

Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler

Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim WaitingFor As String

Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)

DoCmd.RunCommand acCmdSaveRecord
WaitingFor = Me!serviceremail
With objMailItem

.To = WaitingFor
.Subject = "Missing Work Order SO# " & [WorkOrder#]
.body = "We have not received the signed work order for the
call referenced at the bottom of this message. Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the work
order to: (e-mail address removed)" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it. I have just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13) &
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"(e-mail address removed)" & Chr(13) & "www.serviceenvoy.com"
.display
End With

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub

I have 3 different fields for "servicers" which can be assigned to a
ticket. Each field is named progressively, servicer1, servicer2,
servicer3. When we enter them, I have an after update option that
automatically loads their respective email address into the fields,
email1, email2, email3.

 

[Jeff Boyce]

You will find that you (and Access) have to work much harder and come up
with "work arounds" to deal with the data structure you've described.

A well-normalized relational database design would reflect the fact ("fact",
in your situation) that there can be more than one email address associated
with whatever entity. Rather than pressing forward to develop a
work-around, consider creating a table structure that makes better use of
Access' relationally-oriented features and functions. That is, create a
"many" table that holds one record for each valid entity-email pair.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It all starts with the data. We aren't there -- we can't see your data
structure.

How is it that you can have "more than one email field filled"? Does
that
mean your table has multiple (repeating) fields for the same kind of data
(e.g., email address)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have setup a button with an event procedure behind it. I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled. The email fields to pick
from would be: serviceremail or serviceremail2

Here is the code I currently have that works for the first email only:

Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler

Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim WaitingFor As String

Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)

DoCmd.RunCommand acCmdSaveRecord
WaitingFor = Me!serviceremail
With objMailItem

.To = WaitingFor
.Subject = "Missing Work Order SO# " & [WorkOrder#]
.body = "We have not received the signed work order for the
call referenced at the bottom of this message. Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the work
order to: (e-mail address removed)" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it. I have just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13) &
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"(e-mail address removed)" & Chr(13) & "www.serviceenvoy.com"
.display
End With

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub

I have 3 different fields for "servicers" which can be assigned to a
ticket. Each field is named progressively, servicer1, servicer2,
servicer3. When we enter them, I have an after update option that
automatically loads their respective email address into the fields,
email1, email2, email3.

 

[ServiceEnvoy]

You will find that you (and Access) have to work much harder and come up
with "work arounds" to deal with the data structure you've described.

A well-normalized relational database design would reflect the fact ("fact",
in your situation) that there can be more than one email address associated
with whatever entity. Rather than pressing forward to develop a
work-around, consider creating a table structure that makes better use of
Access' relationally-oriented features and functions. That is, create a
"many" table that holds one record for each valid entity-email pair.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It all starts with the data. We aren't there -- we can't see your data
structure.
How is it that you can have "more than one email field filled"? Does
that
mean your table has multiple (repeating) fields for the same kind of data
(e.g., email address)?
Regards
Jeff Boyce
Microsoft Office/Access MVP

I have setup a button with an event procedure behind it. I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled. The email fields to pick
from would be: serviceremail or serviceremail2
Here is the code I currently have that works for the first email only:
Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler
Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim WaitingFor As String
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)
DoCmd.RunCommand acCmdSaveRecord
WaitingFor = Me!serviceremail
With objMailItem
.To = WaitingFor
.Subject = "Missing Work Order SO# " & [WorkOrder#]
.body = "We have not received the signed work order for the
call referenced at the bottom of this message. Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the work
order to: (e-mail address removed)" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" & Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it. I have just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13) &
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) & "Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"(e-mail address removed)" & Chr(13) & "www.serviceenvoy.com"
.display
End With
Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub

I have 3 different fields for "servicers" which can be assigned to a
ticket. Each field is named progressively, servicer1, servicer2,
servicer3. When we enter them, I have an after update option that
automatically loads their respective email address into the fields,
email1, email2, email3.

I've done my best to normalize in every place I can. However, I don't
believe I can use a subform for this use. If we were to use a
subform, we could not filter for tickets assigned to that servicer nor
could we setup double click and after update events to happen based on
those servicer and related email fields located in the subform. Now,
if I'm ignorant of something here where these things can be done with
a subform then I'm a student ready to learn. Please enlighten me.
Otherwise, I have to work with my data this way.

To make it as simple as possible, I set up the following related
fields:

servicer1ID servicer1name servicer1email
servicer2ID servicer2name servicer2email
servicer3id servicer3name servicer3email

This allows us to have up to 3 different servicer assigned to one
ticket and has served our needs to this point. I have an auto update
that inserts the servicer name and email address based on the unique
id selected. I have also setup the command button I was asking about
which will do what we want for servicer1. I just didn't know if there
might be a way to make the button ask us which servicer email to apply
the command to. If not, of course, I can make 3 sets of buttons for
the 3 servicers.

I'm willing to expand my knowledge and alter the way I'm doing things
but I'm a relative novice so whatever you suggest needs to come with
detailed explanations.

 

[Pieter Wijnen]

Oh, You can
The Parent Form is available through the Parent Property of the SubForm
ie

Private subEmail_Click()
Me.Parent.Email.Value = Me.subEmail.Value
End Sub

HtH

Pieter

You will find that you (and Access) have to work much harder and come up
with "work arounds" to deal with the data structure you've described.

A well-normalized relational database design would reflect the fact
("fact",
in your situation) that there can be more than one email address
associated
with whatever entity. Rather than pressing forward to develop a
work-around, consider creating a table structure that makes better use of
Access' relationally-oriented features and functions. That is, create a
"many" table that holds one record for each valid entity-email pair.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It all starts with the data. We aren't there -- we can't see your
data
structure.

How is it that you can have "more than one email field filled"? Does
that
mean your table has multiple (repeating) fields for the same kind of
data
(e.g., email address)?

Jeff Boyce
Microsoft Office/Access MVP

I have setup a button with an event procedure behind it. I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled. The email fields to pick
from would be: serviceremail or serviceremail2

Here is the code I currently have that works for the first email
only:

Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler

Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim WaitingFor As String

Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)

DoCmd.RunCommand acCmdSaveRecord
WaitingFor = Me!serviceremail
With objMailItem

.To = WaitingFor
.Subject = "Missing Work Order SO# " & [WorkOrder#]
.body = "We have not received the signed work order for
the
call referenced at the bottom of this message. Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the
work
order to: (e-mail address removed)" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" &
Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid
closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it. I have
just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13)
&
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) &
"Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"(e-mail address removed)" & Chr(13) & "www.serviceenvoy.com"
.display
End With

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub

I have 3 different fields for "servicers" which can be assigned to a
ticket. Each field is named progressively, servicer1, servicer2,
servicer3. When we enter them, I have an after update option that
automatically loads their respective email address into the fields,
email1, email2, email3.

I've done my best to normalize in every place I can. However, I don't
believe I can use a subform for this use. If we were to use a
subform, we could not filter for tickets assigned to that servicer nor
could we setup double click and after update events to happen based on
those servicer and related email fields located in the subform. Now,
if I'm ignorant of something here where these things can be done with
a subform then I'm a student ready to learn. Please enlighten me.
Otherwise, I have to work with my data this way.

To make it as simple as possible, I set up the following related
fields:

servicer1ID servicer1name servicer1email
servicer2ID servicer2name servicer2email
servicer3id servicer3name servicer3email

This allows us to have up to 3 different servicer assigned to one
ticket and has served our needs to this point. I have an auto update
that inserts the servicer name and email address based on the unique
id selected. I have also setup the command button I was asking about
which will do what we want for servicer1. I just didn't know if there
might be a way to make the button ask us which servicer email to apply
the command to. If not, of course, I can make 3 sets of buttons for
the 3 servicers.

I'm willing to expand my knowledge and alter the way I'm doing things
but I'm a relative novice so whatever you suggest needs to come with
detailed explanations.

 

[ServiceEnvoy]

Oh, You can
The Parent Form is available through the Parent Property of the SubForm
ie

Private subEmail_Click()
Me.Parent.Email.Value = Me.subEmail.Value
End Sub

HtH

Pieter

You will find that you (and Access) have to work much harder and come up
with "work arounds" to deal with the data structure you've described.
A well-normalized relational database design would reflect the fact
("fact",
in your situation) that there can be more than one email address
associated
with whatever entity. Rather than pressing forward to develop a
work-around, consider creating a table structure that makes better use of
Access' relationally-oriented features and functions. That is, create a
"many" table that holds one record for each valid entity-email pair.
Regards
Jeff Boyce
Microsoft Office/Access MVP

It all starts with the data. We aren't there -- we can't see your
data
structure.
How is it that you can have "more than one email field filled"? Does
that
mean your table has multiple (repeating) fields for the same kind of
data
(e.g., email address)?
Regards
Jeff Boyce
Microsoft Office/Access MVP

I have setup a button with an event procedure behind it. I need to
edit the vba code so it will ask me which email address to use if
there is more than one email field filled. The email fields to pick
from would be: serviceremail or serviceremail2
Here is the code I currently have that works for the first email
only:
Private Sub WorkOrderMissing_Click()
On Error GoTo ErrorHandler
Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim WaitingFor As String
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)
DoCmd.RunCommand acCmdSaveRecord
WaitingFor = Me!serviceremail
With objMailItem
.To = WaitingFor
.Subject = "Missing Work Order SO# " & [WorkOrder#]
.body = "We have not received the signed work order for
the
call referenced at the bottom of this message. Please make sure you
have done one of the following:" & Chr(13) & "Scan and email the
work
order to: (e-mail address removed)" & Chr(13) & "or" & Chr(13) &
"Fax the signed work order to: 888-858-2846 or 817-394-4236" &
Chr(13)
& Chr(13) & "No other email addresses or fax numbers are valid
closing
methods." & Chr(13) & "If you think you have already sent the work
order in via one of the above methods, please resend it. I have
just
looked in my database and it wasn't received. Please resend the work
order and feel free to call or email to make sure we receive it this
time." & Chr(13) & "Please remember, we must receive the signed work
order before we can close the call and pay you." & Chr(13) & Chr(13)
&
[CallData] & Chr(13) & Chr(13) & "Michael Firey" & Chr(13) &
"Service
Envoy, LLC" & Chr(13) & "918-398-4236" & Chr(13) &
"(e-mail address removed)" & Chr(13) & "www.serviceenvoy.com"
.display
End With
Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End Sub
I have 3 different fields for "servicers" which can be assigned to a
ticket. Each field is named progressively, servicer1, servicer2,
servicer3. When we enter them, I have an after update option that
automatically loads their respective email address into the fields,
email1, email2, email3.

I've done my best to normalize in every place I can. However, I don't
believe I can use a subform for this use. If we were to use a
subform, we could not filter for tickets assigned to that servicer nor
could we setup double click and after update events to happen based on
those servicer and related email fields located in the subform. Now,
if I'm ignorant of something here where these things can be done with
a subform then I'm a student ready to learn. Please enlighten me.
Otherwise, I have to work with my data this way.

To make it as simple as possible, I set up the following related
fields:

servicer1ID servicer1name servicer1email
servicer2ID servicer2name servicer2email
servicer3id servicer3name servicer3email

This allows us to have up to 3 different servicer assigned to one
ticket and has served our needs to this point. I have an auto update
that inserts the servicer name and email address based on the unique
id selected. I have also setup the command button I was asking about
which will do what we want for servicer1. I just didn't know if there
might be a way to make the button ask us which servicer email to apply
the command to. If not, of course, I can make 3 sets of buttons for
the 3 servicers.

I'm willing to expand my knowledge and alter the way I'm doing things
but I'm a relative novice so whatever you suggest needs to come with
detailed explanations.

Ah.....that's useful to know. Can you filter for things in a subform
across the entire table like you would a regular field on the main
form?