Selasa, 09 April 2013

RE: [MS_AccessPros] Auto Increment with version number

 

John,

Thank you, and sorry for not coming back sooner, a little bit tied on other
things.

Ok i will manually input the quote number and then hit a button to create a
revision number.

Am not good in code, but I will try to play with your code below if I can
put it in work.

Considering the above scenario, can you tailor your code please to have
behind a button.

Thank you,

Zjubias

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, April 03, 2013 9:19 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Zosimo J.
Ubias
Sent: Wednesday, April 03, 2013 5:20 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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]

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

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar