Hi,
I am currently working on a form that will be used to update a table (tblAuditableEn titiesHistory). I have two private sub procedures that will need to be merged so that the table fields and records update simultaneously when the user selects the 'Add Record' button.
For the 1st prodecure (see Procedure 1 below), we needed to give users the option to enter a single Review Name in a textbox field but allow them to tie it to multiple entities. When multiple entities are selected in the 'Entity' listbox field, a new table record will be created for each selection where only the entity ID from the selection changes on each row. The entity ID field will be used as the foreign key to join to the Entity table for analysis. Procedure 1 is currently working.
Procedure 2 was created to allow users to select multiple cycles. When multiple cycles are selected in the 'InScopeCycles' listbox field, the entries should be saved as comma separated values in the 'Cycles_In_Scop e' field within the table. Procedure 2 is currently not working.
Is there a way to merge Procedure 2 into Procedure 1 so that if the user selects multiple cycles and entities for a single review that all fields and records (if multiple entities are selected) are updated simultaneusly?
Procedure 1 - Private Sub AddRecord_Click()
-
On Error GoTo AddRecord_Click_Err
-
-
Dim MyDB As dao.Database
-
Dim varItem As Variant
-
Dim lst As ListBox
-
-
Set lst = Me![Entity]
-
-
Dim rst As dao.Recordset
-
If lst.ItemsSelected.Count = 0 Or IsNull(Me![ReviewName]) Then
-
MsgBox "You need to select an Entity and Review Name in order to proceed", vbExclamation, _
-
"Missing Data Input"
-
Exit Sub
-
End If
-
-
Set MyDB = CurrentDb
-
Set rst = MyDB.OpenRecordset("tblAuditableEntitiesHistory", dbOpenDynaset, dbAppendOnly)
-
-
With rst
-
For Each varItem In lst.ItemsSelected
-
.AddNew
-
![EntityID] = lst.ItemData(varItem)
-
![ReviewName] = Me![ReviewName]
-
![ReviewRating] = Me![ReviewRating]
-
![ReportNumber] = Me![ReportNo]
-
![Compliance_Assessment] = Me![ComplianceAssessment]
-
![Planned] = Me![Planned_Checkbox]
-
![ReportIssuanceDate] = Me![ReportsIssuanceDate]
-
![AuditPlanYear] = Me![AuditPlanYear]
-
![RiskRating] = Me![RiskRating]
-
![Comments] = Me![Comments]
-
.Update
-
Next varItem
-
End With
-
-
rst.Close
-
Set rst = Nothing
-
-
AddRecord_Click_Exit:
-
Exit Sub
-
-
AddRecord_Click_Err:
-
MsgBox Error$
-
Resume AddRecord_Click_Exit
-
-
End Sub
-
-
Procedure 2
-
Private Sub InScopeCycles_Click()
-
-
Dim SelectedValues As String
-
Dim frm As Form
-
Dim varItem As Variant
-
Dim lstItems As Control
-
Set lstItems = Me!InScopeCycles
-
-
For Each varItem In lstItems.ItemsSelected
-
If SelectedValues > "" Then
-
SelectedValues = SelectedValues & ", " & lstItems.ItemData(varItem)
-
Else
-
SelectedValues = lstItems.ItemData(varItem)
-
End If
-
Next varItem
-
Me!Cycles_In_Scope = SelectedValues
-
-
End Sub
1 3288 jimatqsi 1,276
Recognized Expert Top Contributor
jharding,
Yes, of course the can be merged. These procedures you show are invoked by the click of a button (or other object - more about that below). They could just as easily be invoked by referencing the name like this:
That would make the named procedure run without another click. So at the end of one procedure you could add that line of code to basically continue the processing in the second procedure.
For your AddRecord button, in the click event for that button you could do like this: - Private Sub Addrecord_cmd_Click()
-
AddRecord_Click ' run first update procedure
-
InScopeCycles_Click ' run second update procedure
-
-
end sub
-
Or you could copy the relevant code from each procedure and paste it into the AddRecord procedure in such a way that all you want to do is done. (Not really a good idea to duplicate code, though)
Now, about that Click event on your listbox object. That is going to end up running every time you click on the listbox. How will anybody ever select more than one entry in the list box? It's just going to run each time, doing one at a time.
You might be better off not using the listbox events to trigger that action. Maybe create a button to be used after all the selections are made.
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Seshadri |
last post by:
Dear All,
My apologies if this is not the correct forum. Please redirect. I am a
bit confused on where this kind of a question belongs. However I need help
rather badly. I would like to merge two ASP.NET c# projects into one project.
I am unable to information on going about doing it. I remember seeing a
Microsoft support KB article many months back and can't seem to find it now.
Any help appreciated.
To give a brief of what I am trying...
|
by: svdh2 |
last post by:
I have been looking at a problem the entire week in access, I have
still not been able to find a solution. Hope that you could maybe tell
where to look
Concerns the link between Access and Word. I can not transfer a report
to word without losing out on the lay-out (RTF format). I understand
that there is no way out
ok, mail merge I thought. But here I have the problem that I need to
merge multiple tables and that I can just include...
|
by: Seshadri |
last post by:
Dear All,
My apologies if this is not the correct forum. I am a bit confused on
where this kind of a question belongs. However I need help rather badly. I
would like to merge two ASP.NET c# projects into one project.
I am unable to information on going about doing it. I remember seeing a
Microsoft support KB article many months back and can't seem to find it now.
Any help appreciated.
To give a brief of what I am trying to do:
We are...
|
by: Otto Carl Marte |
last post by:
Hi,
As I understand it, Declared Global Temporary Tables (DGTTs) have a
scope that is session/connection based.
Using the same connection, I have discovered that if I declare a DGTT
in one stored procedure, then I can't create a second stored procedure
that uses the DGTT, as the DGTT is not "visible". The only way around
this is to use dynamic SQL in the second stored procedure. In this way
(using dynamic sql) i can create and use the...
|
by: AccessThis |
last post by:
Hi everyone,
I’m fairly new to Access and VBA, but I have set up the following code to export all the records in my database to a word template (C:\Template.dot) with bookmarks (bkfield1, bkfield2, etc.). Of course, when I run this code, each record prints out in a separate Word document. Is there any way for all the records to print out in a single Word document (i.e. to merge all the documents into one?)?
Thanks in advance for your...
| |
by: Quish |
last post by:
Hey
I am creating a database application that is accessed through a .NET front end.
What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me...
|
by: shimul |
last post by:
Hi All,
I would like to some code (VBA) that will merge multiple access Report to single PDF report.
Thank you in advance.
Shimul
|
by: BarryM |
last post by:
hey does anybody know how to merge any kind of two or several files and split them up. I know how to merge multiple image files but dont know to do it with any other type
Dim sw As New IO.FileStream(NewFilePath, IO.FileMode.Create)
Dim fmt As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
Dim bmp As Bitmap
Filelist.Items.Add(ImageName)
For Each item As String In...
|
by: gelezniyden |
last post by:
I have two stored procedures one for INSERT and second for Update and I'd like to merge them in one so could you please tell me which satement should I use for that ?
CREATE PROCEDURE Tools
(@CategoryID_1 int,
@CategoryName_2 nvarchar(100),
@Description_3 ntext)
AS INSERT INTO Categories
(CategoryID,
CategoryName,
|
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...
|
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,...
| |
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,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |