How to add records on report based on user choice from form

[rehanemis]

Hi,

I would like to get your suggestion regarding the requirements as:
1. A form will be shown with all Tests required.
2. User will click on check-box to include/exclude the test in report

So how can I show a form with all tests and how can I get dynamic report based on user choice? So if user clicked on certain tests and closed the form and reopen the form this form then the clicked tests will be again shown to him.

What is the right approach? any suggestion?

 

[theDBguy]

Hi. Not sure about "correct," but one way to accomplish that is to use a Yes/No field in the table to designate a "selected/clicked" test/record for the report. You would just base the report on a query with a criteria to filter out unchecked records.

 

[rehanemis]

But the issue is how can show all tests on form? just static text with check-box? and how to add the selected only tests to report?

 

[theDBguy]

But the issue is how can show all tests on form? just static text with check-box? and how to add the selected only tests to report?

Hi. Like I said, if you add a Yes/No field to the table, you can then display the table on a form with the Yes/No field that the user can check or not. To display the checked records on a report, you would create a query with a criteria like:


...WHERE Selected=True

 

[rehanemis]

HI,

Here is the snap shot of report I want. Actually not getting into your thoughts. So did I need to create a table with all tests with option of yes/no? All tests will be as fields or data in table?

 

[theDBguy]

HI,

Here is the snap shot of report I want. Actually not getting into your thoughts. So did I need to create a table with all tests with option of yes/no? All tests will be as fields or data in table?

Hi. Where are the tests stored now? Are they not in a table yet?

 

[rehanemis]

Hi,
These tests are not yet stored. It is in just report. So in excel what I do, the tests don't required I just hide the rows but don't know how to achieve it in access.

 

[theDBguy]

Hi,
These tests are not yet stored. It is in just report. So in excel what I do, the tests don't required I just hide the rows but don't know how to achieve it in access.

Okay, if you want to use Access, you will have to transfer the data into Access. If you want to keep the data in Excel, you can link to it in Access but still need a table to store the user selections.

 

[rehanemis]

All my data is in Access now, I want to prepare that report based on user selection.

 

[theDBguy]

All my data is in Access now, I want to prepare that report based on user selection.

Did you add a Yes/No field in the table? If so, create a query based on your table and add a criteria under the Yes/No field to equal True. Then, you can create a report based on that query.

 

[rehanemis]

Suppose I did that then how can I show the selected records on the report(records with yes values) with similar layout as showed you in snap shot.

 

[theDBguy]

Suppose I did that then how can I show the selected records on the report(records with yes values) with similar layout as showed you in snap shot.

Hi. Don't suppose, do it. I'd say give it a try and let us know how it goes. We're here to help, but we can't do everything for you. For starters, can you show us what the table you created looks like? It would give us an idea how you could create the report.

 

[rehanemis]

Sure,

let me work on it and will be back after what I have designed so far.

 

[Galaxiom]

A selection Boolean field in the table does have a potential limitation of being shared by all users. Everyone's settings become those of the last user.

Some developers get around this by creating a temporary table in the user's front end and copying the data into it. Note that all the data to be displayed does not need to be copied to the temp table. All that is required is a key field and the Boolean field for the checkbox. The rest of the data is joined for display on the selection form.

Of course the user's settings are lost if the front end is overwritten. An alternative is a table in the back end. Another field in this table stores the UserID so the same table can store the setting for all users.