After Update on form selecting wrong record.

[Prysson]

I am running into a problem with a form with a List box that has and after update event.

The frmTraining System has a command button for ading records that opens a pop-up form with a new record entry. You enter the new record and click ok and that closes the form and does a requery on the list box to update the displayed list.

The problem is that the after update event process after adding the new entry doesn not select the correct event associated with the newly added record. Rather it defaults to the first record in the table so that if you added a record and the decided you wanted to immediately delete it...should you select the newly added record in the list box and click delete it actually is deleting the top record in the table rather then the newly entered record.

For all other records howveer it works fine..only the newly entered record seems to do this...if you close the form and reopen it it will then select the correct corresponding record.

 

[Prysson]

MOre information

Maybe I should be more specific...


Here is a more detailed explanation...


I have a form named frmEditTrainingSystems
There are four buttons on this form Add, Edit, Delete, Close.

There is also a list box that has as its source the a query on the tblTrainingSystems that reads as follows
SELECT tblTrainingSystem.[Training System ID], tblTrainingSystem.[Training System] FROM tblTrainingSystem WHERE (((tblTrainingSystem.[Training System])<>"na"));

There is also an after update event associated with this list box that reads thusly

Private Sub List12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Training System ID] = " & Str(Nz(Me![List12], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

First lets deal with the add sequence

Clicking on the add button has an event procedure for the on click event which opens up a pop-up form called frmAddTrainingSystem.

This frm has an on open event that reads:

Private Sub Form_Open(Cancel As Integer)


DoCmd.GoToRecord , , acNewRec


End Sub


This adds a new record into the tblTrainingSystem..which is the record source of the frmAddTrainingSystem

You then enter the name of the training system you wish to add to the table and click the OK button.

The OK button returns you to the frmEditTrainingSystems form (which never closed but remained open in the background)

Now the frmEditTrainingSystems for has an on activate event the requeries the listbox whenever the form is activated…in this instance closing the frmAddTrainingSystem reactivates the frmEditTrainingSystems form….

The on Activate event reads:

Private Sub Form_Activate()

Me!ListTrainingSource.Requery

End Sub



The problem is that once you add a new record and return to the frmEditTrainingSystems form and you click in the list box…the after update event is no longer working properly. When you select a record in the list box the actual record that gets selected is the topmost record in the table.

The effect of this is that if you were to say delete…or edit that new record without closing and reopening the form you might think you were dleting the correct record having selected it from the list box…but you actually are changing a completely different. Record. Closing and reopeing the form fixes the problem but I need it to work correctly without having to close and reopen the form…otherwise I suppose I could have a sequence that closes and reopens the form automatically but that seems like a an unnecessary procedure.