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
Tidak ada komentar:
Posting Komentar