+ Reply to Thread
Results 1 to 30 of 30

Small help with user form to create a database

  1. #1
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Small help with user form to create a database

    Hello,

    I would like to develop a small database using a userform. The input values are

    1. Date 2. Account Number 3. Nature of Deposit ( Savings,Fixed,Recurring) 4. Amount

    The User form should be able to disable the closing balance of account number that i enter in the input box.

    This is just my beginning to develop a complex database and any help which would enable me to begin would be greatly helpful to me.

    Thank you

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Small help with user form to create a database

    You will need to explain some more. What do you mean by disable the closing balance? In the userform or in the sheet?

    Do you have a sample file that you can upload?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,

    I am sorry for the typo..It's Display and not Disabled. I am trying to create a Banking database. I am herewith attaching the sample file.

    Regards,

    Zaska
    Attached Files Attached Files

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Small help with user form to create a database

    I suggest you look at a database rather than Excel and read up on relational database concepts - especially if you're going to be dealing with a lot of data - you'll end up making making an unholy amount of work for yourself trying to do this in Excel

  5. #5
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Thank you but i am not dealing with lot of data. The database is small and i am experimenting to learn new things with userforms .

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    What Kyle says is correct. However as long as you are aware that this isn't the best approach to creating a database and still wish to achieve the original thread goal, I have no objections to trying to help you.

    I am very busy at the moment so I can't produce this for you this week however I have uploaded a non-working prototype that I have thrown together. (It currently isn't working and only has a entry form not a retrieval form etc. However this should help give you a head start).
    Attached Files Attached Files
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  7. #7
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,

    I have tried the above macro but after entering the data in the userform it is not getting stored in the excel sheet and i am not able to understand what's wrong.

    Thank you

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    As per my post this is a non-working prototype. It is not finished so it is not saving data to the worksheet (amongst other things).

    I am very busy for the next few days but I hope to finish this off for you sometime next week.

  9. #9
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Thank you very much

  10. #10
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,

    Can any one help me?

    Thank you

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Small help with user form to create a database

    Are you using mc84excel's file or your own?

  12. #12
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,

    Mc84excel's file is a prototype. I am not using any file right now.

    Thank you

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Small help with user form to create a database

    So which file should i work on to assist you?

  14. #14
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Thank you very much..You can assist me with the file which will be easy for you..

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Small help with user form to create a database

    You have a file attached in post 3 and post 6. So which one do you need help with? Do you want me to work on your file or carry on with mc84excel's file?

  16. #16
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Please help me with the file in Post 3 i.e my own file.

    Thank you very much.

  17. #17
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    Hello zaska - I was busier then I thought I would be. I will try to have something for you in one weeks time (unless Arlu has solved it for you by then).

  18. #18
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Thank you Very Much

  19. #19
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    A few quick questions:
    1. Is the Place field tied to the account or to the transaction? (Is it where the account holder resides or is it the branch that completed the transaction?)
    2. Under Nature of Deposit - I see you have Loan. Can I assume that Loan will be a withdrawal/will lower account balance?

  20. #20
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,

    1. The place field is tied to the account and not the transaction. It is where the account holder resides.

    2. Loan has no effect on savings. Each member has a loan account and also savings account.

    Interest will be calculated @6% per annum on savings and @ 12 % on Loans. Is it possible to retrieve the account balance on a particular date along with accrued interest portion on savings and accrued interest on loans?

    on savings interest will be paid and on loans interest will be collected from members.

    Thank you very much

  21. #21
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    Quote Originally Posted by zaska View Post
    2. Loan has no effect on savings. Each member has a loan account and also savings account.
    You stated previously that you want to be able to display the closing balance of account number. Will this "closing balance" be the combined total of the loan account and the savings account under this account number? If so, am I right in assuming that the balance of the loan account should be subtracted from the savings balance?

    Also are Fixed and Recurring referring to separate account types or a type of interest? If interest type, then will this be linked to the account type?


    Quote Originally Posted by zaska View Post
    Interest will be calculated @6% per annum on savings and @ 12 % on Loans. Is it possible to retrieve the account balance on a particular date along with accrued interest portion on savings and accrued interest on loans?

    on savings interest will be paid and on loans interest will be collected from members.
    It is certainly possible to do this but unfortunately this is beyond what I can provide for you at the moment. Maybe another forum user can help you with this.

  22. #22
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,

    The closing balance will be separate for Loans and savings for each account number and it should not be subtracted. May be a filter can be applied to select which balance ( Loan/ Savings) should be displayed.

    Fixed and recurring are referring to separate account types only.

    I thank you very much

  23. #23
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    Sorry zaska - a few more questions so that I have a better understanding of your needs:

    1. Just so I have this clear - the account number is tied to the account holder, not the account type? For example - account holder number #1234 could have a Loan account and a Savings account and a Fixed account - but all these accounts would be using the same number? If yes - then how do you determine which account you are referring to? Do you just use account holder number & account type name or do you use account numbers in addition to account holder numbers?
    2. How would you prefer to enter withdrawals? As a negative amount or have two seperate inputs on the form - one for deposits, one for withdrawals?


    I stated previously that I wouldn't take on the interest calculations . However I may be able to set up the excel database so that it would make it easier for future implementation of interest (by yourself or somebody-else) - It depends on how much work is involved to set this up (I am doing this for free you realise!) - see the next few questions:
    1. Is the interest rate fixed or variable? In other words, does the agreed rate remain the steady for the life of the account or could it change over time?
    2. Is the interest straight line or compounding?
    3. When is the interest calculated?
    4. Is the interest paid out or accumulated/added to the account balance? When would it be paid/accumulated - Monthly?

  24. #24
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Hello,
    1. Yes the account number is tied to the account holder and he may have different account types. In the excel sheet I am applying filter. For example if I want to see the collection of fixed deposit then I apply filter to that particular account type. I don’t know if it’s possible in user form.
    2. Separate user form for withdrawls because sometimes I may forget to put the negative sign.
    3. The interest rate is fixed @ 6 % simple interest (straight line interest only) on savings.
    4. Interest is calculated on daily basis @ 6% per annum and is only accumulated and gets added to the account balance monthly. How ever I should be able to know how much is the accumulated interest and how much is the savings as at any particular date during the year.

    I realise that you are doing this for free and i thank you very much. what ever you think you can help me out in your time limits.

    Thank you very much.

  25. #25
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    Quote Originally Posted by zaska View Post
    I would like to develop a small database using a userform. The input values are

    1. Date 2. Account Number 3. Nature of Deposit ( Savings,Fixed,Recurring) 4. Amount

    The User form should be able to disable the closing balance of account number that i enter in the input box.
    See attached. It isn't polished as well as I would like it to be but I've tested it and it meets the original goals for this thread.
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Thank you very much. It's working as desired. I will modify it accordingly.

    Regards,

    Zaska

  27. #27
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    No problems. Just a few tips.
    • Place the BankingDatabase.xlsx in the same folder as the XLSM before running the XLSM.
    • The BankingDatabase.xlsx uploaded contains the sample data you provided. To start with a fresh version, delete the BankingDatabase.XLSX from the folder containing the XLSM, go to m_DB_CreateDatabase in the XLSM and use F5 to run the first sub. (I didn't make this sub as a button to avoid accidents)
    • You can change the name of the system and 'database' file by changing the strSystemName and strExcelDatabaseName values in m_DB_Constants. (You will need to rename the XLSX file after changing the strExcelDatabaseName value)
    • Try to avoid using Excel to edit the data in the "BankingDatabase" directly - always use the XLSM to add data.
    • This is an extremely basic system I have created - as your data grows in complexity, I recommend you consider changing to Access and eventually onto a SQL Database program

  28. #28
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    To give an idea of the possibilities from modifying the code for this database system, I have quickly created a very rough 'Transaction History' feature - see uploaded XLSM.
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Small help with user form to create a database

    Sir,

    Thank you very much for the kind help.It was very useful to me.

  30. #30
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Small help with user form to create a database

    No worries.

    If you want to thank me, you can click on "Add Reputation" icon on the lower left hand corner of one of my posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1