reports based on forms

[mstekkie]

i know a lot of other people have asked about this, but none of those threads work for me.

i have a form that displays a company's list of employee info. what i want is a button on the form that will open a report based on all the records that are shown in the current form.

i've tried this code:

DoCmd.OpenReport stDocName, acPreview, , "firmnumber=" & firmNumber

but when i click on the report button, it asks me to input the firm number (which is already displayed on the form) and then it shows all the employees; it doesn't show the current form's employees.

if anyone understands anything that i'm talking about, please help. this has been bugging me for too long.

thank you in advance.

cate

 

[LarryDeLaruelle]

cate:

Sounds like what you're asking for is a query by form. That is, use a value on your form to filter the report.

Is the report based on a query?

If so, you will need to reference the form and the field in the query (in the criteria cell for the field to be filtered.

The syntax looks like this:

Form!frmYourFormName.YourControlName

The easiest way to do this is to use the Expression Builder; right click in the criteria cell for the field and select builder. Then select the form and the control you want to use (when you find the proper control, double click on it to move it to the top display).

If you don't currently have a query as the report source, you'll need to create one to use this process.

Hope this helps. Feel free to ask if you have any questions. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[mstekkie]

the report is based on a query. what do you mean by control name?

the code that i used was:

Form!frmEmployeeInfo2.companyName

but i wasn't really too sure if companyName is the correct control to use.

i did what you suggested but i didn't do it right because it's not working.

cate

 

[LarryDeLaruelle]

cate:

Looks like you are on the right track. Control Name is the control on your form that sets the value for the criteria in the query (in your case, CompanyName). For example, I frequently use combo boxes to allow users to select the specific record they want to view. The combo box contains two columns: RecordID (as the bound column) and Record Name (description).

If the user want to run a report for the displayed record, I reference the combo box (as you did above) in the query that is the recordsource of the report. I do this by placing the reference in the criteria cell for RecordID.

If you are going to use CompanyName as the criteria, your reference to it in the query must be in the column where company name is.

Let me know if this helps clear it up or if you are still having problems.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org