eof and recordsets

[david123]

I have a form that shows an employee’s name and position, a subform that shows which courses the employee has completed for their position, and another subform which lists all the courses required for the position.

In the second subform I inserted a text box which basically compares the two courses fields - IIf([Courses]=Forms![Staff Training]![CoursesCompletedforPosition Query subform].Form!Courses,"Yes","No"). So basically, if an employee has completed a course in the first subform and it matches a course in the second one, "Yes". This works, but it only checks the first record. I need it to check the entire recordset of the first subform.

I want to compare the courses completed for position to the courses required for position so that the user can figure out which ones they have left to do.

Is there any way to do this using an eof statement? i've tried but it doesn't work at all.


Any help would be great!


Thanks

 

[MarkK]

Referencing the form gives you the values that exist in the current record only.
I would use DCount() since essentially you need to count the number of records in the whole table that satisfy certain criteria. If there are none then the employee hasn't taken the course.

=Format(DCount("*", "StaffTraining", "CourseID = ? AND EmpID = ?"), "Yes/No")

Zero is a boolean false, everyting else is a boolean true, and you'll need to replace the '?' with proper references.

 

[LPurvis]

This would be, more ideally, performed at the query level rather than faffing on with distinct lookups.
Can you describe the schema of the three tables at play here?

 

[david123]

Thanks for the tip! This is the code I used in the text box at the query level:

=Format(DCount("*","PositionCourses Query","[PositionCourses Query.Courses]<>[Courses] and [PositionCourses Query.Position]<>[Position]"))

However nothing came up.

So I tried this in the AfterUpdate event in the textbox of the second subform:

Dim RS As DAO.Recordset
Set RS = [Forms![Staff Training]![CoursesCompletedforPosition Query subform].Form!Courses].Recordset
Dim found As Boolean
Do Until RS.EOF
If RS("Courses") = Courses.Value Then found = True
Loop
If found Then
Text4.Value = "Yes"
Else: Text4.Value = "No"
End If

but nothing comes up.

Any insight would be much appreciated! Thanks

 

[LPurvis]

Just in case you were referring to my suggestion, when I said "at the query level" I meant joining table data rather than performing lookups in the query (which is best avoided whenever possible).

However if you're wanting summary data and an updatable resultset then you won't be able to do it all in the query alone (as aggregated results aren't updatable obviously).

A description of your tables and intention are probably the most useful information you can provide.

 

[david123]

This would be, more ideally, performed at the query level rather than faffing on with distinct lookups.
Can you describe the schema of the three tables at play here?

I've tried to, but the way i've structured it I don't think a query works.
There is a positions table, a positioncourses table (which lists positions and the relevant courses to each one), a courses table, an employee training table (listing the employee's name, the courses they've completed, and the date it's been completed) and a staff list table (listing each employee with their position).

I can run a query to give me the courses someone has completed relevant to their position, but trying to compare the completed courses to those relvant to the position multiplies the records.
e.g. If someone has done 5 courses relevant to their position, and their position has 15 relevant courses, then 75 records show up.

 

[david123]

Just in case you were referring to my suggestion, when I said "at the query level" I meant joining table data rather than performing lookups in the query (which is best avoided whenever possible).

However if you're wanting summary data and an updatable resultset then you won't be able to do it all in the query alone (as aggregated results aren't updatable obviously).

A description of your tables and intention are probably the most useful information you can provide.

Sorry, I'm still pretty new to this stuff!

There is a positions table, a positioncourses table (which lists positions and the relevant courses to each one), a courses table, an employee training table (listing the employee's name, the courses they've completed, and the date it's been completed) and a staff list table (listing each employee with their position).

I've made a query to get the courses completed by employee which are relevant to their positions, but now I need to come up with a way to show which relevant courses an employee has left to complete.