display one record on report

[Guest]

I have a Form and a Report is linked to it. What I have done is a Save As on
the Form and saved it in Report Format. What I want to do is be able to run
the report and display the record that is shown on the form. Also I want to
show a label that uses the visible function on the report - shows when a
certain record is displayed.

 

[gibsonsgman]

I have a Form and a Report is linked to it. What I have done is a Save As on
the Form and saved it in Report Format. What I want to do is be able to run
the report and display the record that is shown on the form. Also I want to
show a label that uses the visible function on the report - shows when a
certain record is displayed.

your in luck, this is an easy thing to accomplish. as for your first
problem, if you are opening the report from a form use the following
code. (assuming the field you want to filter by is called 'ID')

Report_rptTemp.Filter = "[ID] = '" & me.ID & "'"
Report.filteron = true

just copy that code and paste it into your command button after the
code for opening the report. OF course you will have to replace the
text 'rptTemp' with whatever your report is named and you will also
have to replace 'ID' with whatever your field is called, but other than
that this should work.

now for your secod question, to display some label based on if it is a
specific record, in the reports 'Open' you will need and if statement

If me.ID = "some_value" then
SomeLabel.visible=true
end if

you will have to adapt this code to your specific control names
(SomeLabel will be the name of your label, "some_value" is the specific
value you want to display, and 'ID' is still the field) Let me know if
this is not what you were looking for.

~Brian

 

[Guest]

Thanks,
I have done this but when I click the button to open the Report a message
pops up "Object required" also it is still showing all the recoerds that are
in the Database not just the one shown on the Form.
--
Thank you for your help

I have a Form and a Report is linked to it. What I have done is a Save As on
the Form and saved it in Report Format. What I want to do is be able to run
the report and display the record that is shown on the form. Also I want to
show a label that uses the visible function on the report - shows when a
certain record is displayed.

your in luck, this is an easy thing to accomplish. as for your first
problem, if you are opening the report from a form use the following
code. (assuming the field you want to filter by is called 'ID')

Report_rptTemp.Filter = "[ID] = '" & me.ID & "'"
Report.filteron = true

just copy that code and paste it into your command button after the
code for opening the report. OF course you will have to replace the
text 'rptTemp' with whatever your report is named and you will also
have to replace 'ID' with whatever your field is called, but other than
that this should work.

now for your secod question, to display some label based on if it is a
specific record, in the reports 'Open' you will need and if statement

If me.ID = "some_value" then
SomeLabel.visible=true
end if

you will have to adapt this code to your specific control names
(SomeLabel will be the name of your label, "some_value" is the specific
value you want to display, and 'ID' is still the field) Let me know if
this is not what you were looking for.

~Brian

 

[gibsonsgman]

ok, forget about my first suggestion, here is what you can do. Try
using an SQL statement, i know this method will work because i
implemented it in a database i am working on.

Private Sub cmdPreviewCurrent_Click()
On Error GoTo Err_cmd_PreviewCurrent_Click

'variable declaration
Dim stDocName As String, queryname As String, MySql as string, cr
as string
Dim curr as string
Dim mydb As Database, tblFld As QueryDef
Set mydb = CurrentDb()

'deletes previous query under the name qry_temp
queryname = "qry_temp_report" 'base report off of this
query
DoCmd.DeleteObject acQuery, queryname

'sets tblFld equal to the queryname, will later be used to run
query
Set tblFld = mydb.CreateQueryDef(queryname)

'sets up the variable locations
'place holder
cr = Chr$(13)
curr = me.ID 'change 'ID' to your field that you want to
filter by

'SQL Statement
'change 'tblMyTable' to your table
MySql = "SELECT tblMyTable.* FROM tblMyTable WHERE
tblMyTable.[Equipment_ID] LIKE '" & curr & "*';" & cr

'runs the sql to make a custom report
tblFld.SQL = MySql

'opens up report
stDocName = "rptMyReport" 'rptMyReport is the name of your
report
DoCmd.OpenReport stDocName, acPreview

Exit_cmd_PreviewCurrent_Click:
Exit Sub

Err_cmd_PreviewCurrent_Click:
MsgBox Err.Description
Resume Exit_cmd_PreviewCurrent_Click
End Sub

Just make a command button and add that code. Then in the query
window, create a query and name it 'qry_temp_report' and save it. You
don't have to make any changes to the query, just create one with that
name, the SQL statement in the code will do what you need it to. then
base your report off of that query and change this code to match yoru
variables.