Report Whats NOT in a table? Mandatory Courses that Employees haven't taken.

[TrevorThielen]

On to the next wonderful adventure in Access 2010. Making great progress with Access and VBA in a Safety/Training Database, but now it gets fun.

I have a table for Employees, a table for TrainingCourses (including a check box for "Required" courses) and a TrainingTable that holds all the info for courses the employees HAVE taken.
How would I go about building a Report for Required Courses that Employees HAVEN'T taken?

Any help would be greatly appreciated. I was thinking of building a report of TrainingCourses that Employees HAVE taken and then reversing the conditions? Maybe looping through recordsets, then if no records in Training match, add that name to a list?

Hmmm...

[ssanfu]

There is a "Find unmatched Query Wizard". Did you try that?

[TrevorThielen]

ssanfu - I tried the Wizard to see if it would help, but because I was using 3 tables, I don't think it would work. I did however do some more Googling and between this site and others I managed to loop through some recordsets and do what I wanted. I did change my approach a bit however, because I already have a Report that shows all the Training that is or soon to be expired, I decided that if someone doesn't have the required training course, I would add the course to their name with an expiry date of Date(). That way as the courses are fulfilled, they will come off that list automatically and it plays in to some other user friendly stuff I dont want to duplicate.

For those curious:

Code:
Private Sub Command40_Click()
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim db As Database
Dim recCheck As Variant
Dim IDCheck As Integer
Dim CodeCheck As Integer
Dim ExpCheck As Date
Const chkDateFormat = "\#mm\/dd\/yyyy\#"
Dim i As Integer
Dim ii As Integer
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Training")
Set rs2 = db.OpenRecordset("TrainingCodes")
Set rs3 = db.OpenRecordset("Workers")
rs2.MoveFirst
Do Until rs2.EOF = True
If rs2!Required = True Then
CodeCheck = rs2!Code
Debug.Print "Code" & CodeCheck
rs3.MoveFirst
Do Until rs3.EOF = True
IDCheck = rs3!ID
recCheck = Nz(DLookup("ID", "Training", "WorkerID = " & IDCheck & "" & "And Code = " & CodeCheck & ""), 0)
If recCheck = 0 Then
Debug.Print "ID" & IDCheck
Debug.Print recCheck
End If
If recCheck = 0 Then
rs1.AddNew
rs1("WorkerID") = IDCheck
rs1("Code") = CodeCheck
rs1("tHours") = rs2!Hours
rs1("ProvidedBy") = rs2!ProvidedBy
rs1("Location") = rs2!Area
rs1("ExpiryDate") = Date
rs1.Update
End If
rs3.MoveNext
Loop
rs2.MoveNext
Else
rs2.MoveNext
End If
Loop
rs1.Close
rs2.Close
rs3.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
db.Close
End Sub

[ssanfu]

"Required" is a reserved word in Access. Reserved words shouldn't be used as object names.

I modified your code to clean up some things.

Private Sub Command40_Click()
   '   Const chkDateFormat = "\#mm\/dd\/yyyy\#"

   Dim db As DAO.Database
   Dim rs1 As DAO.Recordset
   Dim rs2 As DAO.Recordset
   Dim rs3 As DAO.Recordset
   Dim recCheck As Variant
   Dim IDCheck As Integer
   Dim CodeCheck As Integer
   '   Dim ExpCheck As Date
   '   Dim i As Integer
   '   Dim ii As Integer

   Set db = CurrentDb

   Set rs1 = db.OpenRecordset("Training")
   Set rs2 = db.OpenRecordset("TrainingCodes")
   Set rs3 = db.OpenRecordset("Workers")

   rs2.MoveFirst
   Do Until rs2.EOF
      If rs2![Required] Then
         CodeCheck = rs2!Code
         '         Debug.Print "Code" & CodeCheck
         rs3.MoveFirst
         Do Until rs3.EOF
            IDCheck = rs3!ID
            recCheck = Nz(DLookup("ID", "Training", "WorkerID = " & IDCheck & " And Code = " & CodeCheck), 0)
            If recCheck = 0 Then
               '               Debug.Print "ID" & IDCheck
               '               Debug.Print recCheck
               With rs1
                  .AddNew
                  !WorkerID = IDCheck
                  !Code = CodeCheck
                  !tHours = rs2!Hours
                  !ProvidedBy = rs2!ProvidedBy
                  !Location = rs2!Area
                  !ExpiryDate = Date
                  .Update
               End With

            End If
            rs3.MoveNext
         Loop
      End If
      rs2.MoveNext
   Loop

   rs1.Close
   rs2.Close
   rs3.Close
   Set rs1 = Nothing
   Set rs2 = Nothing
   Set rs3 = Nothing
   '   db.Close         'rule is:  if you open it, close it. You didn't open dB
   Set db = Nothing

End Sub

Then I modified it a lot..... sorry, I couldn't help myself

Private Sub Command40_Click()

   Dim db As DAO.Database
   Dim rs1 As DAO.Recordset
   Dim rs2 As DAO.Recordset
   Dim rs3 As DAO.Recordset

   Dim sSQL As String
   Dim recCheck As Variant
   Dim IDCheck As Integer
   Dim CodeCheck As Integer

   Set db = CurrentDb

   'select only the required training. 
   sSQL = "SELECT [Required], Code, Hours, Area, ProvidedBy"
   sSQL = sSQL & " FROM TrainingCodes"
   sSQL = sSQL & " WHERE [Required] = TRUE"
   '            Debug.Print sSQL
   Set rs2 = db.OpenRecordset(sSQL)
   Set rs3 = db.OpenRecordset("Workers")
   rs2.MoveFirst

   'loop through all of the required Training Codes
   Do Until rs2.EOF
      CodeCheck = rs2!Code
      '      Debug.Print "Code" & CodeCheck
      rs3.MoveFirst

      ' find missing required training
      Do Until rs3.EOF
         IDCheck = rs3!ID

         sSQL = "SELECT Workers.WorkerID, Training.ID, Training.Code"
         sSQL = sSQL & " FROM Training INNER JOIN Workers ON Training.WorkerID = Workers.WorkerID"
         sSQL = sSQL & " WHERE Workers.WorkerID = " & IDCheck & " AND Training.Code =" & CodeCheck & ";"
         '            Debug.Print sSQL
         Set rs1 = db.OpenRecordset(sSQL)

         'check for records found
         If rs1.BOF And rs1.EOF Then   'no records - insert a new training record
            Debug.Print "ID" & IDCheck
            Debug.Print recCheck
            sSQL = "INSERT INTO Training (WorkerID, Code, tHours, ProvidedBy, Location, ExpiryDate) "
            sSQL = sSQL & " VALUES (" & IDCheck & ", " & CodeCheck & ", " & rs2!Hours & ", " & rs2!ProvidedBy & ", " & ", #" & Date & "#);"
            '            Debug.Print sSQL

            ' bypasses Access/ uses Jet to insert the record
            db.Execute sSQL, dbFailOnError
         End If
         rs1.Close
         rs3.MoveNext
      Loop
      rs2.MoveNext
   Loop

   On Error Resume Next
   rs1.Close
   rs2.Close
   rs3.Close
   Set rs1 = Nothing
   Set rs2 = Nothing
   Set rs3 = Nothing

   '   db.Close        'rule is:  if you open it, close it. You didn't open dB
   Set db = Nothing
End Sub

I don't know if "ProvidedBy" & "Location" are text fields or not. If they are text fields, delimiters will need to be added (In my version of the code, the blue text).

My code is untested, but I don't think there are any errors.......

[TrevorThielen]

lol... Thanks eh! (From Canada if it's not shown anywhere) I like your clean up because I can understand everything you did. I'm self taught from the likes of Google and forums like this one so I'm sure nothing I do is exaclty perfect or like anyone else out there. I have played a bit with sql in some small areas, but the syntax nearly drives me nuts come compile time.
Anyways, your clean up works perfectly, but now I need to go the other way. If a course is no longer "Required" then we should be able to delete all the records where the Course hasn't been taken yet. IsNull(CourseDate) kind of thing. I set this up :
rs2.MoveFirst
Do Until rs2.EOF = True
If rs2![Required] = False Then
CodeCheck = rs2!Code
Debug.Print "Code" & CodeCheck
rs3.MoveFirst
Do Until rs3.EOF = True
IDCheck = rs3!ID
recCheck = Nz(DLookup("ID", "Training", "WorkerID = " & IDCheck & "And Code = " & CodeCheck & "And IsNull(CourseDate)"), 0)
If recCheck <> 0 Then
Debug.Print "ID" & IDCheck
Debug.Print recCheck
With rs1
.Delete
End With

End If
rs3.MoveNext
Loop
End If
rs2.MoveNext
Loop

But get an error after it deletes the first record it finds, "No current record!"

Any ideas?

[TrevorThielen]

OK so I added a .Requery after the .Delete and it seems to work.

Is this the proper way of doing it?

[ssanfu]

Shouldn't need the Requery...hmm

----------
About posting code/quotes......

If you want your code to be in boxes (like my posts), there are codes - kind of like HTML.
If you want to quote something, at the beginning you would type an opening bracket ([), the word "quote" (without the quote marks), then a closing bracket(]). To stop quoting, you would type an opening bracket ([), a forward slash (/),the word "quote" (without the quote marks), then a closing bracket(]). I have to add spaces to show an example:
[ quote ] this is quoted [ / quote ]

For code, replace "quote" with "code"
[ code ] this is quoted [ / code ]

Remember, the spaces are so you can see what the tag looks like. If you are in Advanced mode, you can use the hash (#) in the menu to add the tags.
----------

About my coding style.......

You use:

Do Until rs2.EOF = True
If rs2!Required = True Then

For the same two lines, I used

Do Until rs2.EOF 
If rs2!Required Then

Your code is not "wrong", its just that "EOF" is a boolean; it can be either TRUE or FALSE. So "Do Until rs2.EOF" essentially means stop when the value of EOF becomes TRUE.

Same with "rs2!Required". "Required" is (appears) to be a Boolean field (TRUE or FALSE).
If the value of "rs2!Required" is TRUE, replacing "rs2!Required" with the value, the line becomes
"If TRUE Then"

If the value of "rs2!Required" is FALSE, the line evaluates to
"If FALSE Then"

Also, it is less typing and less chance for me to screw up...
----------

And now back to your question.

Deleting non-required records is easier.
Think about it. If you are adding required training, you have to check each person to see if they have the training.

But removing non-required training is not dependent on the person. Just find all records with the non-required training code and delete those records.

So here is the code I came up with:

Public Sub DeleteNonRequiredTraining()

   Dim db As DAO.Database
   Dim rs2 As DAO.Recordset

   Dim sSQL As String
   Dim CodeCheck As Integer

   Set db = CurrentDb

   'select only the NON required training.
   sSQL = "SELECT [Required], Code, Hours, Area, ProvidedBy"
   sSQL = sSQL & " FROM TrainingCodes"
   sSQL = sSQL & " WHERE [Required] = FALSE"
   '            Debug.Print sSQL
   Set rs2 = db.OpenRecordset(sSQL)

   If rs2.BOF And rs2.EOF Then
      ' no records
      MsgBox "No non required records fornd!"
   Else
      'record found
      rs2.MoveLast
      rs2.MoveFirst

      Do Until rs2.EOF
         CodeCheck = rs2!Code
         sSQL = "DELETE * FROM Training WHERE Training.Code =" & CodeCheck & ";"
         '            Debug.Print sSQL
         ' bypasses Access/ uses Jet to insert the record
         db.Execute sSQL, dbFailOnError
         rs2.MoveNext
      Loop
    End If


   On Error Resume Next

   rs2.Close

   Set rs2 = Nothing

   '   db.Close        'rule is:  if you open it, close it. You didn't open dB
   Set db = Nothing
End Sub

[TrevorThielen]

Thanks for all your help! I did get everything working the way I want it. I did search for a way to "Quote" my code but couldn't find it for some reason, so thanks for the info!

[ssanfu]

Excellent!
Ready to mark this solved?

[TrevorThielen]

You bet!

Thanks again!