Doyce-
You said the record wasn't being saved, so I assumed you were adding a record.
You don't have all the keys listed in the Record Source, so if there is a
missing matching row in one of the Outer Join tables and you try to change a
"blank" control to a value, Access doesn't know where to find and set the
matching key value. It probably works when a matching record already exists,
but won't work if you try to set a value in a "missing" row.
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 dnwinberry
Sent: Saturday, July 16, 2011 2:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Me.dirty not saving changes after initial save
Wow, you guys had some interesting discussion overnight. At least it was
overnight for me John. <grin> The PK for tblReconPrimary is UnitID. It is always
unique so it makes a great natural key. I never add records from this form. The
different tables get populated from other forms at other times, example when the
trailer is delivered, when it is inspected, etc. The VIN and licensing comes
from a table that contains data for every trailer in the fleet. This form is
mainly used to view the information but sometimes we have to correct some of it
or add comments for various reasons. For instance sometimes we schedule a
trailer for delivery and enter the delivery date but something happens and the
trailer does not go until the next day. We open the record on this form and
change the delivery date. I do not change allow additions anywhere. John, your
idea to create this form and bring in the additonal information with subforms is
very interesting and an approach I haven't thought of. I think I will try that
as time permits.
Many thanks for everyone's help.
Doyce
--- In MS_Access_Professionals@yahoogroups.com, "patrickawood" <pwood57@...>
wrote:
>
> 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
> >
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar