selectable drop down box / text box calendar

[Dachbo]

Sry I'm little confusing.

One entire task (A) is split up in more phases: start-phase, .... middle-phase, ... and end-phase. Every phase takes an individual period of time. One staff member will not work on every phase of (A), but maybe for the start- and end-phase. Between start- and end-phase of (A), he will work on a different task (B), but some staff members will work on every phase of (A).
So I would like to assign every individual staff member to his task for an individual phase. Assignment for one phase at a time would be enough. So the reassignment would have to be done when one staff member is done with his phase.

I hope I could explain it a little better.

[June7]

Need to establish how task, phase, assignment start and end dates are managed. Whether it's even important to document start and end dates.

Tasks have a start and end date?

Phases for a task have a start and end date?

Staff assignments for a phase have a start and end date?

[Dachbo]

I think it kind of should work like that:
The end user will see a form where he can see the upcoming tasks with start and end date. At the same form he will have an interface to type in the criterias to look after the staff, that is able to do a certain task. The next form comes and shows the end user all the staff, that fulfill the requirements. In this form the end user should be able to select the staff that he wants/needs and assign them to the task and the phase of the task via a calendar.

Yes, tasks have a start and end date.
Yes, phases are in order and have a certain amount of days, so start and end date could be calculated.
The staff assignment will be for one phase, so staff assignment would have the same start and end date like the phase staff is assigned to.

[June7]

Then consider:

tblStaff

tblTasks

tblPhasesTasks
ID (PK)
TaskID
StartDate
EndDate

tblStaffPhases
PhaseTaskID (FK)
StaffID (FK)

[Dachbo]

I think that'll work. But how do I accomplish an user-friendly interface for the end user, so it's easy to find, select the staff and assign them to the tasks and phases?

[Dachbo]

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

I tried this with the INSERT INTO and only with the employee ID (without assigning any dates), but nothing happens. That's what I got:

Private Sub btn_slct_Click()

Dim varItem As Variant
 
    With Me.lstbx_EMPLOYEES
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                CurrentDb.Execute "INSERT INTO tbl_slct[(EMPLOYEES_ID)] SELECT [tbl_EMPLOYEES.]EMPLOYEES_ID FROM tbl_EMPLOYEES"
            End If
        Next
    End With


End Sub

I think the "FROM tbl_EMPLOYEES" is definitely wrong

[June7]

Drop the [] characters. They aren't needed in your statement.

However, that does not follow the example I provided. Why are you pulling data from tbl_employees?

[Dachbo]

I was trying some different things but nothing puts the values in the new table.
Now I have this but nothing happends

Dim varItem As Variant
    With Me.lstbx_EMPLOYEES
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                CurrentDb.Execute "INSERT INTO tbl_slct(EMPLOYEES_ID) Values(" & varItem & ")"
            End If
        Next
    End With

Does it matter what columns are listed in the listbox? Do I have to put all the names of the columns between the tbl_slct() parenthesis which are listed in the listbox?

[June7]

Do not need all the columns.

Don't see anything wrong with code. Learn to debug. Refer to link at bottom of my post for debugging guidelines.

[Dachbo]

Now it kind of works, but...

when I select some records and want to insert them into the tbl_slct, it only inserts the numeric number of the record. When I choose the first record a 0 will be listed in the EMPLOYEES_ID column in tbl_slct, when I choose the 2nd record, a 1 will be listed and so on...

When I want to insert more than only EMPLOYEES_ID, an error message comes up and says: 3346 the number of query fields and destination fields are not the some. But in the list box are 5 different entries for every record and in the tbl_slct are the same columns.

[June7]

Would have to examine db and code to debug.

[Dachbo]

I used the Debug.Print varItem and it shows me only the numeric numbers, the same ones which will be stored in the table.

I think it's not getting the values out of the list box, but only the numeric number

Do I need to change the properties of the list box?

[June7]

The employee numeric ID is what you want. I don't understand what is happening. Would have to review db.

What do you mean by 'insert more than only EMPLOYEE_ID'? Each employee selected in the listbox should result in a record saved by the looping code. Why would the number of query fields not agree with destination fields? The SQL statement should not be changed.

[Dachbo]

With more than EMPLOYEE_ID, I mean data like first and last name...

It just stores the counting number in the new table of each entry, which has been selected in the list box.

[June7]

Should store only the employee ID, not name parts.