CheckBook Application: Skip Fields and Running Balance

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10531-checkbook-application-skip-fields-running-balance.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> 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

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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> > 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
>
>
>

"Robert T" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> 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/te...175341033.aspx


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

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
>
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
>
> 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/te...175341033.aspx
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
>

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" wrote in message
news:12842D38-C129-498B-A9C4-BF6D7C04494E@microsoft.com...
> Hi Doug:
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> 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" 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.
> >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> > 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/te...175341033.aspx
> >
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> >
> >

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> 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" 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
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/te...175341033.aspx
> > >
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > >
> > >
>
>

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
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" 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" 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.
> > >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> > > 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/te...175341033.aspx
> > >
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > >
> > >
>
>
>

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
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" 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
> > >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10531
> > > "Douglas J Steele" wrote:
> > >
> > > > "Robert T" 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/te...175341033.aspx
> > > >
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no e-mails, please!)
> > > >
> > > >
> > > >
> > > >
> >
> >
> >