Update Records based on Criteria

[vbJupiter]

I'm working on a main form to assign batch numbers used to process transactions. Based on the type of transaction needed, the first character of the batch number varies. There are about 15 rules.

I'm trying to formulate a plan for this and here's what I've got. Create an outer join query that contains a calculated field using the Left Function to extract the first letter of the unassigned batch numbers. Create a function procedure on the main form which processes the batch request information and outputs the starting character for the batch request. Create a sub procedure that Calls the Function, Opens the Query, and finds the matching record(s), and updates the assignedbatch table.

Does this sound like I'm on the right track? I could use some pointers on how to structure the procedure(s) to handle updating multiple records. The function can't return more than one value right?

TIA!

 

[gemma-the-husky]

why is the batch number significant?

why cant you assign internal batch numbers as an autonumber, then hold another text field with a "true" batch number. You can then use a sub (say attached to an appropriate before or after update, or before or after insert to retrieve the real ref you want - ie lookup the next refs from counter tables as appropriate. you always have the problem that if a process is cancelled you may have "burnt" one of your true internal refs.