if and loop statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Scenario:

I have a table which contains employee details and a field which holds
status details, there maybe 5+ employees to 1 manager. Currently I use If
statements which check if the status = "To Be Sent" If this is true it sends
out the relevant forms to that manager.

My problem which i guess there are 2 of,

1) within the If statement it checks the status and the emp_name which
currently looks like this:

If Forms![MAIN]![NFORM subform]![STATUS] = "To Be Sent" And
Forms![MAIN]![NFORM subform]![EMP_NAME] = "Test Er" Then
send email
End If

what would be more helpful is if it were to auto return the name of the
employee as apposed to typing it in which is not v practical.

So if there were 8 employees with the same manager and a staus of to be
sent, instead of continuous if statements it returned each value in turn to a
variable. I guess this would be done through a loop?

Cheers
 
Your post doesn't give enough information to give an exact way to accomplish
what you want to do; however, here is a generic way to do this that you
should be able to modify to suit your needs. First, you will need to create
an SQL statement or build a query that will return only the Employees of a
selected Manager with the status of "To Be Sent"
For demo purposes, lets say you have built ans SQL string that will return
the employee names (and whatever else you need to send them an e-mail)

Set rstEmails = CurrentDB.OpenRecordSet(strSQL)
If rstEmails.RecordCount <> 0 Then
Do While Not rstEmails.EOF
SendEmail
Loop
 
Using Employee Name isn't a good choice, what if you have more than one
employee with the same name? Use the unique ID field for the record instead.

Now, to do the loop:
Dim db As DAO.Database, rst As DAO.Recordset
Dim strSQL As String
'First, let's limit how many records you need to go through.
strSQL = "SELECT ID, EMP_NAME FROM [" & _
Forms!MAIN![NFORM subform].Form.RecordSource & _
"] WHERE Status = ""To Be Sent"" And ManagerID = " & _
Me.txtManagerID & ";"
'The ManagerID field in the above will be the same field as the Child Link
'for the subform. This will limit the selection to the current manager.
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Check to see if there are any records that met the criteria.
'If so, then continue. If we're at EOF to start with, then
'there were no records.
Do Until rst.EOF
SendMail rst!EMP_NAME
'Or whatever code you're using to send the mail.
'The field rst!EMP_NAME will have the name to send to
'include any other fields you may need, such as email address
'instead of EMP_NAME.
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
 
Back
Top