Sorry for late response (have just got back online)... but what you might
want to do, is go to the form design, open the properties dialog box, choose
the 'Event' tab and find the 'Before Update' option, choose the drop down
box and select [Event Procedure]. Then click the button to the right (with
the three dots)... Add the following code;
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lstNumber As Long
If [Order Number] <> 0 Then Exit Sub
If DCount("[Last Number]", "[Last Used Numbers]", _
"[Prefix]=" & glbPrefix) < 1 Then
lstNumber = 1
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Last Used Numbers] " & _
"([Prefix],[Last Number]) VALUES (" & _
glbPrefix & ",1);"
DoCmd.SetWarnings True
Else
lstNumber = DLookup("[Last Number]", _
"[Last Used Numbers]", _
"[Prefix]=" & glbPrefix)
lstNumber = lstNumber + 1
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [Last Used Numbers] SET " & _
"[Last Used Numbers].[Last Number] = " & _
lstNumber & " WHERE (([Last Used Numbers]." & _
"[Prefix])=" & glbPrefix & ");"
DoCmd.SetWarnings True
End If
Me![Order Number] = Val(Str$(glbPrefix) & Format(lstNumber, "000000"))
End Sub
NOTE: this assumes that the field of the current table (with your orders),
is called 'Order Number' and assumes there is a table, called 'Last Used
Numbers', which has two fields of 'Prefix' and 'Last Number'. Also, you will
need to replace the glbPrefix with the prefix number of this user (not sure
how you are determining the prefix number for your user).... BEWARE: this
may cause errors, depending on the data type of your order number field, in
the order table. It will work fine, if the data type is a double. If it is a
string, then remove the VAL function. If it is a long, then you will get an
error, as the formatted number, is now 10 digits in length (the maximum long
value is 2147483648, but your numbers will start with 5001000001).
MAIN ISSUE: the big issue, is that the order number is not shown to the
user, until the order is written to the table... Thus, they may never see
the order number (depending if it is a single record per page, form design,
where the record is written, if you go to the next record, and thus, the
order number is updated in the background). Therefore, another option, may
be to allocate the order number (if one doesn't exist), when the user starts
entering the order details. Thus, if they are editing a current record, the
order number will already exist and be displayed (making sure that the order
number field is NOT editable) and if they are entering a new record, then
the order number will be determined and displayed, but not actually stored
in your orders table yet.
SparrowCE said:
Thank you so much, I apologize for any confusion, I
accidentally posted this two different times yesterday.
I've made a table per below but get an error on the form
telling me the variable is not defined (myPrefix). What am
I doing wrong? Code is like another language to me that I
have not learned yet, and am struggling with what may seem
simple things to those who know. So thank you for your
understanding and time, it is greatly appreciated.
-----Original Message-----
Meant to add, there is also an issue with this option... If you allocate the
next number and the user opts not to save the record, then number sequences
can be missed... This can be fixed, by when the number is written (ie: in
the beforeupdate of the entire record, if there is no order number).
Alternatively, you could have two fields in the order table, of prefix and
autonumber and create the primary key of the table, as a combination of the
two fields....
Ruskin Hardie said:
Had this problem myself, with sales order numbers. The main issue, is that
the number could be assigned from the sales order table, but since the
fields weren't filled out, before the data was written, then another user
could also get the same number.
The solution, was to create another table, that had two fields;
Prefix (in this case 5000, 5001, etc)
Last Number
The Last Number field, holds the last order number for this particular
prefix. Thus, when an order is raised, the system looks at the "last used
number" table, to find the last number for that prefix.
It then adds one
to
this table and formats that number, like;
LastNumber = DLookup("[Last Number]","[Last Used Numbers]", _
"[Prefix]=" & myPrefix)
'Update last number, with last number + 1
OrdNum = Str$(myPrefix) & Str$(Format(LastNumber, "00000"))
What is best way to auto assign/save consecutive numbering
that will be combined with a prefix to make a complete
order number. Prefix is a 6 digit alphanumeric number
assigned to a site location ID, each site will have
numbering beginning with 5000, 5001, etc., so the full
number will be 6 digit prefix5000 .... prefix 5001, etc.,
the prefix according to the site.
My form filters the user down to the right site ID and
opens with a new number (DMax + 1) however, I'm not able to
fill the other fields (prefix & site ID) with the default
data per site location and save that number back to main
table. Once the number is assigned and the user confirms
by submitting, it would take them to another form that is
set up for them to enter numerous other manual detail.
Would appreciate any help, thank you.
.
