Generating a Report from A Form - How?

[dataheadache]

Generating a Report from A Form

As you may have worked out by now (from my previous posts) I am still relatively new to Access, and would like some more help in simple terms (with directions as to where to find things) on the following:

I have built a database which contains a table of programmes I have recorded on TV onto my DVD Recorder. The Fields I use are:

Name = Name Of Programme Recorded
Type = Type Of Programme Recorded (TV, FILM, DV (Camcorder), Other)
Date = Date Programme Recorded
Channel = TV Channel Recorded
Length = Length Of Programme Recorded
Quality = Recorded Quality (XP, SP, LP, EP)
Protection = Whether the programme has been protected on the disc from deletion
Disc ID = This is either a Disc Number (01, 02 etc.) or HDD for the Hard Drive.
Disc Side = Which Side Of The Disc the programme was recorded (A, B or HDD)

I've inputted all the data for now, and I've built a report layout how I want it to show all the above apart from Channel Recorded field, and I have created a query which asks in Parameter Value Boxes for the Disc ID and Disc Side to report what is on a particular side of a disc (They ask for "Which Disc?" and "Which Side?").

Now, I want to make a form which asks for Disc ID and Disc Side at the same time (rather than using those horrible Paramater Value Boxes), click OK or Cancel (If I change my mind) and then auto-generate the report with all the fields above (excluding Channel) from the template I have designed to find out what is on a particular side of a particular disc

Please can somebody explain to this BEGINNER how to go about doing this in SIMPLE terms for somebody who has NO VBA programming knowledge at all.

 

[dataheadache]

Anybody got any ideas?

 

[neileg]

Build a form that has two controls, one that specifies the disk, and one the side. (I would base the Disk one on a list box that pulls its data from your table, but that's an option you could add later). Add a button that runs the report. Base your report on a query. In the criteria row of the query, enter the names of the controls from your form in the Disk and Side columns, e.g. Forms.MyForm.DiskID. So now when you enter Disk and Side in the form and click the button, the report calls the query and the query looks for its criteria in the form.

Add a second button that simply closes the form and this is your cancel option.

 

[dataheadache]

Build a form that has two controls, one that specifies the disk, and one the side. (I would base the Disk one on a list box that pulls its data from your table, but that's an option you could add later). Add a button that runs the report. Base your report on a query. In the criteria row of the query, enter the names of the controls from your form in the Disk and Side columns, e.g. Forms.MyForm.DiskID. So now when you enter Disk and Side in the form and click the button, the report calls the query and the query looks for its criteria in the form.

Add a second button that simply closes the form and this is your cancel option.

Thanks for your reply. I've done as you have suggested and created list boxes and a buton called "Preview" which I think will bring up the report if... I knew how to get rid of the parameter box asking for "Forms.Disc Enquiry.Disc ID" once I press the Preview button. Any ideas?

 

[neileg]

Access is asking for this as a parameter because it can't find the control referred to. Possible causes are
- Misspelling
- Wrong name
- Form not open when query runs
- Name confused

I think the last is the case. It is poor practice to have spaces or special characters in your object names. The space in the middle of your form name is a problem. Type the criterion in your query as [Forms].[Disc Enquiry].[DiscID]

Do a bit of research on naming conventions. Most of us use a style like
tblMyTable
frmMyForm
cboMyComboBox
etc

 

[dataheadache]

Thanks again for your reply.

I have renamed a few things after I posted my last message and before you answered - which got the form/query/report working - but no matter what I select from the form now, whether it be Disc 2 Side A, Disc 4 Side B, Disc 1 Side A or the HDD it always brings up the report with the results from Disc 3 Side A! I've checked the query, which collects the data from the form, and the report calls the query directly, and I can't see anything wrong, can you think why it is bringing up the contents of Disc 3 Side A no matter what I enter as the DiscID/Disc Side??

All help gratefully received.

 

[neileg]

For reasons that I've never figured out, Access sometimes hangs on to data as a record source, even when you think it should have 'forgotten' it. Excel does the same with pivot tables, by the way.

What may be happening here is that you have managed to get a valid set of data into the report and the result of your name changes is a failure to provide a new valid dataset. Thus you run the report and you get the same old data included.

I'm attaching a very simple model of how this should work. Open the form, select the title and run the report. It works. Does this help?

 

[dataheadache]

After looking at your example - I have managed to get it to bring back the data on a particular disc.

However, in the combo box that has been built, I have a list which looks like:
(formatted DiscID | Disc Side)

02 | A
02 | B
03 | A
03 | B
04 | A
04 | B
HDD|HDD

(I used the DISTINCT option after the SELECT - so it only called up discs that actually had something on them) - when I select a particular disc and side, say 04 | A, The report brings back data from both sides A and B (when I only asked for data from side A) - what do I need to edit to get it to do this?

EDIT: PLEASE IGNORE ABOVE, I HAVE NOW GOT THIS WORKING COMPLETELY AND TO THE WAY I WANT - Thanks for all your help and time.