ACCESS FEED: RE: [MS_AccessPros] Code for Contracts Expiring

Kamis, 02 Juni 2011

RE: [MS_AccessPros] Code for Contracts Expiring

Kat-

"I looked at the query in design view and I don't see any join lines between the
tables."

THAT is your problem! You must link the three tables in the query, or you'll
get multiple bogus rows.

I find it bizarre that if the user wants to see expiring contracts that you
close the Main Menu. Why are you doing that?

The sample database on the group website does not have a Contracts Expiring
form, nor does it have your 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 ka0t1c_ang3l
Sent: Thursday, June 02, 2011 6:17 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Code for Contracts Expiring

John -

When I first open up the database and login a msgbox pops up telling me I have
contracts that are about to expire, and then it asks if I want to view them;
when I click "yes" it another box pops up that asks me to enter the parameter
value for Forms!Contracts Expiring! txtNotify, which is the query...I then enter
a value (i.e. 60) and it opens then opens the Expired Contracts query showing
the information I posted before.

I looked at the query in design view and I don't see any join lines between the
tables.

Thanks!

Kat
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Kat-
>
> Are you saying the query prompts you with [Forms]![Contracts
> Expiring]![txtNotify], or is the Contracts Expiring form open?
>
> And you didn't answer my question about Joins. Open your query in Design
view.
> There should be join lines between the three tables in the 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 ka0t1c_ang3l
> Sent: Thursday, June 02, 2011 5:44 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Code for Contracts Expiring
>
> John -
>
> The Contracts Expiring form asks me to enter a number then when I hit the
"Run"
> Command Button, it sends an email notification to everyone who has a contract
> expiring and a cc copy to my email address. If you look at the database I
have
> in Kat's Files you can see what codes I have set up for the contract expiring
> form and what I have setup for the query.
>
> Your help is greatly appreciated!
>
> Thanks!
>
> Kat
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Kat-
> >
> > Is there anything in the txtNotify control on the Contracts Expiring form?
> >
> > And where are your joins in the query? You're getting the cartesian product
> of
> > Contract Information, Vendor Information, and Employee Input Information.
> > That's why you're getting multiple rows.
> >
> > 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 ka0t1c_ang3l
> > Sent: Thursday, June 02, 2011 4:21 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Code for Contracts Expiring
> >
> > John -
> >
> > This is the SQL for the query:
> >
> > SELECT [CONTRACT INFORMATION].[CONTRACT START DATE], [CONTRACT
> > INFORMATION].[CONTRACT END DATE], [VENDOR INFORMATION].[VENDOR E-MAIL],
> > [CONTRACT INFORMATION].[CONTRACT ID], [EMPLOYEE INPUT INFORMATION].[E-MAIL],
> > [VENDOR INFORMATION].[CONTACT PERSON], [CONTRACT INFORMATION].[VENDOR ID]
> > FROM [CONTRACT INFORMATION], [VENDOR INFORMATION], [EMPLOYEE INPUT
> INFORMATION]
> > WHERE ((([CONTRACT INFORMATION].[CONTRACT END
> DATE])<(Date()+[Forms]![Contracts
> > Expiring]![txtNotify])));
> >
> >
> > Thanks!
> >
> > Kat
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Kat-
> > >
> > > What is the SQL for your Expired Contracts query?
> > >
> > > You really shouldn't open a table or query directly in an application.
You
> > > should open a filtered form instead.
> > >
> > > 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 ka0t1c_ang3l
> > > Sent: Thursday, June 02, 2011 3:53 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Code for Contracts Expiring
> > >
> > > Good Morning!
> > >
> > > I'm having a problem with the code I have set up when I login into my
> > database.
> > > The code is supposed to let me know if there are any contracts expiring
and
> > then
> > > asks if I would like to view them. When I click yes, it opens up a query
> with
> > > all the contracts due to expire; however, when the form opens it shows all
> the
> > > contracts I've entered with the Contract End Date of the contract that is
> due
> > to
> > > expire. See the following:
> > >
> > > CONTRID CONTRSTARTDATE CONTRENDDATE CONTPERSON VENDOR ID
> > > 4 2/4/2010 5/31/2010 Michelle Locke Art Museum
> > > 4 2/4/2010 5/31/2010 Scott Cordero Art Museum
> > > 4 2/4/2010 5/31/2010 Eduardo Perez Art Museum
> > > 4 2/4/2010 5/31/2010 Capt. John Pasch Art Museum
> > >
> > > This indicates that all these contracts are for the Art Museum and expired
> on
> > > 5/31/2010, but only Michelle Locke is with the Art Museum and only this
> > contract
> > > expired on 5/31/2010.
> > >
> > > I don't know why it's doing that. How can I get it to only show those
> > contracts
> > > that are expiring for example if I enter 60 days, instead of showing me
the
> > > above.
> > >
> > > This is the code I have set up for this query:
> > >
> > > Private Sub Form_Load()
> > > Call ShowHideButtons(Me)
> > > If glngRank >= 100 Then
> > > If Not IsNull(DLookup("[Contract ID]", "[CONTRACT INFORMATION]",
"[CONTRACT
> > END
> > > DATE] <= #" & Date + 30 & "#")) Then
> > > If vbYes = MsgBox("There are contracts expiring. Would you like to view
> > them?",
> > > vbQuestion + vbYesNo) Then
> > > ' Open the contract expire form
> > > DoCmd.OpenQuery "Expired Contracts"
> > > DoCmd.Close acForm, "MAIN MENU"
> > > End If
> > > End If
> > > End If
> > > End Sub
> > >
> > > Any help is appreciated!
> > >
> > > Thanks!
> > >
> > > Kat
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

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

Yahoo! Groups Links