using 'stlinkcriteria' to open report?

[adsfx]

Hi,

I have a report that displays all results when opened.
IF I put [forms]![purchasersinv]![vendsearch]
in the 'ID' criteria then it will open the report based
on a combo in a form.
But I want the report to open all results by default and
only to show specific results if the combo on a form is used.

is it possible to open a report specifyng criteria for the reports query from the onclick event of button.
At present I have :

Dim stDocName As String
stDocName = "vendor invoice"
stLinkCriteria = "[reports]![purchasers invoice]![VendorID]=" & Me![vendsearch]
DoCmd.OpenReport stDocName, acPreview

this returns error, but am i on the right track?

 

[LaurieHamlin]

Maybe you just let it off of the code you posted, but you need to put the stLinkCriteria in the command line and I don't know about Access 2000, but at least in 97 it's acViewPreview.

DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria

Hope that helps.

 

[mc98015]

stLinkCriteria = "[VendorID]='" & Me![cmbpp] & "'"
' '...' : if cmbpp is a string
stDocName = "vendor invoice"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Reports![vendor invoice]![VendorID] = Me![cmbpp]

VendorID must be a filled coonected to the report you open, not a textbox name.

It works for me.
Good luck!

 

[hymn]

try the code below not to sure about No of comma's


Dim stDocName, Reply As String
stDocName = "vendor invoice"
stLinkCriteria = "[reports]![purchasers invoice]![VendorID]=" & Me![vendsearch]


Reply = MsgBox("Do you want a Single Report? ", 36, "Which Report&quot
If Reply = 6 Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName,stLinkCriteria ,, acPreview

End if


Hope this helps
Hymn

 

[GingerR]

or this--determines first if the combo box has anything in it. put this in OnClick event of a button on your form:

Dim stDocName, stLinkCriteria
stDocName = "vendor invoice"
if isnull(me.vendsearch) then
stLinkCriteria = ""
else
stLinkCriteria = "[reports]![purchasers invoice]![VendorID]=" & Me![vendsearch]
end if

DoCmd.OpenReport stDocName,acPreview, ,stLinkCriteria

 

[adsfx]

thanx to all who dived in!

have tried your ideas but still cant get the bugger to wurk.
am enclosing onclick event and sql for report- if yur bored (really bored) HELP!

********************************************
Private Sub vendsearch_Click()

Dim stDocName, stLinkCriteria
stDocName = "vendorinvoice"

stLinkCriteria = "[reports]![vendorinvoice]![VendorID]=" & Me![vendname]

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
End Sub
**********************************************

SELECT vendors.name, lots.description, lots.[price sold], lots.vendorID, lots.lotno, saledate.*, vendors.address1, vendors.address2, vendors.address3, vendors.town, vendors.county, vendors.postcode, vendors.commission, vendors.[carriage/additional], vendors.insurance
FROM vendors INNER JOIN (saledate INNER JOIN lots ON saledate.saleID = lots.saleID) ON vendors.VendorID = lots.vendorID
WHERE (((lots.[price sold])>0));

 

[GingerR]

stLinkCriteria = "[VendorID]=" & Me![vendsearch]

 

[LaurieHamlin]

What error do you get?