Having to re-enter a parameter for a select query to print a report from an open form

[Roncc]

I have a form which uses a select query as the data source. The select query has a parameter; "Enter the record number" which presents in a dialogue box when I open the form. Sometimes I would like to print a report (based on the same select query) from a button I have placed on that form. When I click the button I am asked to re-enter the parameter ("Enter the record number"). Since the query has already been run (as evidenced by the fact that the form has opened) one would think there would be a way to print the report without re-entering the parameter but, if there is, I can't find it.



Any ideas?

Thanks in advance for the help.

[Micron]

Sorta, then again no. You can't base the report on the same or any other query that contains a parameter as criteria. There are a few ways you could do this
- base the report on a table (or query without the parameter) and apply a filter to the report, using the form field as the filter criteria
- build the report record source as sql in code that you run when opening the report, and reference the form field in that sql
either of those, or similar methods, require the form to remain open in order to get the field value
- pass the form field value as a parameter of the report OpenArgs property and use that property for the report sql (or filter) - then the form doesn't have to be open
- there are more complicated ways (such as passing the form's recordset clone to the report) but the above are the simplest I can think of at present. Others may have better suggestions.

[June7]

@Micron, not sure what you meant to convey with "can't base the report on the same or any other query that contains a parameter as criteria", because I am able to do exactly that. There is no way to prevent the popup input prompt when the dynamic parameter is not a reference to a control.

However, I have NEVER used dynamic parameterized queries. I prefer to construct filter criteria with VBA. Although I have used the Filter and FilterOnLoad properties in report design to utilize a dynamic parameter that references form control/field.

Micron's first suggestion is probably the most commonly used by experienced developers. Code (VBA or macro) uses the WHERE CONDITION argument of OpenReport (or OpenForm) method.

[Roncc]

Thank you for taking the time to respond. I appreciate it. I don't know enough about sql to follow your advice but, if you don't mind, I had one other thought; Create an update query with a parameter that places a number i.e. 1, in a field in the table, a field that is normally empty. I could run the update query first, then open a form based on a select query where the field being used value is equal to 1. A report could then be run with the same select query as the source without having to re-enter the parameter. When I close the form I could run a second update query that sets all the field values (for the field being used to 'store' the 1) to null, then I'm good to go again. I like this idea but I think I may have a potential problem with it; I plan to put this database on a server to allow multiple users to work with it at the same time. If two people have the form open (and therefore two records would have the number "1" in the selected field) when one of the users closer the form and runs the update query it will remove the 1 from the field in all the records in the table, including the record that the other person is using which, I believe, may create a problem. I'm not a server guy either so I'm not sure if this will cause a problem or not.. This question (about the server issue) may be best answered by starting a new thread. Thanks again for your help.

Ron

[June7]

Yes, that will be a problem. DO NOT use that method. Already provided options that are simpler and suited to multi-user db. You already know enough SQL to accomplish because you have the query designer to do the heavy lifting for you. It's VBA knowledge you may be lacking. However, suggestions include methods that do not require VBA. Use form and report design properties and/or macros.

[ssanfu]

Just read your Post #4 and big red flags popped up.

I plan to put this database on a server to allow multiple users to work with it at the same time. If two people have the form open (and therefore two records would have the number "1" in the selected field) when one of the users closer the form and runs the update query it will remove the 1 from the field in all the records in the table, including the record that the other person is using which, I believe, may create a problem.

It sounds like your dB is NOT split and you plan on putting the dB where multiple people will access that 1 dB on the server at the same time.
If this is TRUE, then you'd better have lots of backups. This plan is an excellent plan for corruption of your dB!


For a multi-user dB, it should be split - this means having a back end (BE) and a front end (FE), where the BE has only tables and the FE has everything except the tables. The FE is linked to the BE that is on the server.

Each person has a COPY of the FE on their local computer.

[Roncc]

Thanks for the heads up, I thought I was headed for trouble that's why I posted the server comment. As I said, I'm not a server guy so this is an area where I am completely lost. Question for you though; If I do split the database into BE and FE as you describe, how are the tables on the backend updated with continually changing data coming from multiple users? In other words, if two users assign a 1 to a field in the update query described, and then one runs the update query to replace the 1 with a null, why don't both records get updated to null in the field? And, if both records don't get updated, how does the BE table get updated? If, for example, one of the two users changes a street address on a record?

Thanks again to all.

[June7]

FE has links to BE tables. Each copy of the FE is pointing to the same tables and the same records in the BE. There are no 'both' records, there is only one. Edits are passed through the links to BE tables. The last user edit is what the record will retain. This is why your idea for a 'flag' field will not work.

An exception to the rule of no tables in FE is the use of 'temp' table. Table is permanent but records are temporary. The table is populated only for the duration of some process

[ssanfu]

The way you describe is not a good method.

Lets say you and I are sitting in different offices and we want to print the SAME report, but you want company 2 data and I want company 3 data.
We are looking at the same form (we each have a copy of the FE) where there is a text box labeled CompanyID. We enter the number of the company we each want.

In VBA, there is a command to open a report and there is a way to specify criteria.
I hit my print button and the command is sent that says, in effect, print "Report1", but limit the records to company 3.
A millisecond later, you hit your print button and the command is sent that says, print "Report1", but limit the records to company 2.
We each get "Report1", but with different data, based on the criteria specified. No updating a table, no conflicts.

That is just one method to print reports.

[Roncc]

Thank you to all for the help. I'm going to check with the IT people at the International Rescue Committee (that is who I do volunteer work for) and get their input. I suspect they are familiar with the "splitting" concept. I'll be very careful not to design the database where corruption is likely.

I appreciate all the guidance.

Ron

[Roncc]

Got it. Thanks.

[Micron]

June7: I didn't complete that thought. I was so sure I had written "and not expect the report to prompt you for the same parameter" or something similar, that I had to go back to be sure you hadn't missed it. Sorry for the confusion. OP seemed to think that just because the query had run (or was opened) that it somehow persists throughout for every object that might be based on it. At this point, I haven't got the time to digest the posts that have followed, but it seems you have the situation in hand.

[Roncc]

Just to clarify; does this mean that if I split the database as described, I can use the technique where I run an update query first (to set a specific field value to 1) then run a select query to select the single record where that field value is 1 (in order to bypass the need to re-enter the parameter for the report) or will it still cause a problem when I close with the second update query to reset the field value of "all" records to null?

[June7]

As already advised, it will still cause a problem because multiple users could be attempting to set the field value.

Do users really need to make arbitrary selection of records? Why is selection not based on real data? Ssanfu provided one example of selection based on data.

[Roncc]

The overall algorithm looks like this: I have a table containing clients, each of which has his own client number. A user will meet with a client and access his record in the database to edit the data for the client(address, nest appointment date and time, etc.) and, in the process, print out reports one for the client and one for a paper file kept on the client. There will be multiple users, each seeing his own client, calling up that clients record, etc. As described in the original post, I would like to use a parameter query (based on the client number) to select the client record but would like to avoid having to re-enter the client number when the reports are to be printed.

Thanks for the help.

Ron