I have an Employee database, that tracks Employee information and training. I have a form set up, that captures all the Employee Information, with the Employee No being the primary key. In this form, I have a subform, that lists all the training for that Employee (rows of training can be added). This subform information is housed in a MasterMatrix, which includes the Employee No (parent/child link), and the training class, and status of the training.

What I need to do is, create a button in the form that when a new employee is added to the database, it will take the Employee No from the record, and add that and 4 training classes (which are standard classes for new employees), and add them to the MatrixMaster.

I know how to do the SQL statement by just adding taking info from 1 table to the other, but how do I get it from 2 tables? I know that I need a JOIN, but I'm not sure what type I need.

Any help would be great.

1st table - tblEmployee (need EmployeeNo from here) - This is also main form
2nd table - tblTrainingClass (need Training Class, Training Status, WHERE NewEmployeeTraining = yes)

3rd table - tblTrainingMatrixMaster (where info is going to - this is also a subform, with parent/child link to EmployeeNo): - EmployeeNo, Training Class, Training Status

Hi Sandra,

This looks to be a little tricky...

Looks like what you are looking for, is to click the button, and then have the MatrixMaster automatically populated with 4 separate records.

Since there is not really anything common between Training Class and Employee (Nor should there be!) a join won't cut it in this case.

I'm not sure how familiar you are with VBA, but let me share what my approach would be... There is likely a better solution out there, so if anyone would like to add to this, please feel free...

Public Sub cmdNewEmployeeButton_Click()
Dim rs as ADODB.recordset
Dim cn as ADODB.connection
Dim strSQL as String
Dim empID as String

set cn = Application.CurrentProject.Connection
set rs = New ADODB.recordset

empID = Me.txtEmpID 'This should reference the field that holds your employee ID Number
strSQL = "SELECT [Training Class], [Training Status] FROM tblTrainingClass WHERE [NewEmployeeTraining]='yes'"
rs.Open strSQL

If not(rs.EOF and rs.BOF) Then
    While rs.EOF=False
     strSQL = "INSERT INTO tblTrainingMatrixMaster ([EmployeeNo], [Training Class], [Training Status]) VALUES ("
     strSQL = strSQL & "'" & empID & "', "
     strSQL = strSQL & "'" & rs.fields("Training Class") & "', "
     strSQL = strSQL & "'" & rs.fields("Training Status") & "')"

     DoCmd.RunSQL strSQL

     rs.MoveNext
    Wend
Else
    MsgBox "No New Hire Classes were found"
End If

rs.close
cn.close

set rs=Nothing
set cn=Nothing
End Sub

Hope that helps - If not let me know and we can try a different approach.

Mike



Mike
EchoVue.com