Find the Max Value of Column and Add 1 for new (1 Viewer)

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
Hi everyone,

I have following sql to get Max value of column and i want to add 1 for new account number on after update event of cboType to assign the new account number, in order to do that i have following sql, which find the last account number under the Main account (tblAccounts.AccHeaderID) category, but unable to add 1 to latest value to get new account number.

Code:
SELECT Max(tblAccounts.AccountNo) AS MaxOfAccountNo, tblAccountsHeader.AccHeaderID
FROM tblAccountsHeader INNER JOIN tblAccounts ON tblAccountsHeader.AccHeaderID = tblAccounts.AccHeaderID
GROUP BY tblAccountsHeader.AccHeaderID


tblAccountsHeader - PK [AccHeaderID] (Integer)
- [HeadingName] (String)

tblAccounts - PK [AccountNo] (Integer)
- FK [tblAccountHeader].[AccHeaderID]

frmCreateAccount - txtNewAccountNo in this text box i need result.

for example

if i have AccHeaderID is 102 and under the heading i have 102001,102002,102003 accounts then new value should be 102004 and same for all other headings whatever is selected 203,304,502 or any it has to look up the latest account number under this headings and add 1 to give the new number.


How i can do it in after update event of cboType combo box?
 

Attachments

  • Test1.zip
    127.3 KB · Views: 64

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
I've not looked at your example but the usual method is to use DMax e.g.

Code:
=Nz(DMax("AccountNo","tblAccounts",some filter if needed here),0)+1

If the AccountNo is a PK field you can omit the Nz part as it can't be null
 

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
This function works for the main accounts which have accounts under it

This was placed on control source of txtNewAccountNo text box

Code:
=DMax("AccountNo","tblAccounts","AccHeaderID = txtMainAccount")+1

Above works ok, when main account have accounts for example main account have 104 and their is accounts like 104001,104002 then it will give 104003 as new value.

but if its dont have account then its not showing any number at all lets say main account is 105 and its not having any account yet than it should show 105001 but its showing blank.

Also i would rather use the after update event of cboType than a control source function.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
Code:
=DMax("AccountNo","tblAccounts","AccHeaderID = txtMainAccount")+1

Above works ok, when main account have accounts for example main account have 104 and their is accounts like 104001,104002 then it will give 104003 as new value.

but if its dont have account then its not showing any number at all lets say main account is 105 and its not having any account yet than it should show 105001 but its showing blank.

It was for that reason that I included the Nz function. You could use e.g. 0 for the false part if you prefer ....
 

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
I have add the function with NZ and its working ok, i have no problem with what main have accounts already being created.

The problem with main that still dont have an account, as you can see in my previous post here the account format should be a six digit if main account is 105 then new account should start with 105001 and not 106 this formula return 106 which i dont want i want result should be 105001 all six digit account.

Code:
=Nz(DMax("AccountNo","tblAccounts","AccHeaderID = txtMainAccount")+1,[txtMainAccount]+1)

their might be some input mask stuff or any validation etc that i am missing but this is the format i want six digits.
 

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
OK it makes sense now.
Unfortunately I can't do anything with your form as a required table tblGroups wasn't supplied.

However, I've created a test function to show you one way of solving this:

Code:
Function GetNewAccount(txtMainAccount As Long)
    
    GetNewAccount = Nz(DMax("AccountNo", "tblAccounts", "AccHeaderID = " & txtMainAccount) + 1, CLng([txtMainAccount] & "001"))
    
    Debug.Print txtMainAccount; GetNewAccount
End Function

I then tested using the account values 105/106/107 in turn

Code:
GetNewAccount 105
 105  105002 

GetNewAccount 106
 106  106001 

GetNewAccount 107
 107  107001

See Module1 attached and adapt to work on your form.

NOTE:
I thought you had decided not to continue with the accounting app in Access.
My advice (like those of others previously) is still to use a ready made package for accounting.
If you use this with clients and even one part of the app gives incorrect outcomes, there could be major and costly implications for you as a developer
 

Attachments

  • Test1_CR.zip
    53.7 KB · Views: 60

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
Yes you are right, but before going to sea directly i wish to prepare my self in river and get basics swimming right, i have grip now on database tables, relationships and its structure which is only one of the many things required for a professional application development.

I am making a very basic model of accounting and HR to see how it can be implemented in languages like python and Java also i could convert the major functions and formats that also help me a lot to save my efforts and time.

I have already started with Sqlite made some tables structure and working with their relationships before get to code anything.
 

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
OK - anyway you now have the solution for this particular problem
 

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
i need to add some parameters to creating account in my attached db

I will be adding sub Account value to account in tblAccounts.

before you guided for creating new account function.

Code:
Option Compare Database

Function GetNewAccount(txtMainAccount As Long)
    
    GetNewAccount = Nz(DMax("AccountNo", "tblAccounts", "AccHeaderID = " & txtMainAccount) + 1, CLng([txtMainAccount] & "001"))
    
    Debug.Print txtMainAccount; GetNewAccount
End Function

In above i need to add criteria for subaccount

for subaccount it will be account "001"+"01" for example (main account is 105 then new account value based on dmax 105001) but i need to add if subaccount is selected then (main account 105, new account 001, sub account 01, result should be 10500101 otherwise 105001 in order to understand the criteria i have attached forms and tables.

in tblAccounts their is column name subaccount which will be filled with account number to represent the subaccount of which account it is. in both cases starting of main account will remain same which is 105.
 

Attachments

  • Test1.zip
    128.4 KB · Views: 60

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
OK I've made several changes - see attached

I've modified GetNewAccount so the outcome now depends on whether a boolean value blnSubAC is true. The value is set when the checkbox is ticked/unticked
I think the account details will now do what you want but you need to check

Other points:
1. ALWAYS include the line Option Explicit in EVERY code module
2. Add error handling to every procedure
3. Form frmAddNewAccount has a LOT of superfluous, repeated code.
I've simplified much of this for you.

However if you use an option group containing your 8 buttons, the code would be much simpler still.
I'll leave the rest for you to deal with
 

Attachments

  • mba110_4Feb_CR.zip
    66.9 KB · Views: 61

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
Thanks for you help and guidance.


However, their is some conditions are not correct.

As i said before IF i select subaccount than subaccount number will be added with account number that means if i select account number 105001 in sub account combo box then new account number will be 105001001 but its populating directly without selecting the subaccount which is wrong.

in this breakdown

Main account will be 105 (System fixed account)
subaccount will be 105001 (it is already created by user using you first code)
Need to create account 10500101 (now this code has create)

so it will be like tree

105
105001
10500101


All this reconciled with starting three digits.


without subaccount our original the first code is ok no need to change in that, need changes only when i select the subaccount.

I have already made the function when i select any account in subaccout combo box that account number will be populated in txtSubaccount based on that you have to assign the new account number and not based on txtMainAccount if we are selecting the subaccount, else if we are not selecting the subaccount then our first code is fine for that.
 

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
Yes I understood that as that's precisely why I adapted the function as below

Code:
Function GetNewAccount(txtMainAccount As Long)

    'CR 03/02/2019
    If blnSubAC = True Then
        GetNewAccount = Nz(DMax("AccountNo", "tblAccounts", "AccHeaderID = " & txtMainAccount)  & "01", CLng([txtMainAccount] & "00101"))
    Else
        GetNewAccount = Nz(DMax("AccountNo", "tblAccounts", "AccHeaderID = " & txtMainAccount) + 1, CLng([txtMainAccount] & "001"))
    End If
    
    Debug.Print txtMainAccount; GetNewAccount
End Function


Sub TestNewAccount()
    blnSubAC = True 'False
    GetNewAccount 205
End Sub

I tested it using TestNewAccount for various values and with the Boolean both true and false. I thought it worked. Did you actually check it?
 

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
I have checked and result are not showing correct, may be i am not placing your code write or in event procedure the way you want, for your information i have put this
Code:
=GetNewAccount([txtMainAccount])
in txtNewAccountNo control source, if its wrong then tell me the correct placement.

Nothing is happening after check box it is getting the value directly in txtNewAccountNo.

for example

if you select ASSET in first combo box and then in next combo box you select ACCOUNTS RECEIVABLES then you will see the txtNewAccountNo which is result field is showing full account number including the sub account even i still did not select anything further, and same for others some time showing only the value 01 if it does not hold any account under its main.

Secondly i dont want to depend on check box user can just select and ignore selecting the account from combo box i need cboSubaccount after update event to perform your function and not after check box, because after combo box selection account it will show that account number in txtSubAccount based on this txtNewAccountNo will be provided.

Code:
Private Sub ChkSubAccount_Click()
blnSubAC = Me.ChkSubAccount
If Me.ChkSubAccount = True Then
Me.cboSubAccount.Enabled = True
Else
Me.cboSubAccount.Enabled = False
Me.txtSubAccount = Null
Me.cboSubAccount = Null

End If
End Sub
 

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
I remember doing a huge amount of coding for you on the login form thread.
Sorry but I don't have the time to keep doing all the work for you.

I spent a lot of time improving the code in your database & updating the function.

However I deliberately left you to alter the code in the checkbox yourself.
That way you'll learn more and become more self reliant.

Good luck
 

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
Many many Thanks for your help so far in any question/post is asked by me and you have contributed by any means, i am very well aware of your contributions for anything you have done for me to help.

However, i have make some modifications in your code and procedures and get it done easily.


Code:
Option Compare Database
Option Explicit

Function GetNewAccount(txtMainAccount As Long)

    GetNewAccount = Nz(DMax("AccountNo", "tblAccounts", "AccHeaderID = " & txtMainAccount) + 1, CLng([txtMainAccount] & "001"))
    
    Debug.Print txtMainAccount; GetNewAccount
End Function


Function GetSubAccount(txtMainAccount As Long)

    GetSubAccount = Nz(DMax("AccountNo", "tblAccounts", "AccHeaderID = " & txtMainAccount) & "01", CLng([txtMainAccount] & "00101"))
    
    Debug.Print txtMainAccount; GetSubAccount
End Function

If check box is true and combo box is selected for account then.

Code:
Private Sub cboSubAccount_Click()
Me.txtNewAccountNo = GetSubAccount(Me.txtMainAccount)


After update or selected Type of account in combo box to provide user with new account number.

Code:
Private Sub cboType_AfterUpdate()
Me.txtNewAccountNo = GetNewAccount(Me.txtMainAccount)


if check box is removed/false for sub account.

Code:
Private Sub ChkSubAccount_Click()
If Me.ChkSubAccount = False Then
Me.txtNewAccountNo = GetNewAccount(Me.txtMainAccount)
End If

I hope you dont mind helping/solving the queries here.
 

isladogs

MVP / VIP
Local time
Today, 01:09
Joined
Jan 14, 2017
Messages
18,186
That looks fine to me. I just chose to do the same in one function rather than two in order to reduce the code.

Do look at the other changes I made as you've not commented on those

Good luck with the rest of your project
 

mba_110

Registered User.
Local time
Yesterday, 18:09
Joined
Jan 20, 2015
Messages
280
Hi everyone, I am recalling the post because I face some difficulties in above function.

As you can see in above posts 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 functionality is working correct for this first time only.

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.
 

Attachments

  • Test.zip
    47.8 KB · Views: 56
Last edited:

Users who are viewing this thread

Top Bottom