querying several times ???

[Robert Painter]

Hi,
Hoping someone out there can help me with this.

I have an application for an employment agency. 3 tables
tblEmployee
tblSkill
tblEmployeeSkills

There are at present 70 skills available on the enrollment form for the
employee to select from.
My task is to make it easy to find an employee based on these skills.
My way of thinking is to count employees. msgbox with number of employees
available, open form with combo box of skills, search on selected skill,
count employees, msgbox with number etc. until halted.
The reasoning behind this is there may be only 3 or as many as say 9 skills
required and if you put in all 9 at once you could end up with a nil count
of employees.

I have the count worked out:

Private Sub cmdSearch_Click()
Dim rs As DAO.Recordset
Dim buttonpress As Integer
'/ Create your recordset..

mysql = "SELECT COUNT(EmployeeIDFK) AS CountOfEmployeeIDFK"
mysql = mysql & " FROM tblEmployeeSkills"
mysql = mysql & " GROUP BY EmployeeIDFK;"


Set rs = CurrentDb.OpenRecordset(mysql)
'/ Populate your message box..
buttonpress = MsgBox("You have " & rs!CountOfEmployeeIDFK & "
candidates." & vbCrLf & "Continue search", 4 + 0, "Candidate Search")


'/ Close shop..
rs.Close
Set rs = Nothing
If buttonpress = 6 Then
DoCmd.OpenForm "frmcbosearch"
Else
End If

Exit Sub
errhandler: MsgBox "error halted"
End Sub

and the search:

Private Sub cmdOkbutton_Click()
' ¦¦=====================¦¦
' ¦¦Search for candidates¦¦
' ¦¦search for candidates¦¦
' ¦¦=====================¦¦

Dim rs As DAO.Recordset
Dim mysql As String
Dim mysearchrs As String

'/ Create your recordset...
mysql = "SELECT tblEmployees.EmployeeName AS mySearchrs " & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills!SkillIDFK = " & Forms!frmcboSearch!cboSearch &
"" & _
" ORDER BY tblEmployees.EmployeeName"

Set rs = CurrentDb.OpenRecordset(mysql)

Do Until rs.EOF

Debug.Print (rs!mysearchrs)

rs.MoveNext
Loop

'/ Close shop..
rs.Close
Set rs = Nothing
End Sub

I am now wondering if it will be easy to join these and carry out my search.

Firstly may i thank you for wading thru all this and secondly thankyou for
any help you may be able to give me.

Robert