Query Automation question - Newbie

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

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.
 
Your code is okay except for a minor syntac problem. You need a double quote
elseif input = "za-tm-4 then Should be
elseif input = "za-tm-4" then
Click to expand...

The only thing missing is what do you do if input is not za-tm-3 or za-tm4?
 
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
 
Post back with the code including where you run the query and send the email,
please.
 
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
 
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.
 
Ok thanks for the info..
--
Les


Klatuu said:
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.
Click to expand...
 
Back
Top