Solved

User input

Posted on 2014-01-15
10
334 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

707 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

13 Experts available now in Live!

Get 1:1 Help Now