[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

User input

Hi,

I would like to attach the VBA below to a button to print reports, I want the user to enter into a field called Dept and Segment, I dont know how to add the user input box to the VBA, could someone please help.  

Option Compare Database

'------------------------------------------------------------
' Run department reports
'
'------------------------------------------------------------
Function Find_department()
On Error GoTo Find_department_Err

    DoCmd.OpenReport "TB_Monthly report_summ_segment _subreporting2", acViewPreview, "Run reports", "((([Master_table].[Dept and segment])=[Enter dept and segment:]) And (([Master_table].[Exclude_YESOR NO])=No))", acNormal
    DoCmd.OutputTo acOutputReport, "TB_Monthly report_summ_segment _subreporting2", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint


Find_department_Exit:
    Exit Function

Find_department_Err:
    MsgBox Error$
    Resume Find_department_Exit

End Function


Secondly is there a way to autosave the pdf file that will be previewed to a field name in my query?

Thanks
0
Kdankwah
Asked:
Kdankwah
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Anthony BerenguelCommented:
this should help you get started with input boxes
'create a string variable
dim tempString as string

'populate the string variable with inputbox
tempString = inputbox("Enter a value")

'put that tempString into you reports criteria
DoCmd.OpenReport "TB_Monthly report_summ_segment _subreporting2", acViewPreview, "Run reports", "((([Master_table].[Dept and segment])='" & tempstring & "' And (([Master_table].[Exclude_YESOR NO])=No))", acNormal

Open in new window

0
 
Anthony BerenguelCommented:
You probably want to do some data validation on the tempString after it's populated by the inputbox, to make sure they entered a valid [dept and segment]. If you need help with that let me know.
0
 
PatHartmanCommented:
This is code I use to assign a name and save the file
 strFileName = Me.txtPath & "\" & Me.cboJob.Column(1) & "_" & strReport & "_" & Format(Date, "yyyymmdd") & ".pdf"
 DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName

Open in new window


The best way to get the arguments for the Where clause is to reference form fields.  That way you won't get any prompts at all.
DoCmd.OpenReport "TB_Monthly report_summ_segment _subreporting2", acViewPreview, "Run reports", "((([Master_table].[Dept and segment])= Forms!yourform!deptand) And (([Master_table].[Exclude_YESOR NO])=No))", acNormal

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
KdankwahAuthor Commented:
PatHarman

I get an invalid use of Me. keyword when I use this code.

 strFileName = Me.txtPath & "\" & Me.cboJob.Column(1) & "_" & strReport & "_" & Format(Date, "yyyymmdd") & ".pdf"
 DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName

Thanks
0
 
PatHartmanCommented:
You need to change the code to suit your situation.  In my app this code is in a button click event on a form.  I had a control on the form where the user could pick a location (Me.txtPath).  There was another control where they had chosen a Job (Me.cboJob.Column(1)).  Both of those were used to construct a name for the document along with the fixed portion and ending with today's date.  You can hard code the name for now and make it variable later after you've had a chance to examine the code and understand it.

strFileName = "C:\myReport.pdf"

I see that you are running this from within a function so it is probably not in the class module of a form.  That means you would not have a "Me." object.

What is calling the function?  If it is a form, you can pass in a form object which will give you direct access to controls on the form.
0
 
KdankwahAuthor Commented:
Thanks Sir.
0
 
Jeffrey CoachmanCommented:
Another way around this is to use a form to gather all of the needed info.

The advantage here is that it is easier to do validation on a textbox on a form, than it is to validate data via an input box.

It may be easier from a user interface standpoint as well.
Input boxes disappear after you click ok, whereas with a form, the use can see all of the data they entered up until they activate the code.
Also you may need special handling in the case the user "cancels" the input box

In a larger sense, input boxes should be uses in the very simplest of needs.

In your case, the user should be presented with a dropdown or listbox, that lists all the departments.  
The user should never have to "Guess" (type in manually) a predetermined, (already existing), value like the "Department"
Also note the comboboxes/listboxes have built in validations

Also, (unless your needs are very specific), I don't really see the need for this to be called as a function.
If you are just running this from a button on a form, you can just code it there and take advantage of the "Me." keyword


Final notes:
1. What is also confusing here is the field called "Dept and segment".  What is this field exactly?  Is it one field, or two?
2. Do not insert error handling until the code is full tested for all scenarios, this is so that you can actually  break into the code and view the offending segment.
3. You did not state what the underlying "Dept and segment" data is  (Text or numeric), as this will affect he syntax you need to use, and also determine the type of validation required

Here is one fairly common way to d what you are asking.
Take form it what you like.
;-)
JeffCoachman
0
 
Jeffrey CoachmanCommented:
here you go
Database1.mdb
0
 
KdankwahAuthor Commented:
Thanks so much for your prompt reply.  

Final notes:
1. What is also confusing here is the field called "Dept and segment".  What is this field exactly?  Is it one field, or two?
Answer: These are two text fields that I combine using calculation.

2. Do not insert error handling until the code is full tested for all scenarios, this is so that you can actually  break into the code and view the offending segment.

Advice well taken.

3. You did not state what the underlying "Dept and segment" data is  (Text or numeric), as this will affect he syntax you need to use, and also determine the type of validation required

Answer is above.

Here is one fairly common way to d what you are asking.
Take form it what you like.

I really appreciate your input.  I will take your advise and use what you sent me.

Thanks once more.
0
 
Jeffrey CoachmanCommented:
Also review what aebea and Pat Hartman have posted as well.

jeffCoachman
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now