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

Try putting in a

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

and see whether it's converting the value correctly.


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



"Jan Il" wrote in message
...
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








  #12  
Old December 23rd, 2004, 03:01 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi :-)
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.


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

Jan


  #13  
Old December 23rd, 2004, 03:55 PM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Thu, 23 Dec 2004 08:40:40 -0500, "Jan Il"
wrote:

)

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.


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
  #14  
Old December 23rd, 2004, 06:01 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

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.


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

Jan


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



"Jan Il" wrote in message
...
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










  #15  
Old December 23rd, 2004, 06:13 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi :-)

On Thu, 23 Dec 2004 08:40:40 -0500, "Jan Il"
wrote:

)

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.


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


I still get the error Data conversion error

Jan


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

first 10 lines in the message
MW



  #16  
Old December 23rd, 2004, 06:24 PM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Thu, 23 Dec 2004 12:13:56 -0500, "Jan Il"
wrote:

Hi Andi :-)



I still get the error Data conversion error


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
  #17  
Old December 23rd, 2004, 06:59 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi,

On Thu, 23 Dec 2004 12:13:56 -0500, "Jan Il"
wrote:

Hi Andi :-)



I still get the error Data conversion error


which line?


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")


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

Jan


  #18  
Old December 23rd, 2004, 11:11 PM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Thu, 23 Dec 2004 12:59:42 -0500, "Jan Il"
wrote:


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.


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



---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
  #19  
Old December 23rd, 2004, 11:43 PM
Andi Mayer
external usenet poster
 
Posts: n/a
Default

On Thu, 23 Dec 2004 12:59:42 -0500, "Jan Il"
wrote:

please post the whole function, there must be a typo somewhere
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
  #20  
Old December 24th, 2004, 12:17 AM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Andi :-)

On Thu, 23 Dec 2004 12:59:42 -0500, "Jan Il"
wrote:


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.

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


'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



---
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 10:04 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.