Sub Account Indentations (1 Viewer)

Hi everyone,

I am posting the db and function here for new account and sub account functions which need to be rectified as it was created for single stage account and now i need multi sub accounts which can led the new account number up to 10 digits maximum more than that is not allowed as i set validation rules.

I suspect that Dmax function is not what i want here, but i dont know is there any other way like Looping etc which can provide the correct results.

As you can see in attached db we are trying to figure out the sub account value with Dmax function, which is working fine but not perfectly as I want to be it has some issues which i will be highlighting in this post.

1. When any new sub account created it is working correct for without selecting the sub account.

2. When second time i select this function to create another sub account it's not giving me the main account number and its going directly to the value which is greater or Max in the column, obviously because of Dmax which is wrong as per my requirement let's say main account 11 and I create first subaccount 1101 and then again when I try to create account it pop up the value 110101 rather then 1102 which is wrong.

3. What is my requirement ?

Firstly I don't know this action can be perform by using Dmax or some other functionality, I want function to identify the main and show its subaccount's max value (not the column's Max value) and add 1 (as 01) to it before selecting the subaccount.

Secondly when I select any of subaccount then new account value will follow the same procedure as above BUT main account will be account that selected in subaccount and to that number only 1 will be added without 01 because the length of account will be more and user will have subaccount until the number reach to 10 digits then it will not be not allowed as I set that condition in table field.

Example for above points that I want in this functionality.

1st Create account
Main account 15
New account 1501

2nd Create account
Main Account 15
Subaccount 1501
New account 15011 this last number will keep increasing when ever 1501 selected as subaccount like 15012,15013

3rd Create Account
Main Account 15
Subaccount 15011
New Account 150111 this last number will keep increasing when ever 15011 selected as subaccount like
150112,150113

4th Create Account
Main Account 15
New Account 1502 as we have already created 1501 in above.

5th Create Account
Main Account 15
Subaccount 1501
New Account 15012 because we have created 15011 in above.

6th create Account
Main Account 15
Sub Account 15011
New Account 150112 as we have created 150111 in above.

I hope this examples mentioned almost every point that I am trying to do and this code's issue, however I will add the db also tomorrow to this post which will be more easy to understand the issue.

You are heading down path to insanity. You want 2nd account to increment like 15011, 15012,... now we get to 150110. Then 150111 conflicts with 3rd account 150111.

Sorting a number field will not retain the Main>1st>2nd etc. order.
Sorting a text field will follow alpha rules and 15011 will sort before 1502.

I could add "01" instead of "1" if its conflicts at later on, it was just an example or you can suggest any other way.

The main thing in what would be the method and procedure to capture, when sub account is selected to add increment value to its number rather than columns max value, for that i said i suspect the Dmax can do this or not.

i am sorry but at one stage everyone need to head insanity before achieving goals some are highlighted and some are not.

Generating custom unique identifier is a common topic. But your requirement is more complicated than usual. For a start, review http://www.accessforums.net/showthread.php?t=75707

I had my doubts about this numbering structure from the start.
I think you have a degree of tunnel vision on this issue and need to step back and look at the wider picture.

If it was just the one level of subaccount that I helped you with at the start, then possibly it was acceptable. However you've since added a 2nd and 3rd level adding extra complexity each time. Where will it end? 4th, 5th...27th?

Why not just use a standard account length of say 8 digits as for bank current accounts. Junction tables will tell you which main account is linked with each subaccount and vice versa.

If you want you could have say 00 for the first 2 digits in the main account, 01 for sub accounts etc...though personally I wouldn't bother.

I'm not sure if this is what you want (see attached zipped db).

Though, as a general rule, Account Codes should be text, not long (even if they are actually numbers) for numerous reasons, and most importantly, sorting. For example, when you sort the chart of accounts you want 150001 < 160.

Anyhow, hope I've helped!

Thanks i can have a start from this point.