Solved

User input

Posted on 2014-01-15
10
336 Views
Last Modified: 2014-01-21
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
Comment
Question by:Kdankwah
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 250 total points
ID: 39783403
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
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39783412
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 39783418
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Kdankwah
ID: 39783762
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 39783861
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
 

Author Comment

by:Kdankwah
ID: 39785463
Thanks Sir.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 39786431
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39786433
here you go
Database1.mdb
0
 

Author Comment

by:Kdankwah
ID: 39786909
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39787246
Also review what aebea and Pat Hartman have posted as well.

jeffCoachman
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question