How to link a field from a different table in a form in MS Access

Question

Bear with me; I have very little MS Access experience, by plenty of SQL experience.

This is the basic data structure:

I have an Employee table, a Training table, and a joining Employee_Training table (many-to-many relationship). There is a list of employees, and a list of possible training that those employees can do. Any employee may have attended several training sessions, where each entry has a recorded attendance date.

I am trying to make a form that performs in the following way:

• You select an employee from a combobox at the top of the page
• The form contains a list of all possible training sessions, each with a label and a textbox. If the employee has attended a training session, the corresponding textbox has the attendance date in it, otherwise the text box is blank.
• I want to be able to enter a date in a blank textbox, or change the date in a non-blank textbox.
• I can't use a DataSheet view because there are about 150 training types, and this needs to be converted into a printable form, so the information is compressed in columns on the form, like below:

Rough Layout:

Select Employee: [ John Smith | v ]
Training type 1: [          ]  Training type 25: [          ] 
Training type 2: [          ]  Training type 26: [ 05/06/15 ] 
Training type 3: [          ]  Training type 27: [          ] 
...                            ...

The form was originally created by hand, but now is created by a VBA script, though I don't think that will be too relevant here.

How do I structure the form's record source and the control source of the text boxes to allow me to see what I want to see, and edit what I need to edit?

Answer 1

There are a ton of possible solutions for this question. I'm gonna try to stay close to your rough layout. I imagine a database with 3 tables like this :
* table: Employee (EmpID, EmpName)
* table: Training (TraID, TraName)
* table: Employee_Training (EmpTraID, ET_EmpID, ET_TraID, ET_Date)

Create a continuous form based on a query like:

SELECT Training.TraID, Training.TraName, Employee_Training.EmpTraID, Employee_Training.ET_EmpID, Employee_Training.ET_Date FROM Training LEFT JOIN Employee_Training ON Training.TraID = Employee_Training.ET_TraID;

In the header-section of the main form, place a combobox called "ChosenEmpoyee". As recordsource for this combobox "SELECT EmpID, EmpName FROM Employee", so 2 columns, and the ColumnWidths set to "0;5" to hide the first column. The column with EmpID is needed to filter the records.

When selecting an employee from the combobox, the afterupdate event will change the recordsource of the form to a query like this example:

SELECT Training.TraID, Training.TraName, A.EmpTraID, A.ET_EmpID, A.ET_Date FROM ( SELECT Employee_Training.EmpTraID, Employee_Training.ET_EmpID, Employee_Training.ET_TraID, Employee_Training.ET_Date
FROM Employee_Training WHERE Employee_Training.ET_EmpID = 3 ) As A
RIGHT JOIN Training ON Training.TraID = A.ET_TraID

The code to perform this :

Private Sub ChosenEmployee_AfterUpdate()
Dim SQL As String

    SQL = "" & _
" SELECT Training.TraID, Training.TraName, A.EmpTraID, A.ET_EmpID, A.ET_Date " & _
" FROM (SELECT EmpTraID, ET_EmpID, ET_TraID, ET_Date " & _
"       FROM Employee_Training WHERE ET_EmpID = " & ChosenEmployee & _
"       ) As A " & _
" RIGHT JOIN Training ON Training.TraID = A.ET_TraID"

    Me.RecordSource = SQL
    Me.Requery
End Sub

---

A second part of the question; having a contineous form with 5 columns, presenting the information in 5 textboxes with the same name: TraID / TraName / EmpTraID / ET_EmpID / ET_Date

The employee is chosen by selecting in the combobox in the header. The training will be chosen by double clicking on the textbox in the desired row. If there was already a date, the EmpTraID will have a value greater then 0.

The code to perform this :

Private Sub AT_Date_DblClick(Cancel As Integer)
Dim S As String
Dim D As Date
Dim SQL As String
Dim EmpTraID As Long

    S = "Please give the date of attendance."
    S = InputBox(S)
    If Not IsDate(S) Then
        S = "I'm sorry, the entered value > is not recognised as a date."
        MsgBox S, vbExclamation
        'Cancel = True
    Else
        D = CDate(S)
        S = "Are you sure to enter the following date of attendance ?" & vbCrLf & Format(D, "dddd, dd mmmm yyyy")
        If MsgBox(S, vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
            '
            ' the following 2 lines will result into troubles ...
            ' Ms Access is not able to alter values
            ' in a recordset based on a query with a JOIN-statement
            '
            'Me.AT_Date = CDate(S)
            'Me.Refresh
            '
            ' if there was already a date then
            '   change the date by using AttTraiID
            ' else
            '   insert a record in AttTrai_T
            ' end if
            '
            EmpTraID = Nz(Me.EmpTraID.Value, 0)
            If EmpTraID <> 0 Then
                SQL = ""
                SQL = SQL & "UPDATE Employee_Training "
                SQL = SQL & " SET ET_Date = "
                SQL = SQL & " #" & Format(D, "mm/dd/yyyy") & "# " ' US-date-format needed when using '#' in a query in MsAccess
                SQL = SQL & " WHERE EmpTraID = " & EmpTraID
            Else
                SQL = ""
                SQL = SQL & "INSERT INTO Employee_Training "
                SQL = SQL & " (ET_EmpID, AT_TraID, ET_Date) "
                SQL = SQL & " VALUES "
                SQL = SQL & " (" & ChosenEmployee
                SQL = SQL & " ," & Nz(Me.TraID.Value, 0)
                SQL = SQL & " , #" & Format(D, "mm/dd/yyyy") & "# " ' US-date-format needed when using '#' in a query in MsAccess
                SQL = SQL & " )"
            End If

            CurrentDb.Execute SQL
            DoEvents: DoEvents: DoEvents
            Me.Requery

        End If
    End If
End Sub

Answer 2

First, for printing out data, you will create a report. So drop the idea that because of repeating data you have some problem or issue here. You can format your report like an invoice or whatever. The idea that you have “many” choices effects this choice is simply a non-issue. You form/report likely not to include all 150 choices, and makes it hard to read. And if you really must, then you can include all 150 choices along with the “only” ones chosen showing a date.

Next up: Attempting to display 150 choices on the form is going to be REALLY hard – you run out of room fast on the form. And even your above mock-up shows a datasheet like repeating data display – if it goes on for 150 rows, you not likely to fit all that on a screen without using some kind of repeating data display anyway.

Think of ANY existing software you used – VERY rare to see 150 choices all at once – you have to “choose” from a list. Think of an invoice form in an accounting package – you can display all the choices – you have to scroll or “search” a bit to narrow down choices.

The most easy would be to have the main form based on the employee (so name, phone and all that jazz). The sub form would then display a combo box to select the training/course, and then a date box to enter the date. (The date could even automatic be defaulted to today, or the course start date or whatever).

The result is you point + shoot, point + shoot – much like a click, click click type of process.

Here is an example of an invoice form in Access. To make this animation I did not touch the keyboard – it was just mouse click, mouse click etc. However the combo box DOES allow partial matching and search to narrow down the list presented (again with your long list you don’t want to torture users!). So you can type in a “few” characters into the combo box to narrow down the list of choices.

I mean, either way you going to force the user to pick from 150 choices. However, having to “scroll” through 150 choices ALWAYS to JUST view the courses the person has taken is going to be torture to the user.

So display JUST the courses selected – that way when you bring up a student, you can EASY see what courses they have, and as this follow animation shows, it would be a snap to add additional courses.

As noted, the droop down list could “remove” existing choices, or display the date if any course has already been selected. Simple logic to prevent duplicates would be added to the combo box before update event.

Answer 3

you have to drop out the databinded forms. if you use Recordset objects, you yan use a query binded to comboboxes and some small vba code to save your data to tables. I think the printing in access more flexible if u use the reports instead of the forms :)