INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...This is easily the most helpful website I've ever used, and this is the best forum with the quickest response time bar none...."
Geography
Where in the world do Tek-Tips members come from?
|
Add Records Combination of Multiple List Boxes
|
|
|
BrianLe (Programmer) |
26 Feb 12 14:14 |
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 |
|
|
PHV (MIS) |
26 Feb 12 14:36 |
|
|
BrianLe (Programmer) |
26 Feb 12 15:37 |
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 |
|
Howdy PHV . . . As long as I've known your MO thru the years, wouldn't ... CODE Dim db As DAO.Database, SQL As String
Set db = CurrentDb
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) & ");"
db.Execute SQL, dbFailOnError
Next
Next
Set db = Nothing ... be better ... no reference to legacy! ... Your Thoughts? . . . See Ya! . . . . . .
Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! 
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! |
|
|
BrianLe (Programmer) |
26 Feb 12 22:44 |
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
|
|
|
BrianLe (Programmer) |
1 Mar 12 22:03 |
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 |
|
|
BrianLe (Programmer) |
3 Mar 12 11:50 |
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 |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One