Recordset Question

[cpsqlrwn]

I have the following code in the AfterUpdate event of a subform datasheet. It is working properly, going to the next record in the list, and if on the last record in the datasheet, moving to the first record rather than saving the record and going to a new record. However, if my datasheet only has one record in it, it does not stay on that record after editing even though that record is the first and the last record. It moves to a new record. Can someone explain this please and help me resolve? Thank you!!

Dim CurDB As Database
Dim rs As recordset

Set CurDB = CurrentDb
Set rs = Me.RecordsetClone

If Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
    DoCmd.RunCommand acCmdRecordsGoToFirst
    rs.Close
    Set rs = Nothing
    Exit Sub
    Else
    DoCmd.RunCommand acCmdRecordsGoToNext
    rs.Close
    Set rs = Nothing
    Exit Sub
End If

 

[PHV]

If you don't want to create new records, why not simply set the AllowAdditions property of the form to False ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cpsqlrwn]

The allow additions property is set to No. Only allow edits is set to Yes. This is a datasheet form on one page of a tab control. The other pages on the tab control have forms that are for adding, viewing, and deleting records and their properties are set accordingly. This tab control resides on a subform where new records are added, so the properties of the subform is set to allow additions. Anyway the result of all these circumstances is that this form, even though properties are set to No Additions, allows additions after the last record is edited.

 

[PHV]

And what about the Cycle property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cpsqlrwn]

I need to clarify. If there is only one record on the edit quantities datasheet (form set to edit only), after editing that record, instead of remaining on that record (since it is the last and the first record in the recordset), it jumps from the form and the subform record which is being edited is saved, thus allowing no more editing at that point. The datasheet records previously being edited are no longer in the current record. The current record in the subform is a new record. If there are multiple records on my datasheet, my code works fine. I can move from item to item to edit quantities and if I leave the last record in the datasheet after editing quantities, my cursor moves to the first record in the datasheet. When I am finished editing quantities, I can move to the other tab pages or save my record with a command button. The only problem is when there is only one record on the datasheet. Also, the datasheet form is set to cycle the current record.

 

[PHV]

Seems to confirm the consensus, here at TT, to avoid Datasheets and play with continuous forms ...

 

[TheAceMan1]

How are ya cpsqlrwn . . .

. . . and this:

[blue]   If Me.CurrentRecord = Me.Recordset.RecordCount Then
      DoCmd.RunCommand acCmdRecordsGoToFirst
   Else
      DoCmd.RunCommand acCmdRecordsGoToNext
   End If[/blue]

Be aware: you loose alot of form functionality with [blue]DataSheet View[/blue]. Its easy enough to make continuous view look just like a datadheet and [blue]regain full functionality![/blue]

See Ya! . . . . . .