"really" advanced search form

[HansD]

Hey Everybody

I've got another question for you experts

I have a table with lots of addresses ( with: name, address, postalcode, but also a selection code like friend or family, now i want to create a advanced search form with a subform where the results must come.

For example:

* I want to simply search on place or postalcode and see everybody who lives there
* I want to find people who live in a postalcode area (for example: between 5000 and 5050)
* but also i want to search combinations of text, like a friend who lives in a certain postalcode area


I can create a form and search for everything apart but i don't know how to create a form and select different kinds of data

what i want is a subform with all the data when you open the form and then select different criteria to specify the people

I really hope that someone out there can help me on the way


Thanks

Hans

 

[WayneRyan]

Hans,

Make your form so that it is a traditional bound for and
also has your two "unbound" controls: srchPerson and
srchPostal. Make the default value for your controls
"*" (with the quotes).

Then base the form on a query, and obviously, doesn't
reference your two "srch" controls.

You should have a functional form that displays and
allows edits for ALL records.

Now, change the query so that in the Postal code field
you have criteria that says:

Like "*" & Forms![YourForm]![srchPostal] & "*"

Then do the same for srchPerson in its field.

If you run the form now, the query uses the fields as
its criteria, but they are still "*" and you should see
all records.

Now get the form in design view and use the AfterUpdate
event of each field to do a Me.Requery.

Now when you run the form, you initially get all, but when
you enter something in your search fields, it will requery
and trim down the size of the recordset.

This is an AND search, it is possible to do an OR search
by "staircasing" the criteria in the query.

Hth,
Wayne

 

[HansD]

Hey Wayne

Thanks for your fast reply
I am now at work, so I am going to try it tonight

I will let you know how it goes


Hans

 

[PeteJohnston]

If you only want to see them in a list you could have a multi-column list box (with as many columns as the query the data is based on), build the SQL SELECT statment on the fly and make it the RowSource of the listbox. If you then needed to change the data you would need another form which you opened with a filter. The OpenForm wizard will help you with the filter part.

Have a look at this thread for some ideas.

thread702-508951

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[HansD]

Thanks Pete

Your code in the other thread will help me on the way

I am going to "play" with it tonight

i will let you know how it went

Hans

 

[HansD]

Hey Pete

well i am putting your code in piece by piece, but i allready got a error
It is probably a type error but i can'see it
Can you please help me?


Private Sub Cmd_Zoeken_Click()

strFilter = ""
If Not IsNull(Src_Plaats) Then
strFilter = strFilter & " BezoekPlaats LIKE '*" & Src_Plaats & "*' AND"
End If

strSQL = "SELECT BezoekPlaats FROM Tbl_Adres"
If Len(strFilter) > 0 Then
strSQL = strSQL & " WHERE " & strFilter
End If

Lst_view.RowSource = strSQL
Lst_view.Requery

End Sub

 

[HansD]

damm To fast with the submit button

It doesn't really give a error but it doesn't display any results. The listbox is empty

 

[HansD]

YEAHHHHHHHHHH!!!!!!!!!!!!!!

It is working

and yes I know i must think first and then place a question about: it is not working


Thanks man, you really helped me

Hans

 

[PeteJohnston]

Hans

You only need the " AND" part if you have multiple text/combo boxes and you want to do a more complicated selection, but you still have to check for and remove the last AND.

Glad to know it helped.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)