Number AutoCorrect 2

[pushrodengine via AccessMonster.com]

I need an Invoice Number text box in the format “0000-yyâ€. The catch is,
users
are supposed to enter “0345-yyâ€, but some will forget to enter the zero “345-
07â€.
How can I get it so that if the zero is left out an expression will
automatically enter it?

Thanks

 

[BruceM]

One choice is to use an input mask in an attempt to force the users to enter
six digits. Something like this may do it:
0000-00
However, it does not prevent users from entering the zero at the end.
Assuming the year is already in a date field somewhere on the form you could
have the user enter just the number into an InvoiceNumber field, then do
something like:
=Format([InvoiceNumber],"0000") & "-" & Format([InvoiceDate],"yy"))
as the control source for a text box. This would diplay the number in the
desired format.
If the number is to be sequential, you could assign it automatically, and
not count on the user to enter the next number correctly. I can sketch that
out, if you like.

 

[pushrodengine via AccessMonster.com]

BruceM, I would greatly appreciate that!

Thanks

 

[BruceM]

Here is one way, which will work very well for a number that does not need
to start over from 1 at the beginning of a new year.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Another way is to use an Event on the form. For instance, if the invoice
date is manually entered, you could have something like this in the After
Update event for the InvoiceDate text box:
If Me.NewRecord Then
Me.InvoiceNum = Nz(DMax("InvoiceNum", "tblInvoice", _
"Year([InvoiceDate]) = " & Year([InvoiceDate])), 0) + 1
End If
Me.Dirty = False

Quick explanation: The DMax function looks for the record with the largest
value in the InvoiceNum field in the table tblInvoice for which the year
from the InvoiceDate field equals the InvoiceDate on the current record.
This last element (the Where condition) is represented by the part of the
expression after the second comma:
"Year([InvoiceDate]) = " & Year([InvoiceDate])
In other words, if the largest value in InvoiceNumber for a record from this
year is 345, that is the value generated by the DMax function. The +1 adds
1 to this value.
The Nz function allows for the possibility that there is no record yet for
the current year, in which case Nz converts the Null (essentially, Null
means "unknown") to a 0. Adding 1 to this makes the first invoice for the
new year number 1.

If the InvoiceDate defaults to the current date, you could use an Event
other that the text box After Update event to generate the number. The
point of Me.Dirty = False is to save the record as soon as the number is
assigned. This prevents two users from getting the same number if they are
both entering invoice data at about the same time.

I don't know if you are familiar with adding event procedures, but I will be
away off and on for the next week, so here is a summary. To add code to the
After Update event of a text box (I will call it txtInvoiceDate), open the
form in design view, select the text box, and click View > Properties (or
use the Properties icon on the toolbar). This opens the Property Sheet.
Click the Events tab, and click the After Update item. Click the three dots
that appear at the right side, click Code Builder, and click OK. The VBA
editor will open, and you should see:

Private Sub txtInvoiceDate_AfterUpdate()

End Sub

The cursor should be blinking between those two lines. Add the code there,
substituting your actual table and field names.

You would use the same basic procedure to add code to any Event Procedure.
For a command button, use the toolbox to create the command button, click to
select, then open the Property Sheet as before.

 

[pushrodengine via AccessMonster.com]

Thank you BruceM!

 

[BruceM]

Glad to help. I hope this gives you a sense of what Access can do to
automate various tasks.