Results 1 to 4 of 4
  1. #1

    MovePrevious Command (Microsoft Office 97)

    Hi Everybody

    I am fairly new to VB, so please bear with me. I am working on Access 97. I have a database that contains serial numbers and a work history for each serial number. However, for each line of the work history, the serial number repeats itself. What I would like to do is keep the first record for each serial number and delete the others. This is the program I have developped; there seems to be a problem with the moveprevious command. Could somebody help me or suggest another way of proceeding.

    Rachelle <img src=/S/hello.gif border=0 alt=hello width=25 height=29>Option Compare Database
    Option Explicit

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Dim db As Database
    Set db = CurrentDb()

    Dim rs As Recordset

    Set rs = db.OpenRecordset("Select * from [new hires+update histories]")

    Dim intSerial As Integer

    While Not rs.EOF
    intSerial = rs("serial")

    If intSerial <> rs("serial") Then
    End If


    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

    Private Sub Form_Click()

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: MovePrevious Command (Microsoft Office 97)

    Are those serial numbers and work history entries all in one big, flat table? If so and you delete the records, you will lost the work history that relates to them. Is that what you want?

    In your code, you're storing the serial number to a variable and then moving to the next record and comparing it's serial number value to the variable value. If they aren't equal, you're trying to move back and delete the previous record. So if the value has changed, you're deleting the last record with the previous value. But if the value hasn't changed, you just keep going.

    So if you have multiple records with the same serial number, you'll only delete one of them and you probably have no control over which one that is. First has no real meaning in a relational table, because the order is determined by either indexes or the natural order in which the records were entered, something you can't rely on. If you sort the records to group them by serial, they'll come together. If not, they will appear in order of entry or some other order you specify. So define what you mean by "first."

    On the other hand, if you only have one record for a serial number, you will delete that one, whether you want to or not, because the serial value will change in the next record.

    Lastly, you'll never delete any of the last set of serial values because there isn't any next to move to for comparison. You'll also hit a No Current Record error at the end of the recordset.

    Why don't you try explaining more about what you actually want to accomplish and why, and someone will be able to help you figure out how.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Sydney, Australia, New South Wales, Australia
    Thanked 4 Times in 4 Posts

    Re: MovePrevious Command (Microsoft Office 97)

    If you just want to see the first record for each serial number, a simple Group By query with a First on some other field would give this result.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Redditch, Worcestershire, England
    Thanked 22 Times in 21 Posts

    Re: MovePrevious Command (Microsoft Office 97)

    I agree with Peter.

    When you say "first" you need perhaps to define a query to start with that sorts the table by whichever field defines the required sort order.

    A Group By query can then pick out the "first" record for each serial number. If you are happy with the results it gives you can convert it into a Make Table query and then either delete the original table if you don't need it, or keep it as a detail archive.


Posting Permissions

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