Hi, I know this is probably quite a basic question, but I'm still very much learning Access..

OK so I am using MS Access 2010, on which I have created a database for a local church group. It's bascially listing all the people that are visited pastorally - with a page of contact details, then on a tab a table of all visits. Which person from the team visited, what the problem was, and the amount of time.

I have already made two reports, which are very simply every 'client' who requires a visit on a certain day, or those who are needing regular visits (these are tick box options on the main screen for each 'client' and were found using a query, from which I made a report.

Now, what I want to be able to do is create a form which a user can select various options before printing a report. So for example, I might want to select to print a report for each separate member of staff and that outlines who they saw that week, or maybe a report that prints ALL the visits within a certain date range.

Short of creating a query for EVERY possible question, I have no idea where to start with this. Once I get the basic idea, I am sure I can master it to suit, so for examplu if someone can tell me how to print a report of all visits within a certain date range selected on a form, and maybe all the visits by one member of staff, that would be AMAZING

many thanks

If your knowledge is limited at this stage I would suggest that you create a query for selecting an employee from a drop down on a form and that will give you an option to either select by a name or if needed the whole lot.

What you can look to do is as follows:

Create a new query including names, visit dates and follow up dates, and anything extra like client name (strange thing to call someone who part of the church!). Save the query and give it a name, something like qryCheckList.

Then create a form which isn't bound to any table or query, and then add a combo box and 2 text boxes.

Combo Box
When you select this item a wizard will start and you can then specify which table and which fields it should use.

Text Box
You would need to open the properties and set format to Date/Time and name each object

Combo Box - cboNames
Textbox1 - txtStartDate
Textbox2 - txtEndDate

Then keep the form open in design view and then go back to the query

In design view in the query select the criteria below the name option and then select the Build Wizard. In the wizard type in:

Like

Then use the left side to show all forms, and then select your form, then select double click on the cboNames and then type afterwards & "*"

Then click OK

In the date field you need to do something similar

In the criteria you click the Build Wizard again, then Type in

Between

Then select the form name again and double click the txtStart field and then type in And then double click the txtEnd field.

If you then create a report based on the query you can show either a name or show all during a reporting period.

Godbless and I am around if you need some more help.

Trevor, many thanks for such a concise answer

I have gone through all your steps (just for names at the moment), and have named everything as suggested.

I therefore now have a new form, query and report.

If I try and open the query, or report, I get an 'Enter Parameter' Box - which my logic says is correct; it's looking for one of my choices from the list. I am therefore guessing that I want to select a name from the drop down box, and then on the form click a button which will open the report with the chosen parameter... I don't know how to get a button to open the report with all the chosen parameters.. names and dates etc.

FYI, the choice of word 'client' was the clergy's choice... they are actually a hospital chaplaincy with no money etc, so I am helping out - the word patients would not cover the members of staff, so a generic 'Client' was chosen. You were quite right though!

Excellent so far well done.

The only other thing then is to add a button on the form to open the Report, so on the form, go into design view, then look for the command button again it should start a wizard, you can then select the report section on the left, then the next step would be selecting the report by its name and finally ensure you use print preview.

At this stage again save the form.

If you then change the view of the form you should be able to select from the drop down a persons name and then add the dates into the text box's then when you click the button it should give you the precise information you requested.

I hope this helps in the final step.

If not then please consider uploading a copy of the database perhaps with no real data in it.

Once again, very helpful answer... although...

So just to learn, I haven't worked with the dates function yet, this is just based on the 'name' of the person who visited the client. I set a button as instructed and this does indeed open the report. However if I don't select a name from the combo box it lists ALL visits - perfect, but if i DO select a name, it shows NO results, no matter which name I choose.

Also, how can I choose more than one name at once?

Thanks again

Andrew

Andrew you should check out the criteria of the query for the combo box it should look something like this:

Like[Form Name]![Combo Box Name]&"*"

Only list box will allow multiple selections.

That isnt exactly what is written, my criteria is....

Like [Forms]![ReportsMaster]![whocombo] & "*" where 'ReportsMaster' is the form, and 'Whocombo' is the combo box.

I've spent a few hours going through it, deleting all the files and trying again, with different names etc, but I still get the same problem.

so i thought I would briefly outline what I had done to see if anyone kind find where I go wrong...

1 - created a new query using query design - based on two tables 'Client Information' and 'Visits' linked by 'Client ID' key
2 - selected a number of fields from both tables including the 'ClientID' and 'name'
3 - saved query as 'whoquery'
4 - Create blank form, added a combo box called 'whonames', from the wizzard, i choose get values from another table or query, choose the table 'Visits', and from the next page just choose the 'Name' field, choose that as the sort field on the next page. choose hide key colum on the next page
5 - Go back to the query, and under the 'name' fields criteria, select build wizard, type 'Like', then select the 'whoform', and 'whonames', then type '&"*"', press OK
6 - close the query and form, create a report based on the query called 'whoreport'
7 - finally on the 'whoform' create a button, select 'Report Operations', 'Open Report' and select 'whoreport'
8 - now when I open the form, select a name form the combobox and press the control button I get a blank report

HELPPPPPPPPPPP - should I maybe not choose the table visits when creating the combobox - maybe I should select the whoquery?

the help above has been so clear, but I can't get it to work. Ideally I need to understand this as I have to create several different reports - I've not even tried the date thing yet!

Many thanks in advance

Andrew

Andrew,

Just out of interest how many people would possibly do the visiting, as you could potentially look at having multiple combo box to select.

Let me know and I will make up something for you to look at and adapt.

The Visitors are the members of staff and is currently about 10 names - on the visit form there is a lookup to a separate table of the staff names - this means that the user can edit staff names in the future should they need to

I have got your date function working easily, so maybe it's becuase of the look-up?