display one record on report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
 
Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps
 
Hi Wayne

thank you for the suggestion but I am getting errors on the following lines:

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
--
Thank you for your help


Wayne-I-M said:
Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
Click to expand...
Click to expand...
 
You have the same name for the report and the form
DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
Click to expand...
DoCmd.OpenReport "ProductDetailsNEW",
Click to expand...
ProductDetailsNEW = This should be the Name of the report
"[ProductDetails]![ECMA]=
Click to expand...
ProductDetails=This should be the Name of the query

[Forms]![ProductDetailsNEW]![ECMA]",
ProductDetailsNEW=This should be the Name of the form



--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
Hi Wayne

thank you for the suggestion but I am getting errors on the following lines:

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
--
Thank you for your help


Wayne-I-M said:
Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
Click to expand...
Click to expand...
Click to expand...
 
I have changed the name of the Report but I'm still getting the errors on the
two lines(below). The Report is running from a table (ProductDetails) and not
a query. However the subfrom is a part of a query. All I want to be able to
do is display the current Record shown on the Form. The ProductDetails Table
has the unique ECMA number but on the second table (Form Details) the ECMA
number is a foreign key so it is repeated therfore all records related to the
ECMA number are shown on the report hence multiple Subforms being shown. Hope
thats not too confusing!

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",


--
Thank you for your help


Wayne-I-M said:
You have the same name for the report and the form
DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
Click to expand...
DoCmd.OpenReport "ProductDetailsNEW",
Click to expand...
ProductDetailsNEW = This should be the Name of the report
"[ProductDetails]![ECMA]=
Click to expand...
ProductDetails=This should be the Name of the query

[Forms]![ProductDetailsNEW]![ECMA]",
ProductDetailsNEW=This should be the Name of the form



--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
Hi Wayne

thank you for the suggestion but I am getting errors on the following lines:

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
--
Thank you for your help


Wayne-I-M said:
Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
Click to expand...
Click to expand...
Click to expand...
 
Sorry I assumed that ECMA was the primary (ID or autonumber).

Ensure that the primary (unique ID field( from the table is shown on both
the form and the report - they can both be hidden fields if you want - set
Visible to = No.

Then change the code above by removing the ECMA and inserting the field name
of the ID field. Should work ok then.

Like this

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[IDField]=[Forms]![ProductDetailsNEW]![IDField]",


--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
I have changed the name of the Report but I'm still getting the errors on the
two lines(below). The Report is running from a table (ProductDetails) and not
a query. However the subfrom is a part of a query. All I want to be able to
do is display the current Record shown on the Form. The ProductDetails Table
has the unique ECMA number but on the second table (Form Details) the ECMA
number is a foreign key so it is repeated therfore all records related to the
ECMA number are shown on the report hence multiple Subforms being shown. Hope
thats not too confusing!

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",


--
Thank you for your help


Wayne-I-M said:
You have the same name for the report and the form
DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
Click to expand...
DoCmd.OpenReport "ProductDetailsNEW",
Click to expand...
ProductDetailsNEW = This should be the Name of the report
"[ProductDetails]![ECMA]=
Click to expand...
ProductDetails=This should be the Name of the query

[Forms]![ProductDetailsNEW]![ECMA]",
ProductDetailsNEW=This should be the Name of the form



--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
Hi Wayne

thank you for the suggestion but I am getting errors on the following lines:

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
--
Thank you for your help


:

Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
Click to expand...
Click to expand...
Click to expand...
 
These errors are still occuring for both these lines.

Compile error
Expected: line number or label or statement or end of statement
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",

And

Compile error
Expected: Expression
DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",

--
Thank you for your help


Wayne-I-M said:
Sorry I assumed that ECMA was the primary (ID or autonumber).

Ensure that the primary (unique ID field( from the table is shown on both
the form and the report - they can both be hidden fields if you want - set
Visible to = No.

Then change the code above by removing the ECMA and inserting the field name
of the ID field. Should work ok then.

Like this

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[IDField]=[Forms]![ProductDetailsNEW]![IDField]",


--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
I have changed the name of the Report but I'm still getting the errors on the
two lines(below). The Report is running from a table (ProductDetails) and not
a query. However the subfrom is a part of a query. All I want to be able to
do is display the current Record shown on the Form. The ProductDetails Table
has the unique ECMA number but on the second table (Form Details) the ECMA
number is a foreign key so it is repeated therfore all records related to the
ECMA number are shown on the report hence multiple Subforms being shown. Hope
thats not too confusing!

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",


--
Thank you for your help


Wayne-I-M said:
You have the same name for the report and the form

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",

DoCmd.OpenReport "ProductDetailsNEW",
ProductDetailsNEW = This should be the Name of the report

"[ProductDetails]![ECMA]=
ProductDetails=This should be the Name of the query

[Forms]![ProductDetailsNEW]![ECMA]",
ProductDetailsNEW=This should be the Name of the form



--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

Hi Wayne

thank you for the suggestion but I am getting errors on the following lines:

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
--
Thank you for your help


:

Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
Click to expand...
Click to expand...
Click to expand...
 
Compile error
Expected: Expression
DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
Click to expand...

Change it to this

DoCmd.OpenReport "ProductDetailsNEWRep", acViewPreview, "", "", acNormal




End Sub
Expected: line number or label or statement or end of statement
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
Click to expand...

Change this to

"[Reports]![ProductDetails]![ECMA]=[Forms]![ ProductDetailsNEW]![ECMA]",
acNormal


I thought we had decided that you were not going to use ECMA as it is not
unique to one record one. If you want just one record you need to refer to
the primary field instead of ECMA and ensure that this field is in both the
report and the form


--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
These errors are still occuring for both these lines.

Compile error
Expected: line number or label or statement or end of statement
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",

And

Compile error
Expected: Expression
DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
Click to expand...
Change it to this
DoCmd.OpenReport "ProductDetailsNEWRep", acViewPreview, "", "", acNormal
--
Thank you for your help


Wayne-I-M said:
Sorry I assumed that ECMA was the primary (ID or autonumber).

Ensure that the primary (unique ID field( from the table is shown on both
the form and the report - they can both be hidden fields if you want - set
Visible to = No.

Then change the code above by removing the ECMA and inserting the field name
of the ID field. Should work ok then.

Like this

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[IDField]=[Forms]![ProductDetailsNEW]![IDField]",


--
Wayne
Manchester, England.
Enjoy whatever it is you do


Kirt84 said:
I have changed the name of the Report but I'm still getting the errors on the
two lines(below). The Report is running from a table (ProductDetails) and not
a query. However the subfrom is a part of a query. All I want to be able to
do is display the current Record shown on the Form. The ProductDetails Table
has the unique ECMA number but on the second table (Form Details) the ECMA
number is a foreign key so it is repeated therfore all records related to the
ECMA number are shown on the report hence multiple Subforms being shown. Hope
thats not too confusing!

DoCmd.OpenReport "ProductDetailsNEWRep", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",


--
Thank you for your help


:

You have the same name for the report and the form

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",

DoCmd.OpenReport "ProductDetailsNEW",
ProductDetailsNEW = This should be the Name of the report

"[ProductDetails]![ECMA]=
ProductDetails=This should be the Name of the query

[Forms]![ProductDetailsNEW]![ECMA]",
ProductDetailsNEW=This should be the Name of the form



--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

Hi Wayne

thank you for the suggestion but I am getting errors on the following lines:

DoCmd.OpenReport "ProductDetailsNEW", acViewNormal, "",
"[ProductDetails]![ECMA]=[Forms]![ProductDetailsNEW]![ECMA]",
--
Thank you for your help


:

Hi Kirt

It as you say the subforms are printing it sounds to me as if you are
printing the form and not a report. The code below assumes that you have
created a report ??

Create a button on the form and put somehting like this behind the OnClick
event

Private Sub ButtonName_Click()
On Error GoTo ButtonName_Click_Err
DoCmd.OpenReport "ReportName", acViewNormal, "",
"[QueryThatRunsTheReport]![ECMAOnReport]=[Forms]![FormName]![ECMAOnForm]",
acNormal
ButtonName_Click_Exit:
Exit Sub
ButtonName_Click_Err:
MsgBox Error$
Resume ButtonName_Click_Exit
End Sub


Change the names in the code to what they really are

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a Form where I have done a Save As and created a Report. I have put in
the code (shown below) on a command button to enable the Report to display a
certain Record according to a unique number (ECMA). However, on the Form
there is a SubForm. The problem with this is that when I run the report it
prints all the Subforms which are related to the unique number on the Main
Form.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top