[MS_AccessPros] Re: coding for query

 

Hi Crystal,

Thanks again! The database is working great!

Katrina

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Kat,
>
> you're welcome :)
>
> > "It is possible to setup my "Contract Form" with a code that does not
> allow you to save unless all the required information has been
> completed?  If so, how would I do that?"
>
> sure!  to validate a record and prevent it from being saved, put code in the form BeforeUpdate event
>
>    '----------------- make sure all required data is filled out
>
>    'make sure SomeControlName is filled out
>    If IsNull(me.SomeControlName) then
>  
>       'if it is not filled out,
>       'then move the focus to that control
>       me.SomeControlName.setFocus
>  
>       'give the user a message
>       msgbox "You must enter Some Data",,"Missing Data"
>  
>       'if this is a combobox, drop the list for them
>       me.SomeControlName.dropDown
>  
>       'don't save the record yet
>       Cancel = true
>  
>       'quit checking and give them a chance to fill it out
>       exit sub
>    end if
>  
>    'make sure the first Date is filled out
>    If IsNull(me.Date1) then
>       me.Date1.setFocus
>       msgbox "You must enter the first Date" _
>           ,,"Missing Data"
>       Cancel = true
>       exit sub
>    end if
>  
>    'make sure the second Date is filled out
>    If IsNull(me.Date2) then
>       me.Date2.setFocus
>       msgbox "You must enter the second date" _
>           ,,"Missing Data"
>       Cancel = true
>       exit sub
>    end if
>  
>    'make sure the second Date2 is after Date1
>    If me.Date2 < me.Date1 then
>       me.Date2.setFocus
>  
>       msgbox "The second date, " & me.Date2  _
>           & " must be after the first date, " _
>           & me.Date1,,"Invalid Data"
>       Cancel = true
>  
>       'IF you want to undo the entries to the record
>       'Me.Undo
>  
>       'IF you want to undo the entries to the field
>       'Me.controlname.Undo
>       Cancel = true
>       exit sub
>    end if
>
> '~~~~~~~~~~~~~~~~~~~~
>
> WHERE
> controlname is the Name of the respective control
>
> substitute your controls names for these:
> SomeControlName
> Date1
> Date2
>
> Warm Regards,
> Crystal
>
> Microsoft MVP
> remote training - learn while we get your project done!
>
> Access Basics by Crystal
> http://www.AccessMVP.com/strive4peace
> Free 100-page book that covers essentials in Access
>
>  *
>    (: have an awesome day :)
>  *
>
>
>
>
>
> ________________________________
> From: ka0t1c_ang3l <no_reply@yahoogroups.com>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Friday, February 24, 2012 9:50 AM
> Subject: [MS_AccessPros] Re: coding for query
>
> Crystal,
>
> Thanks!  I've setup the command button and this is definitely working better.
>
> I have another question in regards to this database.  It is possible to setup my "Contract Form" with a code that does not allow you to save unless all the required information has been completed?  If so, how would I do that?
>
> Thanks!
>
> Katrina
>
> --- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@> wrote:
> >
> > Hi Kat,
> >
> > instead of this method, put a commend button on the form that you click to see the information.  then you can click when you want to :)
> > Warm Regards,
> > Crystal
> >
> > Microsoft MVP
> > remote training - learn while we get your project done!
> >
> > Access Basics by Crystal
> > http://www.AccessMVP.com/strive4peace
> > Free 100-page book that covers essentials in Access
> >
> >  *
> >    (: have an awesome day :)
> >  *
> >
> >
> >
> >
> > ________________________________
> >  From: ka0t1c_ang3l <no_reply@yahoogroups.com>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Friday, February 24, 2012 8:54 AM
> > Subject: [MS_AccessPros] Re: coding for query
> > 
> > Ok, I've tried moving the code to different areas of the form (i.e. on current, on open, etc.)  This notice still comes up everytime the Main Menu is loaded.
> >
> > Any help is appreciated.
> >
> > Thanks!
> >
> > Katrina
> >
> > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@> wrote:
> > >
> > >
> > > John -
> > >
> > > Again, you are AWESOME!  One more question and I promise to leave you alone for the rest of today..lol.
> > >
> > > This database has a login in form, once I login with my username and password it takes me to the Main Menu.  The main menu has a code on it when it loads to check to see if there are any contracts due to expire and it prompts you whether you want to see the list or not.  My question is this....how can I get this warning to show up only after I've logged in and not every time the main menu is loaded?  For example, if I click no that I don't want to see the list it takes me to the main menu.  If I click on anyone of the buttons on the main menu it takes me to the form I've chosen, and when I'm done with that form and it's closed, it will go back to the main menu and again I'm prompted if I want to see the contracts due to expire.  Here is the code I'm using.
> > > ----------
> > > 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.OpenForm "Expired Contracts", acFormDS
> > > DoCmd.Close acForm, "MAIN MENU"
> > > End If
> > > End If
> > > End If
> > > End Sub
> > > ------------
> > > This code is in the event procudure of the ON LOAD of the Main Menu form.
> > >
> > > Thanks for your help!
> > >
> > > Irene
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > > >
> > > > Katrina-
> > > >
> > > > That code will skip any record where the Vendor Email is empty.
> > > >
> > > > 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/
> > > > (Waco, TX)
> > > >
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > > > Sent: Wednesday, February 22, 2012 5:00 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: coding for query
> > > >
> > > > John -
> > > >
> > > > You are AWESOME!  I've tested and the emails are sending; however, I did notice
> > > > 1 thing.  When I select more than 2 vendors that should receive an email only
> > > > the first 2 emails are being sent out the others are not.  What do I need to do
> > > > to get it to send all the emails out for those I've selected?
> > > >
> > > > Thanks!
> > > >
> > > > Katrina
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > > > >
> > > > > Katrina-
> > > > >
> > > > > For starters, your Append query should look like:
> > > > >
> > > > > INSERT INTO [SEND TBL] ( [CONTRACT START DATE], [CONTRACT END DATE], [VENDOR
> > > > > E-MAIL], [CONTRACT ID], [E-MAIL], [CONTACT PERSON], [VENDOR ID], [CONTRACT
> > > > > TITLE], [LEGAL EMAIL] )
> > > > > SELECT [Expired Contracts].[CONTRACT START DATE], [Expired
> > > > Contracts].[CONTRACT
> > > > > END DATE], [Expired Contracts].[VENDOR E-MAIL], [Expired Contracts].[CONTRACT
> > > > > ID], [Expired Contracts].[E-MAIL], [Expired Contracts].[CONTACT PERSON],
> > > > > [Expired Contracts].[VENDOR ID], [Expired Contracts].[CONTRACT TITLE],
> > > > [Expired
> > > > > Contracts].[LEGAL EMAIL], *
> > > > > FROM [Expired Contracts];
> > > > >
> > > > > I guess I didn't make it clear that all your send email code should be in SEND
> > > > > FRM.  Move all this code there:
> > > > >
> > > > > -------------------------------
> > > > >         ' Open a recordset on the expiring contracts
> > > > >         Set db = CurrentDb
> > > > >         Set rst = db.OpenRecordset("SELECT * FROM [SEND TBL] WHERE [SEND
> > > > EMAIL]
> > > > > = True")
> > > > >         ' Loop through them all
> > > > >         Do Until rst.EOF
> > > > >             ' Make sure we have a valid email
> > > > >             If Not IsNull(rst![VENDOR E-MAIL]) Then
> > > > >                Dim objOutlook As Outlook.Application
> > > > >           Dim objOutlookMsg As Outlook.MailItem
> > > > >           Dim objOutlookRecip As Outlook.Recipient
> > > > >           Dim objOutlookAttach As Outlook.Attachment
> > > > >
> > > > >           ' Create the Outlook session.
> > > > >           Set objOutlook = CreateObject("Outlook.Application")
> > > > >
> > > > >           ' Create the message.
> > > > >           Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
> > > > >
> > > > >           With objOutlookMsg
> > > > >               ' Add the To recipient(s) to the message.
> > > > >               Set objOutlookRecip = .Recipients.Add(rst![VENDOR E-MAIL])
> > > > >               objOutlookRecip.Type = olTo
> > > > >
> > > > >               ' Add the CC recipient(s) to the message.
> > > > >               Set objOutlookRecip = .Recipients.Add(rst![E-MAIL])
> > > > >               objOutlookRecip.Type = olCC
> > > > >             
> > > > >               ' Add the BCC recipient(s) to the message.
> > > > >               Set objOutlookRecip = .Recipients.Add(rst![LEGAL EMAIL])
> > > > >               objOutlookRecip.Type = olBCC
> > > > >
> > > > >              ' Set the Subject, Body, and Importance of the message.
> > > > >              .Subject = "Expire Date Approaching"
> > > > >              .Body = "ATTN:" & " " & rst![CONTACT PERSON] & " " & _
> > > > >                 "-" & vbCrLf & vbCrLf & "Please be advised that your contract
> > > > > with the Corpus Christi International Airport (CCIA) will expire on " & _
> > > > >                                  rst![CONTRACT END DATE] & _
> > > > >                                  "." & vbCrLf & vbCrLf & "Please contact Irene
> > > > > Segovia, Sr. Staff Assistant, at (361) 289-0171, ext. 1210 or by email at
> > > > > irenes@ regarding this matter.  Your cooperation is greatly
> > > > > appreciated." & vbCrLf & vbCrLf & "Thank you." & vbCrLf & vbCrLf & "CCIA
> > > > > Administration"
> > > > >              .Importance = olImportanceHigh  'High importance
> > > > >
> > > > >              ' Resolve each Recipient's name.
> > > > >              For Each objOutlookRecip In .Recipients
> > > > >                  objOutlookRecip.Resolve
> > > > >              Next
> > > > >
> > > > >              ' Should we display the message before sending?
> > > > >              If DisplayMsg Then
> > > > >                  .Display
> > > > >              Else
> > > > >                  .Save
> > > > >                  .Send
> > > > >              End If
> > > > >           End With
> > > > >           Set objOutlook = Nothing
> > > > >         End If
> > > > >             ' Get the next one
> > > > >             rst.MoveNext
> > > > >         Loop
> > > > >         ' Close out
> > > > >         rst.Close
> > > > >         Set rst = Nothing
> > > > > Set db = Nothing
> > > > > --------------------------------------------------
> > > > >
> > > > > Note that the code is opening the SEND TBL, not your query.
> > > > >
> > > > > Your SEND FRM should be a Continuous Form with a command button in the header
> > > > to
> > > > > run the above code.  Emails will be sent only to the rows selected by the
> > > > user.
> > > > >
> > > > > Contracts Expiring should have this code:
> > > > >
> > > > > Private Sub Command2_Click()
> > > > >  Dim db As DAO.Database, rst As DAO.Recordset
> > > > >     If IsNull(DLookup("[CONTRACT END DATE]", "[Expired Contracts]")) Then
> > > > >         MsgBox "There are no contracts expiring."
> > > > >         DoCmd.Close acForm, Me.Name
> > > > >         DoCmd.OpenForm "Main Menu"
> > > > >     Else
> > > > >         ' Point to this database
> > > > >         Set db = CurrentDb
> > > > >         ' Clean out the working table
> > > > >         CurrentDb.Execute "DELETE * FROM [SEND TBL]", dbFailOnError
> > > > >         ' Avoid prompts
> > > > >         DoCmd.SetWarnings False
> > > > >         ' Load in the rows to select
> > > > >         DoCmd.OpenQuery "Expired Contracts Query"
> > > > >         ' Turn on prompts again
> > > > >         DoCmd.SetWarnings True
> > > > >         ' Clear the database object
> > > > >         Set db = Nothing
> > > > >         ' Open the selection form
> > > > >         DoCmd.OpenForm "SEND FRM"
> > > > >         ' Close me
> > > > >         DoCmd.Close acForm, Me.Name
> > > > >     End If
> > > > > End Sub
> > > > >
> > > > >
> > > > > 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/
> > > > > (Waco, TX)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > > > > Sent: Wednesday, February 22, 2012 12:46 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: [MS_AccessPros] Re: coding for query
> > > > >
> > > > > John -
> > > > >
> > > > > I've created the "SEND Tbl" as my temp table.  I've also created an append
> > > > query
> > > > > based on the expired contracts query.  I've also created the "SEND FRM".  On
> > > > the
> > > > > "Contract Expiring" form I input the number of days and click run, and it
> > > > gives
> > > > > me an error in the FROM part of the code.  I've also included the delete code
> > > > in
> > > > > that coding; however, because I'm getting that error it's unable to open my
> > > > > second form.  I'm sure I have just completed messed up!  I'm just not
> > > > confident
> > > > > that I did what you asked me to.  Please take a look at it.  I've uploaded the
> > > > > databse to the AssistanceNeeded folder under Contract Management System2.
> > > > >
> > > > > Thanks for all your help.
> > > > >
> > > > > Katrina
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > > > > >
> > > > > > Katrina-
> > > > > >
> > > > > > At the bottom of the code behind your Run button.  Last command should be to
> > > > > > open the second form and close the current one.
> > > > > >
> > > > > > 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/
> > > > > > (Waco, TX)
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > > > > > Sent: Wednesday, February 22, 2012 9:56 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] Re: coding for query
> > > > > >
> > > > > > John,
> > > > > >
> > > > > > I apologize I am really clueless when it comes to this stuff.  Where do I
> > > > put
> > > > > > the code to delete?
> > > > > >
> > > > > > Thanks!
> > > > > >
> > > > > > Katrina
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > > > wrote:
> > > > > > >
> > > > > > > Katrina-
> > > > > > >
> > > > > > > To delete:
> > > > > > >
> > > > > > >    CurrentDb.Execute "DELETE * FROM MyTable", dbFailOnError
> > > > > > >
> > > > > > > .. where MyTable is the name of your temp table.
> > > > > > >
> > > > > > > To append:
> > > > > > >
> > > > > > > Create a new query using Expired Contacts as the source.  Change to an
> > > > > Append
> > > > > > > query and use the name of your temp table as the target.  Drag and drop
> > > > all
> > > > > > the
> > > > > > > fields, then save the query.
> > > > > > >
> > > > > > > Use RunQuery to execute it from code - that should pick up the parameter
> > > > > from
> > > > > > > your 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/
> > > > > > > (Waco, TX)
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > > > > > > Sent: Wednesday, February 22, 2012 9:29 AM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > Subject: [MS_AccessPros] Re: coding for query
> > > > > > >
> > > > > > > John -
> > > > > > >
> > > > > > > I've created a "temp" table and entered all the columns from the query and
> > > > > > have
> > > > > > > incuded a yes/no field.  However, you lost me when you said run a Delete
> > > > > query
> > > > > > > to clear the table, append the records from
> > > > > > > > your "results" query into that table, then open a form to display the
> > > > > table.
> > > > > > > I have no idea how to do this.  I'll create a form based on the "temp"
> > > > table
> > > > > > as
> > > > > > > my second form.
> > > > > > >
> > > > > > > Thanks!
> > > > > > >
> > > > > > > Katrina
> > > > > > >
> > > > > > > > contents.
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > > > > wrote:
> > > > > > > >
> > > > > > > > Katrina-
> > > > > > > >
> > > > > > > > You would need a "temp" table that contains all the columns from your
> > > > > query
> > > > > > > and
> > > > > > > > a Yes/No field.  Run a Delete query to clear the table, append the
> > > > records
> > > > > > > from
> > > > > > > > your "results" query into that table, then open a form to display the
> > > > > table
> > > > > > > > contents.  User could click the Yes/No field to "exclude" certain rows,
> > > > > then
> > > > > > > > click a button to send an email to those not excluded.  Your code to
> > > > send
> > > > > > > emails
> > > > > > > > would be in that second 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/
> > > > > > > > (Waco, TX)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > -----Original Message-----
> > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > ka0t1c_ang3l
> > > > > > > > Sent: Wednesday, February 22, 2012 8:33 AM
> > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > Subject: Re: [MS_AccessPros] coding for query
> > > > > > > >
> > > > > > > >
> > > > > > > > John -
> > > > > > > >
> > > > > > > > Thanks!  I filled in the Legal ID on my Contract Information table and
> > > > all
> > > > > > of
> > > > > > > > the records came up.  I have another question.  A little back ground
> > > > > first.
> > > > > > > On
> > > > > > > > my main menu I have a "Contracts Expiring" button which opens the
> > > > > "Contracts
> > > > > > > > Expiring" form.  This is the form you fixed the code on.  What I do is
> > > > > enter
> > > > > > > the
> > > > > > > > number of days and hit run and the system will automatically send each
> > > > > > vendor
> > > > > > > > with a contract expiring an email advising them of such.  However, in my
> > > > > > > system
> > > > > > > > I also have short term agreements which were for maybe only a few month,
> > > > > and
> > > > > > I
> > > > > > > > don't want emails going to those vendors since we would not be renewing
> > > > a
> > > > > > > > contract with them.  Is it possible to select which vendors I want to
> > > > send
> > > > > > > > emails to?  For example, once I get to the "Contracts Expiring" form and
> > > > > put
> > > > > > > in
> > > > > > > > the number of days when I hit run instead of instantally having the
> > > > system
> > > > > > > send
> > > > > > > > an email to all vendors with expiring contracts it will prompt me with a
> > > > > > list
> > > > > > > > that includes a checkbox and those vendors with a checked box will be
> > > > the
> > > > > > only
> > > > > > > > ones who will receive an email.  If this is possible what would I have
> > > > to
> > > > > do
> > > > > > > to
> > > > > > > > get this done?
> > > > > > > >
> > > > > > > > Thanks for your help!
> > > > > > > >
> > > > > > > > Katrina
> > > > > > > >
> > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > > > > > wrote:
> > > > > > > > >
> > > > > > > > > Katrina-
> > > > > > > > >
> > > > > > > > > The query runs fine - and returns no rows because there is nothing in
> > > > > the
> > > > > > > > Legal
> > > > > > > > > ID field in your sample table.  The INNER JOINs will return only rows
> > > > > > where
> > > > > > > > > there are matching values for all the Joins.
> > > > > > > > >
> > > > > > > > > But your code is wrong because you failed to include parens for the
> > > > > > > additional
> > > > > > > > > join, and you had an extra ON keyword in the wrong place:
> > > > > > > > >
> > > > > > > > >         Set rst = db.OpenRecordset("SELECT [VENDOR E-MAIL], [CONTRACT
> > > > > END
> > > > > > > > DATE],
> > > > > > > > > [E-MAIL], [CONTACT PERSON], [LEGAL EMAIL] " & _
> > > > > > > > >                                    "FROM (([CONTRACT INFORMATION]
> > > > INNER
> > > > > > JOIN
> > > > > > > > > [VENDOR INFORMATION] ON " & _
> > > > > > > > >                                    "[CONTRACT INFORMATION].[VENDOR ID]
> > > > =
> > > > > > > > [VENDOR
> > > > > > > > > INFORMATION].[VENDOR ID]) " & _
> > > > > > > > >                                    "INNER JOIN [EMPLOYEE INPUT
> > > > > > INFORMATION]
> > > > > > > ON
> > > > > > > > "
> > > > > > > > > & _
> > > > > > > > >                                    "[CONTRACT INFORMATION].[EMPLOYEE
> > > > ID]
> > > > > =
> > > > > > > > > [EMPLOYEE INPUT INFORMATION].[ID]))  " & _
> > > > > > > > >                                    "INNER JOIN [Legal tbl] ON " & _
> > > > > > > > >                                    "[CONTRACT INFORMATION].[LEGAL ID]
> > > > =
> > > > > > > [Legal
> > > > > > > > > tbl].[LEGAL ID] " & _
> > > > > > > > >                                    "WHERE [CONTRACT END DATE] < #" &
> > > > > (Date
> > > > > > +
> > > > > > > > > Me.txtNotify) & "#")
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > 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/
> > > > > > > > > (Waco, TX)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > -----Original Message-----
> > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > ka0t1c_ang3l
> > > > > > > > > Sent: Tuesday, February 21, 2012 4:44 PM
> > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > Subject: Re: [MS_AccessPros] coding for query
> > > > > > > > >
> > > > > > > > > John -
> > > > > > > > >
> > > > > > > > > If i take out the code regarding the legal id and legal email the
> > > > query
> > > > > > > works
> > > > > > > > > fine, so I know it's something to do with the legal tbl i've added.
> > > > > > > However,
> > > > > > > > I
> > > > > > > > > can't figure out what I'm missing or what I've entered incorrectly.
> > > > > > > > >
> > > > > > > > > Thanks for your help!
> > > > > > > > >
> > > > > > > > > Katrina
> > > > > > > > >
> > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l
> > > > <no_reply@>
> > > > > > > > > wrote:
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > John -
> > > > > > > > > >
> > > > > > > > > > I apologize for the late reply.  I've uploaded the database into the
> > > > > > > > > 2_AssistanceNeeded folder under contract management system2.zip.  The
> > > > > > > Expired
> > > > > > > > > Contracts query is in there.  It was working fine before, but I had to
> > > > > go
> > > > > > in
> > > > > > > > and
> > > > > > > > > the legal email.  The reason for adding in the email is because I want
> > > > a
> > > > > > > > > courtsey copy of the email that will be sent to vendors to also go to
> > > > > our
> > > > > > > > > attorney.
> > > > > > > > > >
> > > > > > > > > > Also, could you please take a look at my contracts expiring form.
> > > > > The
> > > > > > > > "Run"
> > > > > > > > > button event procedure to make sure I added in the legal information
> > > > > > > > correctly.
> > > > > > > > > I'm still new to this and am learning as I go.  I just want to be sure
> > > > > > I've
> > > > > > > > > included everything correctly.
> > > > > > > > > >
> > > > > > > > > > Thanks!
> > > > > > > > > >
> > > > > > > > > > Katrina
> > > > > > > > > >
> > > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
> > > > <john@>
> > > > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > Katrina-
> > > > > > > > > > >
> > > > > > > > > > > FROM ([VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT
> > > > INFORMATION]
> > > > > > > > > > > INNER JOIN [CONTRACT INFORMATION]
> > > > > > > > > > > INNER JOIN [Legal tbl]
> > > > > > > > > > > ON [EMPLOYEE INPUT INFORMATION].ID = [CONTRACT
> > > > > INFORMATION].[EMPLOYEE
> > > > > > > ID])
> > > > > > > >
> > > > > > > > > > > ON [VENDOR INFORMATION].[VENDOR ID] = [CONTRACT
> > > > INFORMATION].[VENDOR
> > > > > > > ID])
> > > > > > > > > > > ON [CONTRACT INFORMATION].[LEGAL ID] = [Legal tbl].[LEGAL ID]
> > > > > > > > > > >
> > > > > > > > > > > I don't see anything wrong with that unless you've misspelled one
> > > > of
> > > > > > the
> > > > > > > > > table
> > > > > > > > > > > or ID names.  Or perhaps there's a problem with an ID data type
> > > > > > > somewhere.
> > > > > > > > > > >
> > > > > > > > > > > What does this look like in Design view?
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > 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/
> > > > > > > > > > > (Waco, TX)
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > -----Original Message-----
> > > > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > > > ka0t1c_ang3l
> > > > > > > > > > > Sent: Tuesday, February 21, 2012 8:58 AM
> > > > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > > > Subject: [MS_AccessPros] coding for query
> > > > > > > > > > >
> > > > > > > > > > > I have an expired contract query which I use to select vendors who
> > > > I
> > > > > > > want
> > > > > > > > > > > emailed advising that their lease is due to expire.  This query
> > > > > works
> > > > > > > > fine;
> > > > > > > > > > > however, I want to add our attorney's email to the query so that
> > > > she
> > > > > > can
> > > > > > > > be
> > > > > > > > > > > included in the courtesy copy of the email.  When I try to add the
> > > > > > legal
> > > > > > > > > email
> > > > > > > > > > > it keeps giving me a syntax error and it higlights the inner join
> > > > > part
> > > > > > > of
> > > > > > > > > the
> > > > > > > > > > > SQL code.  This is what I have.
> > > > > > > > > > >
> > > > > > > > > > > ------------
> > > > > > > > > > > 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],
> > > > > > > > > > > [CONTRACT INFORMATION].[CONTRACT TITLE], [CONTRACT
> > > > > INFORMATION].[LEGAL
> > > > > > > > ID],
> > > > > > > > > > > [Legal tbl].[LEGAL EMAIL]
> > > > > > > > > > > FROM ([VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT
> > > > INFORMATION]
> > > > > > > INNER
> > > > > > > > > JOIN
> > > > > > > > > > > [CONTRACT INFORMATION] INNER JOIN [Legal tbl] ON [EMPLOYEE INPUT
> > > > > > > > > INFORMATION].ID
> > > > > > > > > > > = [CONTRACT INFORMATION].[EMPLOYEE ID]) ON [VENDOR
> > > > > > INFORMATION].[VENDOR
> > > > > > > > ID]
> > > > > > > > > =
> > > > > > > > > > > [CONTRACT INFORMATION].[VENDOR ID]) ON [CONTRACT
> > > > INFORMATION].[LEGAL
> > > > > > ID]
> > > > > > > =
> > > > > > > > > > > [Legal tbl].[LEGAL ID]
> > > > > > > > > > > WHERE ((([CONTRACT INFORMATION].[CONTRACT END
> > > > > > > > > DATE])<(Date()+[Forms]![Contracts
> > > > > > > > > > > Expiring]![txtNotify])));
> > > > > > > > > > > -----------------
> > > > > > > > > > >
> > > > > > > > > > > If anyone can please help me and let me know what it is I'm doing
> > > > > > wrong
> > > > > > > > I'd
> > > > > > > > > > > really appreciate it.
> > > > > > > > > > >
> > > > > > > > > > > Thanks!
> > > > > > > > > > >
> > > > > > > > > > > Katrina
> > > > > > > > > > >
> > > > > > > > > > >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (21)

Recent Activity:

• New Members 11
• New Files 4

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___