RE: Report/Invoice numbering convention

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


Thanks, Mike. Haven't finished putting this example in place yet but I
wanted to let you know your help is greatly appreciated.

"MikeJohnB" wrote:

By the way, you can see the whole issue by typing "Serial Numbers again" (No
qotes) in the "Search For" window for new users news group. (Thats why,
pressing YES to answering the question is important, the thread stays active
longer. Date of posting Dec 2005)

Again hope this helps
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"MikeJohnB" wrote:

I have posted codes sent to me by Jerry Whittle for doing exactly what you
want to do. You can perhaps ignor the first part, the part of interest should
be the where it say "Now what to do about starting over" Have a read, I think
you may be able to adapt this by changind fields and control names to your
convention. It worked a treat for me with thanks going to Jerry.

I really hope this jem helps you?

Regards
Mike B (Now the Code below)

Create a table named tblSeqNum with on Long field named SeqNum and a Date
field called RestartDate.

The default value for the serial number field on your form will be:
=DMax("SeqNum","tblSeqNum")

Also the format for your serial number needs to be 000 in the table, forms,
and all reports to show the leading zeros.

In the BeforeInsert event of your form you need the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum =
[tblSeqNum]![SeqNum]+1;"
DoCmd.SetWarnings True
End Sub

OK. This will increment the number. It's possible that if
two people are entering records at the same time that the numbers could get
out of proper sequence. It also does not take into account if someone
creates a new record then deletes it.

Now what to do about starting over at the beginning of the month..
Put the code below in the Form Open event of the input form.
It checks the RestartDate whenever the form is open and updates it if over
a month old. Watch out for word wrapping if you cut and paste.

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum = 1,
tblSeqNum.RestartDate = Now() WHERE Year([RestartDate]) &
Month([RestartDate])<Year(Date()) & Month(Date());"
DoCmd.SetWarnings True
End Sub

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

.

Relevant Pages

  • Re: reading photoshop layers
    ... Mike D Sutton wrote: ... > Delphi isn't too bad at all to work with, I use it on a regular basis at work and find it to be somewhere in the middle ... > the docs or in the source code itself that suggested it could decode individual layers, ... but even then a few hours with a hex editor and some version of the format spec. ...
    (microsoft.public.vb.winapi.graphics)
  • RE: Not sure this should be Excel or ACCESS
    ... Mike, here's what I tried to do so far. ... Dim cnn As ADODB.Connection ... Dim rowNumber As Long ... As far as the report in access in concerned you dont need it. ...
    (microsoft.public.excel.programming)
  • Re: Self Decrypting Archive Freeware?
    ... mike wrote: ... >> current compression program. ... All I care about is that I have some old hardware that ... common format you have assurance that the FORMAT will be ...
    (sci.crypt)
  • Re: (NBC) The Mitchell List
    ... in the Mitchell Report. ... Mike Bell ... Jeff Williams ... The following players were cited under "Alleged Internet ...
    (rec.music.artists.springsteen)
  • Re: calulate working hours and minutes between 2 dates and times
    ... the original answer from mike works with the last bit from Peo Sjoblom ... Format both as general ... Task are presented across the rows so for example arrival time column ...
    (microsoft.public.excel.misc)