Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert/Delete New Record, Renumber Old (Access 200/SR1)

    We're implementing a new documentation process detailing the specific work done by our employees. These documents have a specific template, with work broken down into logical chunks, then into specific detail. These documents can change often, as improvements for methods to do the job are found. In our infinite wisdom, the template are--of course!--in Microsft Excel. I have decided it would be more appropriate to make an Access database to hold this information. This would give us more publishing flexibility, better document control, and a more user-friendly interface for input.

    This should answer potential questions about why I'm using Access for this.

    The problem now is that the interface needs to be as foolproof as possible. I've overcome many obstacles, but this one is driving me nuts, and I'm placing my faith in the gurus here.

    I have a table called tblSteps that have the specific information for the job. They are the specifics to the "chunks" in tblElements, related by ElementID. These steps, with a StepID, are numbered in order of execution: StepNumber. What I'd like to do is make it so a user can click the "Insert Step" button on the subform sfrmElementSteps. This would increment all subsequent steps by one, create a new record, and assign the appropriate step number to the new record. I'm currently using the AbsolutePosition property to capture the current position within the recordset, using a For...Next loop to the record count and adding 1 to the StepNumber, then creating a record and assigning the old step number to the field StepNumber. The problem is, on occassion, AbsolutePosition returns a -1, and the step numbers don't renumber the way I expect. I have a feeling there's a better way to do this. I've attached a working copy.

    Help is greatly appreciated!

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert/Delete New Record, Renumber Old (Access 200/SR1)

    Hi!
    I couldn't download your file, our security policy won't let me, so apologies if any of this reply is irrelevant.
    I normally manage without using AbsolutePosition - perhaps I'm a bit old fashioned. I'd ascertain what the new stepnumber would be (intStepNumber), set up a recordsource, go to the first record. If stepnumber was less than intStepNumber, ignore, otherwise increment by one. Remember to update recordsource before moving on. Finally, add a new record with the desired stepnumber. Refresh the form's data source. Voila! (at least in my simplistic universe)
    Jules

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Insert/Delete New Record, Renumber Old (Access 200/SR1)

    Code like this might do what you want:

    Private Sub cmdInsert_Click()
    Dim strSQL As String
    Dim intStepNum As Integer
    Dim lngElementID As Long

    intStepNum = Me.StepNumber
    lngElementID = Me.ElementID
    strSQL = "UPDATE tblSteps SET StepNumber = StepNumber + 1 " & _
    "WHERE ElementID = " & lngElementID & " AND StepNumber >= " & intStepNum
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    ' Create a new record, and assign the old step number to it.
    DoCmd.GoToRecord , , acNewRec
    [ElementID] = lngElementID
    [StepNumber] = intStepNum
    [MajorStep] = "x"

    Me.Requery
    ' Ensure the Next/New button says "Next Step>>"
    Me.cmdNext.Caption = "Next Step>>"
    End Sub

    But there is something strange with your Next>> button - it never seems to let you go past the second record. I didn't have time to investigate this.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert/Delete New Record, Renumber Old (Access 200/SR1)

    I knew I could count on the Lounge! Thanks, Hans and Jules--most especially for proving once again the simplest solutions are often (always?) best.

    As a post script, Hans, the reason the "Next Step>>" button only goes to the second record is because for that particular element, there are only two steps. For Element #2, there are several more. The button works exactly like I hoped it would (at least something had to).

    Thanks again for the help.

Posting Permissions

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