How to make record Version and Number

[Jon]

Hi all,
I have a form for Purchasing orders contain the following fields:
ID >>>autonumber
P.ONo. >>>>>number
Version>>>>>Yes/No(Checkbox)
VersionNo.>>>>>number
Amount>>>>number
.....etc
What I want to do is, when the user input the P.O. and he marks it as
Version(Yes) the VersionNo will be(0). After that, if the user updated the
existing P.O , a copy of it will be created and the versionNo. Will be (1) if
the user marks it as a version. Otherwise, the copied record will be hold
until the user marks it as version.
Any suggestion please?

 

[NevilleT]

Hi Jon
If I understand what you are saying, you want to have the option to enter a
new version number. If you put a command button on the form, it can run an
SQL statement to select the highest version number for the PO. You can then
add one and put it in the version number textbox. Another way - and it may
or may not work depending on the way your form operates - is to have a button
that makes version number = version number + 1.

Neville Turbit
www.projectperfect.com.au

 

[Jon]

Hi NevilleT and thank you for response
Yes, but the problem is how to copy the P.O Details if update took place,
and nothing will not happen for the original P.O. after that, how to make the
versionNO. Added. I do not have clear image and experience in how to do that.
If you have any codes which can be helpful, please advice?

 

[NevilleT]

Hi Jon

Not tested this so it may need a bit of tweaking. In the BeforeUpdate event
for the form try this:

Dim lngPONo as Long
Dim lngVersion as Long
Dim dblAmount as Double

Dim dbs as Database
Dim rst as Recordset
Dim qdf as QueryDef
Dim strSQL as String

if Me.chkNewVersion = True then
lngPONo = Me.txtONo
lngVersion = Me.txtVersion
dblAmount = Me.txtAmount

' Create the new version number
strSQL = "SELECT tblPO.ONo, tblPO.VersionNo FROM tblPO WHERE tbl.ONo = "
& lngPONo & " ORDER BY VersionNo;"

Set dbs=CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 0 then ' No records
lngVersion = 0
Else
rst.MoveLast ' The
highest version number
lngVersion = rst!VersionNo + 1 ' Add one to the version
end if

' Create the new record

strSQL = ""INSERT into tblPO(PONo, VersionNo, Amount )" & _
" VALUES(" & lngPONo & ", & lngVersion & ", " & dblAmount & ");"
Set qdf = dbs.CreateQueryDef("", strSQL) ' Create
new QueryDef.
qdf.Execute dbSeeChanges ' Run
the insert query

' Don't save the current record
Cancel=True

End If

As I said, I have not tested it and you will probably have to change some of
the field names but hopefully it will give you a start.