How to create a custom based search form?

[anb001]

I'm in the need of some code, which search for names in records depending on user entry in some text boxes.

I have a Query (qryChurchBooks) with following Fields:

- Name
- Maiden_name
- Date
- Parish
- Type_book
- Year_book

On a Form (frmSearch), I have some text boxes, where a user can write info, which should be searched for. The text boxes are:

- Search_name
- Search_maiden_name
- Search_type_book (the is a combo with flwg entries (All, Born, Marriage, Death)
- Year_from
- Year_to

The idea is that the [Search_name] and the [Search_type_book] text boxes are mandatory, before clicking the Search button..

The user should be able to use the common wildcard characters in the [Search_name] text box.

If [All] I chosen in the [Search_tpe_book] then all records should be included in the search. If e.g. [Born] is chosen, then only records with the value [Born] in the [Type_book field] should be included in the search. And so on with [Marriage] and [Death].

The text boxes [Search_maiden_name], [Year_from] and [Year_to] are used to limit the results of a search.

If you are searching for a person with the name e.g. 'Jane Doe', you might get 100's of results, however if you know that her maiden name was Smith, you can enter 'Jane Doe' in the [Search_name] text box and 'Smith' in the [Search_Maiden_name] text box. Then only results where both match, should be shown.

The same thing goes for the [Year_from] and [Year_to] text boxes. If you know that a person e.g. was born somewhere between 1890 and 1910, those years should be entered in the text boxes, hence limiting the results.

Finally the results should be shown in a Listbox on the same frmSearch as above, with the columns: Name, MaidenName, TypeBook, Date, Parish & YearBook.

I hope above is clear and understandable. Otherwise please ask, and I will try to elaborate.

I would highly appreciate any assistance as text (e.g. the code written in the reply), in stead of an attached file sample, as I can not open/use attached files, where I currently am (a very un-civilized place : ).

Thanks.

/Anders

 

[Kaybaj]

try and edit this you should get what you want
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub

 

[John Big Booty]

This search tool may be of assistance to you.

 

[anb001]

Kaybaj,

Thanks you, however it a little bit off what I am looking for. Appreciate if someone could assist me in getting all criterias included in the search, and result in a text box (if there are any, otherwise a msgbox).

Thanks.

/Anders

 

[anb001]

John Big Booty,

I have tried the link you suggested, and it works fine some of the way. Of course, the use of a button is not included, but that should be okay. I have solved the part of searching for the name, and searching for the type of church book.

The code used is (with query criteria in '()'):

Searching for the name (Like "*" & [forms]![frmSearch]![SrchName] & "*"):

Quote
Private Sub txtName_Change()
'Create a string (text) variable
Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = txtName.Text

'Pass the value contained in the string variable to the hidden text box SrchText, 'that is used as the sear4ch criteria for the Query QRY_SearchAll
SrchName.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.lstResults.Requery


'Tests for a trailing space and exits the sub routine at this point 'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor If Len(Me.SrchName) <> 0 And InStr(Len(SrchName), SrchName, " ", vbTextCompare) Then
Exit Sub
End If

'Set the focus on the first item in the list box
Me.lstResults = Me.lstResults.ItemData(1)
Me.lstResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might 'be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.txtName.SetFocus

If Not IsNull(Len(Me.txtName)) Then
Me.txtName.SelStart = Len(Me.txtName)
End If

End Sub
Unquote

Searching for the book (Like "*" & [forms]![frmSearch]![cboTypeKB] & "*"):

Quote
Private Sub cboTypeKB_AfterUpdate()
'Create a string (text) variable
Dim vSearchStringCbo As String

'Populate the string variable with the text entered in the Text Box SearchFor
vSearchStringCbo = cboTypeKB.Text

'Pass the value contained in the string variable to the hidden text box SrchText, 'that is used as the sear4ch criteria for the Query QRY_SearchAll
txtcboTypeKB.Value = vSearchStringCbo

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.lstResults.Requery


'Tests for a trailing space and exits the sub routine at this point 'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor If Len(Me.txtcboTypeKB) <> 0 And InStr(Len(txtcboTypeKB), txtcboTypeKB, " ", vbTextCompare) Then
Exit Sub
End If

'Set the focus on the first item in the list box
Me.lstResults = Me.lstResults.ItemData(1)
Me.lstResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might 'be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.txtName.SetFocus

If Not IsNull(Len(Me.txtName)) Then
Me.txtName.SelStart = Len(Me.txtName)
End If

End Sub
Unquote

I have a few things that I still would like assistance with, and that is:
1.
I have a field with the women's maiden name. As this is only included for dead women, all the other records are empty in this field. I have tried with similar criteria and code, but then only those with a record in the maiden name, shows in the listbox.

Example:
I know the person's name at birth was 'Jane Doe'. I know she was married, but not her new last name. If I then enter "Jane" in the txtName text box and "Doe" in the txtMaidenName text box, then only those records where Jane is included in the Name field and Doe in the Maiden name field, should be shown in the list.

Can some one assist with how I get around this?


2.
I would also like to be able to narrow the search down by entering a "from year" and/or a "to year", into some text boxes. If only the "from year" text box is used, then all records from and after that year should be listed. If only the "to year" is shown, then those from that year and before, should be shown. And finally if both "from year" and "to year" are used, then everything in between should be shown.

3. And finally I have an Option group, where it should be possible to select either all people, just the men or just the women. I have included a filed (column) in the table, where it is stated if a person is a man or a woman. But I can't get it to work.


I hope some can help with the remainder of my problems.

Thanks.

/Anders

 

[John Big Booty]

Hi Anders

This is a very quick response to question 1, before I go out for the day.

My suggestion would be to use the Family name as the default maiden name, in all cases and overwrite this when it is different to the Family name.

The other two questions are achievable, but I will have to write to those when I have a little more time.

 

[anb001]

Hi John,

--------------------------------------
John Big Booty wrote:
quote
This is a very quick response to question 1, before I go out for the day.

My suggestion would be to use the Family name as the default maiden name, in all cases and overwrite this when it is different to the Family name.

The other two questions are achievable, but I will have to write to those when I have a little more time.
unquote
--------------


I'm not sure this will work, due to the way my table is created. This is the way it looks (with regards to the 'name' issue):

"Number" "Name" "Maiden_name"
1 Jane Doe
2 Jane Doe
3 Jane Doe Smith
4 Jane Doe
5 Jane Doe
6 Jane Doe
7 Jane Doe
8 Jane Doe Smith
9 Jane Doe
etc.


As can be seen from above, there could be a long list of people named "Jane Doe". In stead of searching through all of the results, the idea is that one should write "Jane Doe" in the 'Name' text box, and 'Smith' in the 'Maiden_name' text box. This way the result in the listbox, would be those named 'Jane Doe' and has 'Smith as a maiden name.

Regarding item 3 above, then I forgot to mention an important thing. The combobox I use in order to choose the type of Church book to be searched, uses a table as row source. However, when the option group is used, I need to be able to change the row source as well. Meaning when 'All' is chosen, then all church books should be present in the combo box. If 'Men only' is chosen. then of course only Church books with men are shown, and so on. I guess the row source should be change suiong code, depending on option chosen.

Thanks.

/Anders

 

[John Big Booty]

In that case you could use a non-standard character as your default for maiden name, the hash symbol "#" for example and hide this using conditional formatting on your Maiden name field.

for question (3) check the query QRY_SearchAll and see how it handles the three option groups on the search form.

For your dates you would just need to add a couple of text boxes formatted as dates to the search form, the criteria for the date portion of the query would look something like;

=>[Forms]![FRM_SearchMulti]![StartDate] And =< [Forms]![FRM_SearchMulti]![EndDate]

You might want to expand that to take into account situations when the user had left those fields blank, which might look something like;

Iif (IsNull([Forms]![FRM_SearchMulti]![StartDate]),=<Date() And =< [Forms]![FRM_SearchMulti]![EndDate], Iif(IsNull([Forms]![FRM_SearchMulti]![EndDate]),  =>[Forms]![FRM_SearchMulti]![StartDate] And =>Date(),Iif(IsNull([Forms]![FRM_SearchMulti]![StartDate]) And IsNull([Forms]![FRM_SearchMulti]![EndDate]), < Date() And > Date(), =>[Forms]![FRM_SearchMulti]![StartDate] And =< [Forms]![FRM_SearchMulti]![EndDate])))

I've not tested that second criteria so there may be flaws in the logic, but it should set you on the track as to the sort of thing you will need to do.

 

[anb001]

John,

I have tried with the hash "#" symbol, but I can't seem to get it hidden when that field list with the maiden names are shown in a column in the list box. Is there a way where all empty fields can be ignored, using expressions? Meaning that it shows everything in the list box despite that no maiden name is updated in some records?

The date search issue seems rather complicated. I got the first part working, where you have to use a date in both text boxes. In the second example, where it empty text boxes are allowed, I have some problem. To me it looks like the expression is build just like nested IF functions, as can be made e.g. in Excel. But I have tried creeating it both as you suggested, but also from scratch. It ends up pretty much the same, however no dat appears in the list box. I actually thought of only having to use a year (no date and month) when entering search criteria in the forms text boxes, but Access doesn't seem to allow that.

And if I want the two date text boxes to behave just like the name text box, I assume I have to copy (more or less) the code, as mentioned in a previous post (= on change event). Correct?

/Anders

 

[John Big Booty]

In you query in the field for Maiden Name put;

Iif([MaidenName] = "#", "", [MaidenName])

The code I posted above to cope with blank Text boxes is a nested If statement, as I think I may have mentioned you may wish to check my logic on it, but that is pretty much the way it should look.

Yes each of the text boxes will need code to force a requery on the list box, it would be the simplified code similar to that behind the on click event on the Option Groups.

 

[John Big Booty]

If you want to avoid the complicated Iif statement you You could force a default in the from date text box as the earliest date in your system and the to date as Date(), you could also implement code in the two text boxes to ensure that your user has input valid dates.

 

[anb001]

John, Thanks for all your assistance. Regarding the problems with the Maiden Name, I couldn't get your solution to work. But then I changed the records to a 'zero-length' value in stead of a 'null' value, and then it worked. Regarding the year searching, then I'm getting closer. I'm taking a step at the time, and eventually I will probably get it right (I hope). /Anders

 

[bakkouz]

John Big Booty,
I've used the tool you posted in my form and it works excellent, But i'm a slight problem with an attachment field, I've adjusted my form based on the file provided, the form displays all records correctly but when i added attachments box and set its record source to : =SearchResults.column(15) it doesn't show anything in the box.
how do I make the attachments show up?
thank you.

 

[John Big Booty]

Are you wanting to show the attachment or the location of the attachment?

What is the nature of the attachment?

 

[bakkouz]

I want to show the attachment itself. the attachment can be a pdf or word file mostly.

its basically a personal library indexing program. in the data entering form i added an attachment box for the cover or a pdf version of the book.

now in the form that shows the inventory (whicd I based on your file) I want to show the attachment next to the book details.

 

[John Big Booty]

I think that what you will need to do is add and OLE control to your form and load the attachment into that. I say think as this is not an area I am overly familiar with, but it is where I would start look if I were in your position.

 

[bakkouz]

OLE control? I don't think I know what this means, I'm really new to access and just figuring out stuff as I go along
So there's no easier way to do this?

 

[John Big Booty]

Perhaps this article will give you some pointers.