Printing a report based on the results of a search

[boblarson]

Looks like this line in BuildFilter is messed up:

varWhere = varWhere & "[Product] LIKE """ & Me.TextEnterProduct & "*"" AND "

It should be:

varWhere = varWhere & "[Product] LIKE " & Chr(34) & Me.TextEnterProduct & "*" & Chr(34) & " AND "

I use the Chr(34) because it is easier to spot your problems with quotes that way. You were missing at least one ampersand.

 

[Rockape]

Hi guys,,

Must leave now. I'll try and get back on Monday.....please be there!

 

[JANR]

Part of the OP's problem is that he uses the function BuildFilter to set a recordsource where he needs to set a where-clause form his function:

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "[COLOR="Red"]WHERE [/COLOR]" & varWhere

and as Spikepl stated in his first post on where condition on a Docmd.OpenReport....

the WhereCondition is a
"A string expression that's a valid SQL WHERE clause without the word WHERE."

He now has to stip away the first 6 characters from the BuildFilter string, and he just failed to get the correct syntax for the Right() function, one to many perens. So if he uses Bob's code then this sould work:

Private Sub cmdPrint_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "rep_SearchProduct"
strWhere = BuildFilter

If Len(strWhere) > 0 Then
	strWhere = Right(strWhere,len(strWhere)-6)
	DoCmd.OpenReport strDocName, acPreview, , strWhere
End If
End Sub

JR

 

[boblarson]

Boy, can't believe I missed that too. I need sleep...

 

[Rockape]

Hi,

Thanks all for your comments and patience. The code so far is the following including some of the amendments suggested. So far the Docmd. open report still does not function. I am hopeful that someone can assist me with this problem:

____________________________________________________________

Private Sub cmdPrint_Click()
Dim strDocName As String
Dim strWhere As String

strDocName = "rep_SearchProduct"
strWhere = BuildFilter

If Len(strWhere) > 0 Then
strWhere = Right(strWhere, Len(strWhere) - 6)
DoCmd.OpenReport strDocName, acPreview, , strWhere

End If

'Debug.Print BuildFilter
End Sub
________________________________________________________________________

Private Sub Command4_Click()
Me.frm_SearchProductSubform.Form.RecordSource = "Select * FROM qry_SearchProduct " & BuildFilter

Me.frm_SearchProductSubform.Requery

End Sub

____________________________________________________________________________

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter


' Check for LIKE Product
If Me.TextEnterProduct > "" Then
varWhere = varWhere & "[Product] LIKE """ & Me.TextEnterProduct & "*"" AND "
'varWhere = varWhere & "[Product] LIKE " & Chr(34) & Me.TextEnterProduct & "*" & Chr(34) & " And """
End If


' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere


' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere
Debug.Print BuildFilter
End Function
_____________________________________________________________________________

Private Sub Command5_Click()
Me.TextEnterProduct = Null
Me.Requery

End Sub

 

[JANR]

So far the Docmd. open report still does not function.

That doesn't help, what dosen't work??

If you apply the where directly to OpenReport statement does that work?

ex:

Private Sub cmdPrint_Click()
Dim strDocName As String
Dim strWhere As String

strDocName = "rep_SearchProduct"
DoCmd.OpenReport strDocName, acPreview,,"[Product] Like """ & Me.TextEnterProduct & "*"""
End Sub

Is the report called rep_SearchProduct? Does the field [Product] exist in the recordsource of the report?

JR

 

[Rockape]

Hi, thanks for being there.

What I meant was that the report wont open.

Yes the product field does exist and the report is called rep_SearchProduct

When I click on the search command button the filter works and the results do appear in the subform.

That doesn't help, what dosen't work??

If you apply the where directly to OpenReport statement does that work?

ex:

Private Sub cmdPrint_Click()
Dim strDocName As String
Dim strWhere As String
 
strDocName = "rep_SearchProduct"
DoCmd.OpenReport strDocName, acPreview,,"[Product] Like """ & Me.TextEnterProduct & "*"""
End Sub

Is the report called rep_SearchProduct? Does the field [Product] exist in the recordsource of the report?

JR

 

[JANR]

Can you post the db with the relevant form, report, and table with some dummy records?

JR

 

[Rockape]

Can you post the db with the relevant form, report, and table with some dummy records?

JR

Ok here goes!

 

[JANR]

Nothing wrong, but since your form is set to popup the report opens behind your form!

All is well.

JR

 

[Rockape]

Gosh..... you're right.....what a plonker I am....mind you much of the amendments that you guys have given me must have made it work...


Many Thanks

 

[phatus]

try this create a report based on your query the in your criteria in your query put this

Like [Forms]![searchform].[Name of combo box]& "*"

the create a combo box based on your query field... it works very well for me...
example..
you make a combobox based on Name field in your query

Like [Forms]![searchform].[name]& "*"

then make a print preview button:

Private Sub Printrpt_Click()
On Error GoTo Err_Command12_click
   DoCmd.OpenReport "name of report", acViewReport
Exit_Command12_Click:
   Exit Sub
Err_Command12_click:
   If Err <> 2501 Then
    MsgBox Err.Description
   Else
    Resume Exit_Command12_Click
   End If
End Sub

hope it helps...

 

[boblarson]

try this create a report based on your query the in your criteria in your query put this

Like [Forms]![searchform].[Name of combo box]& "*"

the create a combo box based on your query field... it works very well for me...

phatus:

Putting the criteria in the query totally limits you. Doing it the way we have been working with Rockape to do is WAY more flexible and is way more reusable. I highly suggest NOT putting the criteria in the query like you have suggested. I suggest making things as GENERIC as possible so you can reuse things in different ways. So you can have a GENERIC report and then call it in different ways and get the data you want without writing a bunch of queries and reports to save.

 

[Rockape]

Phatus,

I had tried that but like Bob says it was limiting my search and caused me another prob.

Anyhow guys, thanks a lot yet again.