Hi !
Im developing a contact management software in accesss 2000.
I have been trying this for long but no success. I want to extract
records from a table that satisfy all the conditions specified by the
user. i have a form that provides interface to the user for specifying
his search conditions. (in the form of text boxes and combo boxes).
the user may select enter conditions in all text/combo boxes or he
might specify conditions in only a few of them. All the conditions
that the user specifies should be combined in a query and the
text/combo boxes that he leaves should not be combined in query.
how to display the resulting records in another form/report?

how can this be accomplished? using recordsets?
the whole project is complete except this .....

please help.

thanks in advance,
pragati

You would iterate through the various controls on your form, determine if
there is a value entered, and build a valid Where clause. For example, if
you have a control named ctlFirstName then you'd do something like this:

Function GetWhereClause as String

Dim strWhere As String

If len(ctlFirstName) > 0 Then
If Len(strWhere) = 0 Then
strWhere = " strFirstName='" & Me.ctlFirstName & "'"
Else
strWhere = " AND strFirstName='" & Me.ctlFirstName & "'"
End If
End If

End Function

You'll have to go through each control and check for a value there ... if
there is a value in the control then add it to your Where clause. After
iterating through all controls, then build you SQL and attach the Where
clause

strSQL = "SELECT * FROM YourTable " & strWhere

You'll use this strSQL string to open your recordset. Note that this is VERY
simplistic .... it doesn't take into account the need to exlude records, the
need to use OR in your Where clause, or the need to use LIKE in your results
(i.e. LIKE '*sco*') ... these would take a lot more code to setup.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Pragati" <pragatisagar (AT) yahoo (DOT) com> wrote

Thanks a ton!
I could get that through. My SQL query is ready now... just the way i
wanted.

but am stuck up in creating a recordset now.
how should one connect to database? use adodb or dao connection? are
there any particular setting reqd in vb editor?
what shud be the syntax for opening the above query in a recordset?

also ... can i pass a recordset variable to another form or report?
can a recordset created in one form be accessed in some other
form/report?

please help ....
thanks again
pragati

pragatisagar (AT) yahoo (DOT) com (Pragati) wrote in message news:<6d362a2f.0312270312.47776454 (AT) posting (DOT) google.com>...

In Access 2000, DAO would be the fastest way:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(YourSQL)

Note there may be some other paramaters you can set ...

You can pass a recordset variable to another form or report, but it's often
easier to set the RecordSource of the form or report to your valid SQL
string:

Forms!NameOfYourForm.Recordsource = YourSQL

Reports are different ... you can't set the recordsource once the report has
opened, but you can run code in the Activate event that "looks" at an open
form and pulls the .Recordsource from there.

"Pragati" <pragatisagar (AT) yahoo (DOT) com> wrote