Hello Everyone,
Hope you can help me with this, seeing that my experience with coding is not that great.
What I am trying to do:
I currently have a form where I can assign "tasks" to various "employees" (multi select) with the assigned date. The code kind of works, however instead of listing the each employee to it's individual record, it's in a list.
What table [tblTaskSched] shows:
[ID]................[Tasks].........[EmployeeID]............[DateAssigned]
Auto................Task1..............12, 43, 65, 88.............8/26/16
How it needs to be displayed:
[ID]................[Tasks].........[EmployeeID]............[DateAssigned]
Auto................Task1.............. 12 .............8/26/16
Auto................Task1.............. 43 .............8/26/16
Auto................Task1.............. 65 .............8/26/16
Auto................Task1.............. 88 .............8/26/16
Tables:
tblTaskSched
ID (auto)
Name (short-didn't link to tblTask.Task on tbl end but did link it in the form)
EmployeeID (short- same as above)
DateAssigned
tblTask
ID
Name (hyperlink due to it being link to a file this is being linked to tblTaskSched in the form as well)
TaskName (short version of Task without hyperlink)
TaskCode
tblEmployee
EmployeeID (short)
Name
FName
LName
Code (which is EventProcedure for a button on same form):
'Purpose: This forms allows users to assign the specific Tasks to Employees for them to view
Option Compare Database
Option Explicit
___________________________________
Private Sub cmdAssign_Click()
On Error GoTo Err_cmdAssign_Click
Dim MyDB As dao.Database
Dim varItem As Variant
Dim lst As ListBox
Set lst = Me![EmployeeID]
Dim rst As dao.Recordset
If lst.ItemsSelected.Count = 0 Or IsNull(Me![cboTaskName]) Or IsNull(Me![txtDateAssigned]) Then
MsgBox "Please verify that there is a Task Name and that you've assigned Employee(s) in order to proceed.", vbExclamation, _
"Missing Data Input"
Exit Sub
End If
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblTaskSched", dbOpenDynaset, dbAppendOnly)
With rst
For Each varItem In lst.ItemsSelected
.AddNew
![Name] = Me![cboTaskName]
![EmployeeID] = lst.ItemData(varItem)
![DateAssigned] = Me![txtDateAssigned]
.Update
Next varItem
End With
rst.Close
Set rst = Nothing
DoCmd.OpenTable "tblTaskSched", acViewNormal, acReadOnly
DoCmd.Maximize
Exit_cmdAssign_Click:
Exit Sub
Err_cmdAssign_Click:
MsgBox Err.Description, vbExclamation, "Error in cmdAssign_Click()"
Resume Exit_cmdAssign_Click
End Sub
error I'm receiving when I try to click on the "Assign" button in Form View:
Method 'Collect' of object 'RecordSet2' failed.
other note:
Not sure if this is affecting it but in the form when I multi select the "Employee" to assign, the listbox is showing the check mark on the side.
should it be a listbox that has the ctrl+select option?
Or doesn't matter? If it does, what should I do?
In addition when I go back to the form to Assign another "task", it uses the same record and changes the values instead of adding a new one....
Hope you can help me with this, seeing that my experience with coding is not that great.
What I am trying to do:
I currently have a form where I can assign "tasks" to various "employees" (multi select) with the assigned date. The code kind of works, however instead of listing the each employee to it's individual record, it's in a list.
What table [tblTaskSched] shows:
[ID]................[Tasks].........[EmployeeID]............[DateAssigned]
Auto................Task1..............12, 43, 65, 88.............8/26/16
How it needs to be displayed:
[ID]................[Tasks].........[EmployeeID]............[DateAssigned]
Auto................Task1.............. 12 .............8/26/16
Auto................Task1.............. 43 .............8/26/16
Auto................Task1.............. 65 .............8/26/16
Auto................Task1.............. 88 .............8/26/16
Tables:
tblTaskSched
ID (auto)
Name (short-didn't link to tblTask.Task on tbl end but did link it in the form)
EmployeeID (short- same as above)
DateAssigned
tblTask
ID
Name (hyperlink due to it being link to a file this is being linked to tblTaskSched in the form as well)
TaskName (short version of Task without hyperlink)
TaskCode
tblEmployee
EmployeeID (short)
Name
FName
LName
Code (which is EventProcedure for a button on same form):
'Purpose: This forms allows users to assign the specific Tasks to Employees for them to view
Option Compare Database
Option Explicit
___________________________________
Private Sub cmdAssign_Click()
On Error GoTo Err_cmdAssign_Click
Dim MyDB As dao.Database
Dim varItem As Variant
Dim lst As ListBox
Set lst = Me![EmployeeID]
Dim rst As dao.Recordset
If lst.ItemsSelected.Count = 0 Or IsNull(Me![cboTaskName]) Or IsNull(Me![txtDateAssigned]) Then
MsgBox "Please verify that there is a Task Name and that you've assigned Employee(s) in order to proceed.", vbExclamation, _
"Missing Data Input"
Exit Sub
End If
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblTaskSched", dbOpenDynaset, dbAppendOnly)
With rst
For Each varItem In lst.ItemsSelected
.AddNew
![Name] = Me![cboTaskName]
![EmployeeID] = lst.ItemData(varItem)
![DateAssigned] = Me![txtDateAssigned]
.Update
Next varItem
End With
rst.Close
Set rst = Nothing
DoCmd.OpenTable "tblTaskSched", acViewNormal, acReadOnly
DoCmd.Maximize
Exit_cmdAssign_Click:
Exit Sub
Err_cmdAssign_Click:
MsgBox Err.Description, vbExclamation, "Error in cmdAssign_Click()"
Resume Exit_cmdAssign_Click
End Sub
error I'm receiving when I try to click on the "Assign" button in Form View:
Method 'Collect' of object 'RecordSet2' failed.
other note:
Not sure if this is affecting it but in the form when I multi select the "Employee" to assign, the listbox is showing the check mark on the side.
should it be a listbox that has the ctrl+select option?
Or doesn't matter? If it does, what should I do?
In addition when I go back to the form to Assign another "task", it uses the same record and changes the values instead of adding a new one....
Last edited:
