Looping through 2 listboxes

[mohecan84]

Hi guys,

So I am close to getting my database up and running. Need a bit of advice on how I can get my form submit macro to loop through 2 listboxes. Let me first break it down.

I have some comboboxes and some text fields and 2 listboxes. As is the form works, but only allows for 1 selection on the first Listbox as I only want 1 selection from the first list box and upto 5 selections for the second Listbox for each record when submitted.

my code is as follows:

Private Sub cmdSubmit_Click()

Dim EnteredError As String
Dim ErrorSummary As String
Dim MessageTitle As String
Dim oItem As Variant
Dim CallCentreAgentsTemp1 As String
Dim SubCategoryTemp1 As String
Dim SubCategoryTemp2 As String
Dim SubCategoryTemp3 As String
Dim SubCategoryTemp4 As String
Dim SubcategoryTemp5 As String


    CallCentreAgentsTemp1 = ""
    SubCategoryTemp1 = ""
    SubCategoryTemp2 = ""
    SubCategoryTemp3 = ""
    SubCategoryTemp4 = ""
    SubcategoryTemp5 = ""
    
   
    EnteredError = 0
 
    
    If Me.txtStartDate = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Start Date" & vbCrLf
    End If
    
    If Me.txtEndDate = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - End Date" & vbCrLf
    End If
        
    If Me.cboEmployee = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Coach/Trainer" & vbCrLf
    End If
    
    If Me.cboType = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Type" & vbCrLf
    End If
    
    If Me.cboDepartment = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Department" & vbCrLf
    End If
    
    If Me.cboCategory = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Category" & vbCrLf
    End If
    
    If Me.lstAgents.ItemsSelected.Count = 0 Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Agents" & vbCrLf
    End If
    
    If Me.lstSubCategory.ItemsSelected.Count = 0 Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Sub-Category" & vbCrLf
    End If
    
    If EnteredError = 1 Then
        ErrorSummary = "The Following Fields are Required: " & vbCrLf & vbCrLf & ErrorSummary
        MessageTitle = "Action Required!"
        MsgBox ErrorSummary, vbInformation, MessageTitle
    End If
    
    iCount = 0
    
    If EnteredError <> 1 Then
    
    For Each oItem In Forms![Coaching and Training Logger]!lstAgents.ItemsSelected
        If iCount = 0 Then
            CallCentreAgentsTemp1 = Forms![Coaching and Training Logger]!lstAgents.ItemData(oItem)
        End If
        iCount = iCount + 1
        
    Next oItem
    
    iCount = 0
        
    For Each oItem In Forms![Coaching and Training Logger]!lstSubCategory.ItemsSelected
        If iCount = 0 Then
            SubCategoryTemp1 = Forms![Coaching and Training Logger]!lstSubCategory.ItemData(oItem)
        End If
        If iCount = 1 Then
            SubCategoryTemp2 = Forms![Coaching and Training Logger]!lstSubCategory.ItemData(oItem)
        End If
        If iCount = 2 Then
            SubCategoryTemp3 = Forms![Coaching and Training Logger]!lstSubCategory.ItemData(oItem)
        End If
        If iCount = 3 Then
            SubCategoryTemp4 = Forms![Coaching and Training Logger]!lstSubCategory.ItemData(oItem)
        End If
        If iCount = 4 Then
            SubcategoryTemp5 = Forms![Coaching and Training Logger]!lstSubCategory.ItemData(oItem)
        End If
        iCount = iCount + 1
        
    Next oItem
    
    iCount = 0
        
        
    
    
        'Add Data to Table
        
        CurrentDb.Execute "INSERT INTO Logdata(Logtime,StartTime,EndTime,Employee,Type,Department,Category,MethodUsed,CallCentreAgent,SubCategory1,SubCategory2,SubCategory3,SubCategory4,SubCategory5,Notes)" & "VALUES('" & Now() & "','" & Me.txtStartDate.Value & "','" & Me.txtEndDate.Value & "','" & Me.cboEmployee.Value & "','" & Me.cboType.Value & "','" & Me.cboDepartment.Value & "','" & Me.cboCategory.Value & "','" & Me.cboMethodUsed.Value & "','" & CallCentreAgentsTemp1 & "','" & SubCategoryTemp1 & "','" & SubCategoryTemp2 & "','" & SubCategoryTemp3 & "','" & SubCategoryTemp4 & "','" & SubcategoryTemp5 & "','" & Me.txtNotes & "')"
        'Clear form
        cmdClear_Click
        
    End If

End Sub

I look forward to any advice that can be provided. In the mean time I will carry on trying to figure this out.

kind regards,
Murtadau Gabier

 

[dhookom]

This code might not work because Null is not the same as "":

    If Me.txtStartDate = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Start Date" & vbCrLf
    End If

I would use:

    If Me.txtStartDate[red][b] & ""[/b][/red] = "" Then
        EnteredError = 1
        ErrorSummary = ErrorSummary & " - Start Date" & vbCrLf
    End If

I don't care much for your table structure since it is un-normalized.

I would also write the code to use a string variable for the SQL Statement so you can troubleshoot:

Dim strSQL as String
strSQL = "INSERT INTO Logdata " & _
    "(Logtime,StartTime,EndTime,Employee,Type,Department, " & _
    "etc....) "
strSQL = strSQL & " Values (#" & ...
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

Are any of your fields date/time? Your code suggests the fields are all text fields. Apostrophes in values will cause the SQL to fail.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Also consider:

    If EnteredError = 1 Then
        ErrorSummary = "The Following Fields are Required: " & vbCrLf & vbCrLf & ErrorSummary
        MessageTitle = "Action Required!"
        MsgBox ErrorSummary, vbInformation, MessageTitle
        [blue]Exit Sub[/blue]
    End If
    
    iCount = 0
    
    [green]'If EnteredError <> 1 Then[/green]
    ....

Have fun.

---- Andy

 

[MajP]

To add to Duane's I prefer this for a date value because if I want a date then it is not good enough to have any value.

If not isdate(Me.txtStartDate) Then
EnteredError = 1
ErrorSummary = ErrorSummary & " - Start Date" & vbCrLf
End I

if it is a numeric field
if not isnumeric(me.someNumericField)

if it is a generic string I would slightly modify

If trim(Me.somecontrol & " ") = "" Then
EnteredError = 1
ErrorSummary = ErrorSummary & " - Start Date" & vbCrLf
End I

Although what Duane shows works 99.9% of the time there are somve very rare occurences to get a blank space into a control. And " " does not equal "".

 

[mohecan84]

Hi guys, sorry for only replying now.

Thank you for all the suggestions. We managed to come up with a solution. What we ended up doing was to move the agent loop to the end of the script and we moved the execute before the agent loop ends. so the loop continued running and giving us a new record for each agent selected.

kind regards,
Murtadau Gabier