Trying to add new Transaction code

J

Jan Il

Hi all - Access 2000 XP, W2K

I have a data entry form with a control to enter transactions to the table,
set to LimitToList. I want to be able to add a new transaction at the time
the entry is being made, so that the user does not have to go to the table
to enter the new Transaction. I have the code in the NotInList event and
the add dialog box comes up fine, but, when I enter the new Transaction and
try to go to the next entry control, I get an error message that say:

"Error: The field 'MyCheckRegister.TransactionDate' cannot have a Null value
because the Required property for this field is set to True. Enter a value
in this field."

There is a date entered in that control on the form. Here is the code that I
now have for the Transaction control to add the new Transaction:

/Start Code/
*****************************
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
.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
***************************
/End Code/

The date control works fine with no error message when I enter the date and
tab to the Transaction control. It is only after the new Transaction has
been added and tabbing to the next entry that the error message gets fired.

I have been over and over the code and form controls and such, but, I can't
find where the problem is. I would truly appreciate it if someone could
point me in the right direction here to correct this problem.

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

Andi Mayer

Hi all - Access 2000 XP, W2K

I have a data entry form with a control to enter transactions to the table,
set to LimitToList. I want to be able to add a new transaction at the time
the entry is being made, so that the user does not have to go to the table
to enter the new Transaction. I have the code in the NotInList event and
the add dialog box comes up fine, but, when I enter the new Transaction and
try to go to the next entry control, I get an error message that say:

"Error: The field 'MyCheckRegister.TransactionDate' cannot have a Null value
because the Required property for this field is set to True. Enter a value
in this field."

There is a date entered in that control on the form. Here is the code that I
now have for the Transaction control to add the new Transaction:

/Start Code/
*****************************
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
Click to expand...

the error is here:
your table needs the TransactionDate set to a Value,
because addnew adds this field with a Null Value

.Fields("TransactionDate") = me.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
***************************
/End Code/

The date control works fine with no error message when I enter the date and
tab to the Transaction control. It is only after the new Transaction has
been added and tabbing to the next entry that the error message gets fired.

I have been over and over the code and form controls and such, but, I can't
find where the problem is. I would truly appreciate it if someone could
point me in the right direction here to correct this problem.

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

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
J

Jan Il

Hi Andi :)
the error is here:
your table needs the TransactionDate set to a Value,
because addnew adds this field with a Null Value

.Fields("TransactionDate") = me.TransactionDate



If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
Click to expand...

Ahmmm....not sure I understand this one, but, no...when I post to the
newsgroup I come here for the answers so that other readers may also benefit
from the information provided. That's the second purpose of the newsgroups.
<g>

But, thanks for the offer.... :)

Jan :)
 
A

Andi Mayer

have you missed that?
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
J

Jan Il

Hi Andi :)
have you missed that?
Click to expand...

Ahhh...yes....I scanned down too far and did miss this part of your reply.
Sorry.... ;o)

Thank you for your time and assistance, I really appreciate it. :)

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

Jan Il

Hi Andi :)
have you missed that?
Click to expand...

I added the above to the existing code in the Transaction control NotInList
event code, in the area where it appears it should go, and I am now getting
a type mismatch error: Here is what I added and where in the code:

With rst
.AddNew
.Fields("Transaction") = NewData
.Fields("TransactionDate") = Me.txtTransactionDate
.Update

The txtTransactionDate is the name of the control on the form. It does
compile alright, but, when I click OK on the Add box, the error message is
fired. Sorry to be so dense, but, obviously I'm not doing it right.

Thank you.

Jan :)
 
A

Andi Mayer

I added the above to the existing code in the Transaction control NotInList
event code, in the area where it appears it should go, and I am now getting
a type mismatch error: Here is what I added and where in the code:

With rst
.AddNew
.Fields("Transaction") = NewData
.Fields("TransactionDate") = Me.txtTransactionDate
.Update

The txtTransactionDate is the name of the control on the form. It does
compile alright, but, when I click OK on the Add box, the error message is
fired. Sorry to be so dense, but, obviously I'm not doing it right.
Click to expand...

I don't know which Type .Fields("TransactionDate") is and which Type
the Form Field Me.txtTransactionDate is, but both have to match.



If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
D

Douglas J. Steele

Assuming TransactionDate is a Date-type field, you need to enclose the date
in # characters (or else use the CDate function)

.Fields("TransactionDate") = Format(Me.txtTransactionDate,
"\#mm\/dd\/yyyy\#;;;\N\u\l\l")

or

.Fields("TransactionDate") = CDate(Me.txtTransactionDate)

(See http://www.mvps.org/access/datetime/date0005.htm at "The Access Web"
for other formats to use if TransactionDate contains time as well as the
date)
 
J

Jan Il

Hi Doug! :)
Assuming TransactionDate is a Date-type field, you need to enclose the date
in # characters (or else use the CDate function)

.Fields("TransactionDate") = Format(Me.txtTransactionDate,
"\#mm\/dd\/yyyy\#;;;\N\u\l\l")

or

.Fields("TransactionDate") = CDate(Me.txtTransactionDate)

(See http://www.mvps.org/access/datetime/date0005.htm at "The Access Web"
for other formats to use if TransactionDate contains time as well as the
date)
Click to expand...

Yes, the date format does contain data and time, mm/dd/yyyy hh:nn" P"

I'll check out the link you provided and see what the formats are there.

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

Happy Holidays!

Jan :)
 
J

Jan Il

Hi Doug :)
Assuming TransactionDate is a Date-type field, you need to enclose the date
in # characters (or else use the CDate function)

.Fields("TransactionDate") = Format(Me.txtTransactionDate,
"\#mm\/dd\/yyyy\#;;;\N\u\l\l")

or

.Fields("TransactionDate") = CDate(Me.txtTransactionDate)

(See http://www.mvps.org/access/datetime/date0005.htm at "The Access Web"
for other formats to use if TransactionDate contains time as well as the
date)
Click to expand...

Here is what I have now tried:
..Fields("TransactionDate") = Format(Me.txtTransactionDate, "\#mm\/dd\/yyyy
hh\:nn\#\N\u\l\l")

However, now I am getting an Error: Data type conversion error.

The TransactionDate is a date data type, with the date format of mm/dd/yyyy
hh:nn" P". It is a Required field. The format I tried is the one I found
on the link you provided that is closest to the date format I am using;
"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l", but, I removed the seconds and
whatever the ;;; are, as I did not think they were needed. I've been able
to find out some about this error on the web, but, not having much luck in
finding a cure. I can't figure out what is generating the error.

Jan :)
 
D

Douglas J. Steele

Try putting in a

MsgBox Format(Me.txtTransactionDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")

and see whether it's converting the value correctly.
 
J

Jan Il

Hi Andi :)
I don't know which Type .Fields("TransactionDate") is and which Type
the Form Field Me.txtTransactionDate is, but both have to match.
Click to expand...

The .Fields("TransactionDate") is mm/dd/yyyy hh:nn" P"
the form field txtTransactionDate is the same

Jan :)
 
A

Andi Mayer

)
Here is what I have now tried:
.Fields("TransactionDate") = Format(Me.txtTransactionDate, "\#mm\/dd\/yyyy
hh\:nn\#\N\u\l\l")

However, now I am getting an Error: Data type conversion error.

The TransactionDate is a date data type, with the date format of mm/dd/yyyy
hh:nn" P". It is a Required field. The format I tried is the one I found
on the link you provided that is closest to the date format I am using;
"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l", but, I removed the seconds and
whatever the ;;; are, as I did not think they were needed. I've been able
to find out some about this error on the web, but, not having much luck in
finding a cure. I can't figure out what is generating the error.
Click to expand...

first try should be everytime the access help

the help for the format will tell you why and for what you need the
";"

in your case the format give you trobles, because you are not allowed
to use a Null_Value. The fourth ";" is the format for the Null-Value.

Now you have to decide what to do, if this form field is Null, use a
date for no-Input, or use now() as an input.

like:
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\#")
endif


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
J

Jan Il

Hi Doug,
Try putting in a

MsgBox Format(Me.txtTransactionDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")

and see whether it's converting the value correctly.
Click to expand...

No..it is converting it to #12/22/2004# ...not adding the time aspect

Jan :)
 
J

Jan Il

Hi Andi :)
)

first try should be everytime the access help

the help for the format will tell you why and for what you need the
";"

in your case the format give you trobles, because you are not allowed
to use a Null_Value. The fourth ";" is the format for the Null-Value.

Now you have to decide what to do, if this form field is Null, use a
date for no-Input, or use now() as an input.

like:
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\#")
endif
Click to expand...

I still get the error Data conversion error

Jan :)
 
A

Andi Mayer

Hi Andi :)
Click to expand...
I still get the error Data conversion error
Click to expand...

which line?

go to the direct window and show us every variable or field on this
line

like: if this error is on the line
.Fields("TransactionDate") =Me.txtTransactionDate

?Me.txtTransactionDate
?rst.Fields("TransactionDate")


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
J

Jan Il

Hi Andi,
which line?
Click to expand...

It does not show a line, just the dialog box, then it opens the module, and
the top line is highlighted in yellow. That is all that is being indicated.
go to the direct window and show us every variable or field on this
line

like: if this error is on the line
.Fields("TransactionDate") =Me.txtTransactionDate

?Me.txtTransactionDate
?rst.Fields("TransactionDate")
Click to expand...

When I tried this with the various lines in the Immediate Window, they eaid
Invalid watch expression. That's all.

Jan :)
 
A

Andi Mayer

It does not show a line, just the dialog box, then it opens the module, and
the top line is highlighted in yellow. That is all that is being indicated.
Click to expand...

and this is the line I want to know, because there is the error
 
J

Jan Il

Hi Andi :)
indicated.

and this is the line I want to know, because there is the error
Click to expand...

'k...here is it. This is the line that it is highlighting with the error

Private Sub cmbTransaction_NotInList(NewData As String, Response As Integer)

That's the only line it addresses.

Thank you.

Jan :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

React JS Outlook Add-in - VS Code + WebPack - Unable to call external Web Service - CORS Issue 0
Outlook Add-in - Javascript based using React JS - VS Code + WebPack - Unable to call external Web Service 0
VBA concatenate ranges into a new document 0
Working outlook via vba code with xlsx - error 0
Ambiguous Error for NotInList code 19
MS Outlook inbox to MS Accsess Database 0
VSTO C# add in Outlook.Appointment error 0
Error outlook.com 0xc0000005 trying to open custom controls 0

Top