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

John-

Yes I see! That would save the trouble of having to write and test the code.

Thanks, John

Pat

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Pat-
>
> I would use a form on tblReconPrimary and subforms for the related tables below
> to avoid the outer join mess.
>
> I'm not up late - it's just past 10a here!
>
> 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 9:54 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Me.dirty not saving changes after initial save
>
> Hi John,
>
> I can't seem to sleep tonight so I just as well ask a question. I like to keep
> things more separated in single elements (to each his own). And in this case I
> might consider using about 3 or 4 different Update or Append queries in code in
> such sequence so that referential integrity is maintained to save the entered
> values.
>
> Something like strSQL = "INSERT INTO firstTable ..."
> db.Execute strSQL, dbFailOnError
>
> ... a few times with appropriate error trapping of course.
>
> What woudld be the advantages and disadvantages of such an approach?
>
> Please do not stay up late working on this since I may finally get to sleep
> soon.
>
> Kind Regards,
> Pat
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Thanks, Pat. Here's the response again just to keep the thread intact:
> >
> > 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
> >
> > Actually, I'm surprised that Access doesn't refuse to run this query because
> of
> > "ambiguous outer joins."
> >
> > *************************
> > Questions for Doyce: In which fields are you entering data on a "new" record
> > and in what sequence? Is the Primary Key of tblReconPrimary an AutoNumber?
> > *************************
> >
> > 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 8:22 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Me.dirty not saving changes after initial save
> >
> > Hi John,
> >
> > He posted it in Message #91708.
> >
> > Kind Regards,
> > Pat Wood
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > >
> > > 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
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>