Unanswered: Data Entry Form with ComboBox - Load Next in Succession
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.
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:
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
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)
GetNextAssigned = Me.cboAssignedTo.Column(0, i + 1)
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.
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.