How to reference every record in Continuous forms?

[neoman007]

I have a form (frm_approval) which is based on a query (qry_unapproved ). The query queries a table (tbl_total) for records that are unapproved and shows these records in (frm_approval) so the approver can use the form to approve them via checking a check box.
The form is set to Continuous View and I get many rows of textboxes (RecordName & RecordQty), each row for 1 record to be approved. For every record, I have a checkbox field (chkbox) which will be uncheck when the form opens up.
My idea is for the approver to check the records he approves and clicks on an command button that requery the form. (tbl_total) will be updated and everyone is happy.
Now there is a qty for each record. When the record is approved, the approved qty should be deducted from an inventory table (tbl_inventory)

Now, I coded into the command button the following VBA code:
=============== =============== =============== =======
Private Sub Commandbutton_A pproveRequests_ Click()

Dim db As DAO.Database
Dim recset As DAO.Recordset

If chkbox = True Then
Set db = CurrentDb()
Set recset = db.OpenRecordse t("tbl_inventor y", dbOpenDynaset)

recset.FindFirs t "RecordName = '" & Me.RecordName & "'"
recset.Edit
recset!RecordQt y = recset!RecordQt y - Me.RecordQty
recset.Update
End If

Me.Requery
'Me refers to frm_approval

End Sub
=============== =============== =============== =====

The problem here is that when I check more than 1 record, only the qty of 1 record (the most bottom checked record) gets updated in (tbl_inventory) . How do I make it such that any checked record qty gets updated in (tbl_inventory) ?

[MMcCarthy]

Your recordset is only checking the value in the current record that has focus in the form.

You need to create a second recordset based on tbl_total and check all records for approval to reset this total.

Rather than doing this everytime a record is checked you could place the code behind a command button with a caption like reset inventory. You could also set this code to run on form close.

I have a form (frm_approval) which is based on a query (qry_unapproved ). The query queries a table (tbl_total) for records that are unapproved and shows these records in (frm_approval) so the approver can use the form to approve them via checking a check box.
The form is set to Continuous View and I get many rows of textboxes (RecordName & RecordQty), each row for 1 record to be approved. For every record, I have a checkbox field (chkbox) which will be uncheck when the form opens up.
My idea is for the approver to check the records he approves and clicks on an command button that requery the form. (tbl_total) will be updated and everyone is happy.
Now there is a qty for each record. When the record is approved, the approved qty should be deducted from an inventory table (tbl_inventory)

Now, I coded into the command button the following VBA code:
=============== =============== =============== =======
Private Sub Commandbutton_A pproveRequests_ Click()

Dim db As DAO.Database
Dim recset As DAO.Recordset

If chkbox = True Then
Set db = CurrentDb()
Set recset = db.OpenRecordse t("tbl_inventor y", dbOpenDynaset)

recset.FindFirs t "RecordName = '" & Me.RecordName & "'"
recset.Edit
recset!RecordQt y = recset!RecordQt y - Me.RecordQty
recset.Update
End If

Me.Requery
'Me refers to frm_approval

End Sub
=============== =============== =============== =====

The problem here is that when I check more than 1 record, only the qty of 1 record (the most bottom checked record) gets updated in (tbl_inventory) . How do I make it such that any checked record qty gets updated in (tbl_inventory) ?

[neoman007]

Thanks for the advice. Got a sample code for me to look at?

[MMcCarthy]

I'm not sure if the logic of this is correct for what you're trying to do. However, you should get the idea.


Private Sub Form_Close()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim count As Integer

Set db = CurrentDb
Set rs1 = db.OpenRecordse t("tbl_inventor y")
Set rs2 = db.OpenRecordse t("tbl_total" )
count = 0

rs1.MoveFirst
Do Until rs1.EOF
If rs1!RecordName = "'" & Me.RecordName & "'" Then
If rs1!Approved = "No" Then ' Use your own field name and value here
count = count + 1
End If
End If
rs1.MoveNext
Loop

rs2.MoveFirst
Do until rs2.EOF
If rs2!RecordName = "'" & Me.RecordName & "'" Then
rs2.Edit
rs2!RecordQty = count
rs2.Update
Exit Do
End If
rs2.MoveNext
Loop

' always remember to close your recordsets
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

[neoman007]

Thank you mmccarthy. I will try that out.