ACCESS FEED: RE: [MS_AccessPros] Required fields when using CurrentDb.Execute INSERT

Rabu, 26 Oktober 2011

RE: [MS_AccessPros] Required fields when using CurrentDb.Execute INSERT

Connie-

2. I would do this in AfterUpdate of the Xtra Parcel check box. If the value is
True, perhaps ask user to verify that's what they meant to do, and if Yes, then
do steps 3 and 4.
3. Rather than the Close event of the XtraParcelEntry form, consider providing
two command buttons: A "Go" button that performs the insert, opens the
ListingsHomeInfo form, and closes; A "Cancel" button that simply closes the
form. Oh, yes, and in the Open event of this form, verify that the listings
form is open. If not, issue a message that the Listings form must be open and
cancel the open.
4. Good verification step, but make sure you close the XtraParcelEntry form:
DoCmd.Close acForm, Me.Name

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 mrsgoudge
Sent: Wednesday, October 26, 2011 11:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Required fields when using CurrentDb.Execute INSERT

John and all,
1. Listings form is open. Add Xtra Parcel box is checked.
2. When focus is lost on that box, the unbound form "XtraParcelEntry" opens
prompting the user for the HomeInfoID per John's suggestion.
3. On closing the XtraParcelEntry form, the code enters the ListID from the
still open Listings form and the HomeInfoID from the XtraParcelEntry form into
the ListingsHomeInfo table.
4. The Form ListingsHomeInfo is opened so they can see what they entered and
make changes as needed.

Is step 3 above a good way to do it? My concern is relying on the "still open
Listings form". I have my code below. I will be duplicating this type of event
several times (replacing more risky code) so I want to be sure that I'm doing it
right :-)

Thanks!
Connie

On Error GoTo Proc_Err
If (Me.XtraParcelNbr <> "") Then
'Create a new record
CurrentDb.Execute "INSERT INTO ListingsHomeInfo (ListID, HomeInfoID) VALUES
(" & Forms!Listings.ListID & ", " & Me.XtraParcelNbr.Column(0) & ")",
dbFailOnError
End If
If (Me.HomeID <> "") Then
'Create a new record
CurrentDb.Execute "INSERT INTO ListingsHomeInfo (ListID, HomeInfoID) VALUES
(" & Forms!Listings.ListID & ", " & Me.HomeID & ")", dbFailOnError
End If
'Open form
DoCmd.OpenForm "ListingsXtraParcel", acFormDS, , "ListID = " &
Forms!Listings.ListID

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> I suppose you could prompt the user for the HomeInfoID with an unbound dialog
> form, then insert both values.
>
> 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 mrsgoudge
> Sent: Wednesday, October 26, 2011 6:13 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Required fields when using CurrentDb.Execute
INSERT
>
> John,
> I opened the form to a new record having the the ListID entered using code and
> then the user entered the HomeInfoID.
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Connie-
> >
> > Do you not have the HomeInfoID to do the INSERT of both fields? How were
you
> > doing it when you used a 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 mrsgoudge
> > Sent: Wednesday, October 26, 2011 5:55 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Required fields when using CurrentDb.Execute INSERT
> >
> > I am going through my code trying to change instances where I was opening a
> form
> > in order to create a new record to the better method of using the
> > CurrentDb.Execute INSERT method.
> >
> > I have a table ListingsHomeInfo where I had ListID and HomeInfoID as
required
> > fields. Therefore when I entered the ListID using the CurrentDb.Execute
> method
> > it was asking for the HomeInfoID. So I'm guessing that I need to not
require
> > HomeInfoID in the table but rather check to make sure that it is entered in
> the
> > Form's BeforeUpdate event. Is this correct?
> >
> > Thanks!
> > Connie
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

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

Yahoo! Groups Links