database searches

[raymond3090]

Hi,

I'm currently trying to build a form which will be used to search a database and return the search results. I would like the form to include text boxes for user inputs and a command button(s) to begin the search. In each text box, I wish to allow the user to narrow their search by typing in a keyword for each particular field I want searched in a table.

Any suggestions as to how you might go about doing this?
I am by no means efficient in VB, but I fear I'll need to use it to solve this problem!!

Thanks!!

Ray

 

[The_Doc_Man]

Unfortunately, it is a LITTLE ugly, but not TOO bad...

First, build an UNBOUND form as your parent. Put text boxes or other controls as required on this form.

Next, build a subform that does the actual display. You can bind it to your REAL table. No criteria, please. Don't use a query unless you have to.

Next, build a command button to display the subform.

In the code built by the wizard, there will be a DoCmd that opens the sub-form. Before this occurs, you must build a filtration string. Sounds ugly? Well, maybe a little...

This filtration string is what you would put in the .Filter property of the subform. (After which you can do a {sub-form-name}.Requery to get only the selected part.)

The filtration string is the SQL "WHERE" clause without the word "WHERE" included.

Suppose, for example, you had text boxes A, B, and C. Each of these contains a short text qualifier. Further, let's say that you allow for the text in the record to partially match the text box. And, to finish the requirement, we would say that if the box is empty, it doesn't contribute at all to the search.

At the top of the {query-button}_Click routine, include

Dim stQry as String
Dim boAnd as Boolean

Somewhere before the DoCmd that opens the subform....

boAnd = False
stQry = ""

If Len( Nz( [A], "" )) > 0 Then

stQry = "( [FieldA] like ""*" & Trim$( [A] ) & "*"" )"
boAnd = True

End If

If Len( Nz( , "" )) > 0 Then

If boAnd then stQry = stQry & " AND "
stQry = "( [FieldB] like ""*" & Trim$( ) & "*"" )"
boAnd = True

End If

If Len( Nz( [C], "" )) > 0 Then

If boAnd then stQry = stQry & " AND "
stQry = "( [FieldC] like ""*" & Trim$( [C] ) & "*"" )"

End If

Now let the DoCmd run so that the subform is open. Then,

{sub-form}.Filter = stQry
{sub-form}.Requery

Now you should see the results in the subform. You should probably enable the navigation buttons in the subform.

The purpose of stQry is to hold the developing WHERE clause (minus the WHERE).

The purpose of boAnd is remember whether you need to include an AND between the fragments of the developing query.

If one of the items is a number field, you wouldn't use 'LIKE' but rather would use a numeric relational operator.

This is tedious rather than anything else. Just be careful and keep your parentheses balanced in the developing string.