Printing a report based on the results of a search

[Rockape]

Hi all.

Grateful for some guidance witha problem I'm having.

So far I have a parent form and a subform. Basically the parent form is a search form where I have an unbounded box where I enter the product I am looking for. I have a Search button on this form which has an event procedure that causes the requested record(s) to appear in the subform in datasheet view.

I am using the BuildFilter Function as part of the underlying search process.

I am now trying to be able to print a report based on the results of the search carried out.

Is this possible?

I would be willing to provide more details if need be, or further clarification is required.

Thanks

 

[Rockape]

hi all,

This is the code I have so far:-
-----------------------------------------------------------------------
'This is my preview report button code

Private Sub cmdPrint_Click()

Dim strDocName As String
Dim strWhere As String
strDocName = "rep_SearchProduct"
strWhere = Me.frm_SearchProductSubform.Form.RecordSource = "Select * FROM qry_SearchProduct " & BuildFilter
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

_______________________________________________________

'This is my serach button code

Private Sub Command4_Click()

Me.frm_SearchProductSubform.Form.RecordSource = "Select * FROM qry_SearchProduct " & BuildFilter


Me.frm_SearchProductSubform.Requery

End Sub

________________________________________________-

'This is code which I copied from an existing piece of
'code and I amended for my prposes.


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 "
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

End Function

 

[spikepl]

The syntax for OpenReport is here: http://msdn.microsoft.com/en-us/library/bb238032(v=office.12).aspx

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

strWhere = Me.frm_SearchProductSubform.Form.RecordSource = "Select * FROM qry_SearchProduct " & BuildFilter

You cannot make assignments in VBA like A=B=C, as above. Don't you have some error marked there?

When constructing composite strings, eg for SQL, first construct the string by itself, then output it to the immediate window and check what it contains. Use

debug.print mySqlString

to do that

 

[Rockape]

hi,

thanks for your reply.

My entire search process works fine. I'm trying to add a button which will preview the report with the data from the search form, which appears in a subform. The search results can be more than one record.
The code bhind the preview button does not work.

so far I have tried the following code: (but still no joy!)


Private Sub cmdPrint_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "rep_SearchProduct"
strWhere = ""
If Len(BuildFilter) > 0 Then
strWhere = Right(BuildFilter,len(BuildFilter)-6)) <---fails here
End If
DoCmd.OpenReport strDocName, acPreview, , strWhere
End Sub

 

[spikepl]

"fails" how ? what is the error message.
In your BuildFilte, insert

debug.print BuildFilter at the end, and inspect that

Whenever you post failing code, indicate where the code fails and what the message is.

 

[spikepl]

In general, whenever the code fails, inspect every little bit, and especially the content of the variables. Putting a whole bunch of things together and hoping that it works does not usually work.

Use the debugger to step through code to follow the logic, and to inspect the values of the variables. How? See section "To step through code" here: http://www.access-programmers.co.uk/forums/showthread.php?p=705111#post705111

 

[Rockape]

"fails" how ? what is the error message.
In your BuildFilte, insert

debug.print BuildFilter at the end, and inspect that

Whenever you post failing code, indicate where the code fails and what the message is.

Hi,

Ok....when I write the mentioned code it remains in red. i.e. an indication that the line is incorrectly structured. If I save the code, i.e the form and then run the print preview button the Class module appears and that same line is highlighted.

And I get Compile Error: Syntax Error

Now I've noticed that my brackets do not match but still I cant figure it where to put the brackets!!

 

[spikepl]

strWhere = Right(BuildFilter,len(BuildFilter)-6))

count left brackets and right brackets. The number should be the same for each. As to where to put them, put the cursor on the buil-in function you are calling and press F1 - that will take you to the help file for the given function.

 

[Rockape]

hi,

Did the following and it was accepted.

strWhere = Right(BuildFilter,len(BuildFilter - 6))

My apologies... when I write the above code I get a

Run time error "13"
Type mismatch

and the code fails on the same line again.

 

[spikepl]

so? did you inspect the contents of strWhere?

Try reading my post #6 again

 

[Rockape]

Hi,

do I write - debug.print BuildFilter

after End Sub
or just before it.

As a test I wrote it just before it and I got

Run time error "13"
Type mismatch

AmI doing it right??

 

[Rockape]

Hi

I have now tried the following line

strWhere = Right(BuildFilter,len(BuildFilter) - 6)

and it was accepted, however the preview report shows up all the records

 

[boblarson]

Just an FYI for you - Since BuildFilter is a procedure - try to use it only ONCE like this:

Private Sub cmdPrint_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "rep_SearchProduct"
strWhere = [B][COLOR=blue]BuildFilter[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B]
[COLOR=red][B]'If Len(BuildFilter) > 0 Then
'strWhere = Right(BuildFilter,len(BuildFilter)-6)) [/B][/COLOR][COLOR=red][B]<---fails here
'End If
[/B][/COLOR]
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

I don't get what the red part is for. From what I can tell, you don't need the part in red as it already has stripped off the " AND " part in the BuildFilter procedure.

 

[Rockape]

Hi Bob,

When I do you code amendments and run it ,
a message/input box appears requesting that I "Enter Parameter Value"

??

 

[spikepl]

#12 Just because code executes, it doesn't mean it does the right thing. Varaibels cvan be wrong, code logic can be wrong.

IN your BuildFilter FUNCTION, as the last statement before End Function, insert'

debug.print BuildFilter

and LOOK at what came out of this in the Immediate Window.

 

[boblarson]

Hi Bob,

When I do you code amendments and run it ,
a message/input box appears requesting that I "Enter Parameter Value"

??

That would be that the strWhere isn't returning the right thing - as spikepl has also indicated. You need to include a

Debug.Print strWhere

in the code just after the point which has

strWhere = BuildFilter

and then you can copy the results from the Immediate Window and then post them here so we can see what the resulting string is. That can help us troubleshoot what is wrong with the BuildFilter code, or implementation of it.

 

[spikepl]

OK - I leave the scene to Bob - he has much more patience than I

 

[Rockape]

Oooops I accidentally enclosed BuildFilter in quotations....

However I have now redressed that and I'm getting Run time error 3075

Syntax Error (missing operator) In query expression '(Where [Product] LIKE "*mysearch*)

note my search is what I'm looking for and the word mysearch

 

[Rockape]

Ok when I put the debu code in the BuildFilter function i get as above...

Run time error 3075

Syntax Error (missing operator) In query expression '(Where [Product] LIKE "*mysearch*)

 

[Rockape]

Oh./..and it stops at the line

DoCmd.OpenReport strDocName, acPreview, , strWhere

****************************

Please guys must leave now....dont forget me I'll try to get back as soon as I can