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

Rabu, 15 Mei 2013

[MS_AccessPros] Re: Bound form Updating wrong record

John,

Thanks again!!

Doyce

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Doyce-
>
> Yes, that could have contributed. Always best to have the backend at the
> same version as the front end.
>
> 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 10:36 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Bound form Updating wrong record
>
> John,
>
> Thanks for this good advice. I have applied this and will update other code
> that I have as well. However, I did discover this also. The tables in the BE
> were in mdb format and so I updated them to accdb and then relinked the
> tables and that seems to have fixed the problem as the old code was working
> on the tests that I tried. Does that sound reasonable as a cause of the
> problem?
>
> Doyce
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > 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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>