Automatically fill in field in a loop

[RachelBedi]

I'm hoping this could solve some speed problems in the group using my database.

I have a team of three people which assign "leads" to the rest of my team via a combobox filled with usernames.



I would like to have that field automatically populate by looping through the names in that combobox.

Does anyone have some insight as to how I could do this?

[rpeare]

Your question is not very clear, perhaps an example written out of what you are trying to accomplish?

[RachelBedi]

hi rpeare, I am soon becoming the bane of your existence :-P thank you so much for your help!

My bank folks have to verify account numbers for enrollments in our payment system - once the bank account has been verified it moves on to an activator who authenticates the business information. In order for an activator to see the company which the banker has verified, the banker has to "assign" it to an activator.

Here are the fields for the bankers;

Paymode ID, Company name, Date of Enrollment Bank Status, Assigned, Bank Modified Date

The field "Assigned" is a drop down menu filled with the people who are considered "activators"

Currently the banker who has verified the bank account has to physically go to that field and choose a name from the drop down menu, which is a) cumbersome and b) not nec. accurate (if they don't go down the list equally they might assign too many accounts to one person)

I would like to autogenerate the "assigned" field by looping through the options in the list via vba code.

I really hope that makes more sense, if not I can try to upload my form!

[rpeare]

Ok so your combo box with the list of activators is correctly showing WHO can possibly be assigned the task but you want to evenly distribute the workload over those 'activators' and pick the person with the lowest number of accounts to handle.

So what you want is a 'suggested' activator (which presumably they can change if they want to).

There are a couple of ways you can do this fairly easily. The first is to create a list box with a summary of your activators and how many current accounts they have so from your BANKERS table it would be a query something like

SELECT Assigned, Count(Assigned) as AccountsActive FROM Bankers GROUP BY Assigned ORDER BY Count(Assigned) DESC

Then in the ON CLICK event of the list box you would have

ASSIGNED = Lst_AssignedList

where ASSIGNED would be the name of your data entry field and Lst_AssignedList would be the name of the list box.

Barring that you would have to do a little coding but it would be the same basic method

in the ON ENTER property of your ASSIGNED combo box field

dim db as database
dim rst as recordset
dim sSQL as string

set db = currentdb
sSQL = "SELECT Assigned, Count(Assigned) as AccountsActive FROM Bankers GROUP BY Assigned ORDER BY Count(Assigned) DESC"
set rst = db.openrecordset(ssql)
rst.movefirst
ASSIGNED = rst.fields("Assigned")
rst.close

set db = nothing

basically you're opening up the same dataset as if you were going to do it in a list box, read the first value (the person with the lowest number of accounts) and fills in your ASSIGNED field with the id of that person.