ACCESS FEED: RE: [MS_AccessPros] Auto Increment with version number

Selasa, 02 April 2013

RE: [MS_AccessPros] Auto Increment with version number

Zjubias-

How will the user, when entering a new quotation, specify that this is a
revision of an existing quotation?

As for "automatically" providing the revision number, you will need some VBA
code in the form's Before Update event. The code would look up the previous
high revision number for the quotation, add 1, and set it. The code might
look like:

Dim varQuoteNo As Variant, intRev As Integer, strQuoteNo As String

' If this is not a new record,

If Not Me.NewRecord Then

' Do nothing

Exit Sub

End If

' Get leftmost 5 digits of current quote

strQuoteNo = Left(Me.QuotationNumber, 5)

varQuoteNo = DMax("QuotationNumber", "MyTable", "QuotationNumber Like '"
& strQuoteNo & "*'")

' If no previous quote,

If IsNull(varQuoteNo) Then

' This is the first rev

intRev = 1

' If number returned has no revision,

ElseIf Len(varQuoteNo) = 5 Then

' This is the first rev

intRev = 1

Else

' Get the previous high rev number off the end + 1

intRev = CInt(Right(varQuoteNo, 2)) + 1

End If

' Update the value

Me.QuotationNumber = strQuoteNo & "-" & Format(intRev, "00")

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Zosimo J.
Ubias
Sent: Wednesday, April 03, 2013 5:20 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Auto Increment with version number

Hello all,

Is there an easy way to achieve the below referencing version incrementing?

i.e. quotation number: 10001. Then if a revision will occur it will be
automatically giving me "01", i.e. "10001 - 01", 02, 03 and so on..

Thank you in advance for any help..

Zjubias

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]