Enter zero in field if record not found

[SunOfDog]

I have a procedure that opens two record sets and updates rs2.[demand] with rs1.[SumOfLQORD]. It works good except for this problem: If rs1 does not have a match in rs2 the value in rs2 is not updated. I need the value rs2.[demand] to be set to zero if no match is found.

Here is the code:

Public Sub subLoadDemand()

' update 'demand' field in tblPartNmbr with
' qryOrdersGrouped.[SumOfLQORD]

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("qryOrdersGrouped")
Set rs2 = db.OpenRecordset("tblPartNmbr")

rs1.MoveFirst

Do Until rs1.EOF
' if matching rec is found then update field
' in second rec to value
If rs2.RecordCount = 0 Then Exit Sub

rs2.MoveFirst
Do Until rs2.EOF
If rs1![PartNmbr] = rs2![FGItem] Then
rs2.Edit

' ***** need to add code to set value to zero if match
' ***** not found

rs2![demand] = rs1![SumOfLQORD] / 4
rs2.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs2.MoveFirst

Debug.Print rs2![demand]
rs1.MoveFirst

Debug.Print rs1![PartNmbr]

' close tables
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing


End Sub


Thanks so much!

 

[lameid]

Public Sub subLoadDemand()

' update 'demand' field in tblPartNmbr with
' qryOrdersGrouped.[SumOfLQORD]

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("qryOrdersGrouped")
Set rs2 = db.OpenRecordset("tblPartNmbr") 'May not need see "Why?"

rs1.MoveFirst

Do Until rs1.EOF
   ' if matching rec is found then update field
   ' in second rec to value
   If rs2.RecordCount = 0 Then Exit Sub  'Why?
   
   Set rs2 = db.OpenRecordset( _
           "Select * From tblPartNmbr " & _
           "Where FGItem = " & rs1![PartNmbr])
   If rs2.EOF Then 'No matches

   Do Until rs2.EOF
      rs2.Edit
      rs2![demand] = rs1![SumOfLQORD] / 4
      rs2.Update
      rs2.MoveNext
   Loop
   rs1.MoveNext
Loop
rs2.MoveFirst

Debug.Print rs2![demand]
rs1.MoveFirst

Debug.Print rs1![PartNmbr]

' close tables
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing


End Sub

 

[PHV]

Why not simply use an UPDATE query like this (SQL code):

UPDATE tblPartNmbr
SET demand=NZ((SELECT SumOfLQORD FROM qryOrdersGrouped WHERE FGItem=tblPartNmbr.PartNmbr),0)/4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

SunOfDog . . .
You do realize that ...

[blue]If rs1![PartNmbr] = rs2![FGItem] Then[/blue]

... is indicitive of [blue]PartNmbr[/blue] being duplicated in your db. This makes your db un-normalized and twice as big as it needs to be.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]