Autogenerate reference

[CraigBFG]

As we all know, autonumber generates despite a record being saved.

I have a small script which does generate the next job number but I need it to be a bit cleverer.

Set rst = CurrentDb.OpenRecordset("SELECT Max([JobNumber]+1) AS NextJob FROM Jobs")
If Not rst.EOF Then
JobNumber.Value = rst!NextJob

End If

What I'm looking for is to autogenerate numbers based on the year & month.

Example
PL04010001 - 04=2004 01=January 001=next ref
So in february it would start again.
PL04020001
and so on.

Can anyone help me with this task?
Thanks

 

[dcx693]

This is a common topic. Check out this link: daily incrementals. It describes how to use DMax (not DCount!) to do this.

 

[Mile-O]

Can anyone help me with this task?

How's this?

I've actually put some other stuff in there...

 

[CraigBFG]

Mile

That's a fantastic demo, however my requirement is ever so slightly different - perhaps you could help.

Instead of using system date to generate code, I need to use a user entered date - an invoice date.

Then, when the month changes, the ref should start from zero.

0402001,0402002,0402003
0403001,0403002,0403003
0404001, etc

Can you help - Oh wise one!

 

[Mile-O]

    strYear = Format(Date, "yy")
    strMonth = Format(Date, "mm")

Basically becomes:

    strYear = Format(Me.DateTextBox, "yy")
    strMonth = Format(Me.DateTextBox, "mm")

You can verify, however, that something purported to be a date is actually a date with the IsDate() function.

I also used an autonumber as the primary key still and just indexed the code; believe me, it's safer.

 

[CraigBFG]

That's great, I'll try that - how about the reset of counter

 

[Mile-O]

How about the reset of counter

It should do that itself.

 

[CraigBFG]

Changing to me!txtData works a treat, however the counter doesn't reset

01/02/2004 - 0402001
02/02/2004 - 0402002
01/03/2004 - 0403003

any ideas?

 

[Mile-O]

Changing to me!txtData works a treat

No: Me.txtData

however the counter doesn't reset

Change the line in AssignNumber() to this:

strCode = Nz(DMax("DataID", "tblData", "Left([GeneratedCode],4) = '" & strYear & strMonth & "'"), 0)

 

[CraigBFG]

Oooo, that's good...but it starts well, and then uses the autonumber.

01/01/2004 = 0401001
02/01/2004 = 0401020 (record number)
03/01/2004 = 0401021
01/02/2004 = 0402001 (looking good)
02/02/2004 = 0402023 (doh!)

any clues Mile?

 

[Mile-O]

I can't recreate that; my copy is producing the correct results. Wipe the table and start again - it's probably an old record in there that's throwing it off.

 

[CraigBFG]

Tried that, and compacted afterwards so the counter would start at 0.

I'm using A2k on XPPro.
Attached db as it stands after various mods. I've converted the txtData box to be a date input box.

thanks for all your help, if you can recreate this, then excellent.

 

[Mile-O]

Here we go:

You'd commented out a line, for some reason.

 

[CraigBFG]

Oops, got ahead of myself there, commented out the line when I placed in the previous reset line.

I bow at your feet - oh wise one

 

[CraigBFG]

and now....

as if you hadn't helped me enough already - grovel, creep - can this wonderful script be mod'd to place characters before the generated code.

For example.

PL0402001

Basically, this will become a transaction code within my database, the PL showing that it came from the Purchase side.

 

[CraigBFG]

Mile

Cracked it, using your fantastic script as a base, I've added a few line and It now places characters before the generated code.

Thanks again for your help - much appreciated.
CraigBFG

 

[Mile-O]

No Probs!