Top value + 1

  • Thread starter Thread starter Susanne
  • Start date Start date
S

Susanne

I hope this is the last post I have to do for awhile. Everyone has been so
much help.

I have 3 fields: Item #, Sequential #, and Year (they want it to by in 2
digit format). I've done this with 2 variables before, not with 3.

When Item# is selected, I need for the sequentail# to increase by one based
on the current year, again, displayed as 2 digits (07, 08, 09). When it
begins a new year, the sequential# will begin back at 0 (really 1 since the
function will add one to it).

I have this code (taking out my real field names, too long) to run on
AfterUpdate when the Item# is selected:


iNext = Nz(DMax("Select [Sequential#] where [Item#] = Me!Item#",
"[MyTable]", "[NumberYear] = Date()")) + 1

Me!Sequential# = iNext

I suspect it is something in the select clause. I didn't know if I should
use a query.

Thanks in advance!
 
Maybe I am missing something but how can you equate a year to an entire date?
"[NumberYear] = Date()")) - surely you need to use Datapart() function to
extract the two-digit year (Look in Access help). Also, not a good idea to
use special characters in column names.
Also, your solution is not reliable with a multi-user database since 2
people could get the same iNext value.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Susanne said:
I hope this is the last post I have to do for awhile. Everyone has been so
much help.

I have 3 fields: Item #, Sequential #, and Year (they want it to by in 2
digit format). I've done this with 2 variables before, not with 3.

When Item# is selected, I need for the sequentail# to increase by one based
on the current year, again, displayed as 2 digits (07, 08, 09). When it
begins a new year, the sequential# will begin back at 0 (really 1 since the
function will add one to it).

I have this code (taking out my real field names, too long) to run on
AfterUpdate when the Item# is selected:


iNext = Nz(DMax("Select [Sequential#] where [Item#] = Me!Item#",
"[MyTable]", "[NumberYear] = Date()")) + 1

Me!Sequential# = iNext

I suspect it is something in the select clause. I didn't know if I should
use a query.
Click to expand...


You can not use a query anywhere in a domain aggregate
function, much less in the field name argument.

Because I refuse to use special charaters in names, I will
use different names than you did.

If item and year are number type fields, try something more
like:

Me SeqNum = Nz(DMax("SeqNum", "MyTable", _
"ItemNum = " & Me!ItemNum _
& " And NumberYear = Year(Date()) Mod 100), 0) + 1

For multiuser scenarios, the code must be in the **form**
BeforeUpdate event.
 
I don't use special characters. I just used them here as a shorthand. No
reason to type it all out. I have been spending my time reworking too many
other peoples databases with special characters and spaces. Horrible.

I was trying to figure if I needed the year field to be more of a number
field instead of date/time. The date/time type wasn't working, obviously. I
guess you helped confirm that.

Thanks.

Marshall Barton said:
Susanne said:
I hope this is the last post I have to do for awhile. Everyone has been so
much help.

I have 3 fields: Item #, Sequential #, and Year (they want it to by in 2
digit format). I've done this with 2 variables before, not with 3.

When Item# is selected, I need for the sequentail# to increase by one based
on the current year, again, displayed as 2 digits (07, 08, 09). When it
begins a new year, the sequential# will begin back at 0 (really 1 since the
function will add one to it).

I have this code (taking out my real field names, too long) to run on
AfterUpdate when the Item# is selected:


iNext = Nz(DMax("Select [Sequential#] where [Item#] = Me!Item#",
"[MyTable]", "[NumberYear] = Date()")) + 1

Me!Sequential# = iNext

I suspect it is something in the select clause. I didn't know if I should
use a query.
Click to expand...


You can not use a query anywhere in a domain aggregate
function, much less in the field name argument.

Because I refuse to use special charaters in names, I will
use different names than you did.

If item and year are number type fields, try something more
like:

Me SeqNum = Nz(DMax("SeqNum", "MyTable", _
"ItemNum = " & Me!ItemNum _
& " And NumberYear = Year(Date()) Mod 100), 0) + 1

For multiuser scenarios, the code must be in the **form**
BeforeUpdate event.
Click to expand...
 
I was trying to figure if I needed the year field to be more of a number
field instead of date/time. The date/time type wasn't working, obviously. I
guess you helped confirm that.
Click to expand...

08 isn't a date/time. <g>

A Date/Time field (regardless of how it's formatted) is a precise point in
time, a Double Float number of days and fractions of a day since midnight,
December 30, 1899. If you want to use a year you will need to either use an
Integer or Long Integer yearnumber field (don't call it Year!), and/or extract
the year from the datefield with either

Val(Format(datefield, "yy"))

or

Year([datefield]) MOD 100
 
Thanks Marshall and John.

I changed the field to a number instead of date/time. Also, I used the code
but had to add a set of () so the debugger wouldn't kick it back.

My only issue now is finding the best spot to put this in. I tried it under
the form's Before Insert, After Insert, After Update and Before Update
events, and none of them work. My SequenceNo = 1. I believe it is because
the ItemNo does not have a value yet. It works if I put it under the field's
(the item number field) After Update event. Are there major problems if I
put it here?
 
Susanne said:
I changed the field to a number instead of date/time. Also, I used the code
but had to add a set of () so the debugger wouldn't kick it back.

My only issue now is finding the best spot to put this in. I tried it under
the form's Before Insert, After Insert, After Update and Before Update
events, and none of them work. My SequenceNo = 1. I believe it is because
the ItemNo does not have a value yet. It works if I put it under the field's
(the item number field) After Update event. Are there major problems if I
put it here?
Click to expand...


No, that would leave the door wide open for multiple users
to get the same number. As I said before, use the form's
BeforeUpdate event.

If users might try to move to another record, close the form
or do anything else that would cause the current record to
be saved, then you need to add code to the procedure to
prevent the save when all required values have not been
entered. A rough outline of the procedure might look like:

If Me.NewRecord Then
If IsNull(Me.ItemNum) Then
Beep
MsgBox "Item number is required"
Cancel = True
Exit Sub
End If
. . .
Me SeqNum = Nz(DMax( . . .
End If
 
Back
Top