Loop Can't Find Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a loop that ends when one of the child recordsets doesn't have a value
that matches the parent. I'm stuck. Here's what I have:
Set db = CurrentDb
Set rsTM = db.OpenRecordset("SELECT * FROM Territory")
If Not rsTM.EOF Then
Do While Not rsTM.EOF

Set rsStrategicLBD = db.OpenRecordset("SELECT * " & _
"FROM [ShipmentsTerritoryLBD] " & _
"WHERE ProductC1ID = 2 AND [TERID] = " &
rsTM![TerritoryID])

StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS], 0)

When rsTM.TerritoryID does not have a matching value in rsStrategicLBD, the
code terminates. I was hoping that StrategicLBDValue would just set itself to
0 when that happens, hence StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS],
0)
but apparently I need more code.

Any suggestions would be a big help. Thanks!
 
I have a loop that ends when one of the child recordsets doesn't have a value
that matches the parent. I'm stuck. Here's what I have:
Set db = CurrentDb
Set rsTM = db.OpenRecordset("SELECT * FROM Territory")
If Not rsTM.EOF Then
Do While Not rsTM.EOF

Set rsStrategicLBD = db.OpenRecordset("SELECT * " & _
"FROM [ShipmentsTerritoryLBD] " & _
"WHERE ProductC1ID = 2 AND [TERID] = " &
rsTM![TerritoryID])

StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS], 0)

When rsTM.TerritoryID does not have a matching value in rsStrategicLBD, the
code terminates. I was hoping that StrategicLBDValue would just set itself to
0 when that happens, hence StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS],
0)
but apparently I need more code.

Any suggestions would be a big help. Thanks!
Click to expand...

you mean: you get an error? it would be nice to know which one

I think you didn't checked if the recordset give you something,
therefore try this

if rsStrategicLBD.recordcount>0 then
StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS], 0)
else
StrategicLBDValue = 0
endif
 
Andy, That's genius! I can't thank you enough for the help. Thanks!

Andi Mayer said:
I have a loop that ends when one of the child recordsets doesn't have a value
that matches the parent. I'm stuck. Here's what I have:
Set db = CurrentDb
Set rsTM = db.OpenRecordset("SELECT * FROM Territory")
If Not rsTM.EOF Then
Do While Not rsTM.EOF

Set rsStrategicLBD = db.OpenRecordset("SELECT * " & _
"FROM [ShipmentsTerritoryLBD] " & _
"WHERE ProductC1ID = 2 AND [TERID] = " &
rsTM![TerritoryID])

StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS], 0)

When rsTM.TerritoryID does not have a matching value in rsStrategicLBD, the
code terminates. I was hoping that StrategicLBDValue would just set itself to
0 when that happens, hence StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS],
0)
but apparently I need more code.

Any suggestions would be a big help. Thanks!
Click to expand...

you mean: you get an error? it would be nice to know which one

I think you didn't checked if the recordset give you something,
therefore try this

if rsStrategicLBD.recordcount>0 then
StrategicLBDValue = Nz(rsStrategicLBD![SHIPDOLRS], 0)
else
StrategicLBDValue = 0
endif
Click to expand...
 
Andy, That's genius! I can't thank you enough for the help. Thanks!
Click to expand...
No, thats only experience

a genius would have told you:

this does't make sense, except you do something else with an else

and then you should check for Not (EOF and BOF) or recordcount=0
 
Set rsStrategicLBD = db.OpenRecordset( _
"SELECT * " & _
"FROM [ShipmentsTerritoryLBD] " & _
"WHERE ProductC1ID = 2 " & _
" AND [TERID] = " & rsTM![TerritoryID] _
)
Click to expand...



Why not just join the tables?

Tim F
 
Back
Top