auto populate field based on another table
I am creating a database that is going to be used for data collection. He is a short summary of the tables
Active (a boolean field)
Order (numeric starting at 1)
What I want to be able to do is have the layout used to collect the data auto populate the CustomerName field in numeric order based on the Order field for Active customers. Each subsequent record will have the next CustomerName autopopulated until the last customer and then it will start back at the first one. Only customers indicated as Active will be included.
I am having difficulty figuring out how to do this and any help/suggestions would be appreciated.