none
Access 2016 - SQL Commands - Creating a Basic Map Using A Table - Filtering Records On Single Form RRS feed

  • Question

  • Hello and thanks in advance. I am new to the forum.

    I have a little issue working on a project. I have a single form with our floor plan as the background. I am trying to get everyone's name located at their respective spot on this floor plan. Obviously this can be done manually, but I am really trying to link it up to a table for multiple reasons. I was told a list box should work but I am really trying to do this the correct way.

    Is it at all possible to use text boxes to filter records and placing their respective boxes around the form? I have basic knowledge in VB and SQL, nothing crazy but I know how to make things work. Let us assume I have a single table called tblX with Field1 = ID and Field2 = Last. I want Last displayed and filtering on ID.

    Any help?


    • Edited by Jeff_spk Thursday, March 2, 2017 8:10 PM
    Thursday, March 2, 2017 8:10 PM

All replies

  • Hi Jeff_spk,

    from your description , all I understand that you want to show the employee name in textbox for the particular department. so that you can know which department is handle by which employee.

    for that I create one table with employee names like below.

    code:

    Private Sub Command12_Click()
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim sqlStr As String
    Dim i As Integer
    Dim cCont As Control
    
    sqlStr = "SELECT * FROM employee"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlStr)
    
    
    rs.MoveFirst
      For Each cCont In Me.Controls
    
            If TypeName(cCont) = "TextBox" Then
                cCont.SetFocus
                cCont.Text = rs!emp_name
                rs.MoveNext
            End If
    
         Next cCont
    
    End Sub
    

    Output:

    you can try to modify the code as per your requirement. this is just a sample code I provided you above. so that you can get the idea.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 3, 2017 6:34 AM
    Moderator
  • Create an Employees table with columns EmployeeID,  FirstName and LastName, along with a Location column, with distinct values each corresponding to the location in the office occupied by the employee.  In the form add the unbound controls in the appropriate positions.  Set the Tag property of each of these controls to the name of its location. These must of course be distinct and exactly the same as those used in the table.  No other controls in the form should be tagged.  It is not necessary to use all location names in the table, however, if one or more locations is currently unoccupied.  No other controls should be tagged.  Then in the form's Load event procedure put:

        Dim ctrl As Control
        Dim strCriteria As String

         For Each ctrl In Me.Controls
             If Len(ctrl.Tag) > 0 Then
                 strCriteria = "Location = """ & ctrl.Tag & """"
                 ctrl.Value = DLookup("FirstName & "" "" & LastName", "Employees", strCriteria)
             End If
         Next ctrl


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, March 3, 2017 6:48 PM clarified
    Friday, March 3, 2017 6:46 PM
  • Is it at all possible to use text boxes to filter records and placing their respective boxes around the form? I have basic knowledge in VB and SQL, nothing crazy but I know how to make things work. Let us assume I have a single table called tblX with Field1 = ID and Field2 = Last. I want Last displayed and filtering on ID.

    Hi Jeff,

    You have a table with Persons: Lastname, Firstname, Location (and other fields).

    In your form of the map you have controls with names corresponding to the Location. It can be the same name, or systematically created as e.g. "Loc_" & Location.

    Then you can use in the Load event of the form:

      Set pers_set = CurrentDb.OpenRecordset("SELECT * FROM Person_tbl")
      Do While (Not pers_set.EOF)
        Me("Loc_" & pers_set!Location) = pers_set!Firstname & " " & pers_set!Lastname
        'if necessary make the control visible
        pers_set.MoveNext
      Loop
    

    Imb.

    Friday, March 3, 2017 7:38 PM