Query by Form - Working - Get Results in Subform?

[andmunn]

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.

 

[andmunn]

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

 

[SOS]

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

 

[SOS]

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.

 

[andmunn]

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.

 

[Access Hero]

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.

 

[andmunn]

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

 

[Access Hero]

No problem. Enjoy!

 

[SOS]

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

 

[andmunn]

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.

 

[SOS]

Hi Sos,

It's unbelievable that you can whip that up in a few minutes -

I've done it a few times before

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

Glad to hear.

 

[andmunn]

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.

 

[Access Hero]

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.

 

[SOS]

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 [COLOR=red]" & Chr(34) & "*" &[/COLOR]  Me.WhatString [COLOR=red]& "*" & Chr(34) & " And "[/COLOR]
Else
      strFilter = "[memoIncidentDescription] Like [COLOR=red]"[/COLOR] [COLOR=red]& Chr(34) & "*" &[/COLOR]  Me.WhatString [COLOR=red]& "*" &[/COLOR] Chr(34) & " And "
   End If
End If

 

[andmunn]

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.

 

[SOS]

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

 

[andmunn]

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.

 

[andmunn]

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.

 

[SOS]

Pretty much this should work:

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

 

[SOS]

Yep, same thing. Glad you found it.