none
Select Next from a Combo Box and then move selection to the bottom of the list RRS feed

  • Question

  • Hi, A form in my database has cascading combo boxes to arrive at a list of available contractors (say, plumbers) that can be assigned to a job.  However I don't want to give the first contractor in the list every job.  How can I rotate the list so that when the contractor at the top of the list is selected, he then moves to the bottom so the next one is available for the next job?

    The reason for this is that more than 2 people will be capturing data and to keep a mental note of who was last used won't work.

    The SQL for the combo boxes is :

    Me.Contractor.RowSource = "SELECT ContractorDetail.BusinessID, ContractorDetail.BusinessName " & _
                                "FROM ContractorDetail " & _
                                "WHERE (((ContractorDetail.Area.Value)=Forms!frmNewCustomerJob!Area) " & _
                                "AND ((ContractorDetail.FieldOfWork.Value)=Forms!frmNewCustomerJob.subfrmNewJob.form.Discipline)) " & _
                                "ORDER BY ContractorDetail.BusinessName"

    Do I need to use some sort of LOOP?  Please help with a starting point for me.

    Many thanks, David

    Thursday, May 26, 2016 2:05 PM

Answers

  • I would add a date field to the ContractorDetail table or add another linked table that has a field called 'Last_Assigned_Date'.  Then when I populate the form I would order by 'Last_Assigned_Date' descending (DESC).  Update the 'Last_Assigned_Date' field with the current date/time.
    Thursday, May 26, 2016 2:25 PM

All replies

  • I would add a date field to the ContractorDetail table or add another linked table that has a field called 'Last_Assigned_Date'.  Then when I populate the form I would order by 'Last_Assigned_Date' descending (DESC).  Update the 'Last_Assigned_Date' field with the current date/time.
    Thursday, May 26, 2016 2:25 PM
  • Thanks for the suggestion.  I'll give it a try.
    Friday, May 27, 2016 12:48 PM
  • Thanks.  Works perfectly.  Such a simple and logical solution. 
    Monday, May 30, 2016 9:54 AM