Finding First then Next

[Afrosheen via AccessMonster.com]

Thanks for taking the time to read this. What I'm trying to do is to create
an email form from the tblSupervisor. The fields I'm using is; Supervisor,
Email, AssistMan, and UnitMan.

In the form I have a combo box called cmboSupervisor. Once I click on the
name, in the After Update sub it puts the names of the AssistMan and UnitMan
in two text boxes from cmboSupervisor columns 3 & 4. This works ok.

Now once those name are in the text boxes I want each text box to look in the
Supervisor field and get the email address of each text box AssistMan and
UnitMan. But, if the AssistMan or UnitMan doesn't exist then there will be no
CC: What I have works but there's probably a better way to do it. I know it's
probably not the best coding and please don't laugh..

Private Sub cmboSupervisor_AfterUpdate()
txtAssist = Me.cmboSupervisor.Column(3)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
rs.FindFirst "[Supervisor] = '" & Me.cmboSupervisor.Column(3) & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
strCC = Me.Email
MsgBox txtAssist & "--" & strCC
Else
MsgBox "No other matches found"
End If
rs.Close
Set rs = Nothing

txtUnit = Me!cmboSupervisor.Column(4)

Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
rs.FindFirst "[Supervisor] = '" & Me.cmboSupervisor.Column(4) & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
strCC1 = Me.Email
Else
MsgBox "No other matches found"
End If
rs.Close
Set rs = Nothing

If strCC = "" Then
strCC = strCC1
Else
strCC = strCC & "," & strCC1
End If

MsgBox strCC
End Sub

Again, thanks for your help

 

[JimBurke via AccessMonster.com]

I would just do DLookups instead of creating recordsets, etc. Something like
this:

Private Sub cmboSupervisor_AfterUpdate()

dim email as string
dim strCC as string

txtAssist = Me.cmboSupervisor.Column(3)
email = Nz(DLookup("EMail","tblSupervisors","Supervisor = '" & txtAssist &
"'"), _
vbNullstring)
if email <> vbNullString then
strCC = email
End If

txtUnit = Me!cmboSupervisor.Column(4)
email = Nz(DLookup("EMail","tblSupervisors","Supervisor = '" & txtUnit &
"'"), _
vbNullstring)
if email <> vbNullString then
If strCC = vbNullString Then
strCC = email
Else
strCC = strCC & "," & email
End If
End If

End Sub

Add whatever else you need with msgbox, etc. I don't know if the table name
is right in the DLookups - you may need to change that.

Thanks for taking the time to read this. What I'm trying to do is to create
an email form from the tblSupervisor. The fields I'm using is; Supervisor,
Email, AssistMan, and UnitMan.

In the form I have a combo box called cmboSupervisor. Once I click on the
name, in the After Update sub it puts the names of the AssistMan and UnitMan
in two text boxes from cmboSupervisor columns 3 & 4. This works ok.

Now once those name are in the text boxes I want each text box to look in the
Supervisor field and get the email address of each text box AssistMan and
UnitMan. But, if the AssistMan or UnitMan doesn't exist then there will be no
CC: What I have works but there's probably a better way to do it. I know it's
probably not the best coding and please don't laugh..

Private Sub cmboSupervisor_AfterUpdate()
txtAssist = Me.cmboSupervisor.Column(3)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
rs.FindFirst "[Supervisor] = '" & Me.cmboSupervisor.Column(3) & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
strCC = Me.Email
MsgBox txtAssist & "--" & strCC
Else
MsgBox "No other matches found"
End If
rs.Close
Set rs = Nothing

txtUnit = Me!cmboSupervisor.Column(4)

Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
rs.FindFirst "[Supervisor] = '" & Me.cmboSupervisor.Column(4) & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
strCC1 = Me.Email
Else
MsgBox "No other matches found"
End If
rs.Close
Set rs = Nothing

If strCC = "" Then
strCC = strCC1
Else
strCC = strCC & "," & strCC1
End If

MsgBox strCC
End Sub

Again, thanks for your help

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me Jim. I'll try it out and get right back to you.

Thanks

I would just do DLookups instead of creating recordsets, etc. Something like
this:

Private Sub cmboSupervisor_AfterUpdate()

dim email as string
dim strCC as string

txtAssist = Me.cmboSupervisor.Column(3)
email = Nz(DLookup("EMail","tblSupervisors","Supervisor = '" & txtAssist &
"'"), _
vbNullstring)
if email <> vbNullString then
strCC = email
End If

txtUnit = Me!cmboSupervisor.Column(4)
email = Nz(DLookup("EMail","tblSupervisors","Supervisor = '" & txtUnit &
"'"), _
vbNullstring)
if email <> vbNullString then
If strCC = vbNullString Then
strCC = email
Else
strCC = strCC & "," & email
End If
End If

End Sub

Add whatever else you need with msgbox, etc. I don't know if the table name
is right in the DLookups - you may need to change that.

Thanks for taking the time to read this. What I'm trying to do is to create
an email form from the tblSupervisor. The fields I'm using is; Supervisor,

[quoted text clipped - 50 lines]

Again, thanks for your help

 

[Afrosheen via AccessMonster.com]

Looks like it's working good. Thanks again.

Thanks for getting back to me Jim. I'll try it out and get right back to you.

Thanks

I would just do DLookups instead of creating recordsets, etc. Something like
this:

[quoted text clipped - 34 lines]

 

[Afrosheen via AccessMonster.com]

Jim if your still there I have another question based on what you've done for
me so far.

Can a dlookup work with a yes/no field with the field set to true?

The reason I ask is that my boss wants me to put a second BCC in the email
package. This person may be a temp at this job, so I didn't want to hard code
it. This is the original routine you sent me.

20 txtAssist = Me.cmboSupervisor.Column(3)
30 Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = '" &
txtAssist & _
"'"), vbNullString)
40 If Email <> vbNullString Then
50 strCC = Email
60 End If

70 txtUnit = Me!cmboSupervisor.Column(4)
80 Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = '" &
txtUnit & _
"'"), vbNullString)
90 If Email <> vbNullString Then
100 If strCC = vbNullString Then
110 strCC = Email
120 Else
130 strBCC = Email
'strCC = strCC & "," & Email
140 End If
150 End If

I can set this person with a new variable if need be.

Thanks.

Looks like it's working good. Thanks again.

Thanks for getting back to me Jim. I'll try it out and get right back to you.

[quoted text clipped - 5 lines]

 

[Afrosheen via AccessMonster.com]

Sorry for your time Jim. I figured it out through trial and error.

Thanks again for your help..

Jim if your still there I have another question based on what you've done for
me so far.

Can a dlookup work with a yes/no field with the field set to true?

The reason I ask is that my boss wants me to put a second BCC in the email
package. This person may be a temp at this job, so I didn't want to hard code
it. This is the original routine you sent me.

20 txtAssist = Me.cmboSupervisor.Column(3)
30 Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = '" &
txtAssist & _
"'"), vbNullString)
40 If Email <> vbNullString Then
50 strCC = Email
60 End If

70 txtUnit = Me!cmboSupervisor.Column(4)
80 Email = Nz(DLookup("EMail", "tblSupervisor", "Supervisor = '" &
txtUnit & _
"'"), vbNullString)
90 If Email <> vbNullString Then
100 If strCC = vbNullString Then
110 strCC = Email
120 Else
130 strBCC = Email
'strCC = strCC & "," & Email
140 End If
150 End If

I can set this person with a new variable if need be.

Thanks.

Looks like it's working good. Thanks again.

[quoted text clipped - 3 lines]