CheckBook Application: Skip Fields and Running Balance

[Robert T]

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

 

[Douglas J Steele]

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.

 

[Robert T]

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

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

(no e-mails, please!)

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

 

[Douglas J Steele]

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

 

[Robert T]

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

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

 

[Douglas J Steele]

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

(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

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

 

[Douglas J Steele]

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

--
Doug Steele, Microsoft Access MVP

(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

(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

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

similar

to

idea

why

 

[Robert T]

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

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

(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

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

 

[Douglas J Steele]

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

(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,
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

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

(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

:

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

 

[Robert T]

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

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

(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,
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

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

(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

:

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

 

[Douglas J Steele]

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

(no e-mails, please!)

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

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

(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,
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


:

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

(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

:

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