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

Kamis, 02 Juni 2011

RE: [MS_AccessPros] Code for Contracts Expiring

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