Help with multivalue

[Paowtrick]

So I have this problem, probably have to do with knowledge. I a access beginner with restricted VB knowledge. Anyway...

About DB:
I have this DB that will contain maintenance work of machines and service work of machines. This DB will be used by everyone in the company (20 ppl).
Mantenance part is rolling tasks, so if I (and/or more ppl) complete one task it will put a record in an archive and update the date on that task with a later date.


The service part is where someone (one person) is adding a service and then one or many can complete that service.

So my problem here is that at the moment I can only add one person to a completed task, no problem, bound to the PersonelID. but sometimes someone else is helping out and need to get "stats" for that.
And I've read on so many forums that I should never use multivalue fields. Is this correct, will I dig my own grave?

And im not 100% on how to use a many-many junktion correctly. Ive used it on the machine part, but the way I've built it, its kinda awkward to use.

I've also posted this on reddit.

[orange]

Do not use multivalue fields.
If a task can be completed by more than 1 person, then a table that allows the TaskId, PersonId and DateCompleted values would seem appropriate.

For example:
tblTask
taskId Primary key
taskName

tblPerson
personId primary key
personFName
personLName
...
tblCompletedtask
personId PK FK to tblPerson
taskId PK FK to tblTask
CompletionDate PK
Comment

where tblCompletedTask has a multi field/composite Primary key

Task: PaintAutomobile

TaskId 1000

People Involved

PersonId 87 John Smith
PersonId 21 Jeremiah Jones
PersonID 916 Otto Baudy

tblCompletedTask
taskId            PersonId          CompletedDate    Comment
1000              21                    1-Nov-2018        Lead painter
1000              87                    1-Nov-2018        Observer/apprentice
1000              916                  1-Nov-2018         Prep and Masking

[Paowtrick]

ahhhh, that clear some things up. Very good explanation from you. I haven't even thought about making multiple records like that to add to one record. Might help me more in the future.

So basically I need to add task three times if three ppl is completing it to the completed task table.
though, now I need to rethink how I add new posts to the completed task.

This is the code I ues to add the task to completed

Private Sub knpSpara_Click()

    On Error GoTo Spara_Err
    Dim intAnswer As Integer
    Dim strSQL As String


    intAnswer = MsgBox("Underhållet utfördes av " & Me.Signatur.Column(1) & " och tog " & Me.Tid.Column(1) & "." _
        , vbQuestion + vbYesNo, "Uppdarera underhåll?")


        If intAnswer = vbYes Then
           strSQL = "INSERT INTO tblUnderhållArkiv([UnderhållsID], [Datum avklarat], [Tid vid åtgärd], [Signatur]) VALUES ('" & Me.UnderhållsID & "','" & Date & "','" & Me.Tid & "','" & Me.Signatur & "');"
           DoCmd.SetWarnings False
           DoCmd.RunSQL strSQL
           [Datum Planerat] = [Datum Planerat] + [Serviceintervall]
           DoCmd.SetWarnings True
           MsgBox "Underhåll utfört!" & vbCrLf & "Nästa underhåll åtkommer " & [Datum Planerat] & "." _
           , vbInformation, "Bra jobbat " & Me.Signatur.Column(1) & "!"
       Response = acDataErrAdded
  Else
        MsgBox "Underhållet är ej uppdaterat." _
            , vbInformation, "Ångrade."
        Response = acDataErrContinue
    End If
Spara_Exit:
    Exit Sub
Spara_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Spara_Exit
If Me.Dirty = True Then Dirty = False
Me.Lista.Requery
End Sub

If its possible someone could help me with how to sort this out, I'm guessing its requiring to make a loop or something. And that is out of my range of knowledge. Or should I take another approach?

[orange]

If you had a form with a combobox and a listbox and a button.

Combobox to select the Task that has been Completed.

Listbox (a multiselect listbox) to select the People who were involved in the task.

See this post and link from PBaldy re Multiselect listbox processing selected items.

Button that when clicked, read the combobox value, and the selected people in the multiselect listbox,
then invoked code (vba) to add records to the tblCompletedTask

[Paowtrick]

Nice, thanks alot! I will learn the code and try and fit it to my stuff, hopefully I can look in to it tomorrow. Really nice pointers to put me in the right direction. And this is the form I use to select task and complete it. (work in progress though)

[orange]

If you have to select multiple items(eg People) and add a record for each selected item, then the multiselect listbox material from PBaldy should be helpful (model your looping code on his example).

If you only have to select 1 item from a list(eg Task), then the preferred control would be a combobox. If you have to select 1 item from a number of separate lists, then use a combo for each category/list( eg, CompletionDate, HoursWorked....).

Good luck.