open report with current record in forms (1 Viewer)

[kotechaprashant]

Hi all

I have a form based on parameter query. User needs to give at least two parameters to open the form showing matching records to the parameters.

I have command button on form to open a report and have set open report action on click event of the button.

I wish to open report with records selected in form with the query. But on click it asks for parameters and i wish to get it automatically from the form.

I badly need a solution for this. Please help me as soon as possible.

Thanks to all in advance

 

[John Big Booty]

You can use;

Forms!YourFormName!ControlName

in the criteria of your query to use the data held in that field as criteria in your query.

 

[kotechaprashant]

You can use;

Forms!YourFormName!ControlName

in the criteria of your query to use the data held in that field as criteria in your query.

thanks jhon for reply but it could not work.

 

[vbaInet]

You need to create a popup form with two textboxes for collecting the parameters. As John mentioned, pass the values from those textboxes into the criteria of the query. The form must remain open (i.e. it can be hidden).

See if this thread helps:

http://www.access-programmers.co.uk/forums/showthread.php?t=201643&page=2

 

[John Big Booty]

thanks jhon for reply but it could not work.

How so

 

[kotechaprashant]

You need to create a popup form with two textboxes for collecting the parameters. As John mentioned, pass the values from those textboxes into the criteria of the query. The form must remain open (i.e. it can be hidden).

See if this thread helps:


Thanks for your help!!!!!!!!! I read the link you mentioned and tried to understand. However, my case is slight different. My form opens using query that requires the user to input keyword for searching the records in database. The form after matching with the keyword and returns only resulting records. At least two criteria of the query is required to mention.

Now i want to generate the report with the resulting records only. since i have created report using the same query that is used for form. it requires user to provide the inputs for query criteria again. I want to get that criteria from form only.

I am using Access 2007

I hope i didnt made it too complicated. As i am novice to Access please help accordingly.

Thanks in advance

 

[vbaInet]

I might have read your original message wrong. If you want to get the criteria from the form, simply put what John mentioned in the criteria. The only bits you need to substitute for the right names are highlighted in red:

[Forms]![[COLOR=Red][B]YourFormName[/B][/COLOR]]![[COLOR=Red][B]ControlName[/B][/COLOR]]

The form must remain open and after typing in the textbox, move the cursor to another control.

 

[kotechaprashant]

I might have read your original message wrong. If you want to get the criteria from the form, simply put what John mentioned in the criteria. The only bits you need to substitute for the right names are highlighted in red:

[Forms]![[COLOR=Red][B]YourFormName[/B][/COLOR]]![[COLOR=Red][B]ControlName[/B][/COLOR]]

The form must remain open and after typing in the textbox, move the cursor to another control.

Thanks to you for prompt reply!! I tried what the John said but it didnt work. i analyse the things and able understand the problem that might be as under:

1. I put the criteria for one of the field on the report which should be from the form
2. But the same field on the form is again dependent of the query which requires input from the user.

so at the end when I run the report from a command button on the form it asks for the input from user.

I am feeling that there is something wrong in my logic.

 

[vbaInet]

Let's have a look at your db so we can advise accordingly.

 

[kotechaprashant]

Let's have a look at your db so we can advise accordingly.

Please find attached DB file split into 4 parts.
1. first unzip all parts individually
2. then extract part -1 with WINRAR

In database
On startup on main page click "Search" button against "search cases for international taxation" select first criteria from the drop down and put "a" in second criteria, other can be blank

The resulting form has a button on top right side "generate report" which i needed to open with the records on the loaded form

Thank you very much for great help

 

[vbaInet]

This is all a bit too "bitty". I wouldn't have the time to start putting these together. Can you not just deleted the irrelevant forms, reports and queries and include some erroneous data so it can tested?

 

[kotechaprashant]

This is all a bit too "bitty". I wouldn't have the time to start putting these together. Can you not just deleted the irrelevant forms, reports and queries and include some erroneous data so it can tested?

Sorry to waste your time i will try to send you the required file after modification

 

[vbaInet]

Sorry to waste your time i will try to send you the required file after modification

No, you're not wasting my time. I just have a lot of things to do and so many windows open at the moment. Try and pull it all together and send a stripped down version and I will take a quick look and advise. Maybe John will be on later too.

 

[kotechaprashant]

No, you're not wasting my time. I just have a lot of things to do and so many windows open at the moment. Try and pull it all together and send a stripped down version and I will take a quick look and advise. Maybe John will be on later too.

find attached the DB file

In database
On startup on main page click "Search" button against "search cases for international taxation" select first criteria from the drop down and put "a" in second criteria, other can be blank

The resulting form has a button on top right side "generate report" which i needed to open with the records on the loaded form

 

[vbaInet]

See attached and you will see how it was done. I think I actually covered this in my first post.

I would mention two things though:

1. Don't name your objects with the same name even though they different objects. The search query, form and report you had problems with had the same name.

2. Have a good naming convention. Here's a link to help guide you:

http://accesstips.datashark.co.uk/namingconventions.htm

Lastly, your report was corrupt so I created a new one and added only the controls to the detail section so you will need to do the other bits.

 

[Lanser]

I hope I'm getting this right but you already have the form open with the required parameters already selected and you have a button on the form to open the report?

Try using this for the button on the form and make sure you have a field called ID on the report (can be set to Not Visible) and form

Private Sub CommandButton_Click()
Dim strDocName As String
Dim strFilter As String
strDocName = "Report_Name"
strFilter = "Id = Forms!Form_Name!ID"
Me.Refresh
DoCmd.OpenReport strDocName, acPreview, , strFilter
End Sub

 

[vbaInet]

I hope I'm getting this right but you already have the form open with the required parameters already selected and you have a button on the form to open the report?

Try using this for the button on the form and make sure you have a field called ID on the report (can be set to Not Visible) and form

Private Sub CommandButton_Click()
Dim strDocName As String
Dim strFilter As String
strDocName = "Report_Name"
strFilter = "Id = Forms!Form_Name!ID"
Me.Refresh
DoCmd.OpenReport strDocName, acPreview, , strFilter
End Sub

The query has parameters. The report's record source is set to the parameterised query. It will still prompt for the parameters a second time which is what the OP is trying to avoid.

Please see my last post and the db attached.

 

[Lanser]

apologies but I couldn't open the db.accdb file to check if I was right in my original assumptions and I was wrong anyway

 

[vbaInet]

apologies but I couldn't open the db.accdb file to check if I was right in my original assumptions and I was wrong anyway

Always good to chime in with your suggestions when you can. No harm done

You should upgrade to 2007 or 2010

 

[lcook1974]

What about just printing the form? if it has all the information in it? (just a thought)

I haven't upgraded either... (still on 2003)