Solved

User input

Posted on 2014-01-15
10
335 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 34

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
 

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 34

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now