Using a Report based on Query with Prompt 2

[BJNK]

Hi, I wasn't 100% sure where to post this, but I do know that these boards are by far the best and populated with the best minds, so I think you guys will probably be able to answer this.

I have a query that when run comes up with a prompt that asks for a batch number. There are 4 of these queries. One produces the list of all materials required for that batch. One seperates the materials into glass/hardware another into lumber and molding. And the last takes the above materials and sorts them into what machines need to do what work in order to make all the parts for the furniture. They all work great and give exactly what I need.

My questions come when I use them in a report. When the report is run, it prompts for the batch # and gives the information. But I want the report to show the Batch # that is entered. Currently I just have the report to prompt to ask for it again and then is reentered and appears on the report. But this is a little annoying and leaves more room for error. Is there any way to have those keystrokes carry over from the query prompt to the report page?

And secondly I have created a "Print All Reports" button that runs all the queries/reports and prints them all out. The problem is that the prompt comes up a total of 8 times! One for each query and one for each report.. Completely unacceptable lol.

Your help is appreciated, and I hope this form was appropriate to post this question in! Thanks again!

Brad

 

[Remou]

You can pass the prompt to the report, but by far the best way to do this is to create a small form into which the user can fill the batch number. You can the refer to this form in your query and/or report (eg =Forms!frmName!txtBatchNo). Just make sure you do not close the form until you are done with it. It can often be useful to leave such a form hidden, as the user may wish to do several things with the same batch number and will be happy that it reappears filled in.

 

[BJNK]

Ok, I understand what your saying, make a form that asks for the input, and have the queries and reports draw from that. But I do not know how lol.

I understand databases, but the programming behind it is all new to me. I tried setting the control source in my report text box as the above code, modified to my form name and textbox name but it just returns #error.

And where in the queries would I set it to look for this?

If you could spell it out as easily as possible for me I would be extremely appreciative! Thanks again for any help!

 

[lespaul]

it's:

SELECT * FROM TableName WHERE batchNumber = forms!FormName!ControlName

you would replace all the [Enter batch number] prompts

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Remou]

This

http://office.microsoft.com/en-us/access/HA011170771033.aspx

gives step by step instructions.

 

[BJNK]

Ok update: The reports looking for the name in the form, now works, it was just my mistake.

I also figured out the query part, it works great.

But now I have the problem, that when I enter batch # 01-001 or 01-002 (both were already in the DB) it works fine. But when I add a new batch 01-003, the queries all come up blank... Any ideas?

Also is there a way to make the form pop up so they enter the can enter the info and then hide it? Or should I just have a written note on the form saying Keep this window open for as long as you are working with Batch # XX-XXX ??

Thank you both for your help.. Lespaul I think you helped me once before and I have to say, you are definitley a big help, but I think you are almost to smart for me haha. Thank you though!

 

[lespaul]

How are you "adding new batch 01-003"?

leslie

 

[BJNK]

Well I have done so with my forms, and also by going through and adding it manually to all the tables. Neither seems to make a difference.

I have even made a new batch for example 11-111 and used the exact same order information from batch 01-001 and still nothing appears. Any ideas on what to try to narrow down the problem?

 

[BJNK]

I forgot to add that even renaming batch 01-001 to 01-111 comes up with nothing. Its as if it is specifically looking for those numbers even though they are all set to look for the batch # entered in the form.

 

[BJNK]

Sorry I wish there was a way to edit my posts instead of reposting, but I wanted to say that if I rename 01-001 to 01-111 and then name it back to 01-001 it comes up with nothing. Have I stumbled across some sort of painful glitch somehow??

 

[Remou]

Look at the query in SQL view and cut and paste the SQL here, please.

You can use:
Me.Visible=False
To hide the form. This would generally be done in a command button that opens the query or report. However, how you go about things depends very much on how you and your users work.

 

[BJNK]

The database is corrupt, there is no question in my mind. I actually ran into the error "Error accessing file. Network Connection may have been lost." earlier when I had fooled around with some coding. This is a glitch in Access 2000 and the solution in my case was simply to revert to an earlier version of the DB. Unfortunatley for me the earlier version was way obsolete. So I copied my tables and queries from the corrupted DB and started over. Unfortunatley it looks as though that was not a good decision. I have again copied the tables over to a new database and am remaking the queries instead of pasting and they seem to work just fine. And yes I started doing daily backups (after it was too late), but when your backing up a corrupt database it doesn't do much good! But Im back on track and just need to redo what Ive already done. The boss doesn't care so neither do I

About the Me.Visible=False. Will this make it so that the form appears so they can enter the information and then dissapears?

Thank you again for your continued help! I guess I should have mentioned I was working with a potentially bad database before, but everything seemed to be working fine until this, so I guess you live you learn!