473,320 Members | 1,991 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to reference every record in Continuous forms?

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)?
Aug 15 '06 #1
4 9126
MMcCarthy
14,534 Expert Mod 8TB
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)?
Aug 16 '06 #2
Thanks for the advice. Got a sample code for me to look at?
Aug 16 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
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
Aug 16 '06 #4
Thank you mmccarthy. I will try that out.
Aug 16 '06 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
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...
4
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...
3
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...
1
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...
1
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...
1
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...
6
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...
8
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...
3
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...
0
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...
0
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.