Replies

I stepped through and it works that way. But, there is always one of those. Anyway. When I tab out of txtClassification the number doesn’t appear until after I tab through Learner ID(Employee #). Even though the After Update function is set to txtClassification. Thanks for the help. Do you think I should still send a stripped down version?

Fay

Sorry just got out of teaching a class.

Sorry I was frustrated and didn’t post properly. When I tab out of txtClassification the LearnerID (Employee #) isn’t generated. No error messages I fixed the other issue. Thank you. Fay

Okay. Thanks. I had started to do the form/subform route. I will go back to it. Fay

Thanks Hans.

Yes in both cases.

Thanks Hans that did what was needed for the first question. I will turn my attention to the second question. I appreciate your help. Fay

Thanks Hans. I will review your answer on question two. I have attached a stripped down version of the material. The qryRequiredResults is the query that needs to show the class without a date.

Yes, I actually looked at the properties dialog box instead of counting the pages. I just checked the buttons do show when I touch somewhere on the Attendance tab. But why do they show when I click on the tab itself for future? I think I am okay now. Just baffled with the future tab. Thanks Fay

The field is called DateOfClassStart in the first second of code. It is officially called Date in the table that the second part of the code comes from. I put this in the second piece of code “tblCertificationDates.Date AS DateOfClassStart” seen in the previous message. It works when I run it as a query using the grid.

Thanks Mark the first part of the code works correctly. I put Null in place of the “” in pot parts of the code. Now I am getting a 3067 error message “Query input must contain at least one table or query” with the second part of the the code. I have both a location, a table, and a from location in the code.

strSQL = “INSERT INTO tblACLSClassPrerequisites ( LearnerID, LastName, NickName, ClassName, DateOfClassStart, ACLS, CPR )” & _
“SELECT tblCertificationDates.LearnerID, tblLearners.LastName, tblLearners.NickName, tblCertificationDates.Type AS ClassName,” & _
“tblCertificationDates.Date AS DateOfClassStart, IIf(([ClassName] Like ‘ACLS*’),[Date],Null) AS ACLS, ” & _
“IIf(([ClassName] Like ‘BLS*’),[Date],Null) AS CPR” & _
“FROM tblCertificationDates INNER JOIN tblLearners ON tblCertificationDates.LearnerID = tblLearners.LearnerID” & _
“WHERE (((tblClasses.ClassName) Like ‘ACLS*’) AND (Not (tblSession.DateOfClassStart)>Now() And (tblSession.DateOfClassStart)>=DateAdd(‘yyyy’,-2,Date()))) ” & _
“OR (((tblClasses.ClassName) Like ‘BLS*’) AND (Not (tblSession.DateOfClassStart)>Now() And (tblSession.DateOfClassStart)>=DateAdd(‘yyyy’,-2,Date())))”

Thanks for the help.Fay

I converted the query into SQL, but there is an error in the following code that I can’t find. When I run the code the first section creates the table, but…. It creates the CPR field but not the ACLS field. The ACLS correct date is shown in the CPR field. If there is no ACLS date the following shows up.

) AS ACLS, IIf(([ClassName] Like ‘BLS*’),[tblSession].[DateOfClassStart],

I then get an error message about not having equal fields at the designation when it gets to the second part of the code below. That is because there isn’t a ACLS filed created.

Thank you for your help. Fay

‘ SQL for make-table query
strSQL = ” SELECT tblLearners.LearnerID, tblLearners.LastName, tblLearners.NickName, tblClasses.ClassName, ” & _
“tblSession.DateOfClassStart, IIf(([ClassName] Like ‘ACLS*’),[tblSession].[DateOfClassStart],””) AS ACLS, ” & _
“IIf(([ClassName] Like ‘BLS*’),[tblSession].[DateOfClassStart],””) AS CPR INTO tblACLSClassPrerequisites ” & _
“FROM tblLearners INNER JOIN ((tblClasses INNER JOIN tblSession ON tblClasses.ClassID = tblSession.ClassID) ” & _
“INNER JOIN tblRegistrationLearner ON tblSession.ClassNumber = tblRegistrationLearner.ClassNumber) ” & _
“ON tblLearners.LearnerID = tblRegistrationLearner.LearnerID ” & _
“WHERE (((tblClasses.ClassName) Like ‘ACLS*’) AND ((tblSession.DateOfClassStart)>=DateAdd(‘yyyy’,-2,Date()))) OR (((tblClasses.ClassName) Like ‘BLS*’) ” & _
“AND ((tblSession.DateOfClassStart)>=DateAdd(‘yyyy’,-2,Date()))) ”

‘ Execute it
DoCmd.RunSQL strSQL

‘ SQL for append query

strSQL = “INSERT INTO tblACLSClassPrerequisites ( LearnerID, LastName, NickName, ClassName, DateOfClassStart, ACLS, CPR )” & _
“SELECT tblCertificationDates.LearnerID, tblLearners.LastName, tblLearners.NickName, tblCertificationDates.Type AS ClassName,” & _
“tblCertificationDates.Date AS DateOfClassStart, IIf(([ClassName] Like ‘ACLS*’),[Date],””) AS ACLS, ” & _
“IIf(([ClassName] Like ‘BLS*’),[Date],””) AS CPR” & _
“FROM tblCertificationDates INNER JOIN tblLearners ON tblCertificationDates.LearnerID = tblLearners.LearnerID” & _
“WHERE (((tblCertificationDates.Type) Like ‘ACLS*’) And ((tblCertificationDates.Date) >= DateAdd(‘yyyy’, -2, Date)))” & _
“or (((tblCertificationDates.Type) Like ‘BLS*’) And ((tblCertificationDates.Date) >= DateAdd(‘yyyy’, -2, Date)))”

‘ Execute it
DoCmd.RunSQL strSQL

Thank you so much, it worked. I haven’t used that expression before. Fay

A women can hope. I was guessing that would be the answer. Thanks. Fay

Thanks to both of you. I will take your recommendations and save my money. Fay

One of the big reasons I was looking at it is that it is suppose to find the unused objects in the database. I think I have a bunch of currently unused queries. Is there a cheaper product for that task? Fay