Report based on particular field

[Sorbz62]

Hi guys,



I am relatively new to Access and have written a database for students including which department they are, the courses they are doing and so on. I am happy with it til now. I have no idea about VBA or anyother code though!

Anyway, this is what I am after:

I want a report which, on double clicking a report, opens a combo-box asking for departments andthen lists all students in that department.

I don't know how to do this - please advise.

Ta,
Jim

[aytee111]

Put the combo box on a form and a button for opening the report, then use its value in the query record source for your report -
fieldname criteria: =Forms!FormName!BoxName

[Sorbz62]

Thanks mate,

So it's better to base a report on a query rather than a table?

How do I put a combo box on a form?

Sorry for the newbness!
Jim

[aytee111]

1 - No, neither better nor worse. A query gives you more flexibility - a) you can create it beforehand and troubleshoot it and get it working prior to worrying about what the report looks like and b) you can add data from a whole lot of tables all at one time. If your table structure is normalized then you wouldn't be keeping the student names, department names, etc, on every table, just the primary key field. Queries can join these tables up and get names, etc, when required.

2 - on your form, under "Design", click on the icon for Combo Box and answer the questions when the wizard comes up

3 - don't apologize

[Sorbz62]

Brilliant mate!

I'll try this!

Jim

[Aleksandra]

Could you please explain in detail, because I'm new with this too, and I don't understand how to connect Report and a Query?

[aytee111]

Your report is based on some information from somewhere - that is the "record source" which is a property of your report. That record source can be a table or a query - this is all the same as creating a form, same method. A report is easier as there is no user entry, just displaying of data.

1) create the form, put the required selections - the combo box of the departments:
- Create > Form
- Design > Combo Box - answer the questions

2) create a query
- Create - Query
- select the table(s) where the data is stored
- select the fields you want displayed on the record
- under the Department field in the Criteria line, put =Forms!FormName!CombobboxName

3) Create the report based on the query you created in (2)
- Create > Report Wizard - answer the questions

[Aleksandra]

Thanks for the quick answer! But that's not exatcly what I need. I know it's similar, but I can't figure it out.
I made a Switchboard, where I put a button called "Search by department".
When clicked on it, it gives a form with combo box where you choose the department, and a button called Generate Report.
So, I want a report ( with students only from that specific department ) to be generated with the click.
Can you help me with that?

[aytee111]

See (2) and (3) above

What is the name(s) of the table(s) where the data is stored?
What is the name of the form with the combo box?
What is the name of the actual combo box?

[Aleksandra]

I don't know how to do this : "- select the table(s) where the data is stored"
Name of the form with the combo box is SearchDep
Name of the combo box is Dep

[aytee111]

If you don't know what is required to create a query, then I suggest that you learn queries before you do anything else. They are crucial to extracting and manipulating data.

You can also use the query wizard which will ask you some questions, may make it simpler for you.

[Aleksandra]

I managed to make what I attended to, and I don't know what the problem was.
I did everything just like the las time. Nevermind, it works.
The next problem that I have is that all reports are opening in Microsoft Office One note.
It just started, it's been ok earlier.

[aytee111]

Reports will use your default printer, whatever that is set up to be.

[Aleksandra]

Ok. One more problem
I have a form with combo box and 5-6 text boxes,. When I select a number in the combo box, student's characteristics ( name,age, department, number, class and some personal data) are displayed in text boxes. How can i make their length flexible? I want their length of text box Name to vary just like the length of their names.

[aytee111]

If you mean the actual size of the text box, you cannot change how it looks. If you remove the border then the size won't show.