Solved

User input

Posted on 2014-01-15
10
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 38

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 38

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

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.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

695 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