Form/Report/Query aka how to change criteria value (1 Viewer)

[Gunnerp245]

I have built a database with only my department in mind that tracks three types of documents; Which works fine. Now some of my fellow department heads desire to use what I have built.

This is not a problem as their data structure is the same. The difficulty lies in changing the 30 queries. In the queries I hard coded my department number in the receiver (department field) criteria.

Additionally, my HQ is requesting me to perform some analysis on the other department's data. So other than manually changing the department number each time; Can I use a form or report to modify these 30 queries? I attempted to use a combo box but it would not hold the value when I closed the form.

I saw this thread today, http://www.access-programmers.co.uk/forums/showthread.php?t=102036 , but am not sure it will do what I want.

Suggestions welcomed.
Gunner...

 

[statsman]

What you can do is change the query criteria to:

[Enter Department Number:]

This will prompt you to enter the department number when you run the query and will only return values for that department.

If this database is going to be shared on a server with all the data for all the departments in the one database, this is probably your best bet.

If the database will only be on a single computer with each department having its own database, then change the queries for each departments computer with their dept. no.

 

[Gunnerp245]

What you can do is change the query criteria to:
[Enter Department Number:]

Yeah, I thought of doing what you wrote but this means entering the department number too many times. The actual department data for the five department is not planned to be on a server, more of a stand-alone. The database is more than just tracking documents as it allows quite a bit of drill down into specific information that the documents represent.

What I would like to do is have the individual department enter their department number in a form and have that number saved to a table; DEP. I know that is against the 'rules'. The queries would then reference the field in the saved table, DEPNO. So the criteria would be similiar to =Tables.DEP.DEPNO, I believe. But I am unsure how to store the data to the DEP table.
Gunner...

 

[Smart]

If you have a form that tha the user can enter the dept no on and leave the form open (or minimise the form ) (Isuggest a combo box that contains all the dept no's)
Then in your query(s) in the criteria for the deptno place the following
forms![Yourformname]![yourcomboboxname]

As the form is open the queries will take the deptno from it

 

[Gunnerp245]

If you have a form that the user can enter the dept no on and leave the form open (or minimise the form ) (Isuggest a combo box that contains all the dept no's). Then in your query(s) in the criteria for the deptno place the following forms![Yourformname]![yourcomboboxname]
As the form is open the queries will take the deptno from it

Smart,
I was working just that scenario last night and it worked as long as the form stayed open. If the form was closed, when the query ran it would ask for the parameter of forms![Yourformname]![yourcomboboxname].

I also found out that my crosstab queries would not work with forms![Yourformname]![yourcomboboxname] even if the form stayed open.

Can the value from the form be saved? Or fetched using SQL and the actual department number ie. 12345 be inserted into the query criteria?

Gunner...

 

[JoeCruse]

Try

[Forms]![yourformname]![fieldname]

putting it in the criteria section for the department field you are querying off of, AND in the Parameters property area (right-click in the design view of the query and choose "Parameter" and then type it in EXACTLY as you have in the criteria for the department field, and using the correct data type).

This should work. I use this all the time for crosstab queries and reports based on them. I have a few that use the same little form for holding/entering the criteria.

 

[Gunnerp245]

Try [Forms]![yourformname]![fieldname]
I use this all the time for crosstab queries and reports based on them./QUOTE]
My crosstabs now work.
Thanks,
Gunner...

 

[JoeCruse]

Glad it worked and glad to help. Thanks for posting back that you got your problem worked out.

 

[DJBummy]

Just wanted to let gunnerp245 that I have been trying to resolve this same issue for a week and this solved my problem. Many thanks..