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!

Insert table value based on listbox selection

Status
Not open for further replies.
BrianLe

BrianLe

Programmer
Feb 19, 2002
229
US
In AC97, I'm trying to create a database of reading done by employees. The records go into tblReqReadDocs (DocID (PK),TopicID,SubjectID,GroupID,DueDate)using the code below
from frmAddReqReadDocs that has lbxTopicID, lbsSubjectID, lbxGroupID, and tbxDueDate.

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
     
   Dim db As DAO.Database
   
   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
        
   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 would like the EmployeeID added to tblReqReadDocs based on the GroupID selected on the frmAddReqReadDocs so I can retrieve which Docs each employee read.

Each employee is assigned to a group. I have tblEmployees (EmployeeID, FirstName, LastName, etc.), and tblEmployeeGroup (EmployeeID, GroupID).


I think I need something like the following in the code:

Code: 
INSERT INTO tblReqReadDocs.EmployeeID from tblEmployees JOIN tblEmployeeGroup on GroupID
WHERE frmAddReqReadDocs.GroupID = tblEmployeeGroup.GroupID

For Each EmployeeID selected in tblEmployeeGroup

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

Any suggestion?

Thanks,

Brian
 
Sort by date Sort by votes
so really you want to track by employee rather than the group?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>
 
Upvote 0 Downvote
At first I thought it would be adequate to track by group, but when I considered that employees could change groups I figured it would be best to track by employee. They don't change groups that often though. The reports we run, usually within a one-two month period after the reading assignment, are by group so I have a tblEmployeeGroup (EmployeeID, GroupID) that has the group each employee is currently in. I'm hoping that I will be able to input the assigned reading to tblReqReadDocs by selecting a GroupID from lbxGroup on frmAddReqReadDocs, and have the tblReqReadDocs populated with the EmployeeID, the employee's current GroupID, the TopicID, and the SupjectID.

Thanks,

Brian
 
Upvote 0 Downvote
Think of it this way. If this were an Orders db you wouldn't place an order by category you would order by item.

so in your case you should store the data by employee and modify the reports as necessary. I am assuming that the groups table has a one to many relationship to the employees table?

Thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>
 
Upvote 0 Downvote
Typed, untested:
Code: 
...
[!]Dim rs As DAO.Recordset[/!]
For Each itm In lbx3.ItemsSelected
    [!]Set rs = CurrentDb.OpenRecordset("SELECT EmployeeID FROM tblEmployeeGroup WHERE GroupID=" & lbx3.Column(0, itm))
    While Not (rs.EOF Or rs.BOF)[/!]
        For Each itm2 In lbx2.ItemsSelected
            SQL = "INSERT INTO tblReqReadDocs (TopicID,SubjectID,DueDate,GroupID[!],EmployeeID[/!]) " & _
                  "VALUES (" & lbx1.Column(0) & "," _
                & lbx2.Column(0, itm2) & "," _
                & "#" & tbx1 & "#" & ", " _
                & lbx3.Column(0, itm) & [!]", " _
                & rs!EmployeeID & [/!]");"
            DB.Execute SQL, dbFailOnError
        Next
        [!]rs.MoveNext
    Wend
    rs.Close[/!]
Next
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PH, I put in your code , but I'm getting a "Object variable or with block variable not set" error with this code.

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!"
   Else
     
  Dim rs As DAO.Recordset
  Dim db As DAO.Database
  
For Each itm In lbx3.ItemsSelected
    Set rs = CurrentDb.OpenRecordset("SELECT EmployeeID FROM tblEmployeeGroup WHERE GroupID=" & lbx3.Column(0, itm))
    While Not (rs.EOF Or rs.BOF)
        For Each itm2 In lbx2.ItemsSelected
            SQL = "INSERT INTO tblReqReadDocs (TopicID,SubjectID,DueDate,GroupID,EmployeeID) " & _
                  "VALUES (" & lbx1.Column(0) & "," _
                & lbx2.Column(0, itm2) & "," _
                & "#" & tbx1 & "#" & ", " _
                & lbx3.Column(0, itm) & ", " _
                & rs!EmployeeID & ");"
            db.Execute SQL, dbFailOnError
       Next
        rs.MoveNext
    Wend
    rs.Close
    db.Close

Next
     
   End If
   
 Set db = Nothing
      
   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 tried a number of changes but with no luck. One change gave me an error that the "INSERT into has unknown GroupID, check spelling...". I checked the tables and they look to be alright with "GroupID"

Any suggestions?

Thanks,

Brian
 
Upvote 0 Downvote
Code: 
...
Dim db As DAO.Database
[!]Set db = CurrentDb[/!]
For Each itm In lbx3.ItemsSelected
    Set rs = db.OpenRecordset("SELECT EmployeeID FROM tblEmployeeGroup WHERE GroupID=" & lbx3.Column(0, itm))
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PH,

Thanks - that was it. I was getting the INSERT Into error because someplace along the line I had deleted "GroupID" from tblReqReadDocs. I moved the last Next to before rs.Close and it works great. Here's the final code.

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!"
   Else
     
  Dim rs As DAO.Recordset
  Dim db As DAO.Database
    
  Set db = CurrentDb
  
For Each itm In lbx3.ItemsSelected
    Set rs = CurrentDb.OpenRecordset("SELECT EmployeeID FROM tblEmployeeGroup WHERE GroupID=" & lbx3.Column(0, itm))
    While Not (rs.EOF Or rs.BOF)
        For Each itm2 In lbx2.ItemsSelected
            SQL = "INSERT INTO tblReqReadDocs (TopicID,SubjectID,DueDate,GroupID,EmployeeID) " & _
                  "VALUES (" & lbx1.Column(0) & "," _
                & lbx2.Column(0, itm2) & "," _
                & "#" & tbx1 & "#" & ", " _
                & lbx3.Column(0, itm) & "," _
                & rs!EmployeeID & ");"
            db.Execute SQL, dbFailOnError
       Next
         rs.MoveNext
    Wend
    
    Next

    rs.Close
    db.Close

   End If
   
 Set db = Nothing
      
   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 again,

Brian

 
Upvote 0 Downvote
Get rid of db.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Johnny S
  • Locked
  • Question
Create folder based on ID field MS Access
Replies
3
Views
156
MattGreer
MattGreer
Moss100
  • Locked
  • Question
Transfer data from external table to identical local table - urgent please
2
Replies
27
Views
436
Andrzejek
Andrzejek
DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
1K
Norman Sherlock
Norman Sherlock
Moss100
  • Locked
  • Question
Vba adding record to table when field is integer
Replies
8
Views
273
Moss100
Moss100
Moss100
  • Locked
  • Question
Add multiple contacts to outlook - from access table
Replies
12
Views
228
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top