How to select one record for a query of report

[blpierce76]

Thread #1 Post #1:

Curretnly I have made a table to store data. I use a query to do some calculations on the data. I have a form that opens the query for me to reveiw the data. The form also opens up a report. However the query, form, and report currently bring up all records. I would like to be able to select one record to veiw and then report. Can you help?

Thanks

Thread #2 Post #1:

I currently have a database in access and i have a table for data, a query to calculate the data, a form for that query to display the results, and to send to a report. However when I open the query form it includes all records, as does the report. I would like to select one record for veiwing and reporting at a time. Can you help?

---

[Z: Moderator's comments:{blpie rce76 - I've merged your two threads and the comments. In the future, please do not double post your question(s), nor bump the thread(s).
Please keep in mind that moderators, experts, and others are un-paid volunteers that attempt to provide some help and guidance in-between our obligations to work and family. Please be patient – especially on holidays and busy posting days.
Both threads can be deleted by accident if one Moderator deletes one thread and and a different Moderator deletes the other... it has happend... and there's the 411}]

[zmbd]

Certainly we can help.
Yes it can be done.
Hopefully you have a primarykey, simply restrict the recordset so that the constraint is [primary key]=desired record's primary key
Of course, changing the name and value to match what you have. (^_^)

If you don't have a primary key then you'll need field data that is unique to the desired record.

If you will provide some more detail, such as the SQL for the query, which verson of Access, Operating sytem, etc... we can be of much more help.

Also, we'll need to see any other work you've attempted, this is a common homework question.

Please remember to format SQL-Script using the [CODE/] button in the toolbar.

[morganaj]

When you build your query, you have to either build in parameters (criteria section) to filter the query that feeds to the report.

Alternatively in the criteria sections you can enclose a string in square brackets, for example:

[Enter What you are hoping to filter]

This will bring a modal pop-up to have the user enter the criteria to filter on. I hope this makes sense. Let me know if you have any more issues.

[blpierce76]

Heres what I have on the query.
It pulls data from my table and calculates new data.
All of this data is then sent to a report.
I would like to be able to retrieve it by one record at a time based on "Lab ID" which is the primary key.

SELECT Data.[Lab ID #]
   , Data.[ADL Pan Weight]
   , Data.[ADL Pan & Sample Wt]
   , Data.[ADL Pan & Dry Sample Wt]
   , Data.[Residual Moisture]
   , Data.[Ash, Dry]
   , Data.[Volatile, Dry]
   , Data.[Sulfur, As Determined]
   , Data.[BTU, As Determined]
   , ([ADL Pan & Sample Wt]-[ADL Pan & Dry Sample Wt])/([ADL Pan & Sample Wt]-[ADL Pan Weight])*100 
      AS ADL
   , (1-[ADL]/100)*[Residual Moisture]+[ADL] AS Moisture, (1-[Moisture]/100)*[Ash, Dry] AS [Ash AR], (1-[Moisture]/100)*[Volatile, Dry] 
      AS [Volatile AR]
   , [BTU, As Determined]/(1-[Residual Moisture]/100) 
      AS [BTU Dry]
   , (1-[Moisture]/100)*[BTU Dry] 
      AS [BTU AR]
   , [BTU Dry]/(1-[Ash, Dry]/100) 
      AS [MAF BTU]
   , 100-([Ash, Dry]+[Volatile, Dry]) 
      AS [Fixed Carbon Dry]
   , 100-([Moisture]+[Ash AR]+[Volatile AR]) 
      AS [Fixed Carbon AR]
   , Data.Customer
   , Data.[Sample Identification]
   , Data.[Date Sampled]
   , Data.[Date Received]
   , Data.Description, Data.[Sample Wt]
   , [Sulfur, As Determined]/(1-([Residual Moisture]/100)) 
      AS [Sulfur, Dry]
   , [Sulfur, Dry]*(1-([Moisture]/100)) 
      AS [Sulfur, As Received]
   , Customers.CompanyName
   , Customers.BillingAddress
   , Customers.City
   , Customers.State
   , Customers.PostalCode
   , Customers.Department
FROM Customers 
   INNER JOIN Data 
      ON Customers.CustomerID 
         = Data.Customer;

[blpierce76]

Also its Access 2007 on windows XP

[pegicity]

You need to add a WHERE clause to your query, the sytanx would be as follows:

SELECT (fields you want to select)
FROM (source table)
INNER JOIN ON (the other table you are joining with)
WHERE (desired field) = (desired value for the field)
; 
 

[zmbd]

First of all, get rid of all of your hash-tag "#", commas ",", and all other non-alphanumeric characters from your field names... re-write what you have to; however, the sooner you do this the better!
Read why at these two links:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

The form also opens up a report.

So let's see the code that you are using to call the report.
Several ways to filter down the records in the report, from the form, the calling VBA, from the report as we might simply pass the form's "current record" primary key to the report's filter, etc...

>PLEASE< Remember to format your script using the [CODE/] format button. Selecting your VBA (or other scipt) and then clicking on the [CODE/] format button will place the required [code] [/code] tags around your script.

[zmbd]

pegicity & morganaj:
You have the correct idea; we may also use the form's current record to allow OP to select the record in the open form and pass that as a filter to the report; thus, no need to change the SQL which may prove advantagous if it is used in other aspects of the database.

[blpierce76]

OK here what I have for the report. Currently I have a form based on the query that allows me to search for the record I want based on lab ID# and dispays it on the form. Then I have a command button to bring it up on a report that I formatted. It works pretty much like I want it to except that it generates a report for each record.

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
 
Exit_Find_Record_Click:
    Exit Sub
 
Err_Find_Record_Click:
    MsgBox Err.Description
    Resume Exit_Find_Record_Click
 
End Sub
Private Sub Command45_Click()
On Error GoTo Err_Command45_Click
 
    Dim stDocName As String
 
    stDocName = "Prox Report"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_Command45_Click:
    Exit Sub
 
Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
 
End Sub
 

[blpierce76]

morganaj

I tried bracketing the Lab ID in the criteria for the query and it works well, except that I have to enter the lab # when I open the form and when I click to open the report. Is there any way around having to do that twice?

zmbd,

Thanks for the tip on getting rid of non alphanumerics. This is my first attempt with Access and have done everything up to this point by just reading and learning, so I appreciate the help.