Open Report filted by value in a control 1

[rodrunner79]

Hi guys...

I have a form that has an unbound textbox control (txtPONumber) and the form also has command button that opens a report when clicked. What I want to accomplished is, when the command button is clicked rather than opening a report that contains all records, I want the report to filter only records that is equal to the value of txtPONumber.

 

[PHV]

Have a look at the 4th argument of the DoCmd.OpenReport method.

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

 

[MajP]

Read the help file on the DoCmd.openForm method.

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

You need to put something in the wherecondition:

A valid SQL WHERE clause (without the word WHERE) or expression that Microsoft Access uses to select records from the form's underlying table or query. If you select a filter with the Filter Name argument, Microsoft Access applies this WHERE clause to the results of the filter.
To open a form and restrict its records to those specified by the value of a control on another form, use the following expression:

[fieldname] = Forms![formname]![controlname on other form]

The fieldname argument is the name of a field in the underlying table or query of the form you want to open. The controlname on other form argument is the name of the control on the other form that contains the value you want records in the first form to match.

probably something like

Docmd.openForm "frmFormName", , ,"[fieldname] = Forms![formname]![txtPONumber]"

 

[rodrunner79]

I'm sorry guys, I'm new to VBA so I don't get it, can you please explain to me in layman terms... I appreciate it. Thanks!

 

[rodrunner79]

It's not a form I'm trying to show the records in, it's a report...

A user is prompted a form to input a PO number and when he clicks the find PO, a report opens up and list the records for that PO that was entered in the form.

Also, how do I apply a filter in VB?

 

[MajP]

Something like this should be in the onClick event of the command button.
DoCmd.OpenReport "rptNameofYourReport",,,"[theFieldname] = Forms![youformName]![txtPONumber]"

If you used the wizard you probably have something like
stLinkCriteria =

you may be able to set
stLinkCriteria = "[yourFieldName] = " & me.txtPONumber

 

[MajP]

disregard what I put the first time.
DoCmd.OpenReport "rptNameofYourReport",,,"[theFieldname] = " & Forms![youformName]![txtPONumber]

That what I meant.

 

[rodrunner79]

Thanks MajP and PHV... Your help is greatly appreciated. I inserted this expression ([Forms]![frmPrice&QTYMatching]![txtPONumber]) on the record source of the report. And it works now. Thank you once again.