Custom Autonumber Conditional Formatting

  • Thread starter Thread starter Frankie via AccessMonster.com
  • Start date Start date
F

Frankie via AccessMonster.com

I have the following problem I still can't solve after reading posts:
1 table : tblInvoice
1 form : FormInvoice
Aim : create a custom autonumber on the form composed of [DateInvoice]
(dd/mm/yy)+ [Year(now)](last 2 digits) and [CustomAutoNum](2 digits) which
will increment within the month but will set back to 01 when month changes.
ex: Invoice of 10/04/05 (3rd in the month) will create autonum as 030405.
Invoice of 12/05/05 (4th in the month) = 040505
I have tried to put the following code in the BeforeUpdate Event of the
form (where [AutoInvoice]is a form control box bound to tblInvoice which
returns the incremented value, and [Nmois] is a form unbound control box
displaying [DateInvoice] formatted as month) :
If IsNull(Me!AutoInvoice) = True Then
Me!AutoInvoice = Nz(DMax("AutoInvoice", "tblFacture", "Month(DateFacture) =
" & Me!Nmois & ""), 0) + 1
End If
But it doesn't work !
May someone tell me what's wrong ??
Thanks in advance.
Frankie
 
Frankie,

Try like this...
If IsNull(Me!AutoInvoice) Then
Me!AutoInvoice =
Nz(DMax("AutoInvoice","tblFacture","Month(DateFacture)=" &
Month(DateInvoice)), 0) + 1
End If

It is not clear what DateFacture refers to, so I hope I have correctly
interpreted. You had a syntax error with "s in the wrong place. But
the main problem, I think, is trying to refer to the Nmois control...
formatting it as month only affects the way the data is displayes, it
does not affect the actual value of the data, so you see the month, but
the full date is what is actually there.
 
Thank you for your answer, Steve.
It works perfect.
Actually [DateFacture] is the same as [DateInvoice]. I just translated it
from french.
Thanks again.
Frankie.
 
Back
Top