Merge multiple private sub procedures

[jharding]

Hi,

I am currently working on a form that will be used to update a table (tblAuditableEntitiesHistory). 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_Scope' 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

[jimatqsi]

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:

AddRecord_Click

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