Automatically fill combo box with next item from drop-down list on next record entry

0
votes

Good day all,

I have a table named Sources and it contains 3 fields

  • ID
  • Type
  • Status

It is the source for a combo box on a form named DailyCash. What I am trying to attempt is, when I open DailyCash for data entry I want it to open with the first Item from the Sources Table. After data entry the next item is filled in the next new record and so on. But in the instance that the next item in the table is 'Inactive' it skip it and move on to the next 'Active' Item

For example:

  • Sources Table
    1. Box1 Active
    2. Box2 Active
    3. Box3 Active
    4. Box4 Inactive
    5. Box5 Active

On opening DailyCash for data entry Box1 will fill the combo box, user enter rest of details in the other text boxes, on next record Box2 is filled in the combo box. After Box3 the record moves to Box5 (only 4 records are available for data entry for that particular day).

1
ms-accessms-access-2016

1 Answers

1
votes

If I understand you correctly this will get you close:

DMin("ID","Sources","Status='Active' AND ID>" & [PrevID])

This will give you the ID of the next active Source after the one with [PrevID]. When [PrevID] is zero you will get the first active Source. All you should have to do is replace the [PrevID] placeholder with whatever variable or control value holds the ID of the previous item.