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.
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).
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"
ElseIf MsgBox("This action will remove this vehicle, do you want to continue and delete the vehicle?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete Vehicle") = vbNo Then
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
sql = "DELETE * From tblVehicles WHERE ID = " & [ID]
Me.AllowDeletions = True
DoCmd.RunSQL sql, True
Me.AllowDeletions = False
If Position > 0 Then DoCmd.GoToRecord acActiveDataObject, , acGoTo, Position
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.