A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trying to add new Transaction code



 
 
Thread Tools Display Modes
  #1  
Old December 22nd, 2004, 05:50 PM
Jan Il
external usenet poster
 
Posts: n/a
Default Trying to add new Transaction code

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_He
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.





  #2  
Old December 22nd, 2004, 06:20 PM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Wed, 22 Dec 2004 11:50:23 -0500, "Jan Il"
wrote:

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


the error is he
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_He
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.





If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
  #3  
Old December 22nd, 2004, 08:00 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi :-)

On Wed, 22 Dec 2004 11:50:23 -0500, "Jan Il"
wrote:

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


the error is he
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_He
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.





If you expect an answer to a personal mail, add the word "manfred" to the

first 10 lines in the message
MW


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




  #4  
Old December 22nd, 2004, 08:05 PM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Wed, 22 Dec 2004 14:00:54 -0500, "Jan Il"
wrote:

have you missed that?

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

.Fields("TransactionDate") = me.TransactionDate


.Update


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
  #5  
Old December 22nd, 2004, 09:07 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi :-)

have you missed that?

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

.Fields("TransactionDate") = me.TransactionDate


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.





  #6  
Old December 22nd, 2004, 10:13 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi :-)

On Wed, 22 Dec 2004 14:00:54 -0500, "Jan Il"
wrote:

have you missed that?

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

.Fields("TransactionDate") = me.TransactionDate


.Update


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



If you expect an answer to a personal mail, add the word "manfred" to the

first 10 lines in the message
MW



  #7  
Old December 23rd, 2004, 10:26 AM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Wed, 22 Dec 2004 16:13:01 -0500, "Jan Il"
wrote:

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.


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
  #8  
Old December 23rd, 2004, 12:30 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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)

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



"Jan Il" wrote in message
...
Hi Andi :-)

On Wed, 22 Dec 2004 14:00:54 -0500, "Jan Il"
wrote:

have you missed that?

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

.Fields("TransactionDate") = me.TransactionDate


.Update


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



If you expect an answer to a personal mail, add the word "manfred" to

the
first 10 lines in the message
MW





  #9  
Old December 23rd, 2004, 02:11 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

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)


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


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



"Jan Il" wrote in message
...
Hi Andi :-)

On Wed, 22 Dec 2004 14:00:54 -0500, "Jan Il"
wrote:

have you missed that?

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

.Fields("TransactionDate") = me.TransactionDate


.Update


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



If you expect an answer to a personal mail, add the word "manfred" to

the
first 10 lines in the message
MW







  #10  
Old December 23rd, 2004, 02:40 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

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)


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


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



"Jan Il" wrote in message
...
Hi Andi :-)

On Wed, 22 Dec 2004 14:00:54 -0500, "Jan Il"
wrote:

have you missed that?

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

.Fields("TransactionDate") = me.TransactionDate


.Update


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



If you expect an answer to a personal mail, add the word "manfred" to

the
first 10 lines in the message
MW







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Barcodes TarponZeke General Discussion 4 October 25th, 2004 08:04 PM
Form to generate a Report code Pat Coleman Setting Up & Running Reports 4 July 16th, 2004 12:58 PM
Hide or show Control Button on Form in Access 97 Pete Sperling Using Forms 8 July 13th, 2004 04:13 PM
Is it possible to include Begin And End Transaction code in VBA ? Jean New Users 10 June 29th, 2004 11:31 PM


All times are GMT +1. The time now is 09:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.