code Receiptno increment not working.........

[2015sandeep]

i have data entry form entry field [Receiptno] required number 1 to 100 increment by 1 in each entry auto started from 1.

The code below not working........

Private Sub ReceiptNo_AfterUpdate()
If Me.NewRecord = True Then
    ReceiptNo = Nz(DMax("[receiptno]", "[receiptno]"), 0)
    Me.ReceiptNo = ReceiptNo + 1
    End If
End Sub

plz help....

[twinnyfo]

2015sandeep,

Welcome to Bytes!

First, please use Code Tags when posting your code.

What is the name of the Table that holds the field "ReceiptNo"? This should be the second argument in your DMax() Function. You can also streamline your Code:

If Me.NewRecord = Then Me.ReceiptNo = Nz(DMax("[ReceiptNo]", "[tblName]"), 0) + 1

However, the big problem I see is what you will do when you exceed 100 records? Why not just use an AutoNumber which will auto increment all your records each time a new one is made? This would be a more standard methodology, unless, there is some particular reason that the ReceiptNo must reset at 100....

[2015sandeep]

Private Sub Save_Click()
If IsNull(Me![ReceiptNo]) Then
Me.[ReceiptNo] = Format(Nz(DMax("receiptno", "compiled", "bookno]='" & Me.BookNo & "'"), 0) + 1, "000")
End If
End Sub

this code not working what to do for increment receiptno???? plz help

[MikeTheBike]

Hi

You need an opening square bracket to match the closing bracket, ie. this

Me.[ReceiptNo] = Format(Nz(DMax("receiptno", "compiled", "bookno]='" & Me.BookNo & "'"), 0) + 1, "000")

should be like this

Me.[ReceiptNo] = Format(Nz(DMax("receiptno", "compiled", "[bookno]='" & Me.BookNo & "'"), 0) + 1, "000")

I assume that receiptno and bookno are both Text fields.


MTB

[2015sandeep]

i have tabled named compiled, and fields bookno, receiptno
data needed like...
bookno receiptno
1230 1
1230 2
1231 1
1231 2

form created but code not working...

Private Sub ReceiptNo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.NewRecord) Then
Me!ReceiptNo = Nz(DMax("receiptno", "compiled", "bookno='" & BookNo), 0) + 1
End If
End Sub

[MikeTheBike]

Hi

I think pehaps you mean this

Private Sub ReceiptNo_BeforeUpdate(Cancel As Integer)
If IsNull(Me!ReceiptNo) Then
Me!ReceiptNo = Nz(DMax("receiptno", "compiled", "bookno='" & BookNo), 0) + 1
End If
End Sub

However this would also do it (I thiink?)

Private Sub ReceiptNo_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me!ReceiptNo = Nz(DMax("receiptno", "compiled", "bookno='" & BookNo), 0) + 1
End If
End Sub

I think this would be the more logical way because if ReceiptNo is Null and it is not a new record then something is wrong!?

MTB

[jforbes]

I think this line should either be:

Me!ReceiptNo = Nz(DMax("receiptno", "compiled", "bookno=" & BookNo), 0) + 1

or

Me!ReceiptNo = Nz(DMax("receiptno", "compiled", "bookno='" & BookNo & "'"), 0) + 1

depending whether or not BookNo is numeric.