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

Kamis, 02 Juni 2011

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
>