Debit Card Purchase recording

[Jan Il]

Hi all! Access 2002 XP, W2K

I have recently relocated and opened a new checking account, and I now also
have a debit card. I have an existing db that I use to record my checks and
expenditures. I am not quite sure yet what the best method would be to
enter the debit card purchases to the check register in the db to make sure
the expenditures are properly recorded and deducted from the balance. I
previously had the control for the check number set for No duplicates,
however, I am not sure if I should enter the debit card purchase in this
control as well, such as DBT or DCP, which would likely require that I
remove the No Duplicates and number data type for that record.

Thus, I am not sure if I should add a new control to the entry and record
form for these new types of purchases, or perhaps modify the check number
record and data type in the table and the form controls in the entry and
record form.

The primary tracking and recording method is by date, and I have a special
date recording system employed. Therefore, I can call the records up by
date, or even using whom it was purchased from, but, since I will have no
check number to refer to, I will not be able to do so. I don't expect to be
using the debit card that much, but, I do want to ensure such purchases are
properly recorded and trackable.

I am in hopes that perhaps someone here may have a similar situation in use
in some way and might be able to provide me with some insight as to how to
set this type of situation up properly.

Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[RK]

I assume that for your check register that you have a
Primary key that is NOT your check number, yes?

I also assume that you sorting for you check register is:

Date, then check number.

If that being the case, what I would do is set your check
number field to no duplicates, required to No
That way you can input a check number for checks and a
blank (no check number) for your debit card. It would
still be sorted correctly.

Hope this helps

RK

 

[Jan Il]

Hi RK

I assume that for your check register that you have a
Primary key that is NOT your check number, yes?

I also assume that you sorting for you check register is:

Date, then check number.

If that being the case, what I would do is set your check
number field to no duplicates, required to No
That way you can input a check number for checks and a
blank (no check number) for your debit card. It would
still be sorted correctly.

This would work well if perhaps I would never need to sort by debit card
purchases, but, I would like to allow for that need in the onset. With the
current check number field set to No duplicates, required No, I can have a
blank field there when recording deposits, which have no number. The date
system I have set up lists the checks, deposits and transaction balances in
date order descending, which allows the current balance to be at the top of
the list in the form, and all the transactions are also in proper numerical
and date order.

It was my thought that adding the DBT in the check number field would allow
me to sort by DBT (or DCP) as well as check number, however, I fear it may
cause problems with the check number recording and sorting process, as I
would have to have that field set to allow duplicates for the DBT indicated
entries. Unless, there were a way to automatically add a sequential number
to each successive 'DBT' entry in that field, which I am not sure is
possible in Access. However, I have learned to never to say can't with
Access. Thus far, I've not found anything I've wanted/needed to do that I
haven't been able to in Access. ;-)

Thank you very much for your time and help, I really appreciate it. I'll
give your suggestions a run through and see how I fare.

Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[David]

Jan,
I'm not a MVP (so syntax may be an issue), but I have had similar obsticals.

What about setting up an unbound text box to enter your check numbers or
your DBT card transactions. Hide the field you are currently using to enter
the check number.

Write VB code in the after update event of the new text box.

The code could be a simple IF statement that says, IF box = "DBT" then
field1 = me.[New Field] & (Now)
else
field1 = me.[New Field]
End if

This will populate the field1 with either DBTplus the date and time of entry
OR the check number.

Your other forms/reports do not have to change because you are populating
field1 with code from the data entered in the NEW FIELD. You can also keep
your index as NO DUPS to avoid double entry for checks.

Another option would be create two additional text boxes, one would be to
enter the check number or DBT, the 2nd box would be to enter the date&time of
the DBT transaction. Then modify your afterupdate event to concatenate the
two boxes into your field1.

Hope all of this helps.

Good Luck.

David Peterso
---------------------------------------------------------------------------------------------

 

[Jan Il]

Hi David

Jan,
I'm not a MVP (so syntax may be an issue), but I have had similar

obsticals.

No worries, I never stand on ceremony. ;-)

What about setting up an unbound text box to enter your check numbers or
your DBT card transactions. Hide the field you are currently using to enter
the check number.

Write VB code in the after update event of the new text box.

The code could be a simple IF statement that says, IF box = "DBT" then
field1 = me.[New Field] & (Now)
else
field1 = me.[New Field]
End if

This will populate the field1 with either DBTplus the date and time of entry
OR the check number.

Your other forms/reports do not have to change because you are populating
field1 with code from the data entered in the NEW FIELD. You can also keep
your index as NO DUPS to avoid double entry for checks.

Another option would be create two additional text boxes, one would be to
enter the check number or DBT, the 2nd box would be to enter the date&time of
the DBT transaction. Then modify your afterupdate event to concatenate the
two boxes into your field1.

These are two very interesting and possible solutions. I don't like boxing
myself into a corner, and these appear to offer flexibility should another
type of transaction come into play at a later date and need to be included.
I'll give them a go and see what I can come up with.

Thank you very much for your time and assistance. I really do appreciate
it.

Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[Dirk Goldgar]

[...]

It was my thought that adding the DBT in the check number field would
allow me to sort by DBT (or DCP) as well as check number, however, I
fear it may cause problems with the check number recording and
sorting process, as I would have to have that field set to allow
duplicates for the DBT indicated entries. Unless, there were a way
to automatically add a sequential number to each successive 'DBT'
entry in that field, which I am not sure is possible in Access.

However, I have learned to never to say can't with Access. Thus far,
I've not found anything I've wanted/needed to do that I haven't been
able to in Access. ;-)

I see you do know better.

Here's another possibility to add to your collection. You could leave
the current restrictions on check number alone, except that for what I
propose it must be a text field, not a number (if that's what it is
now). You could have Access automatically assign ascending values for
debit-card transactions, in the form DBT######. You could set it up so
that every time you enter DBT for a new record, Access automatically
finds the highest existing "DBT######" check number, extracts the
numeric portion, adds 1 to it, prefixes it with "DBT", and inserts it in
place of the simple "DBT" you entered.

You could do that with code similar to this in the form's module:

'----- start of example code for form's module -----
Option Compare Database
Option Explicit

Private Function NextDBTNumber() As String

' This function finds the highest "DBT" check number currently on
' file and adds 1 to it to get a new DBT number.

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax("CheckNumber", "CheckRegister", _
"CheckNumber Like 'DBT*'")

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function


Private Sub CheckNumber_AfterUpdate()

With Me!CheckNumber
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub
'----- end of example code for form's module -----

 

[Jan Il]

Hi Dirk!

Sorry to be so long getting back, but, my newly installed Wk2 decided to
fuss with me over who's the boss. Imagine that!.... ;o))

[...]

It was my thought that adding the DBT in the check number field would
allow me to sort by DBT (or DCP) as well as check number, however, I
fear it may cause problems with the check number recording and
sorting process, as I would have to have that field set to allow
duplicates for the DBT indicated entries. Unless, there were a way
to automatically add a sequential number to each successive 'DBT'
entry in that field, which I am not sure is possible in Access.

However, I have learned to never to say can't with Access. Thus far,
I've not found anything I've wanted/needed to do that I haven't been
able to in Access. ;-)

I see you do know better.

Here's another possibility to add to your collection. You could leave
the current restrictions on check number alone, except that for what I
propose it must be a text field, not a number (if that's what it is
now). You could have Access automatically assign ascending values for
debit-card transactions, in the form DBT######. You could set it up so
that every time you enter DBT for a new record, Access automatically
finds the highest existing "DBT######" check number, extracts the
numeric portion, adds 1 to it, prefixes it with "DBT", and inserts it in
place of the simple "DBT" you entered.

You could do that with code similar to this in the form's module:

'----- start of example code for form's module -----
Option Compare Database
Option Explicit

Private Function NextDBTNumber() As String

' This function finds the highest "DBT" check number currently on
' file and adds 1 to it to get a new DBT number.

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax("CheckNumber", "CheckRegister", _
"CheckNumber Like 'DBT*'")

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function


Private Sub CheckNumber_AfterUpdate()

With Me!CheckNumber
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub
'----- end of example code for form's module -----

AHH! See! That's exactly what I wanted it to do! Another reason for hoping
to have a numerical series for these transactions is to also provide a
method of sorting information of all DBT purchases. Now.....just to verify,
am I correct that you mean for this to be done in the Entry form module,
when the record is input?

Thank you very much for your help, I really do appreciate it. It just
proves my faith in Access is still well justified. ;-)

Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[Dirk Goldgar]

Sorry to be so long getting back, but, my newly installed Wk2 decided
to fuss with me over who's the boss. Imagine that!.... ;o))

It should know better by now.

[...]

AHH! See! That's exactly what I wanted it to do! Another reason for
hoping to have a numerical series for these transactions is to also
provide a method of sorting information of all DBT purchases.
Now.....just to verify, am I correct that you mean for this to be
done in the Entry form module, when the record is input?

Right. Naturally, you might have to change the name of the text box and
the underlying field, where they appear in the example code as
"CheckNumber", to whatever your field and control really are named. If
they aren't the same, then be aware that in the NextDBTNumber()
function, it's the *field* (in the table) that is being referred to,
while in CheckNumber_AfterUpdate() it's the control on the form.

The way that should work is that, if you enter "DBT" in the text box and
the text box was formerly empty, then it will be expanded to the next
available "DBT######" number. It won't happen if the text box already
had something in it -- my idea is that that will allow you to edit
values safely, without the function sticking its nose in.

Thank you very much for your help, I really do appreciate it. It just
proves my faith in Access is still well justified. ;-)

You're welcome. You can seldom go wrong putting your faith in Access.

 

[Jan Il]

Sorry to be so long getting back, but, my newly installed Wk2 decided

It should know better by now.

You'd think! ;-)

[...]

AHH! See! That's exactly what I wanted it to do! Another reason for
hoping to have a numerical series for these transactions is to also
provide a method of sorting information of all DBT purchases.
Now.....just to verify, am I correct that you mean for this to be
done in the Entry form module, when the record is input?

Right. Naturally, you might have to change the name of the text box and
the underlying field, where they appear in the example code as
"CheckNumber", to whatever your field and control really are named. If
they aren't the same, then be aware that in the NextDBTNumber()
function, it's the *field* (in the table) that is being referred to,
while in CheckNumber_AfterUpdate() it's the control on the form.

The way that should work is that, if you enter "DBT" in the text box and
the text box was formerly empty, then it will be expanded to the next
available "DBT######" number. It won't happen if the text box already
had something in it -- my idea is that that will allow you to edit
values safely, without the function sticking its nose in.

Gotcha! I'll try this out and check back with you.

You're welcome. You can seldom go wrong putting your faith in Access.

Yeppers! The MOW db has presented some pretty unique thingies, but......we
haven't lost one yet. <g>

Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[Jan Il]

Hi Dirk!

Sorry to take so long, but, yesterday afternoon my neighbors kitchen sink
decided to share with mine, which in turn, revolted (true, it could have
been worse.....I think). I have just finished getting the things back to
order, so I'll try out the information on the db this evening or in the
morning.

Jan

Sorry to be so long getting back, but, my newly installed Wk2 decided
to fuss with me over who's the boss. Imagine that!.... ;o))

It should know better by now.

[...]

AHH! See! That's exactly what I wanted it to do! Another reason for
hoping to have a numerical series for these transactions is to also
provide a method of sorting information of all DBT purchases.
Now.....just to verify, am I correct that you mean for this to be
done in the Entry form module, when the record is input?

Right. Naturally, you might have to change the name of the text box and
the underlying field, where they appear in the example code as
"CheckNumber", to whatever your field and control really are named. If
they aren't the same, then be aware that in the NextDBTNumber()
function, it's the *field* (in the table) that is being referred to,
while in CheckNumber_AfterUpdate() it's the control on the form.

The way that should work is that, if you enter "DBT" in the text box and
the text box was formerly empty, then it will be expanded to the next
available "DBT######" number. It won't happen if the text box already
had something in it -- my idea is that that will allow you to edit
values safely, without the function sticking its nose in.

Thank you very much for your help, I really do appreciate it. It just
proves my faith in Access is still well justified. ;-)

You're welcome. You can seldom go wrong putting your faith in Access.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Jan Il]

Hi Dirk

[...]Here's another possibility to add to your collection. You could leave
the current restrictions on check number alone, except that for what I
propose it must be a text field, not a number (if that's what it is
now). You could have Access automatically assign ascending values for
debit-card transactions, in the form DBT######. You could set it up so
that every time you enter DBT for a new record, Access automatically
finds the highest existing "DBT######" check number, extracts the
numeric portion, adds 1 to it, prefixes it with "DBT", and inserts it in
place of the simple "DBT" you entered.

You could do that with code similar to this in the form's module:

'----- start of example code for form's module -----
Option Compare Database
Option Explicit

Private Function NextDBTNumber() As String

' This function finds the highest "DBT" check number currently on
' file and adds 1 to it to get a new DBT number.

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax("CheckNumber", "CheckRegister", _
"CheckNumber Like 'DBT*'")

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function

'k.....here is the code that I have put into the Form module of the test
app. for
the above portion of the code. The table field is CheckNo. I have the
table field set to data type Text, and No Duplicates:

Private Function NextDBTNumber() As String

' This function finds the highest "DBT" check number currently on
' file and adds 1 to it to get a new DBT number.

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax(CheckNo", "CheckRegister", _
CheckNo Like 'DBT*'")

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function

And..... for this part of the Code:

Private Sub CheckNumber_AfterUpdate()

With Me!CheckNumber
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub

I have this in the After Update Event of the control in the form which is
txtCheckNo:

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If

End With

End Sub

When I entered a check number in the txtCheckNo control on the entry form
(1001) and hit tab to go to the date control, it fired this error message:

The expression After Update you have entered as the event property setting
produced the following error: Ambiguous name detected: txtCheckNo_After
Update.

* This expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may been an error evaluating the function, event or macro.

I double checked and the control name and table field names are correct. I
also got this same error when I entered the DBT and tried to move to the
next control. Have I missed something somewhere or overlooked something in
the Code?

Also, the debugger balks at this part of the code in the After Update Event
for the txtCheckNo control: Private Sub txtCheckNo_AfterUpdate()

And the error message is:

Ambiguous name detected: txtCheckNo_AfterUpdate()

Thank you for your time and help.

Jan

 

[Dirk Goldgar]

Hi Dirk!

Sorry to take so long, but, yesterday afternoon my neighbors kitchen
sink decided to share with mine, which in turn, revolted (true, it
could have been worse.....I think).

I thought it was only the peasants who were supposed to be revolting (à
la Wizard of Id).

I have just finished getting the
things back to order, so I'll try out the information on the db this
evening or in the morning.

Still glad you moved?

 

[Dirk Goldgar]

Comments inline ...

'k.....here is the code that I have put into the Form module of the
test app. for
the above portion of the code. The table field is CheckNo. I have
the table field set to data type Text, and No Duplicates:

Private Function NextDBTNumber() As String

' This function finds the highest "DBT" check number currently on
' file and adds 1 to it to get a new DBT number.

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax(CheckNo", "CheckRegister", _
CheckNo Like 'DBT*'")

***
Jan, there are a couple of missing quotes there. That last statement
should be:

strMaxNum = vbNullString & _
DMax("CheckNo", "CheckRegister", _
"CheckNo Like 'DBT*'")

I trust the table really is named "CheckRegister"? Otherwise you should
change that, too.
***

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function


And..... for this part of the Code:


I have this in the After Update Event of the control in the form
which is txtCheckNo:

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If

End With

End Sub

***
That looks okay, despite the error message you're getting.
***

When I entered a check number in the txtCheckNo control on the entry
form (1001) and hit tab to go to the date control, it fired this
error message:

The expression After Update you have entered as the event property
setting produced the following error: Ambiguous name detected:
txtCheckNo_After Update.

* This expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may been an error evaluating the function, event or macro.

I double checked and the control name and table field names are
correct. I also got this same error when I entered the DBT and
tried to move to the next control. Have I missed something somewhere
or overlooked something in the Code?

Also, the debugger balks at this part of the code in the After Update
Event for the txtCheckNo control: Private Sub txtCheckNo_AfterUpdate()

And the error message is:

Ambiguous name detected: txtCheckNo_AfterUpdate()

What that message is telling you is that you have two (or more)
procedures named "txtCheckNo_AfterUpdate" in your form's module, so
Access doesn't know which one to call when the AfterUpdate event fires.
Probably, in the course of working at this problem, you created another
AfterUpdate event procedure for this control. Look through the form's
module for the other procedure and delete it if there's nothing in it
that you need to keep. If, on the other hand, there is code in that
other procedure that you still want to have execute when CheckNo is
updated, copy that code into the new txtCheckNo_AfterUpdate() procedure
before deleting the old one.

 

[Jan Il]

Hi Dirk

Comments inline ...

MT..

***
Jan, there are a couple of missing quotes there. That last statement
should be:

strMaxNum = vbNullString & _
DMax("CheckNo", "CheckRegister", _
"CheckNo Like 'DBT*'")

I trust the table really is named "CheckRegister"? Otherwise you should
change that, too.
***

Here is what I now have in the first part fo the code. My bad...I did
forget to replace the quotes when I thought I wanted to change the field
name.then switched back, and did overlook their missing. The name of the
table is MyCheckRegister so I changed that part to the actual name. <g>

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax("CheckNo", "MyCheckRegister", _
"CheckNo Like 'DBT*'")

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function

*******

What that message is telling you is that you have two (or more)
procedures named "txtCheckNo_AfterUpdate" in your form's module, so
Access doesn't know which one to call when the AfterUpdate event fires.
Probably, in the course of working at this problem, you created another
AfterUpdate event procedure for this control. Look through the form's
module for the other procedure and delete it if there's nothing in it
that you need to keep. If, on the other hand, there is code in that
other procedure that you still want to have execute when CheckNo is
updated, copy that code into the new txtCheckNo_AfterUpdate() procedure
before deleting the old one.

I *think* what may have happened here, was that I did a copy and paste of
the this part of the code from your message first, then went on to the
other. Then I didn't see this part when I went back to check and thought it
may have gotten deleted with some other code that I needed to clean out from
the other attempts I made on my own that were still tucked away in there, so
I recopied it.to the module. Thus, unknowingly, I wound up with two of
them. Once the duplicate was removed all compiled without exception. )

I have run several tests on the new test form setup and all seems to be
performing as it should. I've tested by alternating entries of check
numbers and DBT transactions, and the DBT entries did indeed transform into
a numerical format such as DBT000001, DBT000002, etc., when I moved to the
Date control on the form.

Voila! Magnifique!! Merci! Merci!

Jan

 

[Jan Il]

I thought it was only the peasants who were supposed to be revolting (à
la Wizard of Id).

Well...I don't know about the peasant part, but, what their sink was so
happily sharing with mine was indeed pretty revolting. ;o))

Still glad you moved?

Ahmm.....you did have to ask that, didn't you..... ;-)

Jan

 

[Dirk Goldgar]

Voila! Magnifique!! Merci! Merci!

<lol> Il n'y a pas de quoi.