Printing Report based on current record on a form 2

[thermalman]

I have a Form that uses a Query to show only the data where a PartNumber is part of a Van Kit,The PartNumber used,Description,Date Used,Bin Location,Which Engineer Used it, Quantity.

I need to be able to print a report using a command Button but I only want to print the current record. upon printing the report, a checkbox will have it's status changed to checked and the user can then move to the next record and start again.

The problem I am having at present is how do i get the form to pass the current record to the report.Each record has a unique ID called recID.

I have tried setting the record source for the report to the same as the form but this does not work.I have even set the record source as the Form's record ID but still that does not work.

Can anybody offer any suggestions as to how I might accomplish this?

Thermalman

 

[Remou]

You can use the Where argument:
[tt]DoCmd.OpenReport "rptReport", acViewPreview, , "recID=" & Me.recID[/tt]

 

[randy700]

How about....

DoCmd.OpenReport "YourReportName",,,[b]RecID = Forms!FormName!txtRecID[/b]

Randy

 

[thermalman]

Hi randy700,Remou,

I have tried both of your codes and get an error about my query as this is the record source for my report.
"procedure query has no parameters and arguments were supplied"

should i leave the query as the record source or could i achieve the same thing another way.

Thermalman

 

[randy700]

Post the code you have on your button as well as the query code please.


Randy

 

[thermalman]

Hi randy700

Here is the coding for my query:-

SELECT dbo.tblStockUsed.PartNumber, dbo.tblStockParts.Description, dbo.tblStockUsed.DateUsed, dbo.tblStockUsed.Quantity, dbo.tblStockUsed.FromLocation, 
dbo.tblStockParts.IsVanKit, dbo.tblStockParts.BinLocation, dbo.tblStockUsed.PartsPicked, dbo.tblStockUsed.recID

FROM dbo.tblStockParts INNER JOIN                       dbo.tblStockUsed ON dbo.tblStockParts.PartNumber = dbo.tblStockUsed.PartNumber

WHERE  (dbo.tblStockUsed.FromLocation LIKE 'F%') AND (dbo.tblStockParts.IsVanKit = 1) AND (dbo.tblStockUsed.recID = dbo.tblStockUsed.recID)

I have tried using the recID =@recID in the query but this does not work once you open the form

Here is the coding behind the Print Button on my Form

Private Sub cmdPrintPickList_Click()
On Error GoTo Err_cmdPrintPickList_Click

    DoCmd.OpenReport "rptVanStockReplenishments", , , recID = Forms!frmNewVanStockReplenishments!txtrecID

Exit_cmdPrintPickList_Click:
    Exit Sub

Err_cmdPrintPickList_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintPickList_Click
    
End Sub

 

[PHV]

Get rid of this in your WHERE clause:
AND (dbo.tblStockUsed.recID = dbo.tblStockUsed.recID)

And launch the report like this:
DoCmd.OpenReport "rptVanStockReplenishments", , , [tt][!]"[/!][/tt]recID = [tt][!]'" & [/!][/tt]Forms!frmNewVanStockReplenishments!txtrecID [tt][!]& "'"[/!][/tt]

If recID is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thermalman]

Hi PHV,
That is brilliant but it is printing the report with all of the records that meet the criteria.How can i get it to only print the current record on the Form.

Thermalman

 

[BSman]

You could make a version of the report that used a different query as its record source. The different query would include the criteria that the record # (or whatever uniquely identifies the record being displayed on the screen) equals the control on the screen that contains that value (even if the control is hidden).

Bob

 

[randy700]

If you're using the code PHV provided for your report button and are getting more than 1 record, you may have a problem with your table. The code assumes that RecID is a unique field -- that only one record contains that value. If that is not the case, you should replace the RecID with another (unique) field in your code.


Randy

 

[thermalman]

Hi Randy,
I am using data from 2 Tables, on the table tblStockParts the Primary Key is the PartNumber and the Primary key on the Table tblStockUsed is the recID.

I am also having a problem with my checkbox at present, Access keeps telling me that the Form is read-Only and I have not set a Unique Table (I Think)Everytime I try to set the Checkbox PartsPicked it looks as if it has done it but when I exit the form and go back into it the Checkbox is unticked again.

If I set the Control Source for the Checkbox as PartsPicked (bit) in the Table I then get the message above.

I can have more than one record with the same PartNumber that requires replenishing but each record should have a Unique RecID.

Help I am going stir crazy trying to get this to work. Can anybody offer any suggestions?

Thermalman

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886