Consulting

Results 1 to 17 of 17

Thread: Loop and display names from a query

  1. #1

    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]


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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

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

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

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  6. #6
    Quote Originally Posted by OBP View Post
    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]

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  8. #8
    Quote Originally Posted by OBP View Post
    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

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Try removing the
    dbOpenSnapshot from the set statement.
    Do you know where the error occurs?
    Is it the Set rs statement or later?

  10. #10
    Quote Originally Posted by OBP View Post
    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....

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.


  12. #12
    im geting the following error

    Error #:3265
    Item not found in this collection

  13. #13
    OBP, I finally got it....

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

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location


    I hope it does what you want with the display.

  15. #15
    Quote Originally Posted by OBP View Post


    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] & ""

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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"
    Last edited by OBP; 02-05-2019 at 05:19 AM.

  17. #17
    Thanks, that worked!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •