By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,213 Members | 1,507 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,213 IT Pros & Developers. It's quick & easy.

Access report by record

P: 56
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
May 4 '09 #1
Share this Question
Share on Google+
14 Replies


Expert 100+
P: 1,287
Use code like this that filters the report based on the record whose command button was clicked.
Expand|Select|Wrap|Line Numbers
  1. 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 "".
May 4 '09 #2

P: 56
The Code does nothing. Here is my Code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub WHEREUSED_Click()
  2.  
  3.     DoCmd.OpenReport "EXTRUWHEREUSED", acViewReport, , "PrimaryKey = """ & [RecordKey] & """"
  4.  
  5. End Sub
May 4 '09 #3

Expert 100+
P: 1,287
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,
Expand|Select|Wrap|Line Numbers
  1. "CustomerID = " & [ID]
See what I mean?
May 4 '09 #4

Expert 100+
P: 1,287
You can also look at the WhereCondition argument on the microsoft site:
OpenReport Macro Action
May 4 '09 #5

P: 56
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.
May 4 '09 #6

Expert 100+
P: 1,287
I think you have plenty of examples. What error are you getting?
May 4 '09 #7

P: 56
This is the code I am now trying:
Expand|Select|Wrap|Line Numbers
  1. Private Sub WHEREUSED_Click()
  2.  
  3.    On Error GoTo Err_WHEREUSED_Click
  4.  
  5.     Dim stDocName As String
  6.  
  7.     stDocName = "EXTRUWHEREUSED"
  8.     DoCmd.OpenReport stDocName, acPreview, , "EXTRU DOC = """ & Me.[EXTRU DOC].Value & ""
  9.  
  10. Exit_WHEREUSED_Click:
  11.     Exit Sub
  12.  
  13. Err_WHEREUSED_Click:
  14.     MsgBox Err.DESCRIPTION
  15.     Resume Exit_WHEREUSED_Click
  16.  
  17. End Sub
This is the error i am getting:

Syntax Error (missing operator) in query expression '(EXTRU DOC = "F12345)'.
May 4 '09 #8

Expert 100+
P: 1,287
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:
Expand|Select|Wrap|Line Numbers
  1. "[EXTRU DOC] = """ & [EXTRU DOC] & """"
Note that you should be able to omit the .Value (and the Me.) because that is the default.
May 4 '09 #9

P: 56
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.
May 4 '09 #10

P: 56
I mean't "I am now getting and error that reads."
May 4 '09 #11

Expert 100+
P: 1,287
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?
May 4 '09 #12

Denburt
Expert 100+
P: 1,356
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.
May 4 '09 #13

NeoPa
Expert Mod 15k+
P: 31,707
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?
May 5 '09 #14

missinglinq
Expert 2.5K+
P: 3,532
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)>
May 5 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.