ACCESS FEED: RE: [MS_AccessPros] Unwanted Saving a blank record -- form BeforeUpdate

Walter-

Great! Putting the values directly in the WhereCondition of the OpenForm is the
way to go.

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 nkpberk
Sent: Wednesday, November 02, 2011 11:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Unwanted Saving a blank record -- form BeforeUpdate

John:
I think I have it figured out. I'll get back if it blows up.
Walter

--- In MS_Access_Professionals@yahoogroups.com, "nkpberk" <wgriffin48@...>
wrote:
>
> John:
> I thought I would have to requery after I applied the values to the controls.
> The forms recordset is a basic sort query on the table "tblindustry"
> I call the form in question with a docmd.openform with a where clause based on
the comparison of the controls loaded. maybe I should figure out how to use the
values directly in the where clause.
> You say the defaults do not apply until the record is "dirtied", I did not
know that.
> Walter
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Walter-
> >
> > What changes on the form that makes the Requery cause the Recordset to
change?
> > If it's some filter applied using parameters pointing to some of the
controls
> > you set, why not simply apply the filter when you open the form?
> >
> > You can always set the DefaultValue of a control without affecting the
> > underlying data. If you do that, then those values will be automatically
> > supplied if the user "dirties" a new row.
> >
> > Please explain in more detail what it is this form does and why a Requery
> > changes what's displayed by the form. What is the Record Source? (The SQL
if a
> > query.)
> >
> > 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 nkpberk
> > Sent: Wednesday, November 02, 2011 9:19 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Unwanted Saving a blank record -- form
BeforeUpdate
> >
> > John;
> > The requery is needed because the the query relies on the value of some of
the
> > controls.
> > Can I assign the openargs(x) to unbound hidden controls and run the query
> > comparison to those and then make the values in the unbound controls, if the
> > user "dirties" the new record, apply to the Bound controls?
> > Walter
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Walter-
> > >
> > > Well, if txtStationSt, txtIndustry, cboCommodity, txtCarType, or txtSR are
> > bound
> > > controls (have a Control Source), you're "dirtying" the record. The
Requery
> > is
> > > forcing the save that triggers the BeforeUpdate event.
> > >
> > > 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 nkpberk
> > > Sent: Wednesday, November 02, 2011 8:53 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] Unwanted Saving a blank record -- form
> > BeforeUpdate
> > >
> > > John:
> > > This is the form load event
> > >
> > > If Not IsNull(Me.OpenArgs) Then
> > > On Error Resume Next
> > > strOpenArgs = Split(Me.OpenArgs, ";")
> > > Me.txtStationSt = strOpenArgs(0)
> > > Me.txtIndustry = strOpenArgs(1)
> > > Me.txtEra.DefaultValue = strOpenArgs(2)
> > > Me.cboCommodity = strOpenArgs(3)
> > > Me.idStation.DefaultValue = strOpenArgs(4)
> > > Me.IDIndustry.DefaultValue = strOpenArgs(5)
> > > Me.txtCarType = strOpenArgs(6)
> > > Me.cboCarType.DefaultValue = Me.txtCarType
> > > Me.txtSR = strOpenArgs(7)
> > > Me.cboShipRec.DefaultValue = Me.txtSR
> > > Me.Requery
> > > End If
> > >
> > > The default values are so the user cannot add/change values that don't
agree
> > > with the query that loads the form. This form is called from more than one
> > > location and the query criteria are different.
> > > Is there a way to "load" the defaults to a new record after the user
"dirties"
> > > the fields in the new record?
> > > This is a previous incarnation the newest got corrupted some how and
won't
> > load
> > > VBA without crashing Access!
> > >
> > > Walter
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > > >
> > > > Walter-
> > > >
> > > > Are you SURE you're setting the DefaultValue and not the value? Can you
> > post
> > > > the code from the Open and Load events of the problem form?
> > > >
> > > > 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 nkpberk
> > > > Sent: Wednesday, November 02, 2011 12:35 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: Re: [MS_AccessPros] Unwanted Saving a blank record -- form
> > > BeforeUpdate
> > > >
> > > > John;
> > > > Yes there is, there are some default values being set into invisible
> > controls
> > > > from "openargs" from the calling form for the "potential" new record if
the
> > > user
> > > > chooses to make a new record, This is to limit the new record to match
the
> > > query
> > > > that populated the form.
> > > > I have a combo box on the calling form that lists an industry , you
dblclick
> > > on
> > > > it and I am opening a form (the one in question) and it lists all the
> > > > commodities that industry ships and the type of freight cars that the
> > > commodity
> > > > is shipped in. All of the other fields in the industry table are
defaulted
> > to
> > > > the values dictated by the query that loaded the form, the user can only
add
> > > > "commodity" and "appropriate freight car type" and "ship or Receive" to
the
> > > new
> > > > record.
> > > >
> > > > If the user just chooses an existing record it populates controls on the
> > > calling
> > > > form and should not create a new record (says here in small print :-)
> > > >
> > > > It seems the before update is firing before the form load?
> > > >
> > > > Walter
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > wrote:
> > > > >
> > > > > Walter-
> > > > >
> > > > > There has to be some code that is "dirtying" the record and then
forcing a
> > > > save
> > > > > to cause BeforeUpdate to fire. What is all the code behind the
subform
> > and
> > > > the
> > > > > parent form?
> > > > >
> > > > > 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 nkpberk
> > > > > Sent: Tuesday, November 01, 2011 7:54 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] Unwanted Saving a blank record -- form
> > > > BeforeUpdate
> > > > >
> > > > > Crystal,Group;
> > > > > I am still having difficulty with a continuous form that creates a
blank
> > > > record
> > > > > in a table. This time it won't allow the form to populate with the
records
> > > > > called for in the query. The before update event fires before the load
> > event
> > > > and
> > > > > I get no existing records. also I get a warning messagebox about
> > > "encountering
> > > > > some difficulty and can't save record at this time" I don't want this
> > > warning
> > > > as
> > > > > the primary use of the form is to allow the user a list of valid
choices
> > and
> > > > > secondarily the ability to add a new record(choice).
> > > > > using::
> > > > > "Before_update_Event"
> > > > > If IsNull(me.SomeControlName) then (Choose any control)
> > > > > cancel = true
> > > > > endif
> > > > > this does stifle the blank record creation but seems to block the
loading
> > of
> > > > the
> > > > > valid choice list (records)
> > > > >
> > > > > Walter Griffin
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "nkpberk"
<wgriffin48@>
> > > > > wrote:
> > > > > >
> > > > > > Crystal:
> > > > > > Thanks, The primary reason for this form is to allow the user to
choose
> > an
> > > > > existing record to populate controls on the calling form, so most of
the
> > > time
> > > > no
> > > > > new record would be generated. The odd behavior is that it seems to
make a
> > > new
> > > > > record when the query to populate the form runs?? I have looked at the
> > table
> > > > in
> > > > > question right after opening the form (doing nothing else) and a blank
> > > record
> > > > > seems to have appeared?? I am not updating anything (that I know of..)

> > > > > >
> > > > > > Would this stifle that behavior?
> > > > > >
> > > > > > "Before_update_Event"
> > > > > > If IsNull(me.SomeControlName) then (Choose any control)
> > > > > > cancel = true
> > > > > > endif
> > > > > >
> > > > > > Walter Griffin
> > > > > >
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, Crystal
> > > <strive4peace2008@>
> > > > > wrote:
> > > > > > >
> > > > > > > Hi Walter,
> > > > > > >
> > > > > > > absolutely! the purpose of the form BeforeUpdate event is to
cancel a
> > > new
> > > > > record or a change to an existing record. This is exactly what you
want
> > to
> > > > do.
> > > > > > >
> > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > > > > > >
> > > > > > > 'to validate a record and prevent it from being saved, put code in
the
> > > > form
> > > > > BeforeUpdate event
> > > > > > >    '----------------- make sure all required data is filled out
> > > > > > >
> > > > > > >    'make sure SomeControlName is filled out
> > > > > > >    If IsNull(me.SomeControlName) then
> > > > > > >
> > > > > > >       'if it is not filled out,
> > > > > > >       'then move the focus to that control
> > > > > > >       me.SomeControlName.setFocus
> > > > > > >
> > > > > > >       'give the user a message
> > > > > > >       msgbox "You must enter Some Data",,"Missing Data"
> > > > > > >
> > > > > > >       'if this is a combobox, drop the list for them
> > > > > > >       me.SomeControlName.dropDown
> > > > > > >
> > > > > > >       'IF you want to undo the record
> > > > > > >       'Me.Undo
> > > > > > >
> > > > > > >
> > > > > > >       'don't save the record yet
> > > > > > >       Cancel = true
> > > > > > >
> > > > > > >       'quit checking and give them a chance to fill it out
> > > > > > >       exit sub
> > > > > > >    end if
> > > > > > >
> > > > > > >    'make sure the first Date is filled out
> > > > > > >    If IsNull(me.Date1) then
> > > > > > >       me.Date1.setFocus
> > > > > > >       msgbox "You must enter the first Date" _
> > > > > > >           ,,"Missing Data"
> > > > > > >       Cancel = true
> > > > > > >       exit sub
> > > > > > >    end if
> > > > > > >
> > > > > > >    'make sure the second Date is filled out
> > > > > > >    If IsNull(me.Date2) then
> > > > > > >       me.Date2.setFocus
> > > > > > >       msgbox "You must enter the second date" _
> > > > > > >           ,,"Missing Data"
> > > > > > >       Cancel = true
> > > > > > >       exit sub
> > > > > > >    end if
> > > > > > >
> > > > > > >    'make sure the second Date2 is after Date1
> > > > > > >    If me.Date2 < me.Date1 then
> > > > > > >       me.Date2.setFocus
> > > > > > >
> > > > > > >       msgbox "The second date, " & me.Date2 _
> > > > > > >           & " must be after the first date, " _
> > > > > > >           & me.Date1,,"Invalid Data"
> > > > > > >       Cancel = true
> > > > > > >
> > > > > > >       'IF you want to undo the entries to the record
> > > > > > >       'Me.Undo
> > > > > > >
> > > > > > >       'IF you want to undo the entries to the field
> > > > > > >       'Me.controlname.Undo
> > > > > > >       Cancel = true
> > > > > > >       exit sub
> > > > > > >    end if
> > > > > > >
> > > > > > > '~~~~~~~~~~~~~~~~~~~~
> > > > > > >
> > > > > > > WHERE
> > > > > > > controlname is the Name of the respective control
> > > > > > >
> > > > > > > substitute your controls names for these:
> > > > > > > SomeControlName
> > > > > > > Date1
> > > > > > > Date2
> > > > > > >
> > > > > > >
> > > > > > > Warm Regards,
> > > > > > > Crystal
> > > > > > >
> > > > > > > Learn Access on YouTube
> > > > > > > http://www.youtube.com/user/LearnAccessByCrystal
> > > > > > >
> > > > > > > *
> > > > > > >    (: have an awesome day :)
> > > > > > > *
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ________________________________
> > > > > > > From: nkpberk
> > > > > > >
> > > > > > > Hi Group;
> > > > > > >
> > > > > > > I have a annoying problem, one of my forms, when opened and the
query
> > > runs
> > > > > that populates it, saves a blank record in the root table the query is
> > based
> > > > on!
> > > > > > >
> > > > > > > This form is called from more than one control/form and the
criteria
> > is
> > > > > passed thru 'openargs' valued to invisible textboxes to give the
> > appropriate
> > > > > records to the form.
> > > > > > >
> > > > > > > How do I prevent this from happening? I suppose I could do a
"delete"
> > > > query
> > > > > on the root table on form close, but I would like to know if there is
a
> > way
> > > to
> > > > > prevent it.
> > > > > > >
> > > > > > > I do want the user to be able to add a record with data if they
want
> > or
> > > > just
> > > > > choose an existing record to populate other controls on the calling
form.
> > I
> > > > > think I can manage that part.(I think :-)
> > > > > > >
> > > > > > > Walter Griffin
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ------------------------------------
> > > > > > >
> > > > > > > Yahoo! Groups Links
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > [Non-text portions of this message have been removed]
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>

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

Yahoo! Groups Links