moving in a recordset 2

[THWatson]

Using Access 2000

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.

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

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

 

[THWatson]

Which is better? The above method, or the one below? Any reason for choosing one over the other?

If Me.Recordset.RecordCount > 1 Then
    If Me.CurrentRecord = Me.Recordset.RecordCount Then
    DoCmd.RunCommand Command:=acCmdRecordsGoToPrevious
    DoCmd.RunCommand Command:=acCmdRecordsGoToNext
        Else
        DoCmd.RunCommand Command:=acCmdRecordsGoToNext
        DoCmd.RunCommand Command:=acCmdRecordsGoToPrevious
    End If
ElseIf Me.Recordset.RecordCount = 1 Then
DoCmd.RunCommand Command:=acCmdRecordsGoToNew
DoCmd.RunCommand Command:=acCmdRecordsGoToPrevious
End If

Tom

 

[MajP]

The recordsetclone is a "snapshot" copy of the recordset. So you can work with it, but not affect the information displayed on the form and saved to the underlying table. So moving in the clone will not move the form. The recordset of the form is the actual recordset. So if you move the recordset in code or alter the data the form will move / change.

Your syntax will have some very very mild affects on speed, but neither code makes any sense to me. What is the reason to move back, forward, or to a new record only to return? This looks like some very clunky way to requery the form.

 

[MajP]

this is slightly better because in your code you call this
Me.Recordset.RecordCount
three times. It is better to define a variable instead of calling the same thing over again. This is just better coding practice, but with something this small would make no real difference.

Private Sub cmdMoveRec_Click()

Dim intRecCount As Integer
intRecCount = Me.RecordsetClone.RecordCount

If Me.NewRecord Then
  Exit Sub
  'not sure what you want to do here
End If
If intRecCount > 1 Then
    If me.CurrentRecord = intRecCount Then
      DoCmd.RunCommand acCmdRecordsGoToPrevious
      DoCmd.RunCommand acCmdRecordsGoToNext
    Else
       DoCmd.RunCommand acCmdRecordsGoToNext
       DoCmd.RunCommand acCmdRecordsGoToPrevious
    End If
ElseIf intRecCount = 1 Then
  DoCmd.RunCommand acCmdRecordsGoToNew
  DoCmd.RunCommand acCmdRecordsGoToPrevious
End If
End Sub

What is this code really supposed to do?

 

[THWatson]

MajP
Thanks for replying. Let me try to explain further.

Main form is frmCustomers
On a tab page is fsubProjects. It has 2 nested subforms in which Materials and Labour are entered.

User issues a Quote for the Project. The Quote itemizes Materials and Labour that will be used.

In the Project, there is an Invoice (Yes/No) field. When a Quote is accepted by the Customer, a command button duplicates the rows in Materials and the row in Labour and marks the Project as Accepted.

When this occurs, I want only the Invoice (Yes) data to show on the form, so that the integrity of the original Quote is preserved and cannot be altered. That is the reason for cycling to a different record and then back. The OnCurrent event for the form stipulates whether Quote or Invoice data is to be displayed...based on whether or not the Quote has been Accepted.

I tried various other methods to requery, or refresh the form, but always ended up on the first record in the form, and I want to return to this record.

If you can suggest a method that isn't "clunky" I would be grateful.

Tom

 

[MajP]

Public Sub requeryReturn()
  Dim rs As DAO.Recordset
  Dim intID As Long
  'put your unique key below
  intID = Me.PartID
  Me.Requery
  With Me.RecordsetClone
   .FindFirst "PartID = " & intID
  If Not .NoMatch Then
   Me.Bookmark = .Bookmark
  End If
  End With
End Sub

 

[MajP]

delete the
dim rs as dao.recordset (old code)

 

[THWatson]

Thank you very much!!

Works like a charm.

Tom

 

[TheAceMan1]

Howdy THWatson . . .

I knew there was more under the hood! Your 2nd to last post should've been your 1st. [blue]MajP[/blue] beat me to it, but here's my 2 cents:

[blue]   Dim hldID As Long
   
   hldID = Me.PartID
   Me.Requery
   Me.Recordset.FindFirst "PartID = " & hldID[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

TheAceMan1
That's a very interesting 2 cents. Less code, with need for .NoMatch and .Bookmark lines, and no need to create a subroutine and therefore things can be done right in the command button code without need to call the subroutine. Works perfectly.

I apologize that my 1st post didn't convey all that was under the hood. I thought it did but obviously I didn't step back far enough.

Aceman, you've looked under the hood a few other times when I brought this car into the shop along the way. This, I think, was the last piece of the database that needed fixin'

Thanks again.

Tom

 

[TheAceMan1]

THWatson . . .

No need for apologies! However, if you look at your post origination and compare it to your 2nd explanation, you'll see [blue]what bridged the gap[/blue] for us! I'm sure enough of us were waiting for the hood to open. It took [blue]MajP[/blue] to crack the seal! Keep this bridge in the back of your mind in any communication. For myself as well, its been a huge learning curve.

Good to hear that [blue]completion[/blue] of the Db has surfaced!

[blue]In any case, you know where to find us![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

Yep, I see what bridged the gap.

What has been a huge learning curve on this particular database was that I hadn't done one before where both a Quote (whose integrity must be preserved) was needed and also provide the basis for Invoice details which could be edited, added to, rows deleted, or whatever.

Couldn't have done it without the assistance on this site.

Tom