pass parameters from form to report

[aspnetuser]

Is it possible to create a form with one text box, and one button that allows a users to type 1 press the button, open a report in which the ID = the parameter 1.

I know how to do this with a query [enter id] etc...

However, I need to do this via a form.

can someone step me through this?

 

[GingerR]

Put a button on the form, and use the wizard to direct it to preview your report.

Then look at the properties of the button, and in the OnClick event, click the BUILD button (the little button to the right with three dots on it).

Your code will look something like

Docmd.OpenReport strReportName,acviewPreview

Type a comma after acViewPreview
Then type another comma. It should prompt you for a WHERE statement.

You'll enter something like:

"ID = " & Forms!FormName!txtID

where ID = the name of the field in your table
FormName = your form name
txtID = the name of the ID text box on your form.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aspnetuser]

great tip, I just need to try it out on access project with a view instead of a query? Think it will work?

 

[GingerR]

I would think so. i don't know I've never made an ADP. There's a diff forum for that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aspnetuser]

I need to add one more filter. It is on a public variable I set at login. strUserName

I need to add it like this but don't know the code:
Dim stDocName As String

stDocName = "Reports"
DoCmd.OpenReport stDocName, acPreview, , "PayPeriod = " & Forms!ReportsPrompt!txtPP & "[name] = '" & strUserName & "'"

I tried using the server filter ON OPEN of the report but it does not work if you take this method of passing parameters.

Can you show me how to correctly add it to the code you suggested?

 

[GingerR]

you're missing the AND

"PayPeriod = " & Forms!ReportsPrompt!txtPP & " and [name] = '" & strUserName & "'"


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aspnetuser]

ARGHH!

Thanks!

 

[aspnetuser]

how about two of the same field name for a filter.

Me.Filter = "[DC] = '111' and [DC]= '222'"
Me.FilterOn = True

How do you get that to work?

 

[GingerR]

i always just use WHERE statements, not filters.
did you try it? did it work?

seems like you'd want OR instead of AND, since one piece of data cannot be two different things at the same time.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aspnetuser]

ginger everything worked except the OR statement.

thanks for all your help and appreciate if you could help on the OR part.

 

[GingerR]

what's your code?
what's your data? how is it coming out wrong and what do you want it to come out like?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244