Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    11

    Unanswered: Data Entry Form with ComboBox - Load Next in Succession

    Hi, All.

    I have a data entry form (frmMIBEntry) that will be used to log receipt, distribution, and resolution of an assignment. On this form I have a control called cboAssignedTo, which contains a selection of 4 people to choose from (info comes from tblMDList). We divvy up the assignments as equally as possible and in the past, using a notebook and pen it was easy to progress in succession from one person to the next by looking at the line above - I'm trying to bring us into the 21st century and put this in Access 2003, and I am struggling with this part. Ideally, I want the the next person in the list (the one after the one last selected) to populate - when the form opens, which is not every day.

    I appreciate any help you can throw my way!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:

    1. The Table that is used as the RecordSource property of the form "frmMIBEntry" is named "TblMIBEntry" in this example (you did not mention the actual name).

    2. The table "TblMIBEntry" has an Autonumber column "SysCounter" that is used to determine the most recent record.

    3. The name of the people in the combobox "cboAssignedTo" is in the first column of the combo (column 0).

    4. This function (in the form module) will return the next person in the list:
    Code:
    Private Function GetNextAssigned()
    
        Dim strLastAssigned As String
        Dim i As Long
        
        strLastAssigned = DLookup("AssignedTo", "TblMIBEntry", "SysCounter=" & DMax("SysCounter", "TblMIBEntry"))
        For i = 0 To Me.cboAssignedTo.ListCount - 1
            If Me.cboAssignedTo.Column(0, i) = strLastAssigned Then Exit For
        Next i
        If i = Me.cboAssignedTo.ListCount Then
            '
            ' Error: The name was not found in the list of the combo.
            '
        ElseIf i = Me.cboAssignedTo.ListCount - 1 Then
            GetNextAssigned = Me.cboAssignedTo.Column(0, 0)
        Else
           GetNextAssigned = Me.cboAssignedTo.Column(0, i + 1)
        End If
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Posts
    11
    Thanks, Sinndho. I'm not able to get this to work in my database. I must be missing something. I hadn't had a "SysCounter" Autonumber column until you mentioned in your solution. I added that in, saved, and get a clean form each time - the name does not carry over.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The SysCounter columns does not matter really. What's important is that you have a way to determine what's the most recent row in the table. In my example I used the domain function Dmax on an AutoNumber column (SysCounter) but it could be something totally different.
    Have a nice day!

Posting Permissions

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