Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh Problem (Access 2003SP2)

    I am trying to transfer a record from one table to another and the following code works to a point. The desired record actually transfers but I have to close the source form and reopen it to the same record in order to keep viewing and working on the data. At this point, you may be asking why would I want to do that - well I've picked up someone else's database and that's the way it's been setup. Nevertheless, the following code doesn't actually open the form - it displays the title bar and window border and Access seems to freeze - I mean the only thing I can do is close the window which in turn closes Access.

    The point of closing/opening the form is to refresh the recordset because I've tried recalc, requery, and refresh and none of those will work.

    Can someone shed some light on this please?
    =======================================
    Private Sub Append_Applicant_Click()

    Dim rst As DAO.Recordset
    Dim varBookmark As Long
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Check Applicant Against Interest List"

    DoCmd.SetWarnings False
    Set rst = Me.RecordsetClone
    varBookmark = [PA Index No].Value

    DoCmd.Close acForm, "Interest List Menu"
    DoCmd.Close acForm, "Applicant's Menu"

    DoCmd.OpenQuery "Append Potential Applicant to Applicants Table", acViewNormal, acEdit

    DoCmd.Save acForm, stDocName
    DoCmd.Close acForm, stDocName

    stLinkCriteria = "[Potential Applicants.Index No]=" & varBookmark
    MsgBox stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    End Sub
    =======================================
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh Problem (Access 2003SP2)

    Three different forms are named in the code; without knowing what role each plays it's hard to tell what the code tries to accomplish. But I have some remarks:
    <UL><LI>A recordset object rst is assigned, but never used and not set to Nothing. Remove the lines that declare and assign rst.
    <LI>The line

    DoCmd.Save acForm, stDocName

    doesn't save the current record in the form, it saves the design of the form. In a multi-user environment this is undesirable, and in most cases impossible. Remove this line.
    <LI>The prefix 'var' in varBookmark is confusing for a variable of type Long.
    <LI>The 'Check Applicant Against Interest List' form is re-opened with a filter. If it was already opened with a filter, requerying or refreshing it will not display a record that was added after the form was opened.[/list]If the suggestions to remove some lines don't work, we'll need to know more about the database.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh Problem (Access 2003SP2)

    Thanks Hans
    I think I ought to read my own quote from Thoreau!
    The last bullet point did the trick.
    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •