Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

CheckBook Application: Skip Fields and Running Balance

Discussion in 'Information Technology' started by Robert T, Jul 28, 2006.

  1. Robert T

    Robert T
    Expand Collapse
    Guest

    Hello:

    I’ve been using a program called Alpha Five for many years. Most Access
    users never heard of A5 which is much easier to use and more powerful than
    Access. However, on my new job I will have to develop applications in Access.
    Therefore, starting today, I'm beginning to move some of my applications from
    Alpha Five to Access 2003.

    The first one I’m working on is a checkbook application, which has a running
    balance for each transaction and of course an overall current balance for the
    checkbook. I’m having problems with a tabled called tblTRANSACTIONS and I
    would appreciate your help.

    tblTRANSACTIONS has a text field called [Type_Transaction] which is either
    a“debit†or a “creditâ€. I also have two currency fields called [Debits] and
    [Credits]. Two other fields that are relevant for my questions are [Payee]
    and [Run_Bal]. The latter is of course for the running balance.

    First Question:
    ------------------
    In Alpha Five there are Field Rules, one of which is called a SKIP Rule. For
    example, if the [Type_Transaction] is a CREDIT, I want to skip the [Debits]
    and [Payee] fields. In the SKIP field rules for both [Debits] and [Payee], I
    would essentially say Type_Transaction =†Credit†. If that's true, the user
    cannot enter any data into those fields. I don’t want to allow the user to
    inadvertently enter anything into those fields because they would obviously
    be mistakes.

    Is there an equivalent in Access or can I create a workaround that does the
    same thing? If not, I guess I’ll have to divided tblTRANSACTIONS into two
    separate tables, one table for Checks and the second table for
    Deposits/Credits

    Second Question:
    ------------------
    Running a Query and updating it in Access to give me the Current checkbook
    overall Balance is pretty straightforward and easy. However, in Alpha Five I
    can use their Xbasic programming Language to write a script that calculates
    the running balance after each transaction. Can I use Visual Basic to
    accomplish the same task?

    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    Haryana Invites Applications from Minority Students for Scholarship Community Out-Reach Aug 12, 2010
    UK UK lifts temporary suspension on visa applications in N India Breaking News Jul 25, 2010
    Leisure Easter Egg - Bota Singh's Job Application Business, Lifestyle & Leisure Apr 2, 2010
    Legal NRIs will soon be able to file RTI applications online Breaking News Feb 16, 2010
    SALDEF Accepting Applications for 2009 Summer Internship Program Sikh Youth Jan 31, 2009

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    It sounds as though you're planning on working directly with the tables.
    That's not recommended: instead, you should have a form for input purposes.

    In a form, it would be fairly straight-forward to implement what you want:
    you'd put code in the form's Current event to modify which controls are
    enabled depending on the value of Type_Transaction.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    news:6AA4E300-2F9A-4E66-8F03-E641185D0DC8@microsoft.com...
    > Hello:
    >
    > I've been using a program called Alpha Five for many years. Most Access
    > users never heard of A5 which is much easier to use and more powerful than
    > Access. However, on my new job I will have to develop applications in

    Access.
    > Therefore, starting today, I'm beginning to move some of my applications

    from
    > Alpha Five to Access 2003.
    >
    > The first one I'm working on is a checkbook application, which has a

    running
    > balance for each transaction and of course an overall current balance for

    the
    > checkbook. I'm having problems with a tabled called tblTRANSACTIONS and I
    > would appreciate your help.
    >
    > tblTRANSACTIONS has a text field called [Type_Transaction] which is either
    > a"debit" or a "credit". I also have two currency fields called [Debits]

    and
    > [Credits]. Two other fields that are relevant for my questions are [Payee]
    > and [Run_Bal]. The latter is of course for the running balance.
    >
    > First Question:
    > ------------------
    > In Alpha Five there are Field Rules, one of which is called a SKIP Rule.

    For
    > example, if the [Type_Transaction] is a CREDIT, I want to skip the

    [Debits]
    > and [Payee] fields. In the SKIP field rules for both [Debits] and [Payee],

    I
    > would essentially say Type_Transaction =" Credit" . If that's true, the

    user
    > cannot enter any data into those fields. I don't want to allow the user to
    > inadvertently enter anything into those fields because they would

    obviously
    > be mistakes.
    >
    > Is there an equivalent in Access or can I create a workaround that does

    the
    > same thing? If not, I guess I'll have to divided tblTRANSACTIONS into two
    > separate tables, one table for Checks and the second table for
    > Deposits/Credits
    >
    > Second Question:
    > ------------------
    > Running a Query and updating it in Access to give me the Current checkbook
    > overall Balance is pretty straightforward and easy. However, in Alpha Five

    I
    > can use their Xbasic programming Language to write a script that

    calculates
    > the running balance after each transaction. Can I use Visual Basic to
    > accomplish the same task?
    >
    > Thanks
     
  4. Robert T

    Robert T
    Expand Collapse
    Guest

    Hi Doug:

    Thanks for the prompt response.

    Since I'm relatively new to Access, would you be kind enough to give me an
    example of how I can do such in Form Design? For example, if the
    Type_Transaction is a "Debit", I want to ensure the Credits Currency field
    will remain blank?

    Second, do you think using one Transaction Table is a good option or should
    I divide it into two tables, Checks and Deposits/Credits, that would ensure
    there are no NULL fields? That would obviously remove the need to skip fields
    in this particular application.

    Third, it's really nice setting the Skip Field Rule in the table because it
    will be enforced no matter what data entry form is used. Just a thought.

    Fourth, I have to use Firefox to post my questions/replies. When I click on
    Post a Question or Reply in Internet Explorer, I get something similar to the
    Windows hourglass and I never see the Post Question or Reply form. I know
    this isn't an Access question but do you or anyone else have any idea why
    that is happening in IE?

    Thanks

    "Douglas J Steele" wrote:

    > It sounds as though you're planning on working directly with the tables.
    > That's not recommended: instead, you should have a form for input purposes.
    >
    > In a form, it would be fairly straight-forward to implement what you want:
    > you'd put code in the form's Current event to modify which controls are
    > enabled depending on the value of Type_Transaction.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > news:6AA4E300-2F9A-4E66-8F03-E641185D0DC8@microsoft.com...
    > > Hello:
    > >
    > > I've been using a program called Alpha Five for many years. Most Access
    > > users never heard of A5 which is much easier to use and more powerful than
    > > Access. However, on my new job I will have to develop applications in

    > Access.
    > > Therefore, starting today, I'm beginning to move some of my applications

    > from
    > > Alpha Five to Access 2003.
    > >
    > > The first one I'm working on is a checkbook application, which has a

    > running
    > > balance for each transaction and of course an overall current balance for

    > the
    > > checkbook. I'm having problems with a tabled called tblTRANSACTIONS and I
    > > would appreciate your help.
    > >
    > > tblTRANSACTIONS has a text field called [Type_Transaction] which is either
    > > a"debit" or a "credit". I also have two currency fields called [Debits]

    > and
    > > [Credits]. Two other fields that are relevant for my questions are [Payee]
    > > and [Run_Bal]. The latter is of course for the running balance.
    > >
    > > First Question:
    > > ------------------
    > > In Alpha Five there are Field Rules, one of which is called a SKIP Rule.

    > For
    > > example, if the [Type_Transaction] is a CREDIT, I want to skip the

    > [Debits]
    > > and [Payee] fields. In the SKIP field rules for both [Debits] and [Payee],

    > I
    > > would essentially say Type_Transaction =" Credit" . If that's true, the

    > user
    > > cannot enter any data into those fields. I don't want to allow the user to
    > > inadvertently enter anything into those fields because they would

    > obviously
    > > be mistakes.
    > >
    > > Is there an equivalent in Access or can I create a workaround that does

    > the
    > > same thing? If not, I guess I'll have to divided tblTRANSACTIONS into two
    > > separate tables, one table for Checks and the second table for
    > > Deposits/Credits
    > >
    > > Second Question:
    > > ------------------
    > > Running a Query and updating it in Access to give me the Current checkbook
    > > overall Balance is pretty straightforward and easy. However, in Alpha Five

    > I
    > > can use their Xbasic programming Language to write a script that

    > calculates
    > > the running balance after each transaction. Can I use Visual Basic to
    > > accomplish the same task?
    > >
    > > Thanks

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > Hi Doug:
    >
    > Thanks for the prompt response.
    >
    > Since I'm relatively new to Access, would you be kind enough to give me an
    > example of how I can do such in Form Design? For example, if the
    > Type_Transaction is a "Debit", I want to ensure the Credits Currency field
    > will remain blank?


    It will depend on how your form is structured, but try something like the
    following:

    Private Sub Form_Current()

    If Me!txtType_Transaction = "Debit" Then
    Me![Credits Currency].Enabled = False
    Me![Debits Currenty].Enabled = True
    Else
    Me![Credits Currency].Enabled = True
    Me![Debits Currenty].Enabled = False
    End If

    End Sub

    Actually, that can be simplied to:

    Private Sub Form_Current()

    Me![Credits Currency].Enabled = (Me!txtType_Transaction <> "Debit")
    Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")

    End Sub


    > Second, do you think using one Transaction Table is a good option or

    should
    > I divide it into two tables, Checks and Deposits/Credits, that would

    ensure
    > there are no NULL fields? That would obviously remove the need to skip

    fields
    > in this particular application.


    I don't see why you'd need Null fields. I don't believe you need to
    distinguish between Credits and Debits in your table: the sign of the entry
    should indicate for you.

    Just because you've got two fields on your form doesn't mean you must have
    two fields in your table.

    > Third, it's really nice setting the Skip Field Rule in the table because

    it
    > will be enforced no matter what data entry form is used. Just a thought.


    Perhaps, but there isn't, so there's nothing to be gained by going down that
    path...

    > Fourth, I have to use Firefox to post my questions/replies. When I click

    on
    > Post a Question or Reply in Internet Explorer, I get something similar to

    the
    > Windows hourglass and I never see the Post Question or Reply form. I know
    > this isn't an Access question but do you or anyone else have any idea why
    > that is happening in IE?


    Sorry, I never use the web interface.

    > Thanks


    Is this just something you're building for the heck of it? Strikes me that
    buying Quicken or Money would be a lot faster.

    You might also try downloading the Accounts ledger database from
    http://office.microsoft.com/en-au/templates/TC010175341033.aspx


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)
     
  6. Robert T

    Robert T
    Expand Collapse
    Guest

    Hi Doug:

    Once again, thanks for the prompt response showing me how to enable or
    disable a field on a form. I just knew that type of option had to be
    available.

    As for your question about why I'm doing this, I already have a checking
    application in Alpha Five that works perfectly, plus I regularly use Quicken.
    I'm not going through this process because I want a checking application in
    Access. Since I need Access skills for my new job, I'm doing this strictly as
    a learning experience. The skills I pick up by designing this application can
    be applied to many other types of applications. Doug, I just happened to pick
    the checking application randomly.

    I realize you can make a currency transaction positive or negative, but
    most financial applications usually have separate fields for Debits and
    Credits. Do you think using one field is a better idea?

    By the way, that brings me back to my previous question. What do you think
    of the idea of creating two separate tables for transactions, one to record
    checks and a 2nd one to record only deposits/credits? As I stated before,
    that would eliminate null fields. Or do you prefer placing all transactions
    in one table?

    By the way, any thoughts on my other question reference a running balance
    field for each transaction?

    Thanks,
    RT

    "Douglas J Steele" wrote:

    > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > Hi Doug:
    > >
    > > Thanks for the prompt response.
    > >
    > > Since I'm relatively new to Access, would you be kind enough to give me an
    > > example of how I can do such in Form Design? For example, if the
    > > Type_Transaction is a "Debit", I want to ensure the Credits Currency field
    > > will remain blank?

    >
    > It will depend on how your form is structured, but try something like the
    > following:
    >
    > Private Sub Form_Current()
    >
    > If Me!txtType_Transaction = "Debit" Then
    > Me![Credits Currency].Enabled = False
    > Me![Debits Currenty].Enabled = True
    > Else
    > Me![Credits Currency].Enabled = True
    > Me![Debits Currenty].Enabled = False
    > End If
    >
    > End Sub
    >
    > Actually, that can be simplied to:
    >
    > Private Sub Form_Current()
    >
    > Me![Credits Currency].Enabled = (Me!txtType_Transaction <> "Debit")
    > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
    >
    > End Sub
    >
    >
    > > Second, do you think using one Transaction Table is a good option or

    > should
    > > I divide it into two tables, Checks and Deposits/Credits, that would

    > ensure
    > > there are no NULL fields? That would obviously remove the need to skip

    > fields
    > > in this particular application.

    >
    > I don't see why you'd need Null fields. I don't believe you need to
    > distinguish between Credits and Debits in your table: the sign of the entry
    > should indicate for you.
    >
    > Just because you've got two fields on your form doesn't mean you must have
    > two fields in your table.
    >
    > > Third, it's really nice setting the Skip Field Rule in the table because

    > it
    > > will be enforced no matter what data entry form is used. Just a thought.

    >
    > Perhaps, but there isn't, so there's nothing to be gained by going down that
    > path...
    >
    > > Fourth, I have to use Firefox to post my questions/replies. When I click

    > on
    > > Post a Question or Reply in Internet Explorer, I get something similar to

    > the
    > > Windows hourglass and I never see the Post Question or Reply form. I know
    > > this isn't an Access question but do you or anyone else have any idea why
    > > that is happening in IE?

    >
    > Sorry, I never use the web interface.
    >
    > > Thanks

    >
    > Is this just something you're building for the heck of it? Strikes me that
    > buying Quicken or Money would be a lot faster.
    >
    > You might also try downloading the Accounts ledger database from
    > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Personally, since I can't think of different attributes between Debits and
    Credits, I'd be loathe to treat them as separate entities. That implies only
    one table. (I hinted at this before when I said I couldn't see why there
    would be null fields)

    I also don't think you should be storing a calculated value (the running
    balance) in a table. As fellow Access MVP John Vinson likes to say "Storing
    calculated data generally accomplishes only three things: it wastes disk
    space, it wastes time (a disk fetch is much slower than almost any
    reasonable calculation), and it risks data validity, since once it's stored
    in a table either the Total or one of the fields that goes into the total
    may be changed, making the value WRONG."

    It is possible (although usually inefficient) to calculate a running balance
    in a query. For example, you can use the DSum function to calculate the sum
    of all records prior to the current record.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
    > Hi Doug:
    >
    > Once again, thanks for the prompt response showing me how to enable or
    > disable a field on a form. I just knew that type of option had to be
    > available.
    >
    > As for your question about why I'm doing this, I already have a checking
    > application in Alpha Five that works perfectly, plus I regularly use

    Quicken.
    > I'm not going through this process because I want a checking application

    in
    > Access. Since I need Access skills for my new job, I'm doing this strictly

    as
    > a learning experience. The skills I pick up by designing this application

    can
    > be applied to many other types of applications. Doug, I just happened to

    pick
    > the checking application randomly.
    >
    > I realize you can make a currency transaction positive or negative, but
    > most financial applications usually have separate fields for Debits and
    > Credits. Do you think using one field is a better idea?
    >
    > By the way, that brings me back to my previous question. What do you think
    > of the idea of creating two separate tables for transactions, one to

    record
    > checks and a 2nd one to record only deposits/credits? As I stated before,
    > that would eliminate null fields. Or do you prefer placing all

    transactions
    > in one table?
    >
    > By the way, any thoughts on my other question reference a running balance
    > field for each transaction?
    >
    > Thanks,
    > RT
    >
    > "Douglas J Steele" wrote:
    >
    > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > > Hi Doug:
    > > >
    > > > Thanks for the prompt response.
    > > >
    > > > Since I'm relatively new to Access, would you be kind enough to give

    me an
    > > > example of how I can do such in Form Design? For example, if the
    > > > Type_Transaction is a "Debit", I want to ensure the Credits Currency

    field
    > > > will remain blank?

    > >
    > > It will depend on how your form is structured, but try something like

    the
    > > following:
    > >
    > > Private Sub Form_Current()
    > >
    > > If Me!txtType_Transaction = "Debit" Then
    > > Me![Credits Currency].Enabled = False
    > > Me![Debits Currenty].Enabled = True
    > > Else
    > > Me![Credits Currency].Enabled = True
    > > Me![Debits Currenty].Enabled = False
    > > End If
    > >
    > > End Sub
    > >
    > > Actually, that can be simplied to:
    > >
    > > Private Sub Form_Current()
    > >
    > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <> "Debit")
    > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
    > >
    > > End Sub
    > >
    > >
    > > > Second, do you think using one Transaction Table is a good option or

    > > should
    > > > I divide it into two tables, Checks and Deposits/Credits, that would

    > > ensure
    > > > there are no NULL fields? That would obviously remove the need to skip

    > > fields
    > > > in this particular application.

    > >
    > > I don't see why you'd need Null fields. I don't believe you need to
    > > distinguish between Credits and Debits in your table: the sign of the

    entry
    > > should indicate for you.
    > >
    > > Just because you've got two fields on your form doesn't mean you must

    have
    > > two fields in your table.
    > >
    > > > Third, it's really nice setting the Skip Field Rule in the table

    because
    > > it
    > > > will be enforced no matter what data entry form is used. Just a

    thought.
    > >
    > > Perhaps, but there isn't, so there's nothing to be gained by going down

    that
    > > path...
    > >
    > > > Fourth, I have to use Firefox to post my questions/replies. When I

    click
    > > on
    > > > Post a Question or Reply in Internet Explorer, I get something similar

    to
    > > the
    > > > Windows hourglass and I never see the Post Question or Reply form. I

    know
    > > > this isn't an Access question but do you or anyone else have any idea

    why
    > > > that is happening in IE?

    > >
    > > Sorry, I never use the web interface.
    > >
    > > > Thanks

    > >
    > > Is this just something you're building for the heck of it? Strikes me

    that
    > > buying Quicken or Money would be a lot faster.
    > >
    > > You might also try downloading the Accounts ledger database from
    > > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > >
    > >
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    BTW, did you check the downloadable database I referenced earlier?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23HZiFcAhGHA.4712@TK2MSFTNGP05.phx.gbl...
    > Personally, since I can't think of different attributes between Debits and
    > Credits, I'd be loathe to treat them as separate entities. That implies

    only
    > one table. (I hinted at this before when I said I couldn't see why there
    > would be null fields)
    >
    > I also don't think you should be storing a calculated value (the running
    > balance) in a table. As fellow Access MVP John Vinson likes to say

    "Storing
    > calculated data generally accomplishes only three things: it wastes disk
    > space, it wastes time (a disk fetch is much slower than almost any
    > reasonable calculation), and it risks data validity, since once it's

    stored
    > in a table either the Total or one of the fields that goes into the total
    > may be changed, making the value WRONG."
    >
    > It is possible (although usually inefficient) to calculate a running

    balance
    > in a query. For example, you can use the DSum function to calculate the

    sum
    > of all records prior to the current record.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
    > > Hi Doug:
    > >
    > > Once again, thanks for the prompt response showing me how to enable or
    > > disable a field on a form. I just knew that type of option had to be
    > > available.
    > >
    > > As for your question about why I'm doing this, I already have a checking
    > > application in Alpha Five that works perfectly, plus I regularly use

    > Quicken.
    > > I'm not going through this process because I want a checking application

    > in
    > > Access. Since I need Access skills for my new job, I'm doing this

    strictly
    > as
    > > a learning experience. The skills I pick up by designing this

    application
    > can
    > > be applied to many other types of applications. Doug, I just happened to

    > pick
    > > the checking application randomly.
    > >
    > > I realize you can make a currency transaction positive or negative, but
    > > most financial applications usually have separate fields for Debits and
    > > Credits. Do you think using one field is a better idea?
    > >
    > > By the way, that brings me back to my previous question. What do you

    think
    > > of the idea of creating two separate tables for transactions, one to

    > record
    > > checks and a 2nd one to record only deposits/credits? As I stated

    before,
    > > that would eliminate null fields. Or do you prefer placing all

    > transactions
    > > in one table?
    > >
    > > By the way, any thoughts on my other question reference a running

    balance
    > > field for each transaction?
    > >
    > > Thanks,
    > > RT
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > > > Hi Doug:
    > > > >
    > > > > Thanks for the prompt response.
    > > > >
    > > > > Since I'm relatively new to Access, would you be kind enough to give

    > me an
    > > > > example of how I can do such in Form Design? For example, if the
    > > > > Type_Transaction is a "Debit", I want to ensure the Credits Currency

    > field
    > > > > will remain blank?
    > > >
    > > > It will depend on how your form is structured, but try something like

    > the
    > > > following:
    > > >
    > > > Private Sub Form_Current()
    > > >
    > > > If Me!txtType_Transaction = "Debit" Then
    > > > Me![Credits Currency].Enabled = False
    > > > Me![Debits Currenty].Enabled = True
    > > > Else
    > > > Me![Credits Currency].Enabled = True
    > > > Me![Debits Currenty].Enabled = False
    > > > End If
    > > >
    > > > End Sub
    > > >
    > > > Actually, that can be simplied to:
    > > >
    > > > Private Sub Form_Current()
    > > >
    > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <> "Debit")
    > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
    > > >
    > > > End Sub
    > > >
    > > >
    > > > > Second, do you think using one Transaction Table is a good option or
    > > > should
    > > > > I divide it into two tables, Checks and Deposits/Credits, that would
    > > > ensure
    > > > > there are no NULL fields? That would obviously remove the need to

    skip
    > > > fields
    > > > > in this particular application.
    > > >
    > > > I don't see why you'd need Null fields. I don't believe you need to
    > > > distinguish between Credits and Debits in your table: the sign of the

    > entry
    > > > should indicate for you.
    > > >
    > > > Just because you've got two fields on your form doesn't mean you must

    > have
    > > > two fields in your table.
    > > >
    > > > > Third, it's really nice setting the Skip Field Rule in the table

    > because
    > > > it
    > > > > will be enforced no matter what data entry form is used. Just a

    > thought.
    > > >
    > > > Perhaps, but there isn't, so there's nothing to be gained by going

    down
    > that
    > > > path...
    > > >
    > > > > Fourth, I have to use Firefox to post my questions/replies. When I

    > click
    > > > on
    > > > > Post a Question or Reply in Internet Explorer, I get something

    similar
    > to
    > > > the
    > > > > Windows hourglass and I never see the Post Question or Reply form. I

    > know
    > > > > this isn't an Access question but do you or anyone else have any

    idea
    > why
    > > > > that is happening in IE?
    > > >
    > > > Sorry, I never use the web interface.
    > > >
    > > > > Thanks
    > > >
    > > > Is this just something you're building for the heck of it? Strikes me

    > that
    > > > buying Quicken or Money would be a lot faster.
    > > >
    > > > You might also try downloading the Accounts ledger database from
    > > > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > >
    > > >

    >
    >
     
  9. Robert T

    Robert T
    Expand Collapse
    Guest

    Hello Doug:

    Yes I did download the application you mentioned earlier and it seems like
    pretty basic stuff, I didn’t find anything in there that I thought was
    enlightening. Maybe I missed something in there so I’ll definitely take
    another look at the application later. Was there anything specific you wanted
    me to check out?

    In general I agree with both you and John Vinson about storing calculated
    values in the table, however, let me add something different to the equation.
    First, I like the idea of a running balance, that’s obviously what Money and
    Quicken do and I find that helps. So I worked hard and found a way to avoid
    the problem you described.

    I used Xbasic [Alpha Five] to update all of the relevant records in the
    table when a value is changed. So if the user goes back to an old record and
    changes the amount of a check or deposit, that will change the running
    balance for that record and every record that follows. I therefore utilized
    a script that goes back to the previous record of the one that was changed,
    captures the previous balance, and then the script recalculates the running
    balance starting with the changed record, updating all subsequent records in
    the table. It took a long time to figure out how to do such, but I’m sure
    that with a little experience I could do such with Visual Basic.

    Robert


    "Douglas J Steele" wrote:

    > Personally, since I can't think of different attributes between Debits and
    > Credits, I'd be loathe to treat them as separate entities. That implies only
    > one table. (I hinted at this before when I said I couldn't see why there
    > would be null fields)
    >
    > I also don't think you should be storing a calculated value (the running
    > balance) in a table. As fellow Access MVP John Vinson likes to say "Storing
    > calculated data generally accomplishes only three things: it wastes disk
    > space, it wastes time (a disk fetch is much slower than almost any
    > reasonable calculation), and it risks data validity, since once it's stored
    > in a table either the Total or one of the fields that goes into the total
    > may be changed, making the value WRONG."
    >
    > It is possible (although usually inefficient) to calculate a running balance
    > in a query. For example, you can use the DSum function to calculate the sum
    > of all records prior to the current record.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
    > > Hi Doug:
    > >
    > > Once again, thanks for the prompt response showing me how to enable or
    > > disable a field on a form. I just knew that type of option had to be
    > > available.
    > >
    > > As for your question about why I'm doing this, I already have a checking
    > > application in Alpha Five that works perfectly, plus I regularly use

    > Quicken.
    > > I'm not going through this process because I want a checking application

    > in
    > > Access. Since I need Access skills for my new job, I'm doing this strictly

    > as
    > > a learning experience. The skills I pick up by designing this application

    > can
    > > be applied to many other types of applications. Doug, I just happened to

    > pick
    > > the checking application randomly.
    > >
    > > I realize you can make a currency transaction positive or negative, but
    > > most financial applications usually have separate fields for Debits and
    > > Credits. Do you think using one field is a better idea?
    > >
    > > By the way, that brings me back to my previous question. What do you think
    > > of the idea of creating two separate tables for transactions, one to

    > record
    > > checks and a 2nd one to record only deposits/credits? As I stated before,
    > > that would eliminate null fields. Or do you prefer placing all

    > transactions
    > > in one table?
    > >
    > > By the way, any thoughts on my other question reference a running balance
    > > field for each transaction?
    > >
    > > Thanks,
    > > RT
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > > > Hi Doug:
    > > > >
    > > > > Thanks for the prompt response.
    > > > >
    > > > > Since I'm relatively new to Access, would you be kind enough to give

    > me an
    > > > > example of how I can do such in Form Design? For example, if the
    > > > > Type_Transaction is a "Debit", I want to ensure the Credits Currency

    > field
    > > > > will remain blank?
    > > >
    > > > It will depend on how your form is structured, but try something like

    > the
    > > > following:
    > > >
    > > > Private Sub Form_Current()
    > > >
    > > > If Me!txtType_Transaction = "Debit" Then
    > > > Me![Credits Currency].Enabled = False
    > > > Me![Debits Currenty].Enabled = True
    > > > Else
    > > > Me![Credits Currency].Enabled = True
    > > > Me![Debits Currenty].Enabled = False
    > > > End If
    > > >
    > > > End Sub
    > > >
    > > > Actually, that can be simplied to:
    > > >
    > > > Private Sub Form_Current()
    > > >
    > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <> "Debit")
    > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
    > > >
    > > > End Sub
    > > >
    > > >
    > > > > Second, do you think using one Transaction Table is a good option or
    > > > should
    > > > > I divide it into two tables, Checks and Deposits/Credits, that would
    > > > ensure
    > > > > there are no NULL fields? That would obviously remove the need to skip
    > > > fields
    > > > > in this particular application.
    > > >
    > > > I don't see why you'd need Null fields. I don't believe you need to
    > > > distinguish between Credits and Debits in your table: the sign of the

    > entry
    > > > should indicate for you.
    > > >
    > > > Just because you've got two fields on your form doesn't mean you must

    > have
    > > > two fields in your table.
    > > >
    > > > > Third, it's really nice setting the Skip Field Rule in the table

    > because
    > > > it
    > > > > will be enforced no matter what data entry form is used. Just a

    > thought.
    > > >
    > > > Perhaps, but there isn't, so there's nothing to be gained by going down

    > that
    > > > path...
    > > >
    > > > > Fourth, I have to use Firefox to post my questions/replies. When I

    > click
    > > > on
    > > > > Post a Question or Reply in Internet Explorer, I get something similar

    > to
    > > > the
    > > > > Windows hourglass and I never see the Post Question or Reply form. I

    > know
    > > > > this isn't an Access question but do you or anyone else have any idea

    > why
    > > > > that is happening in IE?
    > > >
    > > > Sorry, I never use the web interface.
    > > >
    > > > > Thanks
    > > >
    > > > Is this just something you're building for the heck of it? Strikes me

    > that
    > > > buying Quicken or Money would be a lot faster.
    > > >
    > > > You might also try downloading the Accounts ledger database from
    > > > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  10. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    To be honest, I haven't looked at the sample database myself (and I can't,
    because I'm limited to Access 97 at the moment), but I did notice that it
    does what you're trying to do.

    Just because Money and Quicken display a running balance does not
    necessarily mean that it stores that running balance.

    While you certainly could replicate what you did in Alpha Five using VBA in
    Access, I'd recommend very strongly not to. It's fine and dandy to say that
    you're going to run the code every time the user changes something, but what
    happens if for some reason you use a query to update the data rather than
    using the form? Perhaps Alpha Five has triggers, but Access doesn't.

    If you want a running balance, it's trivial to do in a report (look at the
    RunningSum property that appears in the text box control on reports). For a
    form, I feel it's much safer to create a running sum in a query, and use
    that query as the basis for the report (rather than a table).

    If your table includes a field that represents the Date/Time when the entry
    was posted to table, you can calculate the running sum in the query as
    DSum("MyField", "MyTable", "MyDateTimeField <=" & CDbl([MyDateTimeField]))

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    news:4F3058F8-AA01-4BBF-82B7-FA196AFA9517@microsoft.com...
    > Hello Doug:
    >
    > Yes I did download the application you mentioned earlier and it seems like
    > pretty basic stuff, I didn't find anything in there that I thought was
    > enlightening. Maybe I missed something in there so I'll definitely take
    > another look at the application later. Was there anything specific you

    wanted
    > me to check out?
    >
    > In general I agree with both you and John Vinson about storing calculated
    > values in the table, however, let me add something different to the

    equation.
    > First, I like the idea of a running balance, that's obviously what Money

    and
    > Quicken do and I find that helps. So I worked hard and found a way to

    avoid
    > the problem you described.
    >
    > I used Xbasic [Alpha Five] to update all of the relevant records in the
    > table when a value is changed. So if the user goes back to an old record

    and
    > changes the amount of a check or deposit, that will change the running
    > balance for that record and every record that follows. I therefore

    utilized
    > a script that goes back to the previous record of the one that was

    changed,
    > captures the previous balance, and then the script recalculates the

    running
    > balance starting with the changed record, updating all subsequent records

    in
    > the table. It took a long time to figure out how to do such, but I'm sure
    > that with a little experience I could do such with Visual Basic.
    >
    > Robert
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > Personally, since I can't think of different attributes between Debits

    and
    > > Credits, I'd be loathe to treat them as separate entities. That implies

    only
    > > one table. (I hinted at this before when I said I couldn't see why there
    > > would be null fields)
    > >
    > > I also don't think you should be storing a calculated value (the running
    > > balance) in a table. As fellow Access MVP John Vinson likes to say

    "Storing
    > > calculated data generally accomplishes only three things: it wastes disk
    > > space, it wastes time (a disk fetch is much slower than almost any
    > > reasonable calculation), and it risks data validity, since once it's

    stored
    > > in a table either the Total or one of the fields that goes into the

    total
    > > may be changed, making the value WRONG."
    > >
    > > It is possible (although usually inefficient) to calculate a running

    balance
    > > in a query. For example, you can use the DSum function to calculate the

    sum
    > > of all records prior to the current record.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
    > > > Hi Doug:
    > > >
    > > > Once again, thanks for the prompt response showing me how to enable or
    > > > disable a field on a form. I just knew that type of option had to be
    > > > available.
    > > >
    > > > As for your question about why I'm doing this, I already have a

    checking
    > > > application in Alpha Five that works perfectly, plus I regularly use

    > > Quicken.
    > > > I'm not going through this process because I want a checking

    application
    > > in
    > > > Access. Since I need Access skills for my new job, I'm doing this

    strictly
    > > as
    > > > a learning experience. The skills I pick up by designing this

    application
    > > can
    > > > be applied to many other types of applications. Doug, I just happened

    to
    > > pick
    > > > the checking application randomly.
    > > >
    > > > I realize you can make a currency transaction positive or negative,

    but
    > > > most financial applications usually have separate fields for Debits

    and
    > > > Credits. Do you think using one field is a better idea?
    > > >
    > > > By the way, that brings me back to my previous question. What do you

    think
    > > > of the idea of creating two separate tables for transactions, one to

    > > record
    > > > checks and a 2nd one to record only deposits/credits? As I stated

    before,
    > > > that would eliminate null fields. Or do you prefer placing all

    > > transactions
    > > > in one table?
    > > >
    > > > By the way, any thoughts on my other question reference a running

    balance
    > > > field for each transaction?
    > > >
    > > > Thanks,
    > > > RT
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > > > > Hi Doug:
    > > > > >
    > > > > > Thanks for the prompt response.
    > > > > >
    > > > > > Since I'm relatively new to Access, would you be kind enough to

    give
    > > me an
    > > > > > example of how I can do such in Form Design? For example, if the
    > > > > > Type_Transaction is a "Debit", I want to ensure the Credits

    Currency
    > > field
    > > > > > will remain blank?
    > > > >
    > > > > It will depend on how your form is structured, but try something

    like
    > > the
    > > > > following:
    > > > >
    > > > > Private Sub Form_Current()
    > > > >
    > > > > If Me!txtType_Transaction = "Debit" Then
    > > > > Me![Credits Currency].Enabled = False
    > > > > Me![Debits Currenty].Enabled = True
    > > > > Else
    > > > > Me![Credits Currency].Enabled = True
    > > > > Me![Debits Currenty].Enabled = False
    > > > > End If
    > > > >
    > > > > End Sub
    > > > >
    > > > > Actually, that can be simplied to:
    > > > >
    > > > > Private Sub Form_Current()
    > > > >
    > > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <>

    "Debit")
    > > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
    > > > >
    > > > > End Sub
    > > > >
    > > > >
    > > > > > Second, do you think using one Transaction Table is a good option

    or
    > > > > should
    > > > > > I divide it into two tables, Checks and Deposits/Credits, that

    would
    > > > > ensure
    > > > > > there are no NULL fields? That would obviously remove the need to

    skip
    > > > > fields
    > > > > > in this particular application.
    > > > >
    > > > > I don't see why you'd need Null fields. I don't believe you need to
    > > > > distinguish between Credits and Debits in your table: the sign of

    the
    > > entry
    > > > > should indicate for you.
    > > > >
    > > > > Just because you've got two fields on your form doesn't mean you

    must
    > > have
    > > > > two fields in your table.
    > > > >
    > > > > > Third, it's really nice setting the Skip Field Rule in the table

    > > because
    > > > > it
    > > > > > will be enforced no matter what data entry form is used. Just a

    > > thought.
    > > > >
    > > > > Perhaps, but there isn't, so there's nothing to be gained by going

    down
    > > that
    > > > > path...
    > > > >
    > > > > > Fourth, I have to use Firefox to post my questions/replies. When I

    > > click
    > > > > on
    > > > > > Post a Question or Reply in Internet Explorer, I get something

    similar
    > > to
    > > > > the
    > > > > > Windows hourglass and I never see the Post Question or Reply form.

    I
    > > know
    > > > > > this isn't an Access question but do you or anyone else have any

    idea
    > > why
    > > > > > that is happening in IE?
    > > > >
    > > > > Sorry, I never use the web interface.
    > > > >
    > > > > > Thanks
    > > > >
    > > > > Is this just something you're building for the heck of it? Strikes

    me
    > > that
    > > > > buying Quicken or Money would be a lot faster.
    > > > >
    > > > > You might also try downloading the Accounts ledger database from
    > > > > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  11. Robert T

    Robert T
    Expand Collapse
    Guest

    Hello Douglas:

    OK, you've convinced me, I won't store the running balance in the table.
    That will definitely save me a lot of time in terms of writing a complex
    script for a trivial benefit.

    What did you mean when you said,
    [just because Money and Quicken display a running balance does not
    necessarily mean that it stores that running balance.]

    If they aren't storing the running balance, what are they doing?

    I will definitely look RunningSum property of a report. I haven't tried it
    yet but it does indeed look as if it's pretty easy to implement.

    Thanks so much for taking the time to answer my questions. Now if I can only
    figure out why I can't use IE to pose questions and/or reply, I'll feel a
    little better.

    Robert

    "Douglas J Steele" wrote:

    > To be honest, I haven't looked at the sample database myself (and I can't,
    > because I'm limited to Access 97 at the moment), but I did notice that it
    > does what you're trying to do.
    >
    > Just because Money and Quicken display a running balance does not
    > necessarily mean that it stores that running balance.
    >
    > While you certainly could replicate what you did in Alpha Five using VBA in
    > Access, I'd recommend very strongly not to. It's fine and dandy to say that
    > you're going to run the code every time the user changes something, but what
    > happens if for some reason you use a query to update the data rather than
    > using the form? Perhaps Alpha Five has triggers, but Access doesn't.
    >
    > If you want a running balance, it's trivial to do in a report (look at the
    > RunningSum property that appears in the text box control on reports). For a
    > form, I feel it's much safer to create a running sum in a query, and use
    > that query as the basis for the report (rather than a table).
    >
    > If your table includes a field that represents the Date/Time when the entry
    > was posted to table, you can calculate the running sum in the query as
    > DSum("MyField", "MyTable", "MyDateTimeField <=" & CDbl([MyDateTimeField]))
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > news:4F3058F8-AA01-4BBF-82B7-FA196AFA9517@microsoft.com...
    > > Hello Doug:
    > >
    > > Yes I did download the application you mentioned earlier and it seems like
    > > pretty basic stuff, I didn't find anything in there that I thought was
    > > enlightening. Maybe I missed something in there so I'll definitely take
    > > another look at the application later. Was there anything specific you

    > wanted
    > > me to check out?
    > >
    > > In general I agree with both you and John Vinson about storing calculated
    > > values in the table, however, let me add something different to the

    > equation.
    > > First, I like the idea of a running balance, that's obviously what Money

    > and
    > > Quicken do and I find that helps. So I worked hard and found a way to

    > avoid
    > > the problem you described.
    > >
    > > I used Xbasic [Alpha Five] to update all of the relevant records in the
    > > table when a value is changed. So if the user goes back to an old record

    > and
    > > changes the amount of a check or deposit, that will change the running
    > > balance for that record and every record that follows. I therefore

    > utilized
    > > a script that goes back to the previous record of the one that was

    > changed,
    > > captures the previous balance, and then the script recalculates the

    > running
    > > balance starting with the changed record, updating all subsequent records

    > in
    > > the table. It took a long time to figure out how to do such, but I'm sure
    > > that with a little experience I could do such with Visual Basic.
    > >
    > > Robert
    > >
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > Personally, since I can't think of different attributes between Debits

    > and
    > > > Credits, I'd be loathe to treat them as separate entities. That implies

    > only
    > > > one table. (I hinted at this before when I said I couldn't see why there
    > > > would be null fields)
    > > >
    > > > I also don't think you should be storing a calculated value (the running
    > > > balance) in a table. As fellow Access MVP John Vinson likes to say

    > "Storing
    > > > calculated data generally accomplishes only three things: it wastes disk
    > > > space, it wastes time (a disk fetch is much slower than almost any
    > > > reasonable calculation), and it risks data validity, since once it's

    > stored
    > > > in a table either the Total or one of the fields that goes into the

    > total
    > > > may be changed, making the value WRONG."
    > > >
    > > > It is possible (although usually inefficient) to calculate a running

    > balance
    > > > in a query. For example, you can use the DSum function to calculate the

    > sum
    > > > of all records prior to the current record.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
    > > > > Hi Doug:
    > > > >
    > > > > Once again, thanks for the prompt response showing me how to enable or
    > > > > disable a field on a form. I just knew that type of option had to be
    > > > > available.
    > > > >
    > > > > As for your question about why I'm doing this, I already have a

    > checking
    > > > > application in Alpha Five that works perfectly, plus I regularly use
    > > > Quicken.
    > > > > I'm not going through this process because I want a checking

    > application
    > > > in
    > > > > Access. Since I need Access skills for my new job, I'm doing this

    > strictly
    > > > as
    > > > > a learning experience. The skills I pick up by designing this

    > application
    > > > can
    > > > > be applied to many other types of applications. Doug, I just happened

    > to
    > > > pick
    > > > > the checking application randomly.
    > > > >
    > > > > I realize you can make a currency transaction positive or negative,

    > but
    > > > > most financial applications usually have separate fields for Debits

    > and
    > > > > Credits. Do you think using one field is a better idea?
    > > > >
    > > > > By the way, that brings me back to my previous question. What do you

    > think
    > > > > of the idea of creating two separate tables for transactions, one to
    > > > record
    > > > > checks and a 2nd one to record only deposits/credits? As I stated

    > before,
    > > > > that would eliminate null fields. Or do you prefer placing all
    > > > transactions
    > > > > in one table?
    > > > >
    > > > > By the way, any thoughts on my other question reference a running

    > balance
    > > > > field for each transaction?
    > > > >
    > > > > Thanks,
    > > > > RT
    > > > >
    > > > > "Douglas J Steele" wrote:
    > > > >
    > > > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > > > > > Hi Doug:
    > > > > > >
    > > > > > > Thanks for the prompt response.
    > > > > > >
    > > > > > > Since I'm relatively new to Access, would you be kind enough to

    > give
    > > > me an
    > > > > > > example of how I can do such in Form Design? For example, if the
    > > > > > > Type_Transaction is a "Debit", I want to ensure the Credits

    > Currency
    > > > field
    > > > > > > will remain blank?
    > > > > >
    > > > > > It will depend on how your form is structured, but try something

    > like
    > > > the
    > > > > > following:
    > > > > >
    > > > > > Private Sub Form_Current()
    > > > > >
    > > > > > If Me!txtType_Transaction = "Debit" Then
    > > > > > Me![Credits Currency].Enabled = False
    > > > > > Me![Debits Currenty].Enabled = True
    > > > > > Else
    > > > > > Me![Credits Currency].Enabled = True
    > > > > > Me![Debits Currenty].Enabled = False
    > > > > > End If
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > > Actually, that can be simplied to:
    > > > > >
    > > > > > Private Sub Form_Current()
    > > > > >
    > > > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <>

    > "Debit")
    > > > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction = "Debit")
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > >
    > > > > > > Second, do you think using one Transaction Table is a good option

    > or
    > > > > > should
    > > > > > > I divide it into two tables, Checks and Deposits/Credits, that

    > would
    > > > > > ensure
    > > > > > > there are no NULL fields? That would obviously remove the need to

    > skip
    > > > > > fields
    > > > > > > in this particular application.
    > > > > >
    > > > > > I don't see why you'd need Null fields. I don't believe you need to
    > > > > > distinguish between Credits and Debits in your table: the sign of

    > the
    > > > entry
    > > > > > should indicate for you.
    > > > > >
    > > > > > Just because you've got two fields on your form doesn't mean you

    > must
    > > > have
    > > > > > two fields in your table.
    > > > > >
    > > > > > > Third, it's really nice setting the Skip Field Rule in the table
    > > > because
    > > > > > it
    > > > > > > will be enforced no matter what data entry form is used. Just a
    > > > thought.
    > > > > >
    > > > > > Perhaps, but there isn't, so there's nothing to be gained by going

    > down
    > > > that
    > > > > > path...
    > > > > >
    > > > > > > Fourth, I have to use Firefox to post my questions/replies. When I
    > > > click
    > > > > > on
    > > > > > > Post a Question or Reply in Internet Explorer, I get something

    > similar
    > > > to
    > > > > > the
    > > > > > > Windows hourglass and I never see the Post Question or Reply form.

    > I
    > > > know
    > > > > > > this isn't an Access question but do you or anyone else have any

    > idea
    > > > why
    > > > > > > that is happening in IE?
    > > > > >
    > > > > > Sorry, I never use the web interface.
    > > > > >
    > > > > > > Thanks
    > > > > >
    > > > > > Is this just something you're building for the heck of it? Strikes

    > me
    > > > that
    > > > > > buying Quicken or Money would be a lot faster.
    > > > > >
    > > > > > You might also try downloading the Accounts ledger database from
    > > > > > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Doug Steele, Microsoft Access MVP
    > > > > > http://I.Am/DougSteele
    > > > > > (no e-mails, please!)
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  12. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    While I don't have access to their source code, I would assume that they're
    calculating the running balance on the fly.

    Think of Excel: it's trivial there to define a cell as being the sum of the
    cell above and the cell to the left.

    Why you can't use IE but can use FireFox makes no sense to me either.
    Otherway around, I could understand in a second: it wouldn't surprise me if
    Microsoft deliberately doesn't play well with FireFox!

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    news:FBE6CAED-E989-4EB0-858E-8489DCA9AFDA@microsoft.com...
    > Hello Douglas:
    >
    > OK, you've convinced me, I won't store the running balance in the table.
    > That will definitely save me a lot of time in terms of writing a complex
    > script for a trivial benefit.
    >
    > What did you mean when you said,
    > [just because Money and Quicken display a running balance does not
    > necessarily mean that it stores that running balance.]
    >
    > If they aren't storing the running balance, what are they doing?
    >
    > I will definitely look RunningSum property of a report. I haven't tried it
    > yet but it does indeed look as if it's pretty easy to implement.
    >
    > Thanks so much for taking the time to answer my questions. Now if I can

    only
    > figure out why I can't use IE to pose questions and/or reply, I'll feel a
    > little better.
    >
    > Robert
    >
    > "Douglas J Steele" wrote:
    >
    > > To be honest, I haven't looked at the sample database myself (and I

    can't,
    > > because I'm limited to Access 97 at the moment), but I did notice that

    it
    > > does what you're trying to do.
    > >
    > > Just because Money and Quicken display a running balance does not
    > > necessarily mean that it stores that running balance.
    > >
    > > While you certainly could replicate what you did in Alpha Five using VBA

    in
    > > Access, I'd recommend very strongly not to. It's fine and dandy to say

    that
    > > you're going to run the code every time the user changes something, but

    what
    > > happens if for some reason you use a query to update the data rather

    than
    > > using the form? Perhaps Alpha Five has triggers, but Access doesn't.
    > >
    > > If you want a running balance, it's trivial to do in a report (look at

    the
    > > RunningSum property that appears in the text box control on reports).

    For a
    > > form, I feel it's much safer to create a running sum in a query, and use
    > > that query as the basis for the report (rather than a table).
    > >
    > > If your table includes a field that represents the Date/Time when the

    entry
    > > was posted to table, you can calculate the running sum in the query as
    > > DSum("MyField", "MyTable", "MyDateTimeField <=" &

    CDbl([MyDateTimeField]))
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > news:4F3058F8-AA01-4BBF-82B7-FA196AFA9517@microsoft.com...
    > > > Hello Doug:
    > > >
    > > > Yes I did download the application you mentioned earlier and it seems

    like
    > > > pretty basic stuff, I didn't find anything in there that I thought was
    > > > enlightening. Maybe I missed something in there so I'll definitely

    take
    > > > another look at the application later. Was there anything specific you

    > > wanted
    > > > me to check out?
    > > >
    > > > In general I agree with both you and John Vinson about storing

    calculated
    > > > values in the table, however, let me add something different to the

    > > equation.
    > > > First, I like the idea of a running balance, that's obviously what

    Money
    > > and
    > > > Quicken do and I find that helps. So I worked hard and found a way to

    > > avoid
    > > > the problem you described.
    > > >
    > > > I used Xbasic [Alpha Five] to update all of the relevant records in

    the
    > > > table when a value is changed. So if the user goes back to an old

    record
    > > and
    > > > changes the amount of a check or deposit, that will change the running
    > > > balance for that record and every record that follows. I therefore

    > > utilized
    > > > a script that goes back to the previous record of the one that was

    > > changed,
    > > > captures the previous balance, and then the script recalculates the

    > > running
    > > > balance starting with the changed record, updating all subsequent

    records
    > > in
    > > > the table. It took a long time to figure out how to do such, but I'm

    sure
    > > > that with a little experience I could do such with Visual Basic.
    > > >
    > > > Robert
    > > >
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > Personally, since I can't think of different attributes between

    Debits
    > > and
    > > > > Credits, I'd be loathe to treat them as separate entities. That

    implies
    > > only
    > > > > one table. (I hinted at this before when I said I couldn't see why

    there
    > > > > would be null fields)
    > > > >
    > > > > I also don't think you should be storing a calculated value (the

    running
    > > > > balance) in a table. As fellow Access MVP John Vinson likes to say

    > > "Storing
    > > > > calculated data generally accomplishes only three things: it wastes

    disk
    > > > > space, it wastes time (a disk fetch is much slower than almost any
    > > > > reasonable calculation), and it risks data validity, since once it's

    > > stored
    > > > > in a table either the Total or one of the fields that goes into the

    > > total
    > > > > may be changed, making the value WRONG."
    > > > >
    > > > > It is possible (although usually inefficient) to calculate a running

    > > balance
    > > > > in a query. For example, you can use the DSum function to calculate

    the
    > > sum
    > > > > of all records prior to the current record.
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > > news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
    > > > > > Hi Doug:
    > > > > >
    > > > > > Once again, thanks for the prompt response showing me how to

    enable or
    > > > > > disable a field on a form. I just knew that type of option had to

    be
    > > > > > available.
    > > > > >
    > > > > > As for your question about why I'm doing this, I already have a

    > > checking
    > > > > > application in Alpha Five that works perfectly, plus I regularly

    use
    > > > > Quicken.
    > > > > > I'm not going through this process because I want a checking

    > > application
    > > > > in
    > > > > > Access. Since I need Access skills for my new job, I'm doing this

    > > strictly
    > > > > as
    > > > > > a learning experience. The skills I pick up by designing this

    > > application
    > > > > can
    > > > > > be applied to many other types of applications. Doug, I just

    happened
    > > to
    > > > > pick
    > > > > > the checking application randomly.
    > > > > >
    > > > > > I realize you can make a currency transaction positive or

    negative,
    > > but
    > > > > > most financial applications usually have separate fields for

    Debits
    > > and
    > > > > > Credits. Do you think using one field is a better idea?
    > > > > >
    > > > > > By the way, that brings me back to my previous question. What do

    you
    > > think
    > > > > > of the idea of creating two separate tables for transactions, one

    to
    > > > > record
    > > > > > checks and a 2nd one to record only deposits/credits? As I stated

    > > before,
    > > > > > that would eliminate null fields. Or do you prefer placing all
    > > > > transactions
    > > > > > in one table?
    > > > > >
    > > > > > By the way, any thoughts on my other question reference a running

    > > balance
    > > > > > field for each transaction?
    > > > > >
    > > > > > Thanks,
    > > > > > RT
    > > > > >
    > > > > > "Douglas J Steele" wrote:
    > > > > >
    > > > > > > "Robert T" <RobertT@discussions.microsoft.com> wrote in message
    > > > > > > news:59F49FC9-0A5E-40AD-B6A5-CB8433EF578A@microsoft.com...
    > > > > > > > Hi Doug:
    > > > > > > >
    > > > > > > > Thanks for the prompt response.
    > > > > > > >
    > > > > > > > Since I'm relatively new to Access, would you be kind enough

    to
    > > give
    > > > > me an
    > > > > > > > example of how I can do such in Form Design? For example, if

    the
    > > > > > > > Type_Transaction is a "Debit", I want to ensure the Credits

    > > Currency
    > > > > field
    > > > > > > > will remain blank?
    > > > > > >
    > > > > > > It will depend on how your form is structured, but try something

    > > like
    > > > > the
    > > > > > > following:
    > > > > > >
    > > > > > > Private Sub Form_Current()
    > > > > > >
    > > > > > > If Me!txtType_Transaction = "Debit" Then
    > > > > > > Me![Credits Currency].Enabled = False
    > > > > > > Me![Debits Currenty].Enabled = True
    > > > > > > Else
    > > > > > > Me![Credits Currency].Enabled = True
    > > > > > > Me![Debits Currenty].Enabled = False
    > > > > > > End If
    > > > > > >
    > > > > > > End Sub
    > > > > > >
    > > > > > > Actually, that can be simplied to:
    > > > > > >
    > > > > > > Private Sub Form_Current()
    > > > > > >
    > > > > > > Me![Credits Currency].Enabled = (Me!txtType_Transaction <>

    > > "Debit")
    > > > > > > Me![Debits Currenty].Enabled = (Me!txtType_Transaction =

    "Debit")
    > > > > > >
    > > > > > > End Sub
    > > > > > >
    > > > > > >
    > > > > > > > Second, do you think using one Transaction Table is a good

    option
    > > or
    > > > > > > should
    > > > > > > > I divide it into two tables, Checks and Deposits/Credits, that

    > > would
    > > > > > > ensure
    > > > > > > > there are no NULL fields? That would obviously remove the need

    to
    > > skip
    > > > > > > fields
    > > > > > > > in this particular application.
    > > > > > >
    > > > > > > I don't see why you'd need Null fields. I don't believe you need

    to
    > > > > > > distinguish between Credits and Debits in your table: the sign

    of
    > > the
    > > > > entry
    > > > > > > should indicate for you.
    > > > > > >
    > > > > > > Just because you've got two fields on your form doesn't mean you

    > > must
    > > > > have
    > > > > > > two fields in your table.
    > > > > > >
    > > > > > > > Third, it's really nice setting the Skip Field Rule in the

    table
    > > > > because
    > > > > > > it
    > > > > > > > will be enforced no matter what data entry form is used. Just

    a
    > > > > thought.
    > > > > > >
    > > > > > > Perhaps, but there isn't, so there's nothing to be gained by

    going
    > > down
    > > > > that
    > > > > > > path...
    > > > > > >
    > > > > > > > Fourth, I have to use Firefox to post my questions/replies.

    When I
    > > > > click
    > > > > > > on
    > > > > > > > Post a Question or Reply in Internet Explorer, I get something

    > > similar
    > > > > to
    > > > > > > the
    > > > > > > > Windows hourglass and I never see the Post Question or Reply

    form.
    > > I
    > > > > know
    > > > > > > > this isn't an Access question but do you or anyone else have

    any
    > > idea
    > > > > why
    > > > > > > > that is happening in IE?
    > > > > > >
    > > > > > > Sorry, I never use the web interface.
    > > > > > >
    > > > > > > > Thanks
    > > > > > >
    > > > > > > Is this just something you're building for the heck of it?

    Strikes
    > > me
    > > > > that
    > > > > > > buying Quicken or Money would be a lot faster.
    > > > > > >
    > > > > > > You might also try downloading the Accounts ledger database from
    > > > > > > http://office.microsoft.com/en-au/templates/TC010175341033.aspx
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > http://I.Am/DougSteele
    > > > > > > (no e-mails, please!)
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     

Share This Page