Loop and display names from a query

Hello, I have a form that if I check a box it sets an supervisor as inactive and if unchecked it sets them as active. What I need to do is in my message I need to display the (EmployeesNames) that is associated with the supervisor (txtSupID) I’m trying to set active or inactive. I created a query (qryAssociatedEmpSup) that displays the list of (EmployeesNames) thatis associated with the (SupID).

Display like:

Are you sure you wish to change supervisors status to INACTIVE?"
If so then you have to adjust employees associated with this supervisor,
because thier name will not show up in any list untill you do."
Joe doe
Joe doe
Joe doe
ect


[CODE]If chkStatus = True Then
If vbYes =MsgBox("Are you sure you wish to change supervisors status toINACTIVE?" & vbCrLf & vbCrLf _

& "If so then you have to adjust employees associated with thissupervisor," & vbCrLf _
& "because thier name will not show up in any list untill youdo.", _
vbYesNo + vbQuestion, "Set to Active?") Then
Else
Me.chkStatus = False
Me.Requery
End If
Else

If chkStatus =False Then
IfMsgBox("Are you sure you want to change the supervisors status toACTIVE?", vbQuestion + vbYesNo, "Set to InActive?") = vbYes Then
Me.Requery
Else
Me.chkStatus = True
Me.Requery
End If
End If
End If
[/CODE]

Code:

---

SELECT [EmpFName] & " " & [EmpLName] AS EmployeesName, tblEmployees.SupID
FROM tblEmployees
WHERE (((tblEmployees.SupID)=[Forms].[frmEditSupervisors].[txtSupID]));

---

I can understand what you are trying to do.
But I question the way that you are trying to do it.
The conventional method is to use a Subform in Continuous Forms mode to both display and amend the data for the employees associated with the chosen Supervisor.

Ok I tried the vb below and am getting a to few parameters. Expected 1 error and debug takes me to:

Set rs = db.OpenRecordset("qryAssociatedEmpSup", dbOpenSnapshot)


Here is the VBA Im trying... what am Idoing wrong?

Code:

---

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intX As Integer
    Dim strMissnData As String
    Dim lastemployee As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryAssociatedEmpSup", dbOpenSnapshot)

        With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            lastemployee = .Fields("employeesname")
            strMissnData = .Fields("EmployeesName") & vbCr
            Do Until .EOF
                If Trim(lastemployee) <> Trim(.Fields("employeesname")) Then
                    strMissnData = strMissnData & .Fields("EmployeesName") & vbCrLf
                End If
                lastemployee = .Fields("employeesname")
                .MoveNext
            Loop
        End With
    If chkStatus = True Then
    If vbYes = MsgBox("Are you sure you wish to change supervisors status to INACTIVE?" & vbCrLf & vbCrLf _
                    & "If so then you have to adjust employees associated with this supervisor," & vbCrLf _
                    & "because thier name will not show up in any list untill you do." & vbCrLf _
                    & strMissnData, _
                    vbYesNo + vbQuestion, "Set to Active?") Then
        Else
            Me.chkStatus = False
            Me.Requery
        End If
    Else
        If chkStatus = False Then
            If MsgBox("Are you sure you want to change the supervisors status to ACTIVE?", vbQuestion + vbYesNo, "Set to InActive?") = vbYes Then
                Me.Requery
            Else
                Me.chkStatus = True
                Me.Requery
            End If
        End If
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

---

Thoughts on why im gettin the error? I trid to even use a sql in the code nd am still getting an expectd 1.

Does the query you are using have any Parameters or Criteria set, if so Access VBA does not like them.
You may be able to get the criteria to work by setting them as Parameters.
The alternative is to build the SQL statement for the query in the VBA code.

Quote:

---

Originally Posted by OBP

Does the query you are using have any Parameters or Criteria set, if so Access VBA does not like them.
You may be able to get the criteria to work by setting them as Parameters.
The alternative is to build the SQL statement for the query in the VBA code.

---

Threre isnt really prameters in query other then the text box on my form

Code:

---

SELECT [EmpFName] & " " & [EmpLName] AS EmployeesName, tblEmployees.EmpFName, tblEmployees.EmpLName, tblEmployees.SupID, tblEmployees.SupID
 FROM tblEmployees
 WHERE (((tblEmployees.SupID)=[Forms].[frmEditSupervisors].[txtSupID]));

---

I tried the VBA and same error..

Code:

---

strSQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _        "FROM tblEmployees " & _
        " WHERE SupID= " & Me.[txtSupID]

---

Try

strSQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
"FROM tblEmployees " & _
"WHERE SupID= '" & Me.[txtSupID] & "'"

or

strSQL = "SELECT tblEmployees.* " & _
"FROM tblEmployees " & _
"WHERE SupID= " & Me.[txtSupID]

To test that the name concatenation is not causing a problem.

Quote:

---

Originally Posted by OBP

Try

strSQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
"FROM tblEmployees " & _
"WHERE SupID= '" & Me.[txtSupID] & "'"

or

strSQL = "SELECT tblEmployees.* " & _
"FROM tblEmployees " & _
"WHERE SupID= " & Me.[txtSupID]

To test that the name concatenation is not causing a problem.

---

I tried both strings an both i get a error '91' object varible with block varable not set..

Code:

---

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intX As Integer
    Dim strMissnData As String
    Dim lastemployee As String
    Dim strSQL as String
    strSQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
        "FROM tblEmployees " & _
        "WHERE SupID= '" & Me.[txtSupID] & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            lastemployee = .Fields("employeesname")
            strMissnData = .Fields("EmployeesName") & vbCr
            Do Until .EOF
                If Trim(lastemployee) <> Trim(.Fields("employeesname")) Then
                    strMissnData = strMissnData & .Fields("EmployeesName") & vbCrLf
                End If
                lastemployee = .Fields("employeesname")
                .MoveNext
            Loop
        End With
    If chkStatus = True Then
    If vbYes = MsgBox("Are you sure you wish to change supervisors status to INACTIVE?" & vbCrLf & vbCrLf _
                    & "If so then you have to adjust employees associated with this supervisor," & vbCrLf _
                    & "because thier name will not show up in any list untill you do." & vbCrLf _
                    & strMissnData, _
                    vbYesNo + vbQuestion, "Set to Active?") Then
        Else
            Me.chkStatus = False
            Me.Requery
        End If
    Else
        If chkStatus = False Then
            If MsgBox("Are you sure you want to change the supervisors status to ACTIVE?", vbQuestion + vbYesNo, "Set to InActive?") = vbYes Then
                Me.Requery
            Else
                Me.chkStatus = True
                Me.Requery
            End If
        End If
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

---

Try removing the
dbOpenSnapshot from the set statement.
Do you know where the error occurs?
Is it the Set rs statement or later?

Quote:

---

Originally Posted by OBP

Try removing the
dbOpenSnapshot from the set statement.
Do you know where the error occurs?
Is it the Set rs statement or later?

---

it occurs at the:

Set rs = db.OpenRecordset(strSQL)

same line with the dbOpenSnapshot....

Can you humour me here and just try this simple code on another button.

Code:

---

Dim rs As Object, SQL As String
on error goto errorcatch
SQL = "SELECT tblEmployees.* " & _
"FROM tblEmployees "
Set rs = CurrentDb.OpenRecordset(SQL)

exit sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

---

You may have to remove the space after tblEmployees in the From statement.

Let me know if that works without an error.

im geting the following error

Code:

---

Error #:3265
Item not found in this collection

---

OBP, I finally got it....

Code:

---

SQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
        "FROM tblEmployees " & _
        "WHERE SupID= " & Me.[txtSupID] & ""

---

Thank You!

:beerchug:

I hope it does what you want with the display.

Quote:

---

Originally Posted by OBP

:beerchug:

I hope it does what you want with the display.

---

OBP, it does but I noticed its displaying inactive employees. Ho do I add

And ((tblEmployees.IsInactive) = False)

to the below SQL? Thanks!

Code:

---

strSQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
        "FROM tblEmployees " & _
        "WHERE SupID= " & Me.[txtSupID] & ""

---

SQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
"FROM tblEmployees " & _
"WHERE SupID= " & Me.[txtSupID] & "" & _
"And IsInactive = False"

or
SQL = "SELECT [EmpFName] & ' ' & [EmpLName] AS EmployeesName, EmpFName, EmpLName, SupID " & _
"FROM tblEmployees " & _
"WHERE SupID= " & Me.[txtSupID] & "" & _
"And IsInactive <> -1"

Thanks, that worked!