How can I save and use in future reports?

[BennyWong]

Hello All,
I am currently using Access 2000. I have already created a "Query by Form" interface for the user to select from a series of unbound controls to build their query and output the report. I would like to save the user's selection and be able to recall the saved selection for the future. In other words, in the future the user selects from a listbox the desired saved report. How can I do this?
Thank you in advance for any help or suggestions.

 

[randysmid]

Hi,
This may be much simpler than what it seems. You will be dealing with two events on the form. The idea behind my strategy is to create a table with only record in it. It might be called tblReportCriteria, and will have fields matching the criteria you set up in the form. For a key, set a value of "1", or anything else you prefer. Actually, I wrote an FAQ on how to determine the user name. You can use this to create a criteria record for each user. Here is the FAQ for that: faq705-2814 COOL???

You will be dealing with two events on the form. The first is the Form Open event, which is where you will set the default values of the fields to match those from the table. Here is a sample bit of code that will do this:
Set db = CurrentDb.OpenRecordset("Select * from tblReportCriteria&quot
'if you have the user name, you can add the following
' to the "Select" statement above:
' where User= '" & txtUser & "'"
db.MoveFirst 'get the criteria record
' Presume the criteria fields are:
' lstState, lstCustomerType, cboMonth, cboYear
' ***************************
lstState.DefaultValue = db![State]
lstCustomerType.DefaultValue = db![CustomerType]
cboMonth.DefaultValue = db![Month]
cboYear.DefaultValue = db![Year]
db.Close

The second event to use is the clicked event for the command button that runs the report. It is here that you will want to save the criteria entered by that particular user. The code is very similar to the Form Open event.
Set db = CurrentDb.OpenRecordset("Select * from tblReportCriteria where User = '" & txtUser & "'&quot
db.MoveFirst
db![State] = lstState.Value
db![CustomerType] = lstCustomerType.Value
db![Month] = cboMonth.Value
db![Year] = cboYear.Value
db.Update
db.Close
HTH,
Randy Smith
California Teachers Association

 

[randysmid]

OOPS,
The db.MoveFirst in the clicked event should be this:
db.Edit HTH,
Randy Smith
California Teachers Association

 

[BennyWong]

Hello Randy,
Thank you very much for your response. I appreciate your time in supply me the idea of how to do it. I will try your suggestion. Just crossing my fingers that this works.