Automatically incrementing a value in a field

T

TechyGal

Hi There,

I have a database which will be used to record documents issued from
the company and I need to implement a form of version control for the
documents.

The version format protocol for the company states the following ...

External Documents issued will have the following version format:
1.0, 1.1, 1.2 ... 2.0, 2.1 etc

Internal Documents issued will have the following version format:
1.0.1, 1.0.2 ... 2.0.1, 2.0.2 etc

Therefor any client would only ever see the external version and once
internal changes had been made the internal version would be
incremented to the next External version for issue e.g. 1.0.2 would
become 1.1 for issue.

My problem in the database world is that I have a .adp project
database which has a SQL backend and Access 2007 (well 2000 as it is
a .adp project) front end.

The first problem I have is that I am unsure of what data type I could
use to store these extended version numbers of 1.0.1 etc??

The second is that I need to be able to perform some VB function which
will sit behind an 'Up-Version' button, which will automatically
increment the value stored in the Version number field after having
moved the contents of the record to a 'previous versions' table.

I had this function setup to work when the version format was only
setup as the external structure of 1.0, 1.1, 1.2 etc.

Here is the code that I had behind the button:

Code: ....

Private Sub Cmd_New_Version_Click()
On Error GoTo Err_Cmd_New_Version_Click

Dim intNew_Version As String
Dim intProject_ID As String
Dim strNewVersion As String
Dim str_Version_ID As String

intNew_Version =
Me.Frm_Documents_Issued_External.Form.Document_ID.Value
intProject_ID = Me.Frm_Documents_Issued_External.Form.Project_ID.Value

Dim stDocument_ID As String


DoCmd.RunSQL "INSERT INTO Tbl_Documents_Issued_Version_External
(Document_ID, Project_ID, Version_Number_ID, Audit_Required,
Audit_Completed, Audit_ID, Document_Type, Document_Title,
Document_Description, Date_Issued, Person_Whom_Created_Document,
Person_Whom_Issued_Document, Link_To_Document, Person_Issued_To,
Persons_Copied_To, Links_To_Related_documents, Reason_For_Issue,
Action_Required, Action_ID, Internal_Notes) " & _
"SELECT Document_ID, Project_ID, Version_Number_ID, Audit_Required,
Audit_Completed, Audit_ID, Document_Type, Document_Title,
Document_Description, Date_Issued, Person_Whom_Created_Document,
Person_Whom_Issued_Document, Link_To_Document, Person_Issued_To,
Persons_Copied_To, Links_To_Related_documents, Reason_For_Issue,
Action_Required, Action_ID, Internal_Notes FROM Tbl_Documents_Issued
where Document_ID = '" & intNew_Version & " ' and Project_ID = '" &
intProject_ID & "'"

strNewVersion = Nz(DMax("[Version_Number_ID]",
"Tbl_Documents_Issued_Version_External", "[Document_ID] ='" &
intNew_Version & "'AND [Project_ID] ='" & intProject_ID & "'")) + 0.01

str_Version_ID = Nz(DMax "[Version_ID]",
"Tbl_Documents_Issued_Version_External"))


Me.Refresh

DoCmd.RunSQL "UPDATE Tbl_Documents_Issued SET Version_Number_ID = '" &
strNewVersion & "', Document_Title = NULL, Audit_Required = 0,
Audit_Completed = 0, Date_Issued = NULL, Link_To_Document = NULL,
Person_Whom_Created_Document = NULL, Person_Issued_To = NULL,
Person_Whom_Issued_Document = NULL, Persons_Copied_To = NULL,
Reason_For_Issue = NULL, Action_Required = 0, Internal_Notes = NULL
WHERE (((Document_ID) = '" & intNew_Version & " ') AND ((Project_ID) =
'" & intProject_ID & "'))"

Me.Refresh


Exit_Cmd_New_Version_Click:
Exit Sub

Err_Cmd_New_Version_Click:
MsgBox Err.Description
Resume Exit_Cmd_New_Version_Click

End Sub

Code End.....

Using this method the Version_ID field is of a numeric data type with
a precision of 2 and a Scale of 1.

This worked really well and I was really chuffed with the solution.

However, having now been given the task of extending the version
number to 1.0.1 I am unsure of the best path to take in sourcing a
solution!

If any one can PLEASE make any suggestions or advice of a possible
solution I would appreciate it greatly!

Many Thanks In Anticipation,

TechyGal :)
 
T

Tom Wickerath

Hi TechyGal,
The first problem I have is that I am unsure of what data type I could
use to store these extended version numbers of 1.0.1 etc??
Click to expand...

It looks like a text field is your only choice (varchar in SQL Server?)
The second is that I need to be able to perform some VB function which
will sit behind an 'Up-Version' button, which will automatically
increment the value stored in the Version number field
Click to expand...

Would something like this work?

strVerNumber = (10*Val(strVerNumber)+1)/10


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

TechyGal said:
Hi There,

I have a database which will be used to record documents issued from
the company and I need to implement a form of version control for the
documents.

The version format protocol for the company states the following ...

External Documents issued will have the following version format:
1.0, 1.1, 1.2 ... 2.0, 2.1 etc

Internal Documents issued will have the following version format:
1.0.1, 1.0.2 ... 2.0.1, 2.0.2 etc

Therefor any client would only ever see the external version and once
internal changes had been made the internal version would be
incremented to the next External version for issue e.g. 1.0.2 would
become 1.1 for issue.

My problem in the database world is that I have a .adp project
database which has a SQL backend and Access 2007 (well 2000 as it is
a .adp project) front end.

The first problem I have is that I am unsure of what data type I could
use to store these extended version numbers of 1.0.1 etc??

The second is that I need to be able to perform some VB function which
will sit behind an 'Up-Version' button, which will automatically
increment the value stored in the Version number field after having
moved the contents of the record to a 'previous versions' table.

I had this function setup to work when the version format was only
setup as the external structure of 1.0, 1.1, 1.2 etc.

Here is the code that I had behind the button:

Code: ....

Private Sub Cmd_New_Version_Click()
On Error GoTo Err_Cmd_New_Version_Click

Dim intNew_Version As String
Dim intProject_ID As String
Dim strNewVersion As String
Dim str_Version_ID As String

intNew_Version =
Me.Frm_Documents_Issued_External.Form.Document_ID.Value
intProject_ID = Me.Frm_Documents_Issued_External.Form.Project_ID.Value

Dim stDocument_ID As String


DoCmd.RunSQL "INSERT INTO Tbl_Documents_Issued_Version_External
(Document_ID, Project_ID, Version_Number_ID, Audit_Required,
Audit_Completed, Audit_ID, Document_Type, Document_Title,
Document_Description, Date_Issued, Person_Whom_Created_Document,
Person_Whom_Issued_Document, Link_To_Document, Person_Issued_To,
Persons_Copied_To, Links_To_Related_documents, Reason_For_Issue,
Action_Required, Action_ID, Internal_Notes) " & _
"SELECT Document_ID, Project_ID, Version_Number_ID, Audit_Required,
Audit_Completed, Audit_ID, Document_Type, Document_Title,
Document_Description, Date_Issued, Person_Whom_Created_Document,
Person_Whom_Issued_Document, Link_To_Document, Person_Issued_To,
Persons_Copied_To, Links_To_Related_documents, Reason_For_Issue,
Action_Required, Action_ID, Internal_Notes FROM Tbl_Documents_Issued
where Document_ID = '" & intNew_Version & " ' and Project_ID = '" &
intProject_ID & "'"

strNewVersion = Nz(DMax("[Version_Number_ID]",
"Tbl_Documents_Issued_Version_External", "[Document_ID] ='" &
intNew_Version & "'AND [Project_ID] ='" & intProject_ID & "'")) + 0.01

str_Version_ID = Nz(DMax "[Version_ID]",
"Tbl_Documents_Issued_Version_External"))


Me.Refresh

DoCmd.RunSQL "UPDATE Tbl_Documents_Issued SET Version_Number_ID = '" &
strNewVersion & "', Document_Title = NULL, Audit_Required = 0,
Audit_Completed = 0, Date_Issued = NULL, Link_To_Document = NULL,
Person_Whom_Created_Document = NULL, Person_Issued_To = NULL,
Person_Whom_Issued_Document = NULL, Persons_Copied_To = NULL,
Reason_For_Issue = NULL, Action_Required = 0, Internal_Notes = NULL
WHERE (((Document_ID) = '" & intNew_Version & " ') AND ((Project_ID) =
'" & intProject_ID & "'))"

Me.Refresh


Exit_Cmd_New_Version_Click:
Exit Sub

Err_Cmd_New_Version_Click:
MsgBox Err.Description
Resume Exit_Cmd_New_Version_Click

End Sub

Code End.....

Using this method the Version_ID field is of a numeric data type with
a precision of 2 and a Scale of 1.

This worked really well and I was really chuffed with the solution.

However, having now been given the task of extending the version
number to 1.0.1 I am unsure of the best path to take in sourcing a
solution!

If any one can PLEASE make any suggestions or advice of a possible
solution I would appreciate it greatly!

Many Thanks In Anticipation,

TechyGal :)
Click to expand...
 
T

TechyGal

Hi Tom,

Sorry for the delay in getting back to you, unfortunately got side
tracked by another project.

The method you suggest below works great for the 1.0 increment to
1.1, 1.2, 1.3 ... 2.0

Quoted Text:
Would something like this work?

    strVerNumber =  (10*Val(strVerNumber)+1)/10
Click to expand...

End Quote!

But I need to be able to increment the third tear value as well i.e.
1.1.0, 1.1.1, 1.1.2 ..... 1.1.9 1, .2.0 etc.

Any ideas?

Many Thanks,

TechyGal :)
 
V

viktor chuzhakin

TechyGal said:
Hi Tom,

Sorry for the delay in getting back to you, unfortunately got side
tracked by another project.

The method you suggest below works great for the 1.0 increment to
1.1, 1.2, 1.3 ... 2.0

Quoted Text:


End Quote!

But I need to be able to increment the third tear value as well i.e.
1.1.0, 1.1.1, 1.1.2 ..... 1.1.9 1, .2.0 etc.

Any ideas?

Many Thanks,

TechyGal :)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Updateing a record with a Null value field, fails? 5
return a null value to date field 2
VBA works in orignal but fails in copy of database 1
Syntax to write a null value inside a sql Insert string? 1
variable to field in table 6
Populating a null field in record with previous record value 0
How do you refer to a null field in a UDF 1
need to check records in subform and delete man name from combo bo 0

Top