ACCESS FEED: RE: [MS_AccessPros] Bound form Updating wrong record

Rabu, 15 Mei 2013

RE: [MS_AccessPros] Bound form Updating wrong record

Doyce-

Change this:

Set rs = Me.Recordset
rs.FindFirst "UnitID='" & strComboVar & "'"
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

To:

Me.Recordset.FindFirst "UnitID='" & strComboVar & "'"

No need for an intermediary recordset. The Bookmark you're using may be
pointing to the previously found record.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
Sent: Wednesday, May 15, 2013 8:54 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Bound form Updating wrong record

Hello friends,

I have a form bound to an updateable query. I filter the form to open
without any records showing by using the following code in the form load
event:
Me.filter = "1=2"
Me.filteron = true.
I select records with a combo box that has the following code in its after
update event:
Private Sub Combo28_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset

Dim strComboVar As String

strComboVar = Me.Combo28

If Len(Combo28 & vbNullString) <> 0 Then
Me.Filter = ""
Me.FilterOn = False
Set rs = Me.Recordset
rs.FindFirst "UnitID='" & strComboVar & "'"
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End If

End Sub

If I display a record and update one of the bound controls, then save the
changes with a save button that has this code:
If me.dirty then me.dirty = false
there is no problem. The problem occurs when I display a second record by
selecting it from the combo box. It always displays the record correctly
however if I change something and click the save button, the changes are
saved to the field of the previous record in the set, not the one showing on
screen. For example: I open the form, display record 10. make a change click
save. Everthing is fine. Then I select record 5 from the combo box. Make a
change, click save, the data is changed in record 4, not record five. If I
close the form and re-open it and it always works correctly on the first
record displayed but not on subsequent records. This database is split and I
have managed to duplicate this problem in a couple of other databases. I
tried to create a sample to upload but with tables in the database instead
of linked tables, everything updates correctly. I'll try to create a
stripped down version with linked tables and see if I can duplicate the
problem. I have also observed that this occurs everytime I select a 2nd or
third record even if I haven't changed anything on the first one. I need
help!

Doyce

------------------------------------

Yahoo! Groups Links