ACCESS FEED: RE: [MS_AccessPros] Re: Me.dirty not saving changes after initial save

Doyce-

PMJI, but what is the Record Source of the form? And are you changing Allow
Additions in code somewhere? What code runs when you save the record?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
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 patrickawood
Sent: Saturday, July 16, 2011 12:17 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Me.dirty not saving changes after initial save

Hi Doyce,

You could use a variable to get the ID of the Record you were in and then use it
like below:

Dim rst As DAO.Recordset

If Not IsNull(MyIDVariable) Then
'Search for the first match.
Set rst = Me.RecordsetClone
rst.FindFirst "[MyIDField] = " & MyIDVariable
If rst.NoMatch Then
MsgBox "The Record was not found." & vbCrLf _
& vbCrLf & " Please try again.", vbExclamation
Else
'Display the found record in the form.
Me.Bookmark = rst.Bookmark
End If
End If

Set rst = Nothing

Kind Regards,
Pat Wood
http://accesstips.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, "dnwinberry"
<winberry.doyce@...> wrote:
>
> Pat, I think you are on to something. When I substitute requery for refresh,
it does save the records however, it also returns to the first record in the
table and I would like it to stay on the same record so I think I need to add
some code to make it requery and then filter for the record I was on. Do you
happen to have an example of how to do that?
>
> Doyce
>
> --- In MS_Access_Professionals@yahoogroups.com, "patrickawood" <pwood57@>
wrote:
> >
> > Have you tried Me.Requery instead of Me.Refresh? That is quite a complicated
query there and Me.Requery might help make sure all the data is up to date. This
is from the Access Developer's Reference:
> >
> > "Since the Refresh method doesn't actually requery the database, the current
set won't include records that have been added or exclude records that have been
deleted since the database was last requeried. Nor will it exclude records that
no longer satisfy the criteria of the query or filter."
> >
> > Kind Regards,
> > Pat Wood
> > http://accesstips.wordpress.com
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "dnwinberry"
<winberry.doyce@> wrote:
> > >
> > > Bill or others,
> > >
> > > I don't think this is form corruption. I have started from scratch with a
brand new form and created every control as new. I'm still getting the same
thing. I've tried disecting it taking out one control after another. I've tried
rebuilding the query it is based on. Here's an interesting tidbit, twice now I
have stripped down the database to just the necessary tables, forms, and querys,
so I can upload a sample and when I do, the problem goes away. Could that mean
corruption somewhere else? I have noticed that as long as I stay on the first
record I can change things and the save works every time. It is only after I
move to another record that it stops saving changes. What happens when I move to
a different record?
> > >
> > > Doyce
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@>
wrote:
> > > >
> > > > Ah! Doyce, if a new form worked and one made from copying parts does
not, we
> > > > have corruption. Save the code as a text file just to make that part
easier.
> > > >
> > > >
> > > >
> > > > Delete the form; compact; make a new form from scratch. When you are
finished,
> > > > re-build your event procedures and paste the code from the text file
into each
> > > > event. Don't just paste the entire module because some of the controls
might not
> > > > pick up the events properly.
> > > >
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Bill
> > > >
> > > >
> > > >
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
> > > > Sent: Thursday, July 14, 2011 9:01 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Me.dirty not saving changes after initial
save
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Good morning Bill,
> > > >
> > > > Yes, it works perfectly on the first record then will not work after a
second,
> > > > third, or more record is displayed. It doesn't matter whether I select
the new
> > > > record with a combo box or use the navigation buttons. Once a record has
been
> > > > saved, it no longer saves any changes I make to other records. I have
stripped
> > > > out all other code except Me.dirty = false and me.refresh and it still
happens.
> > > > The reason I display the other form is because it closes after about 2
seconds
> > > > and the user doesn't have to click OK. If there is a way to close a
MSGBOX
> > > > automatically, I don't know how. I've tried creating a new form but I
did copy
> > > > some of the items from the old form and it does the same thing. I've
tried to
> > > > create a sample for uploading and after stripping out the unnecessary
stuff and
> > > > importing the tables from the BE, the sample started working perfectly.
It's
> > > > about to drive me nuts! Perhaps I should try creating a new form and not
copying
> > > > anything?
> > > >
> > > > Doyce
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca"
<wrmosca@>
> > > > wrote:
> > > > >
> > > > > Doyce
> > > > >
> > > > > It works one time and then blows up? Try putting a breakpoint at the
start of
> > > > the event. When the code breaks press F8 to step through it so you can
see what
> > > > is happening.
> > > > >
> > > > > Also comment out all error handling if you have any in that procedure
when
> > > > debugging.
> > > > >
> > > > > One more thing...
> > > > > Why add the extra form when all you need is a MsgBox?
> > > > >
> > > > > Bill
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "dnwinberry"
> > > > <winberry.doyce@> wrote:
> > > > > >
> > > > > > Has anyone had any other ideas on this? I appreciate the help.
> > > > > >
> > > > > > Doyce
> > > > > >
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "dnwinberry"
> > > > <winberry.doyce@> wrote:
> > > > > > >
> > > > > > > Hi Crystal,
> > > > > > >
> > > > > > > Thanks for responding. Here is the SQL for the query:
> > > > > > >
> > > > > > > SELECT tblReconPrimary.UnitID, tblReconPrimary.ArrivalDate,
> > > > tblReconPrimary.PComments, tblReconPrimary.PROGRAM,
tblReconPrimary.LicREquest,
> > > > tblReconPrimary.[Loaded Comments], tblReconPrimary.ShipDocPrinted,
> > > > tblReconPrimary.Proccessed, tblReconPrimary.DateProcessed,
> > > > tblReconPrimary.RegReceived, tblTruckloadTagDate.TagDate,
> > > > tblTruckloadTagDate.Comments, tblTruckloadDeliveryLocations.[DELIVERY
ID],
> > > > tblTruckloadDeliveryLocations.DELIVERYLOCATION,
tblTruckloadDelivery.[Delivery
> > > > Date], tblCTLVINs.[License State], tblCTLVINs.[License Plate Number],
> > > > tblCTLVINs.Year, tblCTLVINs.Make, tblCTLVINs.[Serial Number],
> > > > tblTruckloadDeliveryLocations.DELIVERYLOCATION,
tblTruckloadDelivery.TruckID,
> > > > tblTruckloadTagDate.SuspensionSerialNumber
> > > > > > > FROM tblTruckloadDeliveryLocations RIGHT JOIN (((tblReconPrimary
LEFT JOIN
> > > > tblTruckloadDelivery ON tblReconPrimary.UnitID =
tblTruckloadDelivery.[Unit #])
> > > > LEFT JOIN tblCTLVINs ON tblReconPrimary.UnitID = tblCTLVINs.[Old
Number]) LEFT
> > > > JOIN tblTruckloadTagDate ON tblReconPrimary.UnitID =
tblTruckloadTagDate.UnitID)
> > > > ON tblTruckloadDeliveryLocations.[DELIVERY ID] =
> > > > tblTruckloadDelivery.DeliveryID;
> > > > > > >
> > > > > > > Here are the tables and their PK's:
> > > > > > >
> > > > > > > tblReconPrimary - UnitID
> > > > > > > tblTruckloadTagDate - UnitID
> > > > > > > tblTruckloadDelivery - Unit#
> > > > > > > tblTruckloadDeliveryLocations - Delivery ID
> > > > > > > tblCTLVins - Old Number
> > > > > > >
> > > > > > > 'do you have fields in the table design :
> > > > > > > '- designated as Required? NO
> > > > > > > '- text fields with no for AllowZeroLength? NO
> > > > > > > '- DefaultValue specified when it should be blank (especially
check
> > > > numeric foreign keys) NO
> > > > > > >
> > > > > > > The query is updateable.
> > > > > > >
> > > > > > > 'Do you have a form BeforeUpdate event? NO
> > > > > > >
> > > > > > > I appreciate you taking the time to help!!
> > > > > > >
> > > > > > > Doyce
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , Crystal
<strive4peace2008@>
> > > > wrote:
> > > > > > > >
> > > > > > > > Hi Doyce,
> > > > > > > >
> > > > > > > > what is the SQL for the query?
> > > > > > > >
> > > > > > > > do you have fields in the table design :
> > > > > > > > - designated as Required?
> > > > > > > > - text fields with no for AllowZeroLength?
> > > > > > > > - DefaultValue specified when it should be blank (especially
check
> > > > numeric foreign keys)
> > > > > > > >
> > > > > > > > what unique indexes are on the table(s) in the query?
> > > > > > > >
> > > > > > > > ~~~
> > > > > > > > this attempts to save the record:
> > > > > > > > Me.Dirty = False
> > > > > > > >
> > > > > > > > Do you have a form BeforeUpdate event?
> > > > > > > >
> > > > > > > > although it is cheap, after I do me.dirty = false, I test it
again. For
> > > > now, this might be a good idea till you can figure out why the record
isn't
> > > > saving -- at least your message won't tell the user it is saved.
> > > > > > > >
> > > > > > > >
> > > > > > > > Warm Regards,
> > > > > > > > Crystal
> > > > > > > >
> > > > > > > > *
> > > > > > > > (: have an awesome day :)
> > > > > > > > *
> > > > > > > >
> > > > > > > >
> > > > > > > > --- On Wed, 7/13/11, dnwinberry wrote:
> > > > > > > >
> > > > > > > >
> > > > > > > > > Hello all,
> > > > > > > > >
> > > > > > > > > I have a bound form that displays information from several
> > > > > > > > > different tables. It's record source is a query. When I
> > > > > > > > > change some of the data, I have a Save button that becomes
> > > > > > > > > visible with an on click event to save the changes. It's
> > > > > > > > > code is:
> > > > > > > > > If Me.Dirty Then
> > > > > > > > > Me.Dirty = False
> > > > > > > > > 'display splash form "Record
> > > > > > > > > has been saved" which closes automatically
> > > > > > > > > DoCmd.OpenForm
> > > > > > > > > "frmRecordIsSaved", acNormal
> > > > > > > > > Me.cboLookup.SetFocus
> > > > > > > > > Me.btnSave.Visible = False
> > > > > > > > > Me.Refresh
> > > > > > > > > End If
> > > > > > > > >
> > > > > > > > > This works fine for the first record that is changed.
> > > > > > > > > However, after I have saved one record and display another
> > > > > > > > > and change something the procedure doesn't save the changes.
> > > > > > > > > It gives no errors, the form is refreshed and the changes
> > > > > > > > > are gone. Thinking I might have form corruption, I built a
> > > > > > > > > new form and it is doing the same thing. I went to prepare a
> > > > > > > > > sample for uploading and after deleting all the extra forms,
> > > > > > > > > reports and etc. and importing the tables from the backend
> > > > > > > > > into the front end, the behavior stopped and it started
> > > > > > > > > working perfectly. I'm bum-fuzzled. Does anyone have a clue
> > > > > > > > > as to why this might be happening?
> > > > > > > > >
> > > > > > > > > Doyce
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> >
>

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

Yahoo! Groups Links