In Access 2003, run a report which uses parameters from a combo box on a form,how to set up script

[AllanW]

I am a novice and a dinasore working from home with Access 2003. I have a form with a combo box set up, and a querie which nearly does the right thing but so far it does not recognise the perameter form.

[orange]

Need more info. There are samples/tutorials at Martin Green's site.

[AllanW]

Thanks for your response I will try to see if the information on that site will fix my problem. Some more information: I have a database with many tables with one to many relationships, I have an existing form with a combo box which I use to find a subject on what I will call my primary table. I use this table to open the "primary " table at a desired subject for editing or accessing other tables to add information associated with this entry. It may ge possible to put a button on this table to call up the Report and use the information already selected on the table as the perameter query or go to the "primary' table opened at the correct entry and procede from there to open the Report. Otherwise have the report open and use a Parameters Form. In any of the senarios I would to know what script to use and where to apply it, To the button calling up the Report, in the open and or close events etc. and what alterationd would be necessary to the Querie. Thanks.

[June7]

More:
http://www.datapigtechnologies.com/f...tomfilter.html
http://www.datapigtechnologies.com/f...mtoreport.html
http://www.allenbrowne.com/ser-62.html

[AllanW]

Thanks, it will take me some time to get my head around it all and clear out redundant clutter but I am very confident,

[AllanW]

Sorry my problem is not yet solved
Access 2003 Data Base problem with Parameters.

I used information from this video to get to where I am so far: http://www.datapigtechnologies.com/f...mtoreport.html

In my data base, I have a form, called “Subjects Finder”, on which I have placed a Combo box which allows me to select the desired record by which to use as a Parameter for a Report. On this form I also have a Command button to open the report.
The script behind the button is as follows:

Private Sub REPORT_Click()
DoCmd.OpenReport "Tenterfield Family History Group Research", acViewPreview
End Sub

In the “condition” space under the ID (Primary Key), I have set the Parameter:

[Forms]![Subjects Finder].[Names]
(“Names” is the name of the Combo box).

It nearly works but when I select the required data item in the combo box and hit the report button I get a text box saying “enter parameter value” and indicates a template from one of the other tables which are on the many side of one to many relationships. If I press OK it goes to a second field and then a third and after “OK” again, opens the Report to the correct information, but so far only for one of the entries in that table, if I select other entries, the report opens up ok but without any data.

I cannot see where it is getting the parameters it is asking for, I haven’t set them and they are not apparent in the Query table so I can’t find them to delete them. My next problem would be to find out why the other choices are coming out blank.

A second button set up to do the same thing has similar results but only asks for two of the same parameters.

Sorry if I am using all the wrong names and terminology.

Thanks, Allan.

[June7]

I avoid dynamic parameters in query. I prefer to pass filter criteria to report when it opens, like:

DoCmd.OpenReport "Tenterfield Family History Group Research", acViewPreview, , "ID=" & Me.Names

I don't know why you get multiple popups. Would have to review report structure. If you want to provide db, follow instructions at bottom of my post.

[AllanW]

If I can figure out how to do it I should attach a copy of my db. Ultimately when the db is queried for information about a person I would wish to get all relevent details from associated tables, multiple records if apropriate or "skip" if no data (no empty forms). I still have a lot to do with at least one table not yet included in the reports. Since my last message I entered another lot of dummy data at S_292 and this behaves )for the report) the same as S_201. The rest come up with blanks. I would have expected that any entry in the Subjects Table could be called up, evin with nothing in linked tables.
Having said all that, aparently I can't attach a copy of my db. iTunes and Windows Picture Manager want to get in on the act.

Thanks anyway, Allan.

[orange]

Really need to see a copy of your database.

This info was copied from June7's signature

To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.

[June7]

I suspect the report RecordSource query has INNER JOIN which could be why data does not show. Really need to review db.

[AllanW]

I think it may be attached.

[AllanW]

Hi I think you are right! My Query in SQL View:
SELECT Subjects.S_LName, Subjects.S_FName, Subjects.S_AKA, Subjects.S_Sex, Subjects.S_AproxDOBirth, Subjects.S_DOBirth, Subjects.S_BCountry, Subjects.S_BRegion, Subjects.S_BTown, Subjects.S_BRecord, Subjects.S_AproxDODeath, Subjects.S_DODeath, Subjects.S_DCountry, Subjects.S_DRegion, Subjects.S_DTown, Subjects.S_DRecord, Subjects.S_Occupation, Subjects.[S_ Notes], Siblings.B_LastName, Siblings.B_FirstNames, Siblings.B_S_ID, Happenings.H_Source, Happenings.H_DateOfOrigional, Happenings.H_Record, Happenings.H_Notes, CensusElectoral.[C_CensusOr Roll?], CensusElectoral.C_Date, Subjects.S_FatherLName, CensusElectoral.C_Record, CensusElectoral.C_Notes, Marriage.M_LName, Marriage.M_FName, Marriage.M_Date, Marriage.M_City, Marriage.M_Locality, Marriage.M_Country, Marriage.M_MRecord, Marriage.M_Notes, Marriage.M_DivorceDate, Marriage.M_DivTown, Marriage.M_DivCountry, Marriage.M_DivNotes, Marriage.M_DivRecord, Progeny.P_LName, Progeny.P_FNames, Subjects.S_FatherFName, Subjects.S_MotherFirstName, Subjects.S_MotherLastName, Subjects.S_ID
FROM (((Subjects INNER JOIN Happenings ON Subjects.S_ID = Happenings.H_S_ID) INNER JOIN (Marriage INNER JOIN Progeny ON Marriage.M_ID = Progeny.P_M_ID) ON Subjects.S_ID = Marriage.M_S_ID) INNER JOIN CensusElectoral ON Subjects.S_ID = CensusElectoral.C_S_ID) INNER JOIN Siblings ON Subjects.S_ID = Siblings.B_S_ID;

[June7]

INNER JOIN requires related records in both tables for data to show. If a Subjects record does not have related records in even one of the other tables, it will not show in the query. Same goes for the JOIN between Marriage and Progeny. Change all to LEFT JOIN and more records will display.

Popups are because those fields are not in the query, yet there are textboxes with ControlSource referencing those fields.

[AllanW]

Thank you June 7 and thank you Orange,
With your help I have bumbled through, changed inner joins to left joins and found filters in the reports which I did not know were there. My next challenges will be arranging my report's layout, ensuring that all required information can be in the print out and to avoid wasting space and paper printing void spaces.
Thanks again, Allan.