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