pass values from form to report HELP!

[ges]

Hi,
I'm a beginner access programmer, need help for my work..
I have 4 values in form that I need to pass to my report header and fill in
the report rowsource
Active Form Value :
1. cboAgent (unbound combobox in form)
2. FileType (option that user select from optiongroup in form)
3. lblInfo (unbound label caption in form)
4. BillCycle (unbound textbox in form)

I try to put the above values into my report header texboxes and from these
text boxes I try to create sql to feed my report rowsource.

I created command button in form when click I like to pull out the report
with value populated from the active form that currently open.

How do I open the report from this active open form and populated values from
this active form to my report?

Any help is greatly appreciated.

Thanks in advance for all answer!

Best regards,
Ges

 

[Allen Browne]

Provided the form remains open, you can set the Control Source of a text box
on the report to something like this:
=[Forms].[form1].[cboAgent]

If the combo's bound column is not the display column, you may need to use:
=[Forms].[form1].[cboAgent].Column(1)
Note that the first column is zero, so Column(1) is the 2nd column.

Referring to the option group will give you a numeric value. You could write
code to loop through the Controls in the option group to find the one with
an OptionValue that matches the value of the group, and then examine the
Caption of its attached label which would be Controls(0). If that's too
messy, you might prefer to use Select Case in your code.

To refer to a label, you need to read its Caption, e.g.:
=[Forms].[form1].[lblInfo].Caption

That's assuming that you want to display this stuff on the report. To
actually filter the report, it might be easier to use the WhereCondition of
OpenForm. Here's a couple of simple examples of doing that, the first with
just one record:
http://allenbrowne.com/casu-15.html
and the second with a date range:
http://allenbrowne.com/casu-08.html

For a more comprehensive example of building such a WhereCondition string,
download this sample database:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[ges via AccessMonster.com]

Thank you Allen,
I'll try the tips you posted. Thanks for your web link also, it has a lot of
great examples.
Ges

 

[ges via AccessMonster.com]

Hi Allen,

I tried the code you posted but I got an error. Below my data:

Form Report
cboAgent = txtAgent
lblInfo(caption) = lblTitle

Form Name : frmCollection
ReportName: RptCollection

In frmCollection, I create command button called “View Reportâ€

Below the code I create:

Private Sub cmdbtnViewReport_Click()
Dim strWhere As String

'strWhere = "[cboAgent] = """ & Me.[txtAgent] & """"
DoCmd.OpenReport RptCollection, acViewPreview, , strWhere

(I coma the strWhere because it give me error said “can not find field refer
to your expression)


On Report below the code I create:

Public Sub Report_Open(Cancel As Integer)
txtAgent = [Forms].[frmCollection].cboAgent.Column(0)
lblTitle = [Forms].[frmCollection].lblInfo.Caption

Error message:
“object doesn’t support this property or methodâ€

I try:
txtAgent = Forms("frmCollection")!cboAgent.Column(0)
lblTitle = Forms("frmCollection")!lblInfo.Caption

Error messge:
“can not find frmCollection in refer to macro expression or visual basic
codeâ€

Is there anything I miss?

Thanks!

Ges

 

[Allen Browne]

You don't need code in Report_Open.
Just set the ControlSource of a text box to:
=[Forms].[frmCollection].[cboAgent]

You can't assign value to a text box in Report_Open. If you don't want to
just set the ControlSource, you can use the Format event of the section that
contains this text box. Of, you can use Report_Open to assign the string to
the text box's ControlSource property:
Me.txtAgent.ControlSource = "=[Forms].[frmCollection].[cboAgent]"

To assign the label, set its Caption property:
Me.lblTitle.Caption = Forms!frmCollection!lblInfo.Caption

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I tried the code you posted but I got an error. Below my data:

Form Report
cboAgent = txtAgent
lblInfo(caption) = lblTitle

Form Name : frmCollection
ReportName: RptCollection

In frmCollection, I create command button called “View Reportâ€

Below the code I create:

Private Sub cmdbtnViewReport_Click()
Dim strWhere As String

'strWhere = "[cboAgent] = """ & Me.[txtAgent] & """"
DoCmd.OpenReport RptCollection, acViewPreview, , strWhere

(I coma the strWhere because it give me error said “can not find field
refer to your expression)


On Report below the code I create:

Public Sub Report_Open(Cancel As Integer)
txtAgent = [Forms].[frmCollection].cboAgent.Column(0)
lblTitle = [Forms].[frmCollection].lblInfo.Caption

Error message:
“object doesn’t support this property or methodâ€

I try:
txtAgent = Forms("frmCollection")!cboAgent.Column(0)
lblTitle = Forms("frmCollection")!lblInfo.Caption

Error messge:
“can not find frmCollection in refer to macro expression or visual basic
codeâ€

Is there anything I miss?

Thanks!

Ges

Hi,
I'm a beginner access programmer, need help for my work..
I have 4 values in form that I need to pass to my report header and fill
in the report rowsource
Active Form Value :
1. cboAgent (unbound combobox in form)
2. FileType (option that user select from optiongroup in form)
3. lblInfo (unbound label caption in form)
4. BillCycle (unbound textbox in form)

I try to put the above values into my report header texboxes and from
these text boxes I try to create sql to feed my report rowsource.

I created command button in form when click I like to pull out the report
with value populated from the active form that currently open.

How do I open the report from this active open form and populated values
from this active form to my report?

 

[ges via AccessMonster.com]

Thank you ....thank you Allen! I really appreciate your help and your quick
response.
Have a good day..

Ges

You don't need code in Report_Open.
Just set the ControlSource of a text box to:
=[Forms].[frmCollection].[cboAgent]

You can't assign value to a text box in Report_Open. If you don't want to
just set the ControlSource, you can use the Format event of the section that
contains this text box. Of, you can use Report_Open to assign the string to
the text box's ControlSource property:
Me.txtAgent.ControlSource = "=[Forms].[frmCollection].[cboAgent]"

To assign the label, set its Caption property:
Me.lblTitle.Caption = Forms!frmCollection!lblInfo.Caption

Hi Allen,

[quoted text clipped - 61 lines]