Dynamicaly created where clause for OpenForm

[griffiji]

Ok, I have read this group until I think my eyes are ready to bleed, so
I would be most appreciative of some help.

I have a form, on it are 3 combo boxes, and 3 text boxes
I have 1 table, with about 20 columns
Each of the 3 combo boxes is set to be a field list from the table
All 6 boxes are unbound
I have an Ok button, and a Cancel button

I have a second form with only a listbox that is bound to the table and
is set to display the columns I like the best.

What I am trying to do, is have the user pull down one or more combo
boxes to display which column they would like to search, put their
search criteria into the text box, and click Ok.

The table is Oper_Log
The search form is SearchForm
The results form is DisplayResults

The Objective is that the DisplayResults form would show in it's list
box the results of the persons search.

Down the road I want to be able to double click on a single record in
the list box and have it display a report I have that is a 1 page
report intended for single records. But I'm happy enuf if someone can
help with just this part!!!!! =)

The following code has gone through numerous morphications before
reaching this point, so has some inconsistencies. It should however at
least illustrate for the most part what I am trying to do.

Private Sub ButtonOk_Click()
On Error GoTo Err_ButtonOk_Click

Dim stSF1 As String
Dim stSC1 As String
Const cQ = """"

stSF1 = "Forms![SearchForm]![SearchField1]"
stSC1 = "Forms![SearchForm]![SearchCriteria1]"
If Not (IsNull(Forms![SearchForm]![SearchField3]) Or
Forms![SearchForm]![SearchField3] = " ") Then
GoTo ButtonOkThree
If Not (IsNull(Forms![SearchForm]![SearchField2]) Or
Forms![SearchForm]![SearchField2] = " ") Then
GoTo ButtonOkTwo
If Not (IsNull(Forms![SearchForm]![SearchField3])
Or Forms![SearchForm]![SearchField3] = " ") Then
GoTo ButtonOkOne
End If
End If
End If

ButtonOkOne:

DoCmd.OpenForm "DisplayResults", , , , "stSF1 = stSC1",
acFormReadOnly
ButtonOkTwo:

DoCmd.OpenForm "DisplayResults", , , " &
Forms![SearchForm]![SearchField1] & " = " &
Forms![SearchForm]![SearchCriteria1] & " And " &
Forms![SearchForm]![SearchField2] & " = " &
Forms![SearchForm]![SearchCriteria2] & ", acFormReadOnly

ButtonOkThree:

DoCmd.OpenForm "DisplayResults", , , " &
Forms![SearchForm]![SearchField1] & " = " &
Forms![SearchForm]![SearchCriteria1] & " And " &
Forms![SearchForm]![SearchField2] & " = " &
Forms![SearchForm]![SearchCriteria2] & " And " &
Forms![SearchForm]![SearchField3] & " = " &
Forms![SearchForm]![SearchCriteria3] & ", acFormReadOnly

Exit_ButtonOk_Click:
Exit Sub

Err_ButtonOk_Click:
MsgBox Err.Description
Resume Exit_ButtonOk_Click

End Sub
I Thank you VERY much for any assistance you can give!!!

Jason

[Keith Wilby]

"griffiji" <gr******@aruplab.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Ok, I have read this group until I think my eyes are ready to bleed, so
I would be most appreciative of some help.

The question I have is, will "filter by form" not give you what you want for
free? I did once develop a bespoke search facility which I'm sure I could
dig up if you'd like me to send you a copy. It might not be exactly what
you want but it might point you in the right direction ...

Keith.
www.keithwilby.com

[griffiji]

Ok, don't I feel like an idjit =)

So then, I filter by form, and then how do I pass the filtered
recordset to the second form?

And can I make filter by form a part of my code for the button that
launches the second form?

[griffiji]

Bleh, that can't work, because the Fields of the form are unbound. I
was mistaken when I thought that filter by form had actually done
anything =)