going to next record after inserting a new record 
Author Message
 going to next record after inserting a new record

In Access 95, I have a name and address table which I update with a
form that is based on a query that is sorted in last name, first name
sequence.  When I add a new record, I want this record to be saved and
then have my query refreshed so that the new record is in the proper
sequence.  When I go to the next record after I add this new record, I
want to the record after this new "inserted" record.  My vb code always
goes
to the first record in the form and I receive the message "Can't go to
the specified record."  How can I go to the next record after my new
inserted record?



Sat, 24 Jun 2000 03:00:00 GMT  
 going to next record after inserting a new record

Hi,

Take note that a REQUERY is required to include new record and delete
deleted ones, but a REQUERY also destroy and invalidate any BOOKMARK. So,
basically, you need to store the primary key of the new record. I will try
the following, in the AfterUpdate event of the form:

Dim MyPrimaryKey As Variant

' Do it only if is it a new record....
If Me.NewRecord Then
    MyPrimaryKey = Me!.........    ' Keep the critical value
    Me.Requery    ' Requery the whole recordset
    ' We are now at the first "record" of the recordset,
    '  resynch through the recordsetclone
    With Me.RecordsetClone
        .FindFirst "PrimaryKeyFieldName=" & MyPrimaryKey
        Me.Bookmark = .Bookmark    ' Resynch
    End With
End If

Add a

DoCmd.GotoRecord  acNext

(and catch possible error just in case there is no next record).
Note also that I have assume, in the FindFirst, that the primary key value
is numeric (use the real name for the field name).

Hope it may help,
Vanderghast, Access MVP.


Quote:
>In Access 95, I have a name and address table which I update with a
>form that is based on a query that is sorted in last name, first name
>sequence.  When I add a new record, I want this record to be saved and
>then have my query refreshed so that the new record is in the proper
>sequence.  When I go to the next record after I add this new record, I
>want to the record after this new "inserted" record.  My vb code always
>goes
>to the first record in the form and I receive the message "Can't go to
>the specified record."  How can I go to the next record after my new
>inserted record?



Sun, 25 Jun 2000 03:00:00 GMT  
 going to next record after inserting a new record

You would have to store the key field values in variables, after you carry
out the requery use he variables to find the record in code (in the
recordsetclone), then do a movenext, then set the bookmark property of the
form to the bookmark property of the recordsetclone.

Something like this

Where
    ContactID is the keyfield of the record (numeric)

'***********  Code Start  *****************
    dim lngContactID as long

    with me
        lngContactID=!ContactID
        .Requery
        with .recordsetclone
            .Findfirst "[ContactID] = " & lngContactID
            On error resume next 'In case we're at the end of the recordset
            .Movenext
            Err.Clear
        End With
        .Bookmark = .recordsetclone.bookmark
    End With
'***********  Code End  *******************


Quote:
>In Access 95, I have a name and address table which I update with a
>form that is based on a query that is sorted in last name, first name
>sequence.  When I add a new record, I want this record to be saved and
>then have my query refreshed so that the new record is in the proper
>sequence.  When I go to the next record after I add this new record, I
>want to the record after this new "inserted" record.  My vb code always
>goes
>to the first record in the form and I receive the message "Can't go to
>the specified record."  How can I go to the next record after my new
>inserted record?



Sun, 25 Jun 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Inserting a new record between existing records?

2. Inserting a new record between existing records?

3. record locking while insert new record??

4. How to go to Next Record in a Combo Box

5. How to go to next record using the keyboard

6. adding new records & new record

7. Newbie: Goto Next/New Record in VBA

8. Move next takes user to new record - not wanted

9. Go To New Record in Sub Form

10. Type x to go to new record

11. go to new record

12. How to go to new page after print one record

 

 
Powered by phpBB® Forum Software