search form

[ppoole16]

Can anyone help me with (or point me to an example) creating a search page with unbound textboxs. What I need to be able to do is select a checkbox to include or not to include the textbox in the search.

For example: I have physical descriptors in my table such as "Race", "Age", ect. I need to be able to selcect which descriptors to search by and then be able to type in a textbox what to search for. I want the results to show a list of names of people who fit that description.

Thanks.
-Phil

Private Sub Clear_Click()
txtFilter = vbNullString
lstResults.RowSource = ""
End Sub

Private Sub cmdSearch_Click()
If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any Name to Search.", vbExclamation, "Title"
Exit Sub
End If
With lstResults
.RowSource = "SELECT [Booking Sheet Number], [Last], [First], [Race], FROM BookingSheet WHERE [Race]Like '*" & Me.txtFilter & "*';"
.RowSource = "SELECT [Booking Sheet Number], [Last], [First], [Sex], FROM BookingSheet WHERE [Sex]Like '*" & Me.txtFilter & "*';"
.Requery
End With
End Sub


Private Sub lstResults_AfterUpdate()
DoCmd.OpenForm "BookingSheet", RecordSource = "BookingSheet", acNormal, "[Booking Sheet Number] = " & lstResults.Column(0)
End Sub

 

[Capilano]

Hi Phil

Do you want each description field (eg. Hair color) to contain various choices (blonde, brown, red, etc)?

Thanks

Pat

 

[ppoole16]

Yes, for some of the fields I plan on using combo boxes. But for a field such as Height, I think a text box might be better. I've decided to scrap the checkboxes as I'm sure from what I've been reading, a NotNull might work (although I don't know how) This may be a bit over my head.

 

[Capilano]

Rather than unbound text boxes, my suggestion would be to have unbound combo boxes. Each row source for each combo box would simply be the a grouping of all of the choices in the underlying table. As for height, you text boxes would probably be a better idea.

Once you have all of your choices selected, a number of options present themselves. You could run a report with an underlying query that presents all of individuals with characteristics that meet your query parameters/criterion.

My personal preference would be a subform which would immediately display the individuals which met the criterion.

Cheers

Pat

 

[ppoole16]

code

I see what you're saying, but if one of the cirteria is left blank, all of the records are displayed. I've been working on some code (whith much help from this site) and I'm pretty close. It works if I enter the first name, but a prompt pops up. The prompt has the last name that I just typed. No idea? Case1 refers to Frist and Case2 should be Last. Both of the unbound text boxes are named Criteria1 and Criteria2 respectively and have First and Last in their respective ID property.

Option Compare Database
Option Explicit

Private Sub cmdClear_Click()
Dim i As Integer

For i = 1 To 4
Me("Criteria" & i) = vbNullString
Next i
Me.Filter = vbNullString
Me.FilterOn = False
End Sub

Private Sub cmdSearch_Click()
Dim i As Integer
Dim stSQL As String
Dim stWhere As String
Dim stFieldName As String
Dim stDelim As String

For i = 1 To 2
Select Case i
Case 1: stDelim = "'" 'text data type
Case 2: stDelim = vbNullString ' text data type

End Select
If Nz(Me("Criteria" & i), vbNullString) <> vbNullString Then
stWhere = stWhere & " AND " & Me("Criteria" & i).Tag & " = " & stDelim & Me("Criteria" & i) & stDelim
End If
Next i
If stWhere <> vbNullString Then
stWhere = Mid$(stWhere, 6)
Me.Filter = stWhere
Me.FilterOn = True
Else
MsgBox "Please enter some criteria.", vbExclamation, "No Criteria Entered"
End If

End Sub

 

[WayneRyan]

Phil,

If you base your form on a query, try this:

Make some number of "unbound" textboxes, or combo boxes.

Set their default values to "*", with the quotes.

In your query set the criteria for each search components
criteria to:

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

Then in the After update for each search component, put a
Me.Requery

The behaviour will be:

All records on form open.

An AND search on any criteria entered.

Wayne

 

[ppoole16]

A few questions.

First: will this work if my search form is separate from my main form and the main form isn't open?

Second: Please explain All records on form open and an An AND search on any criteria entered.

Third: Can this work with a search button rather than prompting?

Fourth: Is it possible to click on the results and bring up the corresponding full record?

As always, thanks.
-Phil

 

[WayneRyan]

Phil,

It will work if your search form is another form, the references
are different. But the form to be searched must be open.

When the form opens, the default values will make all
records qualify. As you enter text in one or more search
criteria, the search will narrow down.

It is not prompting, the user just enters search criteria if
desired. A command button could open your form.

The main form is your current record.

Wayne

 

[ppoole16]

As usual, I'm not getting it. What should the code behind the After update looklike. I've only got Me.Requery right now and I'm sure that's not right.

 

[WayneRyan]

Phil,

That is all that you need: Me.Requery.

The criteria in the query does all of the work. Each search
field has a criteria entry in the query, not for itself, it is the
criteria for some field on your main form.

By the way, is this another form, or part of the main form?

Wayne

 

[ppoole16]

it's another form. I have the main form open (although I'd rather not)

 

[WayneRyan]

Phil,

It doesn't need to be visible, just open. But isn't that the
one the user will be working on?

If I remember, your main form took up a lot of screen real
estate. A separate search form can be nice, but in between
uses, you should probably minimize it to save the criteria
instead of closing it.

btw, On your last topic, you said that there was a problem
if the person was in the db twice. Is that still open?

Wayne

 

[ppoole16]

Everything else is working great now. My plan was to have a search form accesable from the Switchboard for a search. When the search is completed I wanted the names of the possible matches to come up. Then the user could click on the name and the booking sheet for that person would display.

If you don't mind, I've reposted what I have at the same site as before. The search form I'm using is Physicaltest, based on a query of the same name. Although the finished product will searh physical descriptors, I've used First and Last as an "easy" example for me to learn with and use as a template.

I'm off to work now. Hopefully it will be a quiet night and I'll have time to come in off the road and try some more.

http:geocities.com/philpoole16

The post should be ready in about 10 min.
-Phil

ok, the post is up.

 

[WayneRyan]

Phil,

I downloaded and took a look at it. It's pretty nice. Are you
saying that you just have to hook it up to the switchboard?

Wayne

 

[ppoole16]

Does it work for you? When I type a name in it doesn't do anything on my main form. All it does is start a new record with the name.

Help.

-Phil

 

[WayneRyan]

Phil,

It worked when I tried it. I'll check again later.

Wayne