Update Query for Revision Number (1 Viewer)

[tmyers]

I am having problems wrapping my head on how to solve an issue with an update query.
The query is simple in that all it does it takes the revision number of the given form and increases it by one. The problem I have discovered is if you choose to start at an earlier revision and run the query from there, you end up duplicating a number.

Example:
Job 123456 has been revised 4 times, so there are 123456-1, 123456-2 etc.
When I am on 123456-4 and create another revision, 123456-5 is created BUT if I am on 123456-3, ANOTHER 123456-4 is created.

I believe I just need to use MAX() somewhere to make sure I get the highest number and then increment, but I am having a hard time with what should be a simple thing.

Here is my current SQL statement:

UPDATE tblQuoteDetails SET tblQuoteDetails.RevisionNum = [RevisionNum]+1
WHERE (((tblQuoteDetails.JobID)=[Forms]![JobQuote]![JobID]));

 

[Pat Hartman]

Typically you get the Max value this way:

If Me.NewRecord = True Then
    Me.RevisionNumber = Nz(DMax("RevisionNumber", "tblQuoteDetils", "JobID = " & Forms!JobQuote!JobID,0))+1
End If

If your form is bound, I'm not sure why you are running an update query. This code would go into the BeforeUpdate event of your form. If the record is new, a rev number is generated. Otherwise, it is not changed.

You should always lock the rev number control to prevent changes to it.

 

[plog]

This doesn't make sense. Specifically, why an UPDATE query? Why not an INSERT query?

You are updating an existing record, but its possible another record exists for that and create a duplicate? How did that record get to that revision number?

 

[tmyers]

To provide context, this is the last process ran when a revision is created. When the user decides to create a revision, the entire recordset on the form is copied, assigned a new JobID for indexing purposes and then the revision number is increased by one. I will admit that I created this whole process while still very much learning (and I am still by no means great at this) and this was just the method I used then.

Here is the process where it is currently situated if it helps:

Private Sub CreateRevisionbtn_Click()

Dim strinput
Dim strsql
Dim strdate

    strinput = InputBox(prompt:="Please name the new revision / package.")
   
        If strinput = vbNullString Then
            Exit Sub
        Else
            Call CreateRevisions
            DoCmd.SetWarnings False
            strsql = " UPDATE tblQuoteDetails"
            strsql = strsql & " SET RevisionName = '" & strinput & "'"
            strsql = strsql & " WHERE JobID = " & [Forms]![JobQuote]![JobID]
            DoCmd.RunSQL strsql
            'CurrentDb.Execute strsql
            DoCmd.SetWarnings True
        End If
       
        If MsgBox("Has the bid date changed?", vbYesNo, "Confirm Bid Date") = vbYes Then
            DoCmd.OpenForm "BidDateForm", acNormal
        End If
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "IncrementRevisionNumQry"
    DoCmd.SetWarnings True
       
    Me.UnlockQuoteBtn.Visible = True
    Me.UnlockQuoteBtn.Enabled = True
    DoCmd.Requery

End Sub

EDIT:
To add yet more clarification on the process:
Job 123456-1 is created when a user creates a new job. If after the job is done and it needs revised, the entirety of 123456-1 is duplicated and a new 123456-1 is created (with a new and unique JobID) and this final query updates it to 123456-2. That order of events is why I am having this problem since if you duplicated 2 when you already have 4 you end up with another 3.

I will 100% admit this entire process is sloppy and needs to be improved, but it is also probably the most complicated process in the entirety of my Access app and I have not yet learned enough to improve it.

 

[Pat Hartman]

I showed you how to get the correct rev number. Use that code to replace:

strinput = InputBox(prompt:="Please name the new revision / package.")

 

[arnelgp]

I am having problems wrapping my head on how to solve an issue with an update query.

for revision Number, you don't Update it.
you simply Create New record for it.
also you should add "remarks/note" what is the revision about.

you can then use DMax() function to get the "latest revision" in use.

 

[Auntiejack56]

For your information, the whole solution suggested by the good people above will look like this:

myFormID = Forms!JobQuote!JobID
NewRevNo = Nz(DMax("RevisionNum", "tblQuoteDetails", "JobID = " & myFormID),0) + 1

strSQL = "UPDATE tblQuoteDetails AS t SET t.RevisionNum = " & NewRevNo & " WHERE t.JobID = " & myFormID & ";"

Be careful with the DMax - one contributor to this thread posted an example that is missing a closing bracket. And you might notice that I have used an aliased table name - when using inline SQL it is much easier to read.

 

[tmyers]

I was able to use your solution and got it working correctly Pat. Thank you so much!

 

[Pat Hartman]

You're welcome