looping query

[boblarson]

Point of order:

Access 9 = Access 2000
Access 10 = Access 2002 (Access XP)
Access 11 = Access 2003

 

[Banana]

If he's using 2003, which I am also using, shouldn't DAO and ADO be on by default?

 

[67flyer]

the program was first started in access 2000 and i haven't converted it to 2003. I want access 2000 users to be able to use it.

 

[67flyer]

Here is a sampledb, the forms and tables associated to the problem.

Thanks

 

[pbaldy]

I'm a little slow on the uptake. I just picked up on the fact that you wanted the groups by AccountLetter. If I'm correctly interpreting that, the attached will do it. Because of the difficulty of incrementing on 2 text fields, I added an autonumber field called UserID. You don't have to use it for anything else. Then I created a query called "qryIncrement", and an update query based on that; "qryUpdateGroup". All you have to do is add that field in your table, import these 2 queries and run the update query. All on a backup of course!

I'm not really sure this is the best solution; that would depend on the overall situation. Frankly, it feels a little "quick and dirty" to me, even though I came up with it. Alternatives would include a function that ran for new records, filling in any blanks left by deletions. That way you wouldn't have to run this all the time, and people would stay in the same group (of course that may not be a concern).

Anyway, see if these help any.

 

[67flyer]

PERFECT!!!! it does what I was looking for. There won't be a large # of records that need to be updated. So running the code on the close of the form if a delete record has happened will not be a big system slow down.

Once again thanks for the help!

Is there a way to have them be listed Alphabetically by lnameloc then run the code for the grouping? I know the userid field wouldn't be an autonumber field, could that be used to set the order by #. (a=1 b=2 and so on) I think it would be something like the code now, just wouldn't have the ' Int([Increment]/12.01)+1 ' it would be /1.01 (reason being if a - k are in rptlevel 1 and then you add one more c and this happens to be the 13th person he would be in the next rptlevel and out of alphabetic order and he happens to be in rptlevel 2. need to order by lnameloc and then run the code to increment. so that k would be in rptlevel 2.) Thinking a reindex after sorting by lnameloc.

Thanks,