selectable drop down box / text box calendar

[Dachbo]

Hello everyone.

I want to create a database tool, which is supposed to help supervisors to schedule and assign their staff to certain tasks for a specific period of time.
What I’ve already got, is that it’s possible to find the “right staff” from Table A by selecting certain criteria in Form AA. The “right staff” will be stored in a new Table B and in the next Form BB, they will be shown in a List Box.

First Problem: After finding the “right staff” I want to select them and assign them to a new task. How can I select the entries from the List Box of Form BB and append them to a new Table C?

Second Problem: I want to use two Text Boxes, which have the format “General Date” to choose a Start Date and End Date. How can I append the Start and End Date to the Table C to the selected Staff?

Thank you!

[June7]

First: Selecting multiple items in a multi-select listbox and then creating records from those selections requires VBA code that loops through the selected items and writes records to table.

Second: The dates can be saved in the records with the same code procedure.

Something like:

    Dim varItem As Variant
    'Loop through the ItemsSelected in the list box.    
    With Me.lstStaff
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'save record to table
                CurrentDb.Execute "INSERT INTO tableC(StaffID, TaskID, DateStart, DateEnd) Values(" & varItem & ", " & Me.tbxTask & ", #" & Me.tbxStart & "#, #" & Me.tbxEnd & "#"
            End If
        Next
    End With

[Dachbo]

Thank you very much.

Since I'm not familiar with VBA, is there a way to tell a query to append the selected entries to the table?

[June7]

There is not. As stated, requires VBA.

In conventional data entry, form would be bound to table and user would select a task, a single individual from combo or list box, and enter dates and this will commit a record to table then move to new record row and repeat. You are trying to do something to automate and speed up the data entry process. More 'user friendly' can mean more code.

However, more user friendly can be implemented with design choices. If you don't want the VBA solution then suggest a form/subform arrangement. Main form bound to Tasks table and subform bound to StaffAssignments table. The task and dates are entered on main form then staff selected in subform. Probably almost as fast to select a staff on each new record row of subform as to do multiple clicks in a listbox.

[Dachbo]

Thanks for the detailed answer.

I like the idea with the form/subform thing. Is it possible to look for staff by criteria and let them pop up at the subform, then elect some staff out of the subform and select the task in the form and get the staff assigned to the task in a new table?

[June7]

What do you mean by 'in a new table'? Look for staff by what criteria?

Purpose of the described form/subform arrangement is to select staff for each task. This data entry process in the subform would create staff assignment records directly into the appropriate table.

[Dachbo]

The "new table" would be the table with all the staff listed but with "new entries" (the new tasks). By "Look for staff by criteria", I mean, that I only want to assign some specific staff to a specific task.
But it sounds like my needs will be fulfilled with that method. I'll try it on monday and then I'll give a report if it worked.

Thanks again :-)

[Dachbo]

- How can I transfer the dates and tasks from the main form to the subform (table)?

-How can I write protect the entries in the subform, so it's impossible to change the entries directly but only by using the form?

[June7]

With the data structure I suggested, there would be no need to have the dates and tasks in the subform. The main form would be bound to a table that has task and dates records. The subform would be bound to a table that has the staff assignments. The two tables would be associated by TaskID primary key/foreign key fields. Unless each staff member would be assigned different dates for the same task.

Don't understand the last question. What do you mean by 'impossible to change the entries directly' - directly where? The form/subform is for data entry/edit.

[Dachbo]

with "impossible to change" I mean write protect certain columns like staffID, Name, Department.....

I'm afraid, several staff members will work for the same period of time on the same task. So I think the primary key/foreign key won't work.
The more I think about it, the more I realize I probably will have to work with VBA. Or do you have another idea that comes to your mind? Or did I get anything wrong?

[June7]

Yes, textboxes can be locked against editing. Set the Locked and TabStop properties.

Why is it an issue for several staff members to work for the same period of time on same task? Consider tables:

tblStaff
StaffID (PK)
LastName
FirstName

tblTasks
TaskID (PK)
TaskDesc
TaskStart
TaskEnd

tblStaffTasks
TaskID (FK)
StaffID (FK)
Role

VBA is not helpful if the data structure is not correct.

[Dachbo]

ok then I must have got you wrong. Because you mentioned

Unless each staff member would be assigned different dates for the same task.

so I thought I can't do that.

Unfortunately the task has several phases and I would like to assign different staff members to the same task but different phases.

[June7]

What is the situation? Will staff assigned to same task all be assigned for the same period? Or do you want to assign staff different periods for the same task? The answer determines whether or not tblStaffTasks also has start and end date fields. This means the task could have start and end dates and each staff could have individual start and end dates that would have to fall within the task start and end dates.

[Dachbo]

For each phase of the same task, different staff members are working on the task.
But what I think will make it even more complex, is that I want to assign staff member A to the task AA at phase 1, phase 2 he will be working on task BB and at phase 3 he might be working for task AA again.
That's why I would like to use a calendar and a list with several tasks and a list where I can select certain staff members and assign them to an individual time period to a certain task. When one phase is over, I want to reassign them to a new task for a new period of time...

[June7]

That introduces another data entity. Tasks have phases? Or phases have tasks? Tasks and phases are components of some broader process? I am confused.