Help please Access Search forms

[Mary01]

I need to create a form that searches based on two
criteria.It's supposed to display a list of the found
records at the bottom of the form (and my supervisor is
insisting that he wants the results to appear on the
form.Please if anybody can help me with this, I have a
deadline to meet and so far I have only been able to
create a dynamic SQL.I appreciat any input.

 

[PHV]

Why not using a ListBox with the dynamic SQL as RowSource ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OnTheFly]

Not knowing what your form is bound to and such...the way I would do this is to get the values of the two criteria and change the forms Record Source to be the new querys SQL.

For example:

frmDisplay.RecordSource="SELECT * FROM Table WHERE Field1=Criteria1 AND Field2=Criteria2"

frmDisplay.Requery

Hope this helps.

OnTheFly

 

[Mary01]

Thank you so much both for your replies.I used both your advice and this is what I came up with.


Private Sub CmdSearch_Click_Click()
On Error Resume Next

Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String
Dim objConn


'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select * from Customers "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Controls used here are text boxes.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acTextBox
.SetFocus
'This is the function that actually builds
'the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
Else
sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 0
objConn.ConnectionTimeout = 0
objConn.Open MM_DB2_STRING

Me.Results = sSQL & sWhereClause
Me.Results.Requery ' Results is the name of my list box

End Sub


But I still don't get anything in my list box when I use a
custId that I know for sure exists in my table.In my list
box property window I have Table/query for Row Source Type
and I have sSQL & sWhereClause for Row Source and Control
Source is blank.


Thanks again for your help

 

[OnTheFly]

I believe you need to use Me.Results.RowSource=sSQL & sWhereClause to get the list box populated.



Hope this helps.

OnTheFly

 

[Mary01]

Hi everybody,

I found the problem and now my list box is showing results.
I did not have the list box RowSource property.

Me.Results.RowSource = sSQL & sWhereClause

Thanks