Loop Has No Record

M

mike

Any suggestions with this problem would be great. My
looping command which sends emails to employees is
terminated midway because the recordset item doesn't have
a match in another query that contains the data.

The recordset is a table of employees. The looping
command goes through the table of employees and sets the
filter for the datasets, like so:

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]")

If Not rs.EOF Then
Do While Not rs.EOF

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" & rs!
[Territory] & "'")

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]

LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]

MsgTOT = LBDValuePFR + LBDValueHTA

DoCmd.SendObject acSendNoObject, , , Email, , ,
DaysOfMonth & rs![Territory], MsgTOT, False

Loop
End If


The tblProducts table looks something like this:

Name Product Sales
South PFR 100
South HTA 100
North PFR 200
East HTA 100
East PFR 100

Almost every employee has sales of PFR and HTA. The
problem is that there's one or two who have no HTA sales,
so when loop gets to...

"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

....and tries to make the calulation at...

LBDValueHTA = rsHTA![LBDDollarsShipped]

....it looks for the tblMain territory's HTA value, can't
find one, and the procedure ends. I can't figure out how
to tell the procedure that if there's no value, then just
set LBDValueHTA to zero. Using Nz doesn't work because
there's not even a null value...there's no value at all.
I'm stuck on this one. Any suggestions would be a real
help. Thanks!!
 
M

Marshall Barton

mike said:
Any suggestions with this problem would be great. My
looping command which sends emails to employees is
terminated midway because the recordset item doesn't have
a match in another query that contains the data.

The recordset is a table of employees. The looping
command goes through the table of employees and sets the
filter for the datasets, like so:

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]")

If Not rs.EOF Then
Do While Not rs.EOF

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" & rs!
[Territory] & "'")

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]

LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]

MsgTOT = LBDValuePFR + LBDValueHTA

DoCmd.SendObject acSendNoObject, , , Email, , ,
DaysOfMonth & rs![Territory], MsgTOT, False

Loop
End If


The tblProducts table looks something like this:

Name Product Sales
South PFR 100
South HTA 100
North PFR 200
East HTA 100
East PFR 100

Almost every employee has sales of PFR and HTA. The
problem is that there's one or two who have no HTA sales,
so when loop gets to...

"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

...and tries to make the calulation at...

LBDValueHTA = rsHTA![LBDDollarsShipped]

...it looks for the tblMain territory's HTA value, can't
find one, and the procedure ends. I can't figure out how
to tell the procedure that if there's no value, then just
set LBDValueHTA to zero. Using Nz doesn't work because
there's not even a null value...there's no value at all.
I'm stuck on this one.
Click to expand...


If I followed all that, I think you want to check each
recordset and either assign the found values or zeros:

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" _
& rs![Territory] & "'")
If rsPFR.NoMatch Then
LBDValuePFR = 0
MTDValuePFR = 0
QTDValuePFR = 0
Else
LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]
End If

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" _
& rs![Territory] & "'")
If rsHTA.NoMatch Then
LBDValueHTA = 0
MTDValueHTA = 0
QTDValueHTA = 0
Else
LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]
End If
 
M

mike

Marshall, thanks for the suggestion. That's exactly what
I'm trying to do. I tried it but it still go hung up on
LBDValuePFR = rsPFR![LBDDollarsShipped]. Can you think of
why that might happen. I've never used .NoMatch before
but it seems to make sense. Can you think of any reason
it didn't work?

-----Original Message-----
mike said:
Any suggestions with this problem would be great. My
looping command which sends emails to employees is
terminated midway because the recordset item doesn't have
a match in another query that contains the data.

The recordset is a table of employees. The looping
command goes through the table of employees and sets the
filter for the datasets, like so:

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]")

If Not rs.EOF Then
Do While Not rs.EOF

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" & rs!
[Territory] & "'")

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]

LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]

MsgTOT = LBDValuePFR + LBDValueHTA

DoCmd.SendObject acSendNoObject, , , Email, , ,
DaysOfMonth & rs![Territory], MsgTOT, False

Loop
End If


The tblProducts table looks something like this:

Name Product Sales
South PFR 100
South HTA 100
North PFR 200
East HTA 100
East PFR 100

Almost every employee has sales of PFR and HTA. The
problem is that there's one or two who have no HTA sales,
so when loop gets to...

"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

...and tries to make the calulation at...

LBDValueHTA = rsHTA![LBDDollarsShipped]

...it looks for the tblMain territory's HTA value, can't
find one, and the procedure ends. I can't figure out how
to tell the procedure that if there's no value, then just
set LBDValueHTA to zero. Using Nz doesn't work because
there's not even a null value...there's no value at all.
I'm stuck on this one.
Click to expand...


If I followed all that, I think you want to check each
recordset and either assign the found values or zeros:

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" _
& rs![Territory] & "'")
If rsPFR.NoMatch Then
LBDValuePFR = 0
MTDValuePFR = 0
QTDValuePFR = 0
Else
LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]
End If

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" _
& rs![Territory] & "'")
If rsHTA.NoMatch Then
LBDValueHTA = 0
MTDValueHTA = 0
QTDValueHTA = 0
Else
LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]
End If
Click to expand...
 
M

mike

LBDValuePFR = Empty is the pop up that I get when I run
my cursor over the code.
-----Original Message-----
Marshall, thanks for the suggestion. That's exactly what
I'm trying to do. I tried it but it still go hung up on
LBDValuePFR = rsPFR![LBDDollarsShipped]. Can you think of
why that might happen. I've never used .NoMatch before
but it seems to make sense. Can you think of any reason
it didn't work?

-----Original Message-----
mike said:
Any suggestions with this problem would be great. My
looping command which sends emails to employees is
terminated midway because the recordset item doesn't have
a match in another query that contains the data.

The recordset is a table of employees. The looping
command goes through the table of employees and sets the
filter for the datasets, like so:

Set rs = Db.OpenRecordset("SELECT * " & _
"FROM [tblMain]")

If Not rs.EOF Then
Do While Not rs.EOF

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" & rs!
[Territory] & "'")

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]

LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]

MsgTOT = LBDValuePFR + LBDValueHTA

DoCmd.SendObject acSendNoObject, , , Email, , ,
DaysOfMonth & rs![Territory], MsgTOT, False

Loop
End If


The tblProducts table looks something like this:

Name Product Sales
South PFR 100
South HTA 100
North PFR 200
East HTA 100
East PFR 100

Almost every employee has sales of PFR and HTA. The
problem is that there's one or two who have no HTA sales,
so when loop gets to...

"WHERE [Product]='HTA' And [Territory]='" & rs!
[Territory] & "'")

...and tries to make the calulation at...

LBDValueHTA = rsHTA![LBDDollarsShipped]

...it looks for the tblMain territory's HTA value, can't
find one, and the procedure ends. I can't figure out how
to tell the procedure that if there's no value, then just
set LBDValueHTA to zero. Using Nz doesn't work because
there's not even a null value...there's no value at all.
I'm stuck on this one.
Click to expand...


If I followed all that, I think you want to check each
recordset and either assign the found values or zeros:

Set rsPFR = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='PFR' And [Territory]='" _
& rs![Territory] & "'")
If rsPFR.NoMatch Then
LBDValuePFR = 0
MTDValuePFR = 0
QTDValuePFR = 0
Else
LBDValuePFR = rsPFR![LBDDollarsShipped]
MTDValuePFR = rsPFR![MTDDollarsShipped]
QTDValuePFR = rsPFR![QTDDollarsShipped]
End If

Set rsHTA = Db.OpenRecordset("SELECT * " & _
"FROM [tblProducts] " & _
"WHERE [Product]='HTA' And [Territory]='" _
& rs![Territory] & "'")
If rsHTA.NoMatch Then
LBDValueHTA = 0
MTDValueHTA = 0
QTDValueHTA = 0
Else
LBDValueHTA = rsHTA![LBDDollarsShipped]
MTDValueHTA = rsHTA![MTDDollarsShipped]
QTDValueHTA = rsHTA![QTDDollarsShipped]
End If
Click to expand...
.
Click to expand...
 
M

Marshall Barton

mike said:
Marshall, thanks for the suggestion. That's exactly what
I'm trying to do. I tried it but it still go hung up on
LBDValuePFR = rsPFR![LBDDollarsShipped]. Can you think of
why that might happen. I've never used .NoMatch before
but it seems to make sense. Can you think of any reason
it didn't work?
Click to expand...


Sheesh, my brain must be somewhere else. NoMatch only
applies if you're using a Find method. It was supposed to
check RecordCount to see if the recordset has no records.
Use these IFs instead:

If rsPFR.RecordSount = 0 Then
and
If rsHTA.RecordSount = 0 Then

Sorry about that.
 
M

mike

Terrific! Thanks Marshall. It worked and it exposed a
flaw in how I've structured my database, so it looks like
a I have some work to do. Thanks again. Much appreciated.
-----Original Message-----
mike said:
Marshall, thanks for the suggestion. That's exactly what
I'm trying to do. I tried it but it still go hung up on
LBDValuePFR = rsPFR![LBDDollarsShipped]. Can you think of
why that might happen. I've never used .NoMatch before
but it seems to make sense. Can you think of any reason
it didn't work?
Click to expand...


Sheesh, my brain must be somewhere else. NoMatch only
applies if you're using a Find method. It was supposed to
check RecordCount to see if the recordset has no records.
Use these IFs instead:

If rsPFR.RecordSount = 0 Then
and
If rsHTA.RecordSount = 0 Then

Sorry about that.
Click to expand...
 
Top