PING: Kaltuu - Dave

[Shell]

You were kind eniough to respond to a question I posted, one which many
others have askied in other ways in other places and not received answers. It
boils down to generating a simple 4 digit number - for PO Order numbers,
Invoice numbers and so on. My visit to the Church of Google searching using a
variety of operands and variables esults in a lot of people asking similar
questions getting know where - and it being available on ebay

You seem to have the answer - but you gave me part of it, and you asked
another question, which I answered. I think it would be interesting to find
an answer.

I copied the Post below so you don't have to hunt it down. I hope you can
help.

Trying to be moer clear, Auto-generating a number using autonumber isn't
sufficient for most operations. many need to prefix it with a letter, or
date. In my case, I need to have each record generate a number in the format
mmyy-#### with, or without the dash - preferably with.

So opening a new record for todays, today's would be 0408-0001 for that
record, and the next record today would be 0408-0002 and so on. When it
comes to next year, that's OK because the year will have changed (08 to 09)
As I won't be around in 99, I don't care about what happens then

Any asnwers that seem to deal with ithis don't explain how to apply the
formula - and for one like me, just diving , up late reading and researching,
waiting for a class to come along, extra help is needed to understand whther
to edit a table, a form, or just how to make it work.

Copy follows-------------
Hi Dave, and thanks for ther reply

Glad you asked - I expected that the new year would start the new number and
the 0000 would roll over after 9999. The numbers for date ned to be 2 digits
each. that is, Month ## and year ## (last 2 for year of course) followed by
the 4 digit.

Many thanks for your help.

The first part is easy
Format(Date,"mmdd")
How do you want to determine the rest?
What will you do next year when the numbers start recurring?

------------------------------ copy ends -------------------

S

 

[Klatuu]

Sorry, Shell, I didn't know we weren't done.
Okay there are a few issues surround this technique. Mostly, it has to do
with what you are wanting and that is an incrementing identifier related to
some other data item. One would initially think a field containing the
entire value would be the correct answer. It is, however, not the best
approach. The coding is much easier and the concept is within database
normalisation rules when the two elements are carried separately.

Now, keep in mind that what is carried in the database and what is presented
to users is not always the same thing. We will use your situation as an
example.

What you want to present is a year, month, and a sequential number. Those,
together, will comprise your PO number. The first thought would be to create
a text field that would show it as 080403-0001, Or as a decimal data type and
carry the year and month as the whole number and the sequential number as the
decimal part and use formatting to present it to the user.

Either of those choices would require parsing the value to retrieve it or to
create it. You would then have to either repeat the logic every where it is
used or create functions you could call throughout your application to
manipulate it.

The better method would be to use two fields. one for the grouping value
(department, PO number, Document number, date, etc.) and another for the
sequential number. This makes the coding much easier.

In your case, a Purchase Order will always have PO date. I have been doing
this for over 30 years and I have never seen a PO system where a PO doesn't
have a date. So, we can use that for the grouping part and just add one
field to the table using a Long Integer data type. So, to create a new PO,
we use the PO date, find the highest existing number in the PO Table, and add
1 to it.

That is the simplistic view, and shortly I will show the technique for that,
but first we have to look at the practicality of a multi user system. It is
remotely possible two users could be creating a new PO at the same time. So
both would use the technique to create a new number and be creating different
POs with the same number. The first to complete the PO and update the
underlying table would be okay, but the second would get an error when trying
to update the table. There are a number of ways to handle this, but I will
keep it simple.

First, here is how to create a new number.

LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = "
& Format(Date,"yymm"),0) + 1

This finds the highest PO number for the year and month and adds 1 to it.
The Nz function is used in the case there is no PO number for the current
month and date. It will convert the Null returned by the DMax function to 0.
0 + 1 = 1, so it will be the first number.

Now, where to put it? As I said, I want to keep it simple. First, use the
Form Current event and if it is a new record, create the number.

If Me.NewRecord Then
Me.txtPoDate = Date
Me.txtPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date],
""yymm"") = " & Format(Date,"yymm"),0) + 1
End If

Now, in the example above, I would make the visible propery of txtPoNum
False so the user doesn't see it and create another unbound control to
display the number to the user. In it's control source property:
=Format([txtPoDate],"yymm-") & Format([txtPoNum], "0000")

Now, to ensure another user has not created a PO with the same number, user
the form' Before Update event to check for the possibility and if it does,
cancel the update and warn the user and add 1 to value saved in the hidden
text box.

If Not IsNull(DLookup("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"")
= " & Format(Date,"yymm")) Then
MsgBox "PO Number " & Me.txtShowPO & " Is in Use " & vbNewLine &
"Creating New Number"
Me.txtPoNum = Me.txtPoNum + 1
Cancel = True
End If

Then you just need to provide a way for the user to retry the update.

Hope that answers your question

 

[Shell]

Many thans Dave - I have replied inline below.

Sorry, Shell, I didn't know we weren't done.
Okay there are a few issues surround this technique. Mostly, it has to do
with what you are wanting and that is an incrementing identifier related to
some other data item. One would initially think a field containing the
entire value would be the correct answer. It is, however, not the best
approach. The coding is much easier and the concept is within database
normalisation rules when the two elements are carried separately.

Now, keep in mind that what is carried in the database and what is presented
to users is not always the same thing. We will use your situation as an
example.

What you want to present is a year, month, and a sequential number. Those,
together, will comprise your PO number. The first thought would be to create
a text field that would show it as 080403-0001, Or as a decimal data type and
carry the year and month as the whole number and the sequential number as the
decimal part and use formatting to present it to the user.

Either of those choices would require parsing the value to retrieve it or to
create it. You would then have to either repeat the logic every where it is
used or create functions you could call throughout your application to
manipulate it.

The better method would be to use two fields. one for the grouping value
(department, PO number, Document number, date, etc.) and another for the
sequential number. This makes the coding much easier.

In your case, a Purchase Order will always have PO date. I have been doing
this for over 30 years and I have never seen a PO system where a PO doesn't
have a date. So, we can use that for the grouping part and just add one
field to the table using a Long Integer data type. So, to create a new PO,
we use the PO date, find the highest existing number in the PO Table, and add
1 to it.

That is the simplistic view, and shortly I will show the technique for that,
but first we have to look at the practicality of a multi user system. It is
remotely possible two users could be creating a new PO at the same time. So
both would use the technique to create a new number and be creating different
POs with the same number. The first to complete the PO and update the
underlying table would be okay, but the second would get an error when trying
to update the table. There are a number of ways to handle this, but I will
keep it simple.

First, here is how to create a new number.

LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = "
& Format(Date,"yymm"),0) + 1

This code above - I assume I need to create a Table and put it in there
somewhere? As it stands, I have a Form that has, until yesterday, created the
Table as I entered text Fileds. I have a text Field called POCalc (Control
Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh
record now has a number there from 1 to 144 (number of records so far).

SIDEBAR: I'm thinking perhaps I blew $700.00 and a new computer for
something that is waaaay over my head - but I'm starting to understand, and I
have been reading the "Microsoft Access 2007 Bible" 1,242 pages to go Mean
while... I'm trial and error, and reading the Forums.

This finds the highest PO number for the year and month and adds 1 to it.
The Nz function is used in the case there is no PO number for the current
month and date. It will convert the Null returned by the DMax function to 0.
0 + 1 = 1, so it will be the first number.

Now, where to put it? As I said, I want to keep it simple. First, use the
Form Current event and if it is a new record, create the number.

I've looked every where - no "Current event. Looked under Propertis, Ecnets,
in Table Propertis and elsewhere - no "Current" event.

If Me.NewRecord Then
Me.txtPoDate = Date
Me.txtPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date],
""yymm"") = " & Format(Date,"yymm"),0) + 1
End If

Now, in the example above, I would make the visible propery of txtPoNum
False so the user doesn't see it and create another unbound control to
display the number to the user. In it's control source property:
=Format([txtPoDate],"yymm-") & Format([txtPoNum], "0000")

Is it something I add to the "Current" event, of create a new Field and put
it in there?

Now, to ensure another user has not created a PO with the same number, user
the form' Before Update event to check for the possibility and if it does,
cancel the update and warn the user and add 1 to value saved in the hidden
text box.

If Not IsNull(DLookup("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"")
= " & Format(Date,"yymm")) Then
MsgBox "PO Number " & Me.txtShowPO & " Is in Use " & vbNewLine &
"Creating New Number"
Me.txtPoNum = Me.txtPoNum + 1
Cancel = True
End If

Then you just need to provide a way for the user to retry the update.

I'm sorry. You took a lot of time and effort to explain this and it all went
right over my head. I've printed it out and read through it and tried a few
things to no avail. I don't have a clue whwere this stuf goes. But all is not
lost on your part. Google will have this in His Church sooon and those
seeking an answer there will find this,. and benefit from it. Mean while,
I'll keep plugging the formulas abocve itno different places and see what
happens.

Thanks - your efforts are very appreciated.

 

[Klatuu]

LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = "

& Format(Date,"yymm"),0) + 1

This code above - I assume I need to create a Table and put it in there
somewhere? As it stands, I have a Form that has, until yesterday, created the
Table as I entered text Fileds. I have a text Field called POCalc (Control
Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh
record now has a number there from 1 to 144 (number of records so far).

No, there is no need for an additional table. I assume you have a table for
a PO. That table probably has a field that carries the date of the PO. The
idea is to add a long integer field to that table. That field would be the
field you would use to carry the sequence number. The DMax fucntion will
return the highest number in the sequence field for the date. Adding 1 to it
will give you the next PO sequence number for that date. If no records exist
in the table for the date, the DMax will return Null. Adding the Nz function
converts the Null to a 0. Adding 1, then creates the first PO sequence for
the date.

I've looked every where - no "Current event. Looked under Propertis, Ecnets,
in Table Propertis and elsewhere - no "Current" event.

The Current event is one of the Form events. Open the properties dialog for
the form, select the Events tab. You will find the Current event there.

Is it something I add to the "Current" event, of create a new Field and put
it in there?

Yes, the code above goes in the form's Current Event.

Hope this will help.

 

[Shell]

It's not you - it's me. I just can't wrap my had around this one. I pasted
the formula you gave (LngPoNum...) into the Current Event via Form. BTW - I
have just one Form, no tables except the one for Form. When I save, I get an
error popup saying it can't find the Object LngPoNum... et cetera.

I've been trying a lot of different ways with your suggestion - I created a
tblPO, and more - to no avail.

I'm checking with the local college to see if anyone there will take the
Form and add your suggestions - I'm sure someone there will understand it.
Keep in mind, I just opened the book on this and some of the terms are way
over my head.

Again, it's not you - and I really appreciate the extra steps you took to
try and explain it to me - but it's all just bouncing off

I'm sure many will benefit from your help.

S

LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = "
& Format(Date,"yymm"),0) + 1

This code above - I assume I need to create a Table and put it in there
somewhere? As it stands, I have a Form that has, until yesterday, created the
Table as I entered text Fileds. I have a text Field called POCalc (Control
Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh
record now has a number there from 1 to 144 (number of records so far).

No, there is no need for an additional table. I assume you have a table for
a PO. That table probably has a field that carries the date of the PO. The
idea is to add a long integer field to that table. That field would be the
field you would use to carry the sequence number. The DMax fucntion will
return the highest number in the sequence field for the date. Adding 1 to it
will give you the next PO sequence number for that date. If no records exist
in the table for the date, the DMax will return Null. Adding the Nz function
converts the Null to a 0. Adding 1, then creates the first PO sequence for
the date.

I've looked every where - no "Current event. Looked under Propertis, Ecnets,
in Table Propertis and elsewhere - no "Current" event.

The Current event is one of the Form events. Open the properties dialog for
the form, select the Events tab. You will find the Current event there.

Is it something I add to the "Current" event, of create a new Field and put
it in there?

Yes, the code above goes in the form's Current Event.

Hope this will help.

 

[Klatuu]

lngPONum is a variable.
You should dim the variable in the event at the top of the event code:

Dim lngPoNum As Long.

tblPO is just an example. You would need to use the name of your table and
use the names of your fields.

Don't give up. It will start making sense after a while.
--
Dave Hargis, Microsoft Access MVP

It's not you - it's me. I just can't wrap my had around this one. I pasted
the formula you gave (LngPoNum...) into the Current Event via Form. BTW - I
have just one Form, no tables except the one for Form. When I save, I get an
error popup saying it can't find the Object LngPoNum... et cetera.

I've been trying a lot of different ways with your suggestion - I created a
tblPO, and more - to no avail.

I'm checking with the local college to see if anyone there will take the
Form and add your suggestions - I'm sure someone there will understand it.
Keep in mind, I just opened the book on this and some of the terms are way
over my head.

Again, it's not you - and I really appreciate the extra steps you took to
try and explain it to me - but it's all just bouncing off

I'm sure many will benefit from your help.

S

LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = "
& Format(Date,"yymm"),0) + 1

This code above - I assume I need to create a Table and put it in there
somewhere? As it stands, I have a Form that has, until yesterday, created the
Table as I entered text Fileds. I have a text Field called POCalc (Control
Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh
record now has a number there from 1 to 144 (number of records so far).

No, there is no need for an additional table. I assume you have a table for
a PO. That table probably has a field that carries the date of the PO. The
idea is to add a long integer field to that table. That field would be the
field you would use to carry the sequence number. The DMax fucntion will
return the highest number in the sequence field for the date. Adding 1 to it
will give you the next PO sequence number for that date. If no records exist
in the table for the date, the DMax will return Null. Adding the Nz function
converts the Null to a 0. Adding 1, then creates the first PO sequence for
the date.

I've looked every where - no "Current event. Looked under Propertis, Ecnets,
in Table Propertis and elsewhere - no "Current" event.

The Current event is one of the Form events. Open the properties dialog for
the form, select the Events tab. You will find the Current event there.

Is it something I add to the "Current" event, of create a new Field and put
it in there?

Yes, the code above goes in the form's Current Event.

Hope this will help.

 

[Clif McIrvin]

Shell, I definitely agree with Dave -- there's a lot of territory to
cover in Access, and there is a very definite learning curve. I like
to use "critical mass" as an analogy -- there is a point where
concepts begin to come together; the pathway leading to that point can
certainly be arduous!

 

[Shell]

Thanks Clif.
I've spent too much time already trying to figure out thow to Dim a LngPoNum
and in what field, macro, or Expression builder, on what Table or Form - plus
looking up How-To-Dim is a whole new world on VBA writing (I think)

I'm trying to get the books suggested, found one and it's only 1300 plus
pages (Access 2007 Bible), Step x Step Access 2007 and Access In and Out.
I've enough reading now to last me into my next life!

I am still trying to digest where exactly it is Klatuu (Dave) is suggesting
those forumlas (statements?) get placed, and how. Still copying, pasting,
testing and putting the Access error codes to the test

Thanks

S