Hello All!

I have a form (temporarily named "QBF_Form") which runs the macro "QBF_Marcro" and returns results in "QBF_Query". A simply query by form. The search seems to be working fine in the form....

However, i would like the results of the search to be displayed in the same form (QBF_FORM) within the subform? How do i go about doing this?

I have attached my database - as i'm thoroughly stumped... Any help greatly appreciated.

Andrew.

Anyone have any "helpful" hints.. i know i'm missing something - and this can't be that diffucult :)
Andrew.

And why do you need it as a subform instead of on the same form with the search controls?

Ouch! And the query behind the subform is wicked. I think you should move to code to build your where clause for bringing back the records. Much better and less intense.

Hi SOS,

I'm not sure i need a subform (although this made sense in my head). I know i need this functionality:

A) Have a combination of 2 text boxes / 3 combo bodes for selecting criteria to search by.

B) Have the results displayed in a "table" in the same form... (i.// like a subform), and allow users to click through to the individual findings...

Would this be accomplished through a subform? I'm not sure what you mean by "search controls".

Andrew.

I just took a quick look. You don't need to do all this. You can use a simple form (with no subform) to accomplish this. In fact, you shouldn't have to write any code at all unless you want a little finer control. Check out Allen Browne's search demo: http://allenbrowne.com/ser-62.html

Simplifying this will make your life better and keep the poor guy who has to support this once you are done from pulling his hair out.

Wow - thanks so much - looks exactly like what i need.... Will give this a go.. THanks so much.
Andrew.

No problem. Enjoy!

Actually, I just did it all in the one you gave. See attached.

Hi Sos,

It's unbelievable that yo ucan whip that up in a few minutes - i've been trying to figure out a few things for an hr now and just realized you had posted this..

Thanks so much for your help - this was / is exactly what i was looking for..

Andrew.

Hi Sos,

It's unbelievable that you can whip that up in a few minutes -
I've done it a few times before :D


Thanks so much for your help - this was / is exactly what i was looking for..
Glad to hear. :)

One more final question ..i've added one more "search" criteria box, but i want to use the "like" (instead of equal to) operator.... It's not working as it should:

If Len(Me.WhatString & "") > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & "[memoIncidentDescription] Like ""*" & Chr(34) & Me.WhatString & "*"" & Chr(34) & " And ""
Else
strFilter = "[memoIncidentDescription]Like ""*" & Chr(34) & Me.WhatString & "*"" Chr(34) & " And """"
End If
End If

I'm obviously butchering some of this code ... lol.... Question, what are the "Chr(34) notations througout this code?

Andrew.

That is a quote mark.

You probably have a quote matching problem. Try doing a Debug.Print of the filter at each step as you build it until you find the problem.

Question, what are the "Chr(34) notations througout this code?

Those are double quotes (") for surrounding text. Sometimes you will see it as single quotes (for example "[FieldName]='" & Me.ComboBox & "'"

But if there is a single quote anywhere in the field, then it blows up. You could also use triple double quotes (""") but I find that a bit confusing to look at. I find Chr(34) easier to read.

As for your additional, if this is at the bottom before the If Right(strFilter...etc.

Then you would need:

If Len(Me.WhatString & "") > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & "[memoIncidentDescription] Like " & Chr(34) & "*" & Me.WhatString & "*" & Chr(34) & " And "
Else
strFilter = "[memoIncidentDescription] Like " & Chr(34) & "*" & Me.WhatString & "*" & Chr(34) & " And "
End If
End If

Thanks for your help again - worked like a charm!

One last question (and then, truly) I have completed the search functionality of my form.

I want to be able to click on the "incident" field (a unique number), and it will bring up the incident in another form i created. How do i code this (i tried using "onclick" in the Macro's, but it doesn't seem to work).

basically i need the form to open based apon what criteria was clicked (i.e.// what incident #).

Thanks again!
Andrew.

DoCmd.OpenForm "FormNameHere", , , "[txtIncidentNumber]=" & Chr(34) & Me.YourTextBoxNameHere & Chr(34)

Thanks!

Thank you so much for your suggestions - what's really great about these is i'm going to be applying them to several database i'm designing, and this "filter" search is easy to implement / works FABULOUSLY (like you said, my "query" code was...um...lets just say not so nice!).

One final question - on that search page - once i've filtered the results (clicked search) how would i generate a report (i.e.// preview report button) to just print those filtered results (before printed)..

I tried creating a macro to print-preview the report, but i can't seem to get it to recognize the filtered results.... Tried playing with the "Filter Name", but can't seem to get it to work...

Andrew.

Well,

Some searching found me this code:

Private Sub cmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "rptSummaryView", acViewPreview, , Me.Filter
End If
End Sub

Seems to work great!
Andrew.

Pretty much this should work:

DoCmd.OpenReport "CompleteAuditReport", acViewPreview, , Me.Filter

Yep, same thing. Glad you found it.

Hi SoS,

One more quick question - instead of having one date field, i have a "from date" and a "to date" field.... I Created the following code:


If Not IsNull(Me.WhatDate) Then
strFilter = strFilter & "([dteAudit] >= " & Format(Me.WhatDate, conJetDate) & ") AND "
End If


If Not IsNull(Me.WhatDateEnd) Then 'Less than the next day.
strFilter = strFilter & "([dteAudit] < " & Format(Me.WhatDateEnd + 1, conJetDate) & ") AND "
End If


I also added the following code to the beginning of the code:

Const conJetDate = "\#mm\/dd\/yyyy\#"

I must be missing something - as i get an error and the search doesn't work anymore...

Basically, i want to return results between whatever dates are chosen.... (from dteAudit).

What am i missing?

Thanks,
Andrew.

Anyone any idea whats going on thats wrong with the code above?
Thanks :)
Andrew.

What is the exact error? What line does it highlight if you hit the Debug button on the error message?

I get a popup which states " Run Time Error - 13" - Type Mismatch.

It then highlights:

strFilter = strFilter & "([dteAudit] < " & Format(Me.WhatDateEnd + 1, conJetDate) & ") AND "

Anyone have any advice?

I've attached the most recent version of the database. The problem comes with the "frmsearch" from / to date fields... Doesn't return the correct results..

The first part of the code seems to work (i.e.// it i enter date "2008/04/30" - it will give me everything from that date, to the end), however, if i enter an end date (i.e.// 2008/04/30 to 2009/04/30) the error seems to creep up.

Any advice how to fix this problem?

Andrew.

Change this:

If Not IsNull(Me.WhatDateEnd) Then 'Less than the next day.
strFilter = strFilter & "([dteAudit] < " & Format(Me.WhatDateEnd + 1, conJetDate) & ") AND "
End If

to this:


If Not IsNull(Me.WhatDateEnd) Then
strFilter = strFilter & "([dteAudit] <=" & Format(Me.WhatDateEnd, conJetDate) & ") AND "
End If

Thanks so much! Works like a charm (like always). My error makes sense as well...

FYI - is there any way to "sort" these results so that when the filter returns the results, they are sorted first by:

Process, than by Date?

Thanks.