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
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar