ACCESS FEED: 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
> >
>