Access report by record

[Desitech]

Hi, I am trying to build a simple report that will return the data from one idividual record of a certain table. I am new to this, So I am sure this is simple. I have a form that lists all records of my table. I have a command button on each record that I want to run a report that only returns the data from the individual record I am referencing. Any help would be greatly appreceiated. Thanks

[ChipR]

Use code like this that filters the report based on the record whose command button was clicked.

DoCmd.OpenReport "myReport", acViewReport, , "primaryKey = """ & [recordKey] & """"

In this case, the report is set up to show all records, but we are filtering it to only the one with primary key = key from the current record. The code assumes the key is a text field. A number would not require the extra "".

[Desitech]

The Code does nothing. Here is my Code:

Private Sub WHEREUSED_Click()

    DoCmd.OpenReport "EXTRUWHEREUSED", acViewReport, , "PrimaryKey = """ & [RecordKey] & """"

End Sub

[ChipR]

I assume by "does nothing" that you mean you don't see any records. Keep in mind that "does nothing" is not very descriptive of what's happening when trying to communicate over a forum like this.

You're going to have to replace "PrimaryKey " with the name of the field that you want to filter the report on, and "RecordKey" with the name of the field from the individual record. It depends on your data. For example,

"CustomerID = " & [ID]

See what I mean?

[ChipR]

You can also look at the WhereCondition argument on the microsoft site:
OpenReport Macro Action

[Desitech]

My Primary Key is "EXTRU DOC" . I want the RecordKey to be what ever the value of the "EXTRU DOC" is on the record I am on. I have a command button on each record of the form.

[ChipR]

I think you have plenty of examples. What error are you getting?

[Desitech]

This is the code I am now trying:

Private Sub WHEREUSED_Click()

   On Error GoTo Err_WHEREUSED_Click

    Dim stDocName As String

    stDocName = "EXTRUWHEREUSED"
    DoCmd.OpenReport stDocName, acPreview, , "EXTRU DOC = """ & Me.[EXTRU DOC].Value & ""

Exit_WHEREUSED_Click:
    Exit Sub

Err_WHEREUSED_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_WHEREUSED_Click

End Sub

This is the error i am getting:

Syntax Error (missing operator) in query expression '(EXTRU DOC = "F12345)'.

[ChipR]

Very close. We are trying to put a string together with the concatenation operator (&). It looks like the result we want is:

[EXTRU DOC] = "F12345"

So the code we need is:

"[EXTRU DOC] = """ & [EXTRU DOC] & """"

Note that you should be able to omit the .Value (and the Me.) because that is the default.

[Desitech]

Very, Very close. I am not getting an error that reads.

The database engine could not lock table 'EXTRUSIONS' because it is already used by another process

The form I have open when running this report is using the same table.

[Desitech]

I mean't "I am now getting and error that reads."

[ChipR]

I'm afraid I haven't seen this before, and I wouldn't expect opening the report to try to lock the table. Maybe someone else has experienced this, or could explain it?

[Denburt]

Is it based on the table itself or is it based on a query that is based on the table? If it is based on the table that could be your problem try creating a query that is based on the table and see if that resolves the issue.

[NeoPa]

I think this will only happen when the very record you want to display is in the process of being edited. That means you have made changes to it on the form.

How do you trigger opening the report? Is the control you use bound to any field in the record by any chance?

[missinglinq]

If you're entering a new record then using a button to run your report, as NeoPa said, you're in the process of editing the record. I think you'll have to explicitly save the record first. Just before the line to run the report, try placing this line:

If Me.Dirty Then Me.Dirty = True

Linq ;0)>