Update subform based on data in Main form

[ISO9000Man]

I have set up some code in the "on click" sub for saving a record on form [frmInspection]. This form has a subform [frmFAInvUpdate] which is linked to the main form control [WorkOrder]. When a record is entered the subform shows all records in tblFAInventory which have the same work order#. The code runs when the record is saved to look at [status] in the subform. If it is "Null" and the inspection passed "Yes", then it changes [status] to "Released' and enters the date in [releasedate]. If the inspection fails "No" it enters "MRB" in [status] field.

The problem I have is when there are multiple records with the same workorder#, and already have "Released" in the [status] field my code does not work on the one which is null. How do I get it to work on the null entry? It works great when there is just one entry for any work order#.
Any help here would be appreciated. I am studying but havn't found out where I am going wrong yet.

--Structure-----------------------------------------------------------
frmInspection: (Main Form)
[WorkOrder]
[Acct_Lot]

frmFAInvUpdatesubform on frmInspection)
[WO] (linked to frmInspection.WorkOrder)
[status]
[releasedate]

tblFAInventory: (data for frmFAInvUpdate)
[Item]
[WO]
[Location]
[Qty]
[arrivedate]
[releasedate]
[status]

---Code Sample------------------------------------------------------------

If Me.Accp_Lot.Value = "Yes" Then
If IsNull(Me.frmFAInvUpdate!status) Then
Me.frmFAInvUpdate!status = "Released"
Me.frmFAInvUpdate!releasedate = Date
End If
End If


If Me.Accp_Lot.Value = "No" Then
If IsNull(Me.frmFAInvUpdate!status) Then
Me.frmFAInvUpdate!status = "MRB"
End If
End If

 

[TheAceMan1]

How are ya ISO9000Man . . .

Try this:

[blue]   Dim sfrm As Form, rst As DAO.Recordset
   
   Set sfrm = [frmFAInvUpdate].Form
   Set rst = sfrm.RecordsetClone
   
   If Not rst.BOF Then
      Do
         If Trim(sfrm!Status & "") = "" Then
            rst.Edit
            
            If Me!Acct_Lot = "Yes" Then
               sfrm!Status = "Released"
               sfrm!ReleaseDate = Date
            ElseIf Me!Acct_Lot = "No" Then
               sfrm!Status = "MRB"
               sfrm!ReleaseDate = Null
            End If
            
            rst.Update
         End If
         
         rst.MoveNext
      Loop Until rst.EOF
      
      sfrm.Requery
   End If
   
   Set rst = Nothing
   Set sfrm = Nothing[/blue]

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

See Ya! . . . . . .

 

[ISO9000Man]

Actually here is what I ended up with. It works fine for this application and I have added matching up [Location] with [Inspection Type] in the main form. Thanks for your input and I will try it out as soon as I can. Always looking for different ways to accomplish these tasks.
--------------------------------------------

Dim strSQL As String
If Me.Accp_Lot.Value = "Yes" Then
strSQL = "UPDATE tblFAInventory SET status = 'Released', " & _
"releasedate = " & Format(Date, "\#mm\/dd\/yyyy\#") & " WHERE WO = '" & _
Me.Work_Order__ & "'" & " And Location = '" & Me.Inspection_Type & "'" & " AND status IS NULL" _



Else: strSQL = "UPDATE tblFAInventory SET status = 'MRB' " _
& " WHERE WO = '" & Me.Work_Order__ & "'" _
& " And Location = '" & Me.Inspection_Type & "'" & " AND status IS NULL"

End If
CurrentDb.Execute strSQL, 128