Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    move within a recordset (2000 all updates)

    When a command button on a form is pressed, a Project is changed from a Quote to Invoice Details, the Project is marked as "Quote Accepted," and data in a subform copied (duplicated in the same table) but the duplicated data marked "Invoice" (a Yes/No field). I want then to show the Invoice data rather than the Quote data, so need to update the form so that the Invoice data (Invoice=Yes) shows and not the Quote data (Invoice=No).

    I also need to test to be see (1)whether or not there is more than one record, and (2)whether or not we are on the last record.

    Following is the code behind the command button to accomplish this.

    <code>Dim recClone As DAO.Recordset
    Set recClone = Me.RecordsetClone
    If recClone.RecordCount > 1 Then
    If recClone.EOF Then
    DoCmd.GoToRecord , , acPrevious
    DoCmd.GoToRecord , , acNext
    Else
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    End If
    ElseIf recClone.RecordCount = 1 Then
    DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToRecord , , acPrevious
    End If

    recClone.Close
    Set recClone = Nothing</code>

    Is this an acceptable method, or is there a better way?

    I initially thought that RecClone.MovePrevious and RecClone.MoveNext would work, but they don't.

    Tom

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

    Re: move within a recordset (2000 all updates)

    You could look at Me.CurrentRecord; this is the "m" in "Record m of n" in the navigation buttons. You can compare this to Me.RecordsetClone.RecordCount.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move within a recordset (2000 all updates)

    Actually, I think the following code does the job more efficiently.

    <code>Dim hldID As Long
    hldID = Me.ProjectID
    Me.Requery
    Me.Recordset.FindFirst "ProjectID = " & hldID</code>

    There is an earlier piece in the code that checks for an empty recordset...
    <code>If Me.Recordset.RecordCount = 0 Then
    Exit Sub
    End If</code>

    Tom

Posting Permissions

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