auto increase code and Field name with a space

[Lynlongley]

I have a field name called "Transaction No" in a table called BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar to auto
number), but I run into problems because the field has a space in it. My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets, but it
doesn't work. I have a bunch of queries that refer to this field, so I'd
rather not change the field name.

 

[Dirk Goldgar]

I have a field name called "Transaction No" in a table called BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar to auto
number), but I run into problems because the field has a space in it. My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets, but it
doesn't work. I have a bunch of queries that refer to this field, so I'd
rather not change the field name.

Is "Transaction No" also the name of a control on the form where this code
is running? If so, you'll need to bracket the reference to the control,
too. Try this:

Me![Transaction No] = Nz(Dmax("[Transaction No]","BiddingData"))+1

 

[Jeanette Cunningham]

if the textbox control for Transaction No is called Transaction No

replaceTransaction No with
Me![Transaction No]

if it is called text123 or txtTransaction No
use Me.[Name of Control] instead of Me![Transaction No]

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

Jeanette Cunningham

 

[Lynlongley]

I added the code, but get the following error:
The expression On Open you entered as the event property setting produced
the following error:
Procedure declaration does not match description of even or procedure having
the same name.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event, or macro.

I've commented out all the other code in my form.

Any idea why the error?

if the textbox control for Transaction No is called Transaction No

replaceTransaction No with
Me![Transaction No]

if it is called text123 or txtTransaction No
use Me.[Name of Control] instead of Me![Transaction No]

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

Jeanette Cunningham

I have a field name called "Transaction No" in a table called BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar to auto
number), but I run into problems because the field has a space in it. My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets, but it
doesn't work. I have a bunch of queries that refer to this field, so I'd
rather not change the field name.

 

[Jeanette Cunningham]

Lyn,
there is an error in this line
Private Sub lot_AfterUpdate(Cancel As Integer)

delete the 3 lines of code

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

--close the VBA editor
--on your form in design view click on the control that is called lot
--on the property dialog, on the event tab, find the event called After
Update
--click the button at right with the ellipsis (...)
--the VBA editor will open with the first line and the end line for the sub
already created,
--in between the Private Sub lot_AfterUpdate and the End Sub, type
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
--save and compile
--if no more errors, run the form and see if it works

Jeanette Cunningham

I added the code, but get the following error:
The expression On Open you entered as the event property setting produced
the following error:
Procedure declaration does not match description of even or procedure
having
the same name.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event, or macro.

I've commented out all the other code in my form.

Any idea why the error?

if the textbox control for Transaction No is called Transaction No

replaceTransaction No with
Me![Transaction No]

if it is called text123 or txtTransaction No
use Me.[Name of Control] instead of Me![Transaction No]

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

Jeanette Cunningham

I have a field name called "Transaction No" in a table called
BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar to auto
number), but I run into problems because the field has a space in it.
My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets, but it
doesn't work. I have a bunch of queries that refer to this field, so
I'd
rather not change the field name.

 

[Bob Quintal]

Lyn,
there is an error in this line
Private Sub lot_AfterUpdate(Cancel As Integer)

delete the 3 lines of code

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

--close the VBA editor
--on your form in design view click on the control that is called
lot --on the property dialog, on the event tab, find the event
called After Update
--click the button at right with the ellipsis (...)
--the VBA editor will open with the first line and the end line
for the sub already created,
--in between the Private Sub lot_AfterUpdate and the End Sub, type
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
--save and compile
--if no more errors, run the form and see if it works

Jeanette Cunningham

I added the code, but get the following error:
The expression On Open you entered as the event property setting
produced the following error:
Procedure declaration does not match description of even or
procedure having
the same name.
*The expression may not result in the name of a macro, the name
of a user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event, or
macro.

I've commented out all the other code in my form.

Any idea why the error?

if the textbox control for Transaction No is called Transaction
No

replaceTransaction No with
Me![Transaction No]

if it is called text123 or txtTransaction No
use Me.[Name of Control] instead of Me![Transaction
No]

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction
No]","BiddingData"))+1 End Sub

Jeanette Cunningham

message
I have a field name called "Transaction No" in a table called
BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar
to auto number), but I run into problems because the field has
a space in it. My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets,
but it doesn't work. I have a bunch of queries that refer to
this field, so I'd
rather not change the field name.

One issue that EVERYBODY missed is the misuse of the nz() function
=Nz(Dmax("[Transaction No]","BiddingData"))+1
Add the missing parameter ,0
=Nz(Dmax("[Transaction No]","BiddingData"),0)+1

 

[Dirk Goldgar]

Lyn,
there is an error in this line
Private Sub lot_AfterUpdate(Cancel As Integer)

Good spot!

 

[Jeanette Cunningham]

Bob,
good catch

Jeanette Cunningham

Lyn,
there is an error in this line
Private Sub lot_AfterUpdate(Cancel As Integer)

delete the 3 lines of code

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

--close the VBA editor
--on your form in design view click on the control that is called
lot --on the property dialog, on the event tab, find the event
called After Update
--click the button at right with the ellipsis (...)
--the VBA editor will open with the first line and the end line
for the sub already created,
--in between the Private Sub lot_AfterUpdate and the End Sub, type
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
--save and compile
--if no more errors, run the form and see if it works

Jeanette Cunningham

I added the code, but get the following error:
The expression On Open you entered as the event property setting
produced the following error:
Procedure declaration does not match description of even or
procedure having
the same name.
*The expression may not result in the name of a macro, the name
of a user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event, or
macro.

I've commented out all the other code in my form.

Any idea why the error?

:

if the textbox control for Transaction No is called Transaction
No

replaceTransaction No with
Me![Transaction No]

if it is called text123 or txtTransaction No
use Me.[Name of Control] instead of Me![Transaction
No]

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction
No]","BiddingData"))+1 End Sub

Jeanette Cunningham

message
I have a field name called "Transaction No" in a table called
BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar
to auto number), but I run into problems because the field has
a space in it. My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets,
but it doesn't work. I have a bunch of queries that refer to
this field, so I'd
rather not change the field name.

One issue that EVERYBODY missed is the misuse of the nz() function
=Nz(Dmax("[Transaction No]","BiddingData"))+1
Add the missing parameter ,0
=Nz(Dmax("[Transaction No]","BiddingData"),0)+1

 

[Lynlongley]

Yes! this works!! Thanks!

Lyn,
there is an error in this line
Private Sub lot_AfterUpdate(Cancel As Integer)

delete the 3 lines of code

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

--close the VBA editor
--on your form in design view click on the control that is called lot
--on the property dialog, on the event tab, find the event called After
Update
--click the button at right with the ellipsis (...)
--the VBA editor will open with the first line and the end line for the sub
already created,
--in between the Private Sub lot_AfterUpdate and the End Sub, type
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
--save and compile
--if no more errors, run the form and see if it works

Jeanette Cunningham

I added the code, but get the following error:
The expression On Open you entered as the event property setting produced
the following error:
Procedure declaration does not match description of even or procedure
having
the same name.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event, or macro.

I've commented out all the other code in my form.

Any idea why the error?

if the textbox control for Transaction No is called Transaction No

replaceTransaction No with
Me![Transaction No]

if it is called text123 or txtTransaction No
use Me.[Name of Control] instead of Me![Transaction No]

Private Sub lot_AfterUpdate(Cancel As Integer)
Me![Transaction No] =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

Jeanette Cunningham

I have a field name called "Transaction No" in a table called
BiddingData.
Yes, I put it in with a space between the words.
I'd like to get the field to automatically increase (similar to auto
number), but I run into problems because the field has a space in it.
My
code:

Private Sub lot_AfterUpdate(Cancel As Integer)
Transaction No =Nz(Dmax("[Transaction No]","BiddingData"))+1
End Sub

I've tried enclosing Transaction No in quotes and in brackets, but it
doesn't work. I have a bunch of queries that refer to this field, so
I'd
rather not change the field name.

 

[John W. Vinson]

One issue that EVERYBODY missed is the misuse of the nz() function
=Nz(Dmax("[Transaction No]","BiddingData"))+1
Add the missing parameter ,0
=Nz(Dmax("[Transaction No]","BiddingData"),0)+1

Actually, that usually won't matter. The second argument to NZ is optional,
and returns zero by default.

John W. Vinson [MVP]

 

[Bob Quintal]

One issue that EVERYBODY missed is the misuse of the nz() function
=Nz(Dmax("[Transaction No]","BiddingData"))+1
Add the missing parameter ,0
=Nz(Dmax("[Transaction No]","BiddingData"),0)+1

Actually, that usually won't matter. The second argument to NZ is
optional, and returns zero by default.

John W. Vinson [MVP]

the help file (AC2003) says
"If the value of the variant argument is Null, the Nz function
returns the number zero or a zero-length string (always returns a
zero-length string when used in a query expression), depending on
whether the context indicates the value should be a number or a
string."

Before posting I tested it and found:
given ID is long integer in an empty table,
from the immediate window I get
? "!" & Nz(DMax("ID", "table13")) & "!"
!!

If nz() did return 0, the !! should be !0!

? "!" & Nz(DMax("ID", "table13"),0) & "!"
!0!
which makes sense, but
? "!" & Nz(DMax("ID", "table13")) + 1 & "!"
!1!

Weird.