473,834 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to reference every record in Continuous forms?

4 New Member
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) ?
Aug 15 '06 #1
4 9169
MMcCarthy
14,534 Recognized Expert Moderator MVP
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) ?
Aug 16 '06 #2
neoman007
4 New Member
Thanks for the advice. Got a sample code for me to look at?
Aug 16 '06 #3
MMcCarthy
14,534 Recognized Expert Moderator MVP
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
Aug 16 '06 #4
neoman007
4 New Member
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
12561
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 Server 2000. I have a continuous bound form that is populated from a small recordset when the form opens. The form has lots of other bits, but my problem is with a single text box and an on-click event.
4
4166
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 want all of the controls in whatever is the CURRENT record to have it's data bolded on the screen. (Question #1: Is there a SIMPLE way to refer to the Current Record?) I've been trying to use a Bookmark to specify the current record, but it
3
7928
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 the other records in the list? Thank you in advance, Mark
1
3209
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 like to write an OnClick event that populates the contents of the current record of the parent form with the values entered in the popup form. So, the activated form needs to set the values of the current record of the parent form. How do you...
1
3437
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 instance, whenever a user uses the mouse wheel to scroll down, the first record disappears off the screen and they can't get it back unless they click on the Refresh button or Page Up. The mouse wheel never scroll back up to the first record. ...
1
30500
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 run some code that affects the main form which forces focus *away* from the subform and puts it to the main form. I need to keep focus on the subform when the code in the
6
5494
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 "Please finish editing". If however the user has explicitly saved the record using save from the access menu then allow to move from record. How do I achieve this via code? Thanks
8
3094
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 continuous form. The user can then checkbox which record he/she wants to move to the next year. Here's my question.... How do i check which checkboxes were checked and ONLY move those that were checked? Also, the table that it pulls from is a linked...
3
2946
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 And an EndDate. Now I will like to have this date in the report footer of my acess report.
0
9799
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9646
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10793
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10548
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10219
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7758
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4427
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3978
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.