RE: Query Automation question - Newbie

The problem here is that the prompt for the department is generated by the
query, so you do not have control over whether the query will run or not. A
better solution would be to put a control on your form for the user to enter
the department. Then refer to that control on the form from the query in the
Criteria row for the column you want to filter on. It is done using:
Forms!FormName!ControlName

To eliminate entry errors, I would suggest a combo box that allows users to
only select one of the 3 departments.
--
Dave Hargis, Microsoft Access MVP


"Les" wrote:

Function EMailVVOrders_6MnthsToMngr()
On Error GoTo EMailVVOrders_6MnthsToMngr_Err
Dim sMngr As String

DoCmd.SendObject acQuery, "qry_MnthsPoOpen>6PerDeptExcel",
"MicrosoftExcelBiff5(*.xls)", "", "", "", "VV Purchase Orders older than 6
Months", "Good day, Please find attached a spreadsheet with purchase orders
per buyer for your department.", False, ""

EMailVVOrders_6MnthsToMngr_Exit:
Exit Function

EMailVVOrders_6MnthsToMngr_Err:
MsgBox Error$
Resume EMailVVOrders_6MnthsToMngr_Exit

End Function
--
Les


"Klatuu" wrote:

Post back with the code including where you run the query and send the email,
please.
--
Dave Hargis, Microsoft Access MVP


"Les" wrote:

I only have 3 departments, za-tm-3, za-tm-4 and za-tm-5.

--
Les


"Klatuu" wrote:

How do you know whether the input is correct or not?
--
Dave Hargis, Microsoft Access MVP


"Les" wrote:

Hi Dave, thanks for your answer, what is confusing me is how to connect the
input with the correct "Dept" field in the Query ??

If the input is not correct then i would like to give them a message wrong
input

--
Les


"Klatuu" wrote:

Your code is okay except for a minor syntac problem. You need a double quote
on this line:
elseif input = "za-tm-4 then
Should be
elseif input = "za-tm-4" then

The only thing missing is what do you do if input is not za-tm-3 or za-tm4?
--
Dave Hargis, Microsoft Access MVP


"Les" wrote:

Hi all, I have a query that askes for an input and then filters for the
results of the input. e.g Please enter the department code
I am tying to create some code that has an if statement in it, so that i can
e-mail the manager of the department with the a spreadsheet wityh the results
in.

if input = "za-tm-3" then
sManager = "Joe Blogs"
elseif input = "za-tm-4 then
sManager = " Mr. X"
end if

Any help is much apprciated.

--
Les
.

Relevant Pages

  • Re: Problem with List box query
    ... Copying a control is one way that the code can get separated. ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... Is the row source for your listbox SQL or the actual query? ...
    (microsoft.public.access.formscoding)
  • RE: Change address fields in Table using Unbound Combo Box
    ... Dave Hargis, Microsoft Access MVP ... record is on the form but it is not in the query "FindDupsVIT". ... Don't confuse the combo's Bound Column property with it's Control ...
    (microsoft.public.access.forms)
  • Re: Help! I do I remove a parameter query?
    ... If you get a pop-up like that when you're opening the form, ... form is based on a query that has a parameter in it, or there's a control on ... > query box pops up again....asking me to enter a value. ... >> Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.gettingstarted)
  • Re: Please Help, second post
    ... "Les Coover" wrote in message ... > Created the Query then built the Form based on the Query. ... >> Doug Steele, Microsoft Access MVP ... >> (no e-mails, please!) ...
    (microsoft.public.access.formscoding)
  • Re: Message box pop up when no reults
    ... The problem you are having is that if you look in the query ... My quess is ServiceID is a control on the parent form of the subform ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.queries)