Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Requerying a form and retaining current record

Discussion in 'Information Technology' started by El Rebelde, Jul 28, 2006.

  1. El Rebelde

    El Rebelde
    Expand Collapse
    Guest

    I have a form which shows records in a multi-table query. My application
    enables me to open another form to add a new record to one of the tables and
    I want to see that new record when I return to the first form. I have read
    about the requery command, but when I do this the form shows the first record
    in the dataset, not the one I was working with. Is there an easy way to
    retain the current record in view?
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    There's not really an "easy" way.

    The solution involves saving the primary key value into a variable, and then
    finding that record again after the requery.

    This example assumes an numeric primary key named ID:

    Dim varID as Variant
    If Me.Dirty Then 'Save any edits.
    Me.Dirty = False
    End If
    varID = Me.[ID] 'Remember the primary key value.
    Me.Requery 'Requery the form.

    If IsNull(varID) Then 'Must have been a new record.
    If Not Me.NewRecord Then
    RunCommand acCmdRecordsGotoNew
    End If
    Else
    With Me.RecordsetClone
    .FindFirst "[ID] = " & varID
    If .NoMatch Then
    MsgBox "Oops: it disappeared."
    Else
    Me.Bookmark = .Bookmark
    End If
    End With
    End If

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "El Rebelde" <El Rebelde@discussions.microsoft.com> wrote in message
    news:58E891BA-4527-4CC8-BAC4-06177D1BF7F4@microsoft.com...
    >I have a form which shows records in a multi-table query. My application
    > enables me to open another form to add a new record to one of the tables
    > and
    > I want to see that new record when I return to the first form. I have read
    > about the requery command, but when I do this the form shows the first
    > record
    > in the dataset, not the one I was working with. Is there an easy way to
    > retain the current record in view?
     
  4. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    You have to retain the value of the unique id field for the current record
    before you requery then move to that record after the requery.

    Example:
    lngCurrentRecord = Me.txtID
    Me.Requery
    Me.Recordset.FindFirst "IDField =" & lngCurrentRecord

    --
    Wayne Morgan
    MS Access MVP


    "El Rebelde" <El Rebelde@discussions.microsoft.com> wrote in message
    news:58E891BA-4527-4CC8-BAC4-06177D1BF7F4@microsoft.com...
    >I have a form which shows records in a multi-table query. My application
    > enables me to open another form to add a new record to one of the tables
    > and
    > I want to see that new record when I return to the first form. I have read
    > about the requery command, but when I do this the form shows the first
    > record
    > in the dataset, not the one I was working with. Is there an easy way to
    > retain the current record in view?
     
  5. El Rebelde

    El Rebelde
    Expand Collapse
    Guest

    Many thanks Allen, with your help it was very easy for me! I've added your
    code to a command button and it works perfectly. I'm also grateful to Wayne,
    although I'd already used your code, by the time I read his post.


    "Allen Browne" wrote:

    > There's not really an "easy" way.
    >
    > The solution involves saving the primary key value into a variable, and then
    > finding that record again after the requery.
    >
    > This example assumes an numeric primary key named ID:
    >
    > Dim varID as Variant
    > If Me.Dirty Then 'Save any edits.
    > Me.Dirty = False
    > End If
    > varID = Me.[ID] 'Remember the primary key value.
    > Me.Requery 'Requery the form.
    >
    > If IsNull(varID) Then 'Must have been a new record.
    > If Not Me.NewRecord Then
    > RunCommand acCmdRecordsGotoNew
    > End If
    > Else
    > With Me.RecordsetClone
    > .FindFirst "[ID] = " & varID
    > If .NoMatch Then
    > MsgBox "Oops: it disappeared."
    > Else
    > Me.Bookmark = .Bookmark
    > End If
    > End With
    > End If
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "El Rebelde" <El Rebelde@discussions.microsoft.com> wrote in message
    > news:58E891BA-4527-4CC8-BAC4-06177D1BF7F4@microsoft.com...
    > >I have a form which shows records in a multi-table query. My application
    > > enables me to open another form to add a new record to one of the tables
    > > and
    > > I want to see that new record when I return to the first form. I have read
    > > about the requery command, but when I do this the form shows the first
    > > record
    > > in the dataset, not the one I was working with. Is there an easy way to
    > > retain the current record in view?

    >
    >
    >
     

Share This Page