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_ApproveRequests_Click()
Dim db As DAO.Database
Dim recset As DAO.Recordset
If chkbox = True Then
Set db = CurrentDb()
Set recset = db.OpenRecordset("tbl_inventory", dbOpenDynaset)
recset.FindFirst "RecordName = '" & Me.RecordName & "'"
recset.Edit
recset!RecordQty = recset!RecordQty - 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)?
4 9126
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_ApproveRequests_Click()
Dim db As DAO.Database
Dim recset As DAO.Recordset
If chkbox = True Then
Set db = CurrentDb()
Set recset = db.OpenRecordset("tbl_inventory", dbOpenDynaset)
recset.FindFirst "RecordName = '" & Me.RecordName & "'"
recset.Edit
recset!RecordQty = recset!RecordQty - 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)?
Thanks for the advice. Got a sample code for me to look at?
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.OpenRecordset("tbl_inventory")
Set rs2 = db.OpenRecordset("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
Thank you mmccarthy. I will try that out.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: aaj |
last post by:
Hi all
I have a small but rather annoying problem with continuos forms, and am
wondering if anyone can suggest a method of getting over it.
The front end is Access 2002 with the BE being SQL...
|
by: DBQueen |
last post by:
I have a subform which is in Continuous Forms view.
I have added a button to the bottom of the page to move to the next record
using the button wizard (result: DoCmd.GoToRecord , , acNext).
I...
|
by: Mark |
last post by:
Hi there, I have a subform, set as a continuous form.
When a user selects a particular record in that subform, how can I make that
particular record stand out (color or font change, size, etc) from...
|
by: Richard Coutts |
last post by:
I have a Continuous Form where each record has a button that activates
another form that simplifies entering values into the record. The
activated form has the equivalent of a "Done" button. I'd...
|
by: jv |
last post by:
I have quite a few of continuous form and subform where I do allow
scroll bars. I run into problems with the mouse wheel whenever the
data on the form does not take up the whole page. In this...
|
by: kaeldowdy |
last post by:
This one is stumping me!
I have a Form/Subform arrangement. The main form is set as a Single
Form and the sub form is set as Continuous Forms.
On the Form_AfterUpdate event of the subform, I...
|
by: John |
last post by:
Hi
I need to block user from moving away from a record using any of
First/Last/Prev/Next/New Record or any other way IF the record has not been
saved, and displaying a message to the effect...
|
by: jmarcrum |
last post by:
I have a table with about 100 records. The user has the ability to click a button on the main form that will open up a "move to the next year" form. A query displays all 100 records on the...
|
by: Fred's |
last post by:
Hi Folks,
I have a report which the record source is a query name: Query3 and in
my Query3 I have this parameter "between And ".
Therefore, when I open my report, it will ask for a Start Date...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |