Field on Form = next record

[Kim Webb]

I don't know if this is why there is always a leading zero kept when I
do the:

=Last([ProjectID])+1

in the control source but also in the text box under FORMAT it is:

00000

and under INPUT MASK it says:

99999;;*

I don't know if either of those help to make the zero stay leading but
I figured I'd mention them.

Thanks again.

 

[Kim Webb]

Well I spoke too soon - it actually does not write the correct project
number:

now for each one I get 6016

the next number should be 06019

I just keep putting the zero's in manually at the front.

 

[Marshall Barton]

Did you use both lines? The strYear line should preserve
the leading zero.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.
--
Marsh
MVP [MS Access]

Since you are combining two values to make the ProjectID, it
should be stored in two separate fields. You can always put
them together in your forms or reports so the users won't
know how the table keeps the values. Actually you may
already have a project date field that already contains the
year information.

Well I'm not combining two values the value of ProjectID is just 95234,
99765, 01987, 06111- I've always just calculated.

Back to your specific question. For the leading zero to be
saved in the table, the ProjectID field must be a TEXT type
field. You should confirm that before making any more
changes.

It is a text type.

If it is a text field, then the DMax (not DLast) could be:

strYear = Format(Date, "yy")

Me.ProjectID = strYear & Nz(DMax("Val(Right(ProjectID,
3))","Projects", "Left(ProjectID,2)='" & strYear & "'"), 0)
+1

This does not work since my last project is 06010 and my next one
assigned needs to be 06011. It gives me 6011 but I just need to add
something to the formula below to get the zero to be the lead number:

Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1

What's wierd is that if I use

=Last([ProjectID])+1

It does give me the leading zero

 

[Kim Webb]

Did you use both lines? The strYear line should preserve
the leading zero.

yes i used both lines but how does it know what the latest project
number is to begin with.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

if i look at field projectID in the table it shows the leading zero for
all the projects after 1999.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

but Dmax doesn't work because 99888 is a larger number than 06010 so it
will always find the biggest number. Also this is a text field not a
number field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.

I don't need to see the number - I can also just have another field
that shows them what it'll be using

=Last([ProjectID])+1

 

[Marshall Barton]

The DMax call I used:
DMax("Val(Right(ProjectID,3))","Projects",
"Left(ProjectID,2)='" & strYear & "'")

retrieves the highest numeric value of the right most three
digits where the first two characters match the current year
(with possible leading zero).

In your example where you said "99888 is a larger number
than 06010", it doesn't matter because the part
"Left(ProjectID,2)='" & strYear & "'" forces the max to only
look at projects that start with 06. Then the value
returned by "Val(Right(ProjectID,3))" is just the 10 in
06010.

Ahhh, I just realized I did not specify the declaration for
strYear and if that's not correct, it might cause the
leading zero to be dropped. I also forgot to force the
zeros in front of the right three digits. The code should
have been:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

Sorry if my mistakes have caused you any grief.
--
Marsh
MVP [MS Access]

Did you use both lines? The strYear line should preserve
the leading zero.

yes i used both lines but how does it know what the latest project
number is to begin with.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

if i look at field projectID in the table it shows the leading zero for
all the projects after 1999.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

but Dmax doesn't work because 99888 is a larger number than 06010 so it
will always find the biggest number. Also this is a text field not a
number field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.

I don't need to see the number - I can also just have another field
that shows them what it'll be using

=Last([ProjectID])+1

 

[Kim Webb]

I used this:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

And it worked - many thanks for all the help.

I have a similar issue with the INVOICE form and I'm wondering if
BeforeUpdate assigns the number when I save the record where can I put
that command so it saves the record ONLY if the number is already
blank?

Can I add an IF/THEN to this statement somehow.

Private Sub Description_BeforeUpdate(Cancel As Integer)
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End Sub

Thanks.

The DMax call I used:
DMax("Val(Right(ProjectID,3))","Projects",
"Left(ProjectID,2)='" & strYear & "'")

retrieves the highest numeric value of the right most three
digits where the first two characters match the current year
(with possible leading zero).

In your example where you said "99888 is a larger number
than 06010", it doesn't matter because the part
"Left(ProjectID,2)='" & strYear & "'" forces the max to only
look at projects that start with 06. Then the value
returned by "Val(Right(ProjectID,3))" is just the 10 in
06010.

Ahhh, I just realized I did not specify the declaration for
strYear and if that's not correct, it might cause the
leading zero to be dropped. I also forgot to force the
zeros in front of the right three digits. The code should
have been:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

Sorry if my mistakes have caused you any grief.
--
Marsh
MVP [MS Access]

Did you use both lines? The strYear line should preserve
the leading zero.

yes i used both lines but how does it know what the latest project
number is to begin with.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

if i look at field projectID in the table it shows the leading zero for
all the projects after 1999.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

but Dmax doesn't work because 99888 is a larger number than 06010 so it
will always find the biggest number. Also this is a text field not a
number field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.

I don't need to see the number - I can also just have another field
that shows them what it'll be using

=Last([ProjectID])+1

 

[Marshall Barton]

I used this:


And it worked - many thanks for all the help.

I have a similar issue with the INVOICE form and I'm wondering if
BeforeUpdate assigns the number when I save the record where can I put
that command so it saves the record ONLY if the number is already
blank?

Can I add an IF/THEN to this statement somehow.

Private Sub Description_BeforeUpdate(Cancel As Integer)
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End Sub

I'm not sure I understand this latest twist. If you are
assigning the record's primary key, then you want to use the
**form** BerforeUpdate event, not a control's event.

I guess I'm guilty of assuming you have all the other logic
in place and it's looking like that was not a valid
assumption, so let me back up and fill in some background.
This activity we've been discussing needs to be done at the
time that the form is saving a **new** record. To reduce
the window where another user can get the same number, we
use the **form** BeforeUpdate event. To make sure this only
happens for a new record (not when an existing record has
just been edited), the code to set the number needs to be
enclosed in an If that checks for the new record situation:

Private Sub FORM_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End If
End Sub

I don't think I've ever seen the entire event procedure for
the Projects form, but, if you don't already have it, the
same If statement needs to be used there too.

 

[Kim Webb]

I'm not sure I understand this latest twist. If you are
assigning the record's primary key, then you want to use the
**form** BerforeUpdate event, not a control's event.

For invoices the form was setup to allow users to print out the invoice
before closing the form. So you enter the invoice info - and most of
the fields are marked as being requred - and then print the invoice-
then close it.

If the number is not assigned until after the form is closed then the
invoice can't be printed until it's closed and opened back up. So I
put the event in after the last field that is requred - which happens
to be the description field.

This works except if someone goes in later and changes the description
the invoice number changes.

 

[Marshall Barton]

You should still use the form's BeforeUpdate procedure. The
issue is that the record needs to be saved before it is
printed.

Presumably, you have a button on the form that users click
on to print the invoice. If so, then the first thing the
button's Click event procedure should do is save the record
using this line of code:
If Me.Dirty Then Me.Dirty = False

As part of saving the record, the form's BeforeUpdate event
will fire and, for a new record, assign the invoice number.
This way, the wole situation hangs together without all the
complications you're running into now.