Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Location
    Hertfordshire, UK
    Posts
    9

    Unanswered: Form Refresh After Table Update

    I have a bound form whose record source is an SQL statement which performs a UNION of two tables - Members and Organisations. The union between the two tables is formed using an OrganisationID field. Sometimes, a Member switches allegiance from one Organisation to another. The form allows the underlying Members Table to be updated by changing the value held in the OrganisationID field using an unbound DAO recordset.

    The code performing this update then executes a Me.Requery to refresh the form data and pick up the modified Member details. The Requery automatically repositions the current record to the first record in the set. In order to return to display the details of the same Member as before (and display the modified Organisation details), the code saves the Form Bookmark (Me.Bookmark) before the Requery and attempts to restore the position using this saved bookmark. Sometimes this seems to work, and sometimes it doesn't. When it doesn't, an error occurs indicating the bookmark is invalid.

    Can someone advise me if this procedure is a valid thing to do please? i.e are bookmarks valid across a requery command? If not, is there some other way to return to the Form record that has been modified? Thanks.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Have you tried using Refersh in stead of Requery ?

    If you are only changing records and not adding/deleting then that may work depending on the query.

    If not you could try picking the bones out of this code for repositioning the record selector (this is on a continuouse form).

    Code:
    Sub DeleteRecord()
        Dim Position As Integer
        Dim Total As Integer
        Dim sql As String
        
        If DCount("VehicleID", "tblEmployeeVehicles", "VehicleID = " & [ID]) > 0 Then
                MsgBox "This vehicle is currently in use and/or allocated and cannot be deleted.", vbCritical, "Vehicle Delete"
                Exit Sub
            ElseIf MsgBox("This action will remove this vehicle, do you want to continue and delete the vehicle?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete Vehicle") = vbNo Then
                Exit Sub
            End If
            
        Total = Me.RecordsetClone.RecordCount
        Position = Me.CurrentRecord
    
        If Total <= 1 Then
            Position = 0
        ElseIf Position = 1 Then
            Position = 1
        ElseIf Position >= Total - 1 Then
            Position = Total - 1
        End If
        sql = "DELETE * From tblVehicles WHERE ID = " & [ID]
        DoCmd.SetWarnings False
            Me.AllowDeletions = True
                DoCmd.RunSQL sql, True
                Me.Requery
            Me.AllowDeletions = False
        DoCmd.SetWarnings True
        If Position > 0 Then DoCmd.GoToRecord acActiveDataObject, , acGoTo, Position
    End Sub
    Let us know how you get on.

    MTB

  3. #3
    Join Date
    Oct 2005
    Location
    Hertfordshire, UK
    Posts
    9
    Well, well. Changed from Me.Requery to Me.Refresh and have had no bookmark errors since. As you say, since no records are added or deleted, the change to the recordset is minimal. There would be a change in the length of the record identifying the Member whose Organisation was changed since the string lengths of the various address fields etc in the entry in the Organisation table are inevitably going to be different, however it doesn't seem to have affected the validity of the record bookmark. Not sure I understand what's going on at a lower level though. The two tables, Members and Organisations, on which the SQL query for the Form is based are both linked tables. Perhaps a more rigorous testing process would still be able to uncover similar bookmark errors, however so far so good. Many thanks for your help.

Posting Permissions

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