Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recordset Question

Status
Not open for further replies.

cpsqlrwn

IS-IT--Management
Jul 13, 2006
106
US
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!!


Code:
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
 
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
 
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.
 
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
 
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.
 
Seems to confirm the consensus, here at TT, to avoid Datasheets and play with continuous forms ...
 
How are ya cpsqlrwn . . .

. . . and this:
Code:
[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] [thumbsup2]

Calvin.gif
See Ya! . . . . . .
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top