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