Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Add Records Combination of Multiple List Boxes 1

Status
Not open for further replies.
BrianLe

BrianLe

Programmer
Feb 19, 2002
229
US
In AC97, I have a tblReqReadDocs with fields docID (pk), TopicID, SubjectID, GroupID, and DueDate. I have a frmAddReqReadDocs with lbxTopic, lbxSubject (multi-select), lbxGroup (multi-select), and tbxDueDate. I would like to add records to the table for each combination of selections from the list boxes. If 4 subjects and 3 groups are selected, I would like 12 (4x3) records added, all with the same TopicID and DueDate.

I have the following code to add records, but only the first SubjectID is combined with each of the GroupID's.

Code: 
Private Sub cmdAddRecords_Click()
On Error GoTo Err_cmdAddRecords_Click

   Dim lbx1 As ListBox, lbx2 As ListBox, lbx3 As ListBox, tbx1 As TextBox, SQL As String, DQ As String, itm
   
   Debug.Print SQL
   
   Set lbx1 = Me!lbxTopic
   Set lbx2 = Me!lbxSubject
   Set lbx3 = Me!lbxGroup
   Set tbx1 = Me!tbxDueDate
   
   DQ = """"
   
   If lbx1.ListIndex = (-1) Then
      MsgBox "No Topic Selected!"
   ElseIf lbx2.ListIndex = (-1) Then
      MsgBox "No Subject(s) Selected!"
   ElseIf lbx3.ListIndex = (-1) Then
      MsgBox "No Group(s) Selected!"
      Else
      For Each itm In lbx3.ItemsSelected
         SQL = "INSERT INTO tblReqReadDocs (TopicID, SubjectID, DueDate, GroupID) " & _
               "VALUES (" & lbx1.Column(0) & "," _
                        & lbx2.Column(0) & "," _
                        & "#" & tbx1 & "#" & ", " _
                        & lbx3.Column(0, itm) & ");"
        
        
        
        DoCmd.RunSQL SQL
      Next
   End If
   
   Set lbx1 = Nothing
   Set lbx2 = Nothing
   Set lbx3 = Nothing
   

Exit_cmdAddRecords_Click:
    Exit Sub

Err_cmdAddRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecords_Click
    
End Sub

I need all the combinations of TopicID, SubjectID, and GroupID to be added to the table.

I need a way to select the first GroupID, cycle through the SubjectID's and add the combinations to the table, and then select the next GroupID and repeat the process.

Thanks,

Brian
 
Sort by date Sort by votes
Something like this ?
Code: 
For Each itm In lbx3.ItemsSelected
  [!]For Each itm2 In lbx2.ItemsSelected[/!]
    SQL = "INSERT INTO tblReqReadDocs (TopicID, SubjectID, DueDate, GroupID) " & _
          "VALUES (" & lbx1.Column(0) & "," _
                   & lbx2.Column(0[!], itm2[/!]) & "," _
                   & "#" & tbx1 & "#" & ", " _
                   & lbx3.Column(0, itm) & ");"
    DoCmd.RunSQL SQL
  [!]Next[/!]
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Works great!!! Here's what I have.

Code: 
Private Sub cmdAddRecords_Click()
On Error GoTo Err_cmdAddRecords_Click

   Dim lbx1 As ListBox, lbx2 As ListBox, lbx3 As ListBox, tbx1 As TextBox, SQL As String, DQ As String, itm, itm2
   
   Debug.Print SQL
   
   Set lbx1 = Me!lbxTopic
   Set lbx2 = Me!lbxSubject
   Set lbx3 = Me!lbxGroup
   Set tbx1 = Me!tbxDueDate
   
   DQ = """"
   
   If lbx1.ListIndex = (-1) Then
      MsgBox "No Topic Selected!"
   ElseIf lbx2.ListIndex = (-1) Then
      MsgBox "No Subject(s) Selected!"
   ElseIf lbx3.ListIndex = (-1) Then
      MsgBox "No Group(s) Selected!"
      Else
       
       For Each itm In lbx3.ItemsSelected
       For Each itm2 In lbx2.ItemsSelected
        SQL = "INSERT INTO tblReqReadDocs (TopicID, SubjectID, DueDate, GroupID) " & _
          "VALUES (" & lbx1.Column(0) & "," _
                   & lbx2.Column(0, itm2) & "," _
                   & "#" & tbx1 & "#" & ", " _
                   & lbx3.Column(0, itm) & ");"
    
    DoCmd.RunSQL SQL
  Next
Next
        
   End If
   
   Set lbx1 = Nothing
   Set lbx2 = Nothing
   Set lbx3 = Nothing
 
Exit_cmdAddRecords_Click:
    Exit Sub

Err_cmdAddRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecords_Click
    
End Sub

Thanks,

Brian
 
Upvote 0 Downvote
Howdy [blue]PHV[/blue] . . .

As long as I've known your [blue]MO[/blue] thru the years, wouldn't ...
Code: 
[blue]   Dim [purple][b]db As DAO.Database[/b][/purple], SQL As String
   
   [purple][b]Set db = CurrentDb[/b][/purple]
   
   For Each itm In lbx3.ItemsSelected
     For Each itm2 In lbx2.ItemsSelected
       SQL = "INSERT INTO tblReqReadDocs (TopicID, SubjectID, DueDate, GroupID) " & _
             "VALUES (" & lbx1.Column(0) & "," _
                      & lbx2.Column(0, itm2) & "," _
                      & "#" & tbx1 & "#" & ", " _
                      & lbx3.Column(0, itm) & ");"
       [purple][b]db.Execute SQL, dbFailOnError[/b][/purple]
     Next
   Next
   
   [purple][b]Set db = Nothing[/b][/purple][/blue]
... be better ... no reference to legacy! ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
TheAceMan1,

Your code works too. I don't know enough to say whether it is better or not. I'm still getting Access crashes with your code, so maybe I have some other code issues elsewhere. Or maybe my computer just isn't fast enough.

Thanks,

Brian


 
Upvote 0 Downvote
BrianLe . . .

In the VBA editor [blue]Tools[/blue] - [blue]References...[/blue], make sure [blue]Microsoft DAO 3.6 Object Library[/blue] is checked!

If you keep having problems just go with that provided by [blue]PHV[/blue].

BTW ... what version Access?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
I'm using Access 97. DAO 3.5 Object Library was checked, so I unchecked it, and checked DAO 3.6 Object Library. I still sometimes get crashes when I run a particular query. I'll take that issue to the query forum after I get a chance to research it.

Thanks,

Brian
 
Upvote 0 Downvote
If I may expand on this issue, because the employees may move to a different group at a later date, I think I have to add the EmployeeID to tblReqReadDocs so I can retrieve which Docs each employee read.

Therefore, I would like to add the EmployeeID of each employee in the GroupID's added to tblReqReadDocs. I have tblEmployeeGroup that has the GroupID that each EmployeeID is currently assigned.

So if there were originally 12 records added, and there are 3 employees in GroupID=1, 2 in Group ID=2, and 4 in GroupID3 for a total of 9 employees, then 108 records (12x9) records would be added to tblReqReadDocs.

In the code below, I think I need some kind of JOIN to tblEmployeeGroup on GroupID.

Code: 
SQL = "INSERT INTO tblReqReadDocs (TopicID, SubjectID, DueDate, GroupID, EmployeeID) " & _
          "VALUES (" & lbx1.Column(0) & "," _
                   & lbx2.Column(0, itm2) & "," _
                   & "#" & tbx1 & "#" & ", " _
                   & lbx3.Column(0, itm) & ");"

Any suggestions?

Thanks,

Brian

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FrankMars
  • Locked
  • Question
Exporting Records to Excel 1
Replies
6
Views
262
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Add multiple contacts to outlook - from access table
Replies
12
Views
228
Moss100
Moss100
JaineyBlue
  • Locked
  • Question
Cascading combo boxes in subform
Replies
11
Views
361
JaineyBlue
JaineyBlue
M
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
424
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
348
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top