Multiple records in one time

[JessicaVdb]

Hi

Is it possible to fill in one team members name and then at multiple trainings. This in different records.

Example:

Team member: Nick
Trainings: Beginning, Advanced, Expert

The trainings need to be different records. But it could be helpfull if the name (and other things) can be tiped one.

Thanks

JessicaVdb

 

[Minty]

You could do this with a list box of all available training courses, and another one with employee names.

Select the employees you want, and the training courses, then make some code to loop round the selections and add the appropriate records. This would works for either adding many employees to a new course, or adding existing courses for a new employee.

You could get really clever and make it check if they are already assigned that course and not duplicate the record.

 

[JessicaVdb]

You could do this with a list box of all available training courses, and another one with employee names.

Select the employees you want, and the training courses, then make some code to loop round the selections and add the appropriate records. This would works for either adding many employees to a new course, or adding existing courses for a new employee.

You could get really clever and make it check if they are already assigned that course and not duplicate the record.

And what code should I use then?

 

[Minty]

Well I'm afraid I've not got time to write a design a whole form and then work the code out for you. However this page is a good start for you to learn ; http://www.fontstuff.com/access/acctut11.htm There are other examples about on here as well.

Do it in small steps;

• Get your listbox of employees.
• make a simple code to list the selected employees to messagebox.
• Do the same for your courses

Once that is all working, you have the building blocks to then take that information and create the code to go through each employee for each course and add their training records.

There is no short cut to this unfortunately. If you want nice functionality in forms you have to put the effort in.

 

[JessicaVdb]

I was thinking to do it with a combo box. In the combo box Station you can chose witch Station and then you have to ad the Level and Revision. But if you want multiple Stations I tought I just could at a second, thirth, ... combo box to make a new selection.

The upper selection subject, Clocknumber, ... Should appear with every station that is been selected in het table.

Is this possible?

 

[Minty]

You could do it that way but the code to update them would be very messy, and your form would't be flexible.
You would have a fixed number of possible updates, based on your form. It wouldn't be considered a good manageable design.

By using a list box you can have 1,2 3, 40 people / courses listed and easily apply your changes to however many of each you select.
When you have a lot of choices I actually "Move" the sections into another list box to confirm who I've selected;

 

[JessicaVdb]

You could do it that way but the code to update them would be very messy, and your form would't be flexible.
You would have a fixed number of possible updates, based on your form. It wouldn't be considered a good manageable design.

By using a list box you can have 1,2 3, 40 people / courses listed and easily apply your changes to however many of each you select.
When you have a lot of choices I actually "Move" the sections into another list box to confirm who I've selected;

What code is behind the button 'Add training records'?

 

[isladogs]

Hey Minty

Is that modern art in your last image?

 

[JessicaVdb]

Hey Minty

Is that modern art in your last image?

Never mind.

 

[isladogs]

Hi Jessica

British humour...

 

[JessicaVdb]

Hi Jessica

British humour...

I already got the humour

 

[JessicaVdb]

Thanks a lot Minty but I solved the problem myself with the use of a combo box.

 

[Minty]

Hey Minty

Is that modern art in your last image?

Yup - Commonly known as my "Spray Can" phase.

Shortly followed by the "Not quite Banksy" phase...

 

[Minty]

What code is behind the button 'Add training records'?

I've not got it to hand, but will post up for interest a bit later. From memory it's just a SQL insert query.

 

[Minty]

As requested code for adding the list objects; Obviously there are a lot of application specific fields but you should get the idea. I also wrote this ages ago so it probably isn't the most efficient or elegant way of doing it.

Private Sub cmdAddTraining_Click()

[COLOR="Olive"][COLOR="Green"]'This will add a training record for each person in the right-hand list on screen.
[/COLOR][/COLOR]
    Dim nResumeCount     As Integer
    Dim iEmp             As Integer
    Dim dDateTaken       As Date
    Dim sExpires         As Variant
    Dim iRev             As Integer
    Dim iCourId          As Integer
    Dim iHeadId          As Integer
    Dim iSupp            As Integer
    Dim iSkill           As Integer
    Dim ctlList          As Control
    Dim varItem          As Variant
    Dim iRow             As Integer
    Dim iTrain           As Integer
    Dim iUpdCnt          As Integer
    Dim sSql             As String
    Dim dEntered         As Date
    Dim iResp            As Integer
    Dim db               As DAO.Database

    If IsNull(Me.cmbSupplier) Then
        MsgBox "Please select a supplier", vbExclamation, "No Supplier error!"
        Exit Sub
    End If

    iResp = MsgBox("You are about to add training records for all the employees in the green list." & vbCrLf & "Are you certain all the information is correct?", vbOKCancel, "Course Update!")
    If iResp = vbCancel Then Exit Sub

    Set db = CurrentDb

[COLOR="green"]    'Set up values for inserted records[/COLOR]
    dDateTaken = Me.txtDateTaken
    iRev = Me.txtRevision
    iCourId = Me.cmbSubCorID
    iHeadId = Me.cmbCHeadID
    iSupp = Nz(Me.cmbSupplier, 0)
    dEntered = Date
    iSkill = Me.txtSkillLevel
    iTrain = Nz(Me.cmbTrainer, 0)
    iUpdCnt = lstEmpSelect.ListCount

    If Not IsNull(Me.txtExpires) Then
        sExpires = "#" & Format(Me.txtExpires, "mm/dd/yyyy") & "#"
    Else
        sExpires = "Null"
    End If

[COLOR="green"]    'Loop through selected listbox items[/COLOR]

    For iRow = 0 To lstEmpSelect.ListCount - 1

        iEmp = lstEmpSelect.Column(0, iRow)

        sSql = "INSERT INTO [TRA_Emp_Record] (EmpID, SubCourse_ID, Date_Entered, Date_Taken, "
        sSql = sSql & "[Expiry_Date], Passed, SkillLevel, InternalTrainer, CourseHeadID, SupplierID, Revision, SignOff) "
        sSql = sSql & "VALUES (" & iEmp & ", " & iCourId & ", #" & Format(dEntered, "mm/dd/yyyy") & "#, #" & Format(dDateTaken, "mm/dd/yyyy") & "#, "
        sSql = sSql & "" & sExpires & ", 1, " & iSkill & ", " & iTrain & ", " & iHeadId & ", " & iSupp & ", " & iRev & ", 0); "

        db.Execute sSql, dbSeeChanges

    Next iRow

    Set db = Nothing

    MsgBox "You have added " & iUpdCnt & " training records to the system.", vbInformation, "Update sucessful"
    
[COLOR="green"]    ' After completion call the routine to remove all the selected entries and reset the main list[/COLOR]
    Call cmdRemAll_Click

    Exit Sub