Random PO Number Generated with a Format Mask? Possible?

[gbwanabe via AccessMonster.com]

I have been using an alpha-numeric PO system at work and have recently been
asked to create a database to automate a great deal of our system. We are a
small company(less than 100 employees, 1 inventory person, me!), and I am
trying to learn Access as fast as possible. Is there anyway to randomly
generate a PO with a format masked field? I have a form with a command button.
I would like to click that button and generate the PO when I need to order
from a Vendor. The field would of course be my primary key for my table and
therefore need to be unique.

Any ideas or help would be most graciously accepted,

Thanks in advance,

Rob

--
Rob
Trying to get ahead at work by dazzling the higher ups with my brain rather
than my stunning good looks and strong back.

Message posted via AccessMonster.com

 

[BruceM]

The short answer is probably Yes. The longer answer is that the details
matter. Fields (and controls such as text boxes) can be formatted, and they
can have input masks, but it is unclear what you mean by "format mask". An
automatically generated number would not have an input mask, which may be
used for things like telephone numbers and other data that need a consistent
pattern. Therefore I assume you are talking about a format, in which case
you will need to describe it. You can create an incremented number by the
method described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
In general you would have something like this as the default value in a text
box bound to the number field:
=Nz(DMax("YourField","YourTable") + 1
The default value comes into play only when it is a new record, so old POs
will have the original number assigned when they were created. To set the
format to 0001, 0002, etc. you would enter 0000 as the format for the text
box bound to the number field.
You can also use VBA to create the number. Again, details matter. Is this
database to have several concurrent users? What is the alphabetic portion
of the number?

 

[gbwanabe via AccessMonster.com]

Sorry. That's what I meant. Input Mask. Wrote That after only one cup of
coffee. The PO would look like this

ABC123456

Three Letters then Six numbers, randomly generated.

The Database would have at the most three concurrent users, but most times
only two. Future plans for the database included linking inventory to our
client services department for orders and automatic deductions from our
inventory.

I am very new to VBA and Access but am not scared of it. Hit me with it.

The short answer is probably Yes. The longer answer is that the details
matter. Fields (and controls such as text boxes) can be formatted, and they
can have input masks, but it is unclear what you mean by "format mask". An
automatically generated number would not have an input mask, which may be
used for things like telephone numbers and other data that need a consistent
pattern. Therefore I assume you are talking about a format, in which case
you will need to describe it. You can create an incremented number by the
method described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
In general you would have something like this as the default value in a text
box bound to the number field:
=Nz(DMax("YourField","YourTable") + 1
The default value comes into play only when it is a new record, so old POs
will have the original number assigned when they were created. To set the
format to 0001, 0002, etc. you would enter 0000 as the format for the text
box bound to the number field.
You can also use VBA to create the number. Again, details matter. Is this
database to have several concurrent users? What is the alphabetic portion
of the number?

I have been using an alpha-numeric PO system at work and have recently been
asked to create a database to automate a great deal of our system. We are

[quoted text clipped - 13 lines]

Rob

--
Rob
Trying to get ahead at work by dazzling the higher ups with my brain rather
than my stunning good looks and strong back.

Message posted via AccessMonster.com

 

[Guest]

If you choose the code approach:

I have a master number table, some times tables, each of which as one
record. That record houses the next number to be used for a unique record
number in another table. (I often have more than one field in that record,
each respresenting different fields in different forms/tables.) Those fields
are number fields.
In my form I assign a new number, if appropiate, resetting the field in the
master table so it houses the next number to be used.
Here is some code:
If IsNull(Me.CmpltNum) Then
Me.CmpltNum.SetFocus
Dim dbs As Database
Dim CBrst As Recordset, CBNumsRst As Recordset
Dim CurID As String, NxtID As String

Set dbs = DBEngine.Workspaces(0).Databases(0)

Set CBNumsRst = dbs.OpenRecordset("TablNums")

CurID = CBNumsRst![CmptNum]
NxtID = (CurID * 1) + 1
CBNumsRst.Edit
CBNumsRst!SID = NxtID
CBNumsRst.Update
Me.CmpltNum = "C" & Pad6Num(CurID)
End If

The "Pad6Num" is a function that I wrote because I want the field to always
be 7 characters long (in your Invoice table, for example, you would have a
text field(or in sql nvarchar). For above it would read C000001, C000002, etc.

Hope that helps, Elaine

 

[BruceM]

Where does the ABC come from? Is that random also? If you truly need a six
digit random number you could use an autonumber field, set its New Values to
Random, and set its format to 000000. I will say it's tough to figure why
you would want that approach, unless you really don't like your accountant
or business manager. Sequential numbering is usually the choice for POs,
invoices, and the like. I'll wait to hear more from you about the
alphabetic part of the number.

Sorry. That's what I meant. Input Mask. Wrote That after only one cup of
coffee. The PO would look like this

ABC123456

Three Letters then Six numbers, randomly generated.

The Database would have at the most three concurrent users, but most times
only two. Future plans for the database included linking inventory to our
client services department for orders and automatic deductions from our
inventory.

I am very new to VBA and Access but am not scared of it. Hit me with it.

The short answer is probably Yes. The longer answer is that the details
matter. Fields (and controls such as text boxes) can be formatted, and
they
can have input masks, but it is unclear what you mean by "format mask".
An
automatically generated number would not have an input mask, which may be
used for things like telephone numbers and other data that need a
consistent
pattern. Therefore I assume you are talking about a format, in which case
you will need to describe it. You can create an incremented number by the
method described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
In general you would have something like this as the default value in a
text
box bound to the number field:
=Nz(DMax("YourField","YourTable") + 1
The default value comes into play only when it is a new record, so old POs
will have the original number assigned when they were created. To set the
format to 0001, 0002, etc. you would enter 0000 as the format for the text
box bound to the number field.
You can also use VBA to create the number. Again, details matter. Is
this
database to have several concurrent users? What is the alphabetic portion
of the number?

I have been using an alpha-numeric PO system at work and have recently
been
asked to create a database to automate a great deal of our system. We
are

[quoted text clipped - 13 lines]

Rob

--
Rob
Trying to get ahead at work by dazzling the higher ups with my brain
rather
than my stunning good looks and strong back.

Message posted via AccessMonster.com

 

[gbwanabe via AccessMonster.com]

That part of the system was put in place before I came to this position. It
isn't an intergral part, but keeping with a consistent format, I decided to
keep it. As far as a hatred for a business manager or accountant, I have
stories, but again, not part of the reason for having it.

The alpha part of the PO corresponded to the initials of the person
requesting the item. The Numeric portion was the date. If the person was
ordering from more than one vendor per day that would create a duplicate
value, violating the unique value in a primary key field. If we put a
randomly generated alphanumeric PO in that field, it would be easier for
someone who is filling in or for others who might log on to the DB and order
supplies.

Where does the ABC come from? Is that random also? If you truly need a six
digit random number you could use an autonumber field, set its New Values to
Random, and set its format to 000000. I will say it's tough to figure why
you would want that approach, unless you really don't like your accountant
or business manager. Sequential numbering is usually the choice for POs,
invoices, and the like. I'll wait to hear more from you about the
alphabetic part of the number.

Sorry. That's what I meant. Input Mask. Wrote That after only one cup of
coffee. The PO would look like this

[quoted text clipped - 42 lines]

 

[BruceM]

Seems like a lot of trouble to go to in order to preserve a flawed system.
You cannot generate a random alphanumeric number directly, although you
could contrive one. If you use autonumber (or a properly designed
incremented number) the number will be unique. You don't need the letters
too. You could make a random letter field (I will call it LetterID) by
creating a table field (text) and setting its default value to:
=Chr(65+CInt(26*Rnd())) & Chr(65+CInt(26*Rnd())) & Chr(65+CInt(26*Rnd()))
Create an autonumber field (I will call it OrderID) as described in the
previous post. This will be your primary key. Make a query based on the
table. Create a new field:
CombinedID: LetterID & OrderID
You can use CombinedID wherever you want the PO number to appear.
If you want to maintain any sort of order with the POs, be sure to make
provision for that, since a random number will not serve that purpose.
Neither will date if there are two or more POs per day.
To increment a number you could use the default value of a number field:
=DMax("OrderID","YourTable") + 1
An incremented rather than random number will also provide a sequence except
that it is very likely to have gaps in the numbering.

That part of the system was put in place before I came to this position.
It
isn't an intergral part, but keeping with a consistent format, I decided
to
keep it. As far as a hatred for a business manager or accountant, I have
stories, but again, not part of the reason for having it.

The alpha part of the PO corresponded to the initials of the person
requesting the item. The Numeric portion was the date. If the person was
ordering from more than one vendor per day that would create a duplicate
value, violating the unique value in a primary key field. If we put a
randomly generated alphanumeric PO in that field, it would be easier for
someone who is filling in or for others who might log on to the DB and
order
supplies.

Where does the ABC come from? Is that random also? If you truly need a
six
digit random number you could use an autonumber field, set its New Values
to
Random, and set its format to 000000. I will say it's tough to figure why
you would want that approach, unless you really don't like your accountant
or business manager. Sequential numbering is usually the choice for POs,
invoices, and the like. I'll wait to hear more from you about the
alphabetic part of the number.

Sorry. That's what I meant. Input Mask. Wrote That after only one cup of
coffee. The PO would look like this

[quoted text clipped - 42 lines]

Rob

 

[gbwanabe via AccessMonster.com]

Thanks so much for responding. I will take your recommendations into
consideration. It really does make more sense to go incremental rather than
random. I will look at the design of my DB and make a decision.

Thanks again. I am sure I will have a million more questions before I am
through with this "Monster"

Seems like a lot of trouble to go to in order to preserve a flawed system.
You cannot generate a random alphanumeric number directly, although you
could contrive one. If you use autonumber (or a properly designed
incremented number) the number will be unique. You don't need the letters
too. You could make a random letter field (I will call it LetterID) by
creating a table field (text) and setting its default value to:
=Chr(65+CInt(26*Rnd())) & Chr(65+CInt(26*Rnd())) & Chr(65+CInt(26*Rnd()))
Create an autonumber field (I will call it OrderID) as described in the
previous post. This will be your primary key. Make a query based on the
table. Create a new field:
CombinedID: LetterID & OrderID
You can use CombinedID wherever you want the PO number to appear.
If you want to maintain any sort of order with the POs, be sure to make
provision for that, since a random number will not serve that purpose.
Neither will date if there are two or more POs per day.
To increment a number you could use the default value of a number field:
=DMax("OrderID","YourTable") + 1
An incremented rather than random number will also provide a sequence except
that it is very likely to have gaps in the numbering.

That part of the system was put in place before I came to this position.
It

[quoted text clipped - 27 lines]

 

[BruceM]

Good luck. This sounds like a pretty good starter project, and the
numbering can be solved readily enough, but sometimes it's worth considering
whether the old way of doing things really needs to be carried forward.
In developing the design, consider a Vendors table. In general, you would
have a Vendors table related one-to-many with an Orders table (one
vendor/many orders), and an OrderDetails table likewise related to the
Orders table (one order/many line items). This will let you do such things
as find out with one mouse click how much you spent per vendor for a
specified time period. A Google groups search should turn up some helpful
links and threads. You haven't said much about your design, but if you are
new to Access it is at least possible that you are thinking in spreadsheet
terms. For something such as you have described, a relational model makes
much better sense.
There is a book called "Database Design for Mere Mortals" that is said to be
very good. I can't recall the author's name, but the title should guide you
on any search for the book.

Thanks so much for responding. I will take your recommendations into
consideration. It really does make more sense to go incremental rather
than
random. I will look at the design of my DB and make a decision.

Thanks again. I am sure I will have a million more questions before I am
through with this "Monster"

Seems like a lot of trouble to go to in order to preserve a flawed system.
You cannot generate a random alphanumeric number directly, although you
could contrive one. If you use autonumber (or a properly designed
incremented number) the number will be unique. You don't need the letters
too. You could make a random letter field (I will call it LetterID) by
creating a table field (text) and setting its default value to:
=Chr(65+CInt(26*Rnd())) & Chr(65+CInt(26*Rnd())) & Chr(65+CInt(26*Rnd()))
Create an autonumber field (I will call it OrderID) as described in the
previous post. This will be your primary key. Make a query based on the
table. Create a new field:
CombinedID: LetterID & OrderID
You can use CombinedID wherever you want the PO number to appear.
If you want to maintain any sort of order with the POs, be sure to make
provision for that, since a random number will not serve that purpose.
Neither will date if there are two or more POs per day.
To increment a number you could use the default value of a number field:
=DMax("OrderID","YourTable") + 1
An incremented rather than random number will also provide a sequence
except
that it is very likely to have gaps in the numbering.

That part of the system was put in place before I came to this position.
It

[quoted text clipped - 27 lines]

Rob

--
Rob
Trying to get ahead at work by dazzling the higher ups with my brain
rather
than my stunning good looks and strong back.

 

[gbwanabe via AccessMonster.com]

I just recieved the two books from Amazon.com " Access 2002 Desktop
Developer's Handbook " and "Access 2002 VBA Handbook ". I will definitely be
looking anywhere and everywhere for help and hints. Design will always be a
great wonder for me. "Did I design the right way?" .."I should have built
this table instead of that table...." Relationships will be a big question.
I will probably be here a lot. Thanks for all of your help.

Good luck. This sounds like a pretty good starter project, and the
numbering can be solved readily enough, but sometimes it's worth considering
whether the old way of doing things really needs to be carried forward.
In developing the design, consider a Vendors table. In general, you would
have a Vendors table related one-to-many with an Orders table (one
vendor/many orders), and an OrderDetails table likewise related to the
Orders table (one order/many line items). This will let you do such things
as find out with one mouse click how much you spent per vendor for a
specified time period. A Google groups search should turn up some helpful
links and threads. You haven't said much about your design, but if you are
new to Access it is at least possible that you are thinking in spreadsheet
terms. For something such as you have described, a relational model makes
much better sense.
There is a book called "Database Design for Mere Mortals" that is said to be
very good. I can't recall the author's name, but the title should guide you
on any search for the book.

Thanks so much for responding. I will take your recommendations into
consideration. It really does make more sense to go incremental rather

[quoted text clipped - 30 lines]