Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: Incrementing numbers

    This code is working for one record - but neeed change multiple records with the same revision number "revision" and "RMR_Nbr" and they all need to be incremented by 1. Is there a way??

    Private Sub RevisionNumber()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim count As Integer
    Set rs = Currentdb.OpenRecordset("SELECT RMR_NBR FROM tblRMR19_3 WHERE RMR_NBR ='" & Me.cmbRMR & "'")

    icount = rs.RecordCount
    Set db = Currentdb

    If rs.RecordCount <> 0 Then
    rs.MoveLast

    Me!revision = (revision) + 1
    Me.RevisionDt = Now
    'Me.Requery

    Else
    Me!revision = 1
    rs.Edit
    rs("revision") = Me!revision
    rs.update
    End If
    ' End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    ''Me.cmbCRM.Requery
    ''Me.Combo191.Requery
    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about a faster, easier, less-typing approach?

    dim strSQL as string
    strSQL = "UPDATE tblRMR19_3 SET revisionField = revisionField + 1 WHERE RMR_NBR ='" & Me.cmbRMR & "';"
    currentdb.execute strSQL


    i don't understand your plans for revisiondt - if this is also supposed to find it's way into the table, maybe you need:
    strSQL = "UPDATE tblRMR19_3 SET revisionField = revisionField + 1, dateField = #" & format$(now, "mm/dd/yyyy hh:nn:ss") & "# WHERE RMR_NBR ='" & Me.cmbRMR & "';"

    your code is fairly strange in any case
    Else
    Me!revision = 1
    rs.Edit
    rs("revision") = Me!revision
    rs.update
    End If

    if the recordset is empty (the meaning of your Else) you can't set the value of a field in a (non-existant) record. in any case, after taking all the effort to increment revision in some records it seems bizarre to want to reset it to 1 again in other records.

    and:
    your SELECT does not include a field (or alias) 'revision' so
    Me!revision = (revision) + 1
    is doomed (and in any case, your (revision) should have been Me!revision)

    finally, your SELECT uses single quotes in
    RMR_NBR ='" & Me.cmbRMR & "'"
    suggesting that RMR_NBR is text. _NBR smells like a NumBeR to me, so you might need to drop those single quotes.

    izy

    using the real names for revisionField and dateField of course
    Last edited by izyrider; 10-29-06 at 02:53.
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2006
    Posts
    2
    Thanks - I will see - the records seem to be duplicating so I will try to fix with your suggestion....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •