Trying to add new Transaction code

[Andi Mayer]

is the input everytime a Number? (NewData)
is the field Transaction numeric?


I changed also the Docmd.openform

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Transaction") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("Transaction") = NewData
If IsNull(Me.txtTransactionDate) Then
.Fields("TransactionDate") = Now()
' .Fields("TransactionDate") =#1/1/1900#

Else
.Fields("TransactionDate") = _
Format(Me.txtTransactionDate, "\#mm\/dd\/yyyy\ hh\:nn\:ss\#")
End If
.Update
' .Bookmark = .LastModified
' lngTransaction = .Fields("Transaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm "frmCkEntry", , , "Transaction =" _
& NewData, , acDialog
Else
Response = acDataErrContinue
End If

Exit_Here:
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub


BTW: its nearly 1' o'clock in the night at my timezone, therefore I am
going to sleep now

read you tomorrow

 

[Jan Il]

Hi Andi

please post the whole function, there must be a typo somewhere

Here is the entire code for the NotInList Event for the cmbTransaction
control:
~~~~~~~~~~~
Private Sub cmbTransaction_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Transaction") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("Transaction") = NewData
If IsNull(Me.txtTransactionDate) Then
.Fields("TransactionDate") = Now()
'.Fields("TransactionDate") = #1/1/1900#
Else
.Fields ("TransactionDate")
.Update
.Bookmark = .LastModified
lngTransaction = .Fields("Transaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmCkEntry", _
wherecondition:="Transaction =" & lngTransaction, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_Here:
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub
~~~~~~~~

Jan

 

[Jan Il]

Hi you guys..

I will be going to FL for the Christmas weekend tomorrow morning, so I will
not be able to respond after the morning. But, I will check back here when
I return the first of next week.

Thank you all for your time and assistance. I wish you, and your families,
a very Happy Holiday.

Jan

 

[Jan Il]

Hi Andi

is the input everytime a Number? (NewData)
is the field Transaction numeric?


I changed also the Docmd.openform

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Transaction") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("Transaction") = NewData
If IsNull(Me.txtTransactionDate) Then
.Fields("TransactionDate") = Now()
' .Fields("TransactionDate") =#1/1/1900#

Else
.Fields("TransactionDate") = _
Format(Me.txtTransactionDate, "\#mm\/dd\/yyyy\ hh\:nn\:ss\#")
End If
.Update
' .Bookmark = .LastModified
' lngTransaction = .Fields("Transaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm "frmCkEntry", , , "Transaction =" _
& NewData, , acDialog
Else
Response = acDataErrContinue
End If

Exit_Here:
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub


BTW: its nearly 1' o'clock in the night at my timezone, therefore I am
going to sleep now

read you tomorrow

Sorry to be so long in getting back to you. I had a last minute invite to
Ft. Myers, FL for a Christmas week holiday. No way was I passing it up,
'specailly when it was 22 degrees here in Warrenton, VA, and 70 in Ft.
Myers. Just got back late this afternoon, so catching up. I'll try this in
the morning and let you know how it goes. Hope you haven't lost interest,
and that you had a wonderful Christmas and New Years holiday.

Thank you for your time and assistance, I really do appreciate it. Again,
sorry to leave you in the dark for so long....... <g>

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

 

[Andi Mayer]

Hi Jan you are a lucky guy ;-)

I had marked this message as ignored and while I was pressing the
I-button I saw a Andi at the top and went back.

I sought this problem was solved.

First of all:
Every time when you change something in you code: COMPILE this code

in your case: you would have run into a compile error like:

there is an ENDIF missing (I am to lazy to start my English version to
give you the exact error message)

second: on top of every module there should be: option explizit

with this two words, you can't use un-Dimmed variables (this is very
helpfull to avoid typing errors, besides that you can't use
uninitialized variables and objects)

 

[Jan Il]

Hi Andi!

Hi Jan you are a lucky guy ;-)

Gal ;o) And.....thank you so much for checking back. Hope you'll hang
tight a bit...I had a tad of problems with my machine today, just being
contrary that I left it alone so long over the holidays I guess. <g> I
didn't get a chance to work with the new information you have been so kind
to provide, yet, but, I'll give it a go now and let you know what gives as
soon as I can.

Thank you for your valuable time and help.....I *really* do appreciate it.
)

Jan

 

[Andi Mayer]

Hi Andi!


Gal ;o) And.....thank you so much for checking back. Hope you'll hang

So lets wait till your Compi loves you again.

If you don't wait another 6 days this thread will still be active in
my newsreader and marked for watching

 

[Jan Il]

Hi Andi

So lets wait till your Compi loves you again.

If you don't wait another 6 days this thread will still be active in
my newsreader and marked for watching

Not sure about the love part on either end.....but....we made up and we're
talking again...so far. ;o)

'k......

In regards to the first two questions of your previous reply, if I
understand correctly::

is the input everytime a Number? (NewData)

No....this is a text data type field.

is the field Transaction numeric?

No...this is a text data type field

I have tried the new code in the NotInList event of the cmbTransaction
control, but, when I enter a check number, such as 2001, tab to the next
control and enter the date, then tab to the Transaction control and enter
the new information, then try to tab to the next control, I get an error
that says: Data type conversion error.

Thank you.

Jan

 

[Andi Mayer]

I have tried the new code in the NotInList event of the cmbTransaction
control, but, when I enter a check number, such as 2001, tab to the next
control and enter the date, then tab to the Transaction control and enter
the new information, then try to tab to the next control, I get an error
that says: Data type conversion error.

and once again you didn't told on which line

I guess:
DoCmd.OpenForm "frmCkEntry", , , "Transaction =" & NewData, ,
acDialog

a string has to be in qutes: therefore

DoCmd.OpenForm "frmCkEntry", , , "Transaction ='" & NewData &"'",
, acDialog

we will give him what he wants.

BTW: how big is your mdb after zipping it?

 

[Jan Il]

Hi Andi

and once again you didn't told on which line

W'll.....I can't tell you which 'line', as again, it does not give any line
reference information as to what part of the code is causing the error. All
the error message consists of is: Error: Data type converstion error. That's

I guess:
DoCmd.OpenForm "frmCkEntry", , , "Transaction =" & NewData, ,
acDialog

a string has to be in qutes: therefore

DoCmd.OpenForm "frmCkEntry", , , "Transaction ='" & NewData &"'",
, acDialog

we will give him what he wants.

'k.....I cut and pasted the above code, making sure it is all on one line,
and I still get the same error message. Again, it is not giving any line
reference as to what is causing the error.

BTW: how big is your mdb after zipping it?

I'm using a sample test copy, which has limited files for test purposes, so
it's only 166 KB zipped.

Jan

 

[Andi Mayer]

I'm using a sample test copy, which has limited files for test purposes, so
it's only 166 KB zipped.

then send it to my adress, but don't forget the magic word, else the
message gets deleted without reaching me.

 

[Jan Il]

then send it to my adress, but don't forget the magic word, else the

message gets deleted without reaching me.

'k...on it's way...but, without the quotes. Right?

Jan

 

[Andi Mayer]

'k...on it's way...but, without the quotes. Right?

one time would have been enough, with or without quotes in the middle
of a single word, this all doesnt matter, luckely the spammers didn't
use this german name (only one I got delivered till now)

mm.dd.yyyy hh:nn" P"

how can you use this in a table field format? Without seeing it I
would have never thought about this.

You should store it as a normal date and use this format only on
forms, to show this.

Are you using Access97? because it is cluttered with DoCmd.DoMenuItem

look for Manfred in the sourceCode, I changed a few things when I
walked into errors and I added to every form to lines on top:
Option Compare Database
Option Explicit
and found a few typos as expected.

The db will be on the way right now.

 

[Jan Il]

Hi Andi

Are you using Access97? because it is cluttered with DoCmd.DoMenuItem

No.....I'm using Access 2002 XP

look for Manfred in the sourceCode, I changed a few things when I
walked into errors and I added to every form to lines on top:
Option Compare Database
Option Explicit
and found a few typos as expected.

The db will be on the way right now.

I have received the zip file. However......I do not find any Manfred at all
in the db. Nor, do I find any changes that you mention.

Thank you for your time and help. I appreciate it.

Jan

 

[Andi Mayer]

Hi Andi


No.....I'm using Access 2002 XP

Then you should change it

I have received the zip file. However......I do not find any Manfred at all
in the db. Nor, do I find any changes that you mention.

go to the vba press ctlr-F ; change to whole project and type in my
name, you will find it at least 10 times.

 

[Jan Il]

Hi Andi

go to the vba press ctlr-F ; change to whole project and type in my
name, you will find it at least 10 times.

I have now found the name items, and 2 log tables I assume you created.

I am still getting the error message Error: Data type conversion error on
with the frmCkEntry form when trying to enter a new record in the
Transaction field.

I am now getting the error message "Error: The field
'MYCreditCardRegister.TransactionDate' cannot contain a Null value because
the required property for the field is set to True. Enter a value in this
field" for the frmCreditCardEntry form when trying to enter a new record. I
also get "Error: 'MYSavingsRec.TransactionDate' cannot contain a Null value
because the required property for the field is set to True. Enter a value in
this field." When I try to enter a new record in the Transaction field on
this form.

Thank you.

Jan

 

[Andi Mayer]

I am still getting the error message Error: Data type conversion error on
with the frmCkEntry form when trying to enter a new record in the
Transaction field.

got to the debugger:

go to:
Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer)

on this line press F9, this sets a break-point

add something new to your combobox

with F8 you step through your code

and then see what your code is doing:
it is calling himself

due to the maximizing you don't see how much forms are open.

do this with all your problems to see where your error is, but first
comment the On error goto ... out that you know which line

 

[Jan Il]

I am still getting the error message Error: Data type conversion error on

got to the debugger:

go to:
Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer)

on this line press F9, this sets a break-point

add something new to your combobox

with F8 you step through your code

and then see what your code is doing:
it is calling himself

due to the maximizing you don't see how much forms are open.

do this with all your problems to see where your error is, but first
comment the On error goto ... out that you know which line

In the frmCkEntry form, doing as you suggested, after creating the break
point, and adding a new name into the Transaction field and tabbing to the
next field, I get the ...add it? message, clicking ok opens the debugger to
the code, and when I press F8, it settles on the line here and fires the
error message again. Set rst = CurrentDb.OpenRecordset("MyCheckRegister").
continuing with the F8, it gets to here .Fields("TransactionDate") =
Format(Me.txtTransactionDate, "mm\.dd\.yyyy\ hh\:nn\ P") and give a RunTime
error of 3421, Data type conversion error. When I click End, a message box
comes up that says 'The text you entered is not an item in the list. Select
an item from the list, or enter text that matches one of the items listed.'

It won't debug any further.

Set rst = CurrentDb.OpenRecordset("MySavingsRec") - same error

Next is here in the same form: Update, and the error is: 'The text you
entered is not an item in the list. Select an item from the list, or enter
text that matches one of the items listed.' I can go no further.

and here: Set rst = CurrentDb.OpenRecordset("MyCreditCardRegister") -
triggers the add it message. Upon click I get the message of a RunTime
error 3314.

Next is here: Update - and the error is: 'The text you entered is not an
item in the list. Select an item from the list, or enter text that matches
one of the items listed.' I can go no further here either.

Jan

 

[Andi Mayer]

look at your mail, I changed this sub totally.

 

[Jan Il]

Hi Andi

look at your mail, I changed this sub totally.

Yes...I have it now. The form is working correctly now. Very interesting.

Thank you very much for all your time and help, I truly appreciate it.

Jan