Data Entry Form with ComboBox - Load Next in Succession

BattyOwl · #1 (**permalink**) 02-07-12, 13:57

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!

Sinndho · #2 (**permalink**) 02-07-12, 15:42

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

BattyOwl · #3 (**permalink**) 02-08-12, 10:46

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.

Sinndho · #4 (**permalink**) 02-08-12, 12:01

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech