Loop and display names from a query

[oxicottin]

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



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]));

[OBP]

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.

[oxicottin]

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?


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

[oxicottin]

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

[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.

[oxicottin]

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


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..


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

[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.

[oxicottin]

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..



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

[OBP]

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

[oxicottin]

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....

[OBP]

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


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.

[oxicottin]

im geting the following error


Error #:3265
Item not found in this collection

[oxicottin]

OBP, I finally got it....


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


Thank You!

[OBP]

:beerchug:

I hope it does what you want with the display.

[oxicottin]

: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!


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

[OBP]

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"

[oxicottin]

Thanks, that worked!