2 Questions about search boxes on a form

[rhett7660]

Hi all..

I am using the following search function to search my database:

Private Sub cmdSearch2_Click()
Dim strLastName As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch2]) Or (Me![txtSearch2]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch2].SetFocus
Exit Sub
End If
'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
DoCmd.ShowAllRecords
DoCmd.GoToControl ("LastName")
DoCmd.FindRecord Me!txtSearch2
LastName.SetFocus
strLastName = LastName.Text
txtSearch2.SetFocus
strSearch = txtSearch2.Text
'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control
If strLastName = strSearch Then
'MsgBox "Match Found For: " & strSearch, , "Congratulations!"
LastName.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!"
txtSearch2.SetFocus
End If
End Sub
Which i got from here: http://www.databasedev.co.uk/text_search.html

Question #1

I have my own record counter setup at the bottom of the form via this:

Private Sub Form_Current()
'Inserts current record number and total number of records
Me.txtCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
Me.txtTotal = Me.RecordsetClone.RecordCount
End Sub

I would like to have this show how many searchs were found. I really don't
care about how many entries at this point... but if you do a search via
lastname for "johnson" I have 15 right now.. I would like to let the end user
know their are 15 enteries with the last name "johnson"... how can I do this
if it is possible with what I have setup..

Question #2
How do I get the search lastname field to go blank after I hit the
search button??

Thanks
R~

 

[alastair69]

can you post your db and i will have a look for you!

 

[buckwheat_311]

I'm not sure about your first question. However, I would use the event OnClick for your search button and set the text of the search field to " " or "". That would be my approach.
Hope it helps,
Buckwheat

 

[rhett7660]

I have attached the database ... The form in question is the form frmDataEntry


Thanks again
R~

 

[rhett7660]

Any luck???

R~

 

[Bodisathva]

OK...noticed there were actually two questions. This is NOT the complete function, but enough to point you in the right direction

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control
    If strLastName = strSearch Then
        'MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        LastName.SetFocus
        MsgBox DCount("LastName", "tblMain", "LastName = '" & txtSearch2 & "'")
        txtSearch2 = ""

I simply have the box pop up a message, but you can set that to a label caption.

 

[rhett7660]

Bodisathva

Thanks... I got it to clear the field.....Just off of what you gave me... Any chance you could take a look at question #1

Thanks
R~

 

[Bodisathva]

if you place a label to the right of the search button and name it lblNumMatches, then dimension a new integer called numMatches:

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control
    If strLastName = strSearch Then
        'MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        LastName.SetFocus
        [COLOR="Red"]numMatches = DCount("LastName", "tblMain", "LastName = '" & txtSearch2 & "'")
        lblNumMatches.Caption =  numMatches & " matches found."[/COLOR]
        txtSearch2 = ""

everytime you click the search button, everything will function as before, but the label will show you how many matches exist with the currently selected criteria.

 

[rhett7660]

thanks agian...

I am getting error with this piece of code thou:

lblNumMatches.Caption

I took out the ".Caption" and it works.....

R~

 

[Bodisathva]

Did you create a label or a text box ?

 

[rhett7660]

Text box... I figured that out too.......doh...... Switched it to a lbl and it now works with the caption....

One more question, with the way I have the search setup.. If they type in johnson they would get 15 entries.. is there a way to set it up so that they can scroll through the johnsons without having to look throu all 150 entries for the johnsons???


R~

 

[rhett7660]

Hi again....

I think I may be on to it..but I cant get it to work.. What I did is
created a query that would activate off of the "Search Button".... I can get
the button to open the query when I type in the last name and it shows me the
entries for the just that last name.. but how would I get it to populate the
fields in my form?

I used this to start off with:

Private Sub cmdSearch2_click()
DoCmd.openquery "qryAllRecords2", acViewNormal, acEdit
End Sub

The query is setup so that all the fields on the form are the same from the
tblMain and those fields are on the form...

R~

 

[RuralGuy]

One more question, with the way I have the search setup.. If they type in johnson they would get 15 entries.. is there a way to set it up so that they can scroll through the johnsons without having to look throu all 150 entries for the johnsons???

You are going to need to look into Filters or SQL to get this part to function as you desire. Here's a link to a search db that may give you some ideas. http://jfconsult.home.comcast.net/tips/

 

[Cire]

questions that i answered regarding search forms: http://www.access-programmers.co.uk/forums/showthread.php?t=107647

and if u want to display the number of records. Open up your sub-form which displays the results, create a textbox in its form footer and under controlsource put "=Count(*)" without "". lets call this textbox txtRecordCount for e.g

then in the main form where your textboxes are, put one more textbox at the top or bottom as u desire. in its controlsource put
=[YourSubFormName]![txtRecordCount]

 

[rhett7660]

Cire..

Do you have to use a subform? Is there anyway you can have it setup like what I have. I am attaching what I have so far... The form I am looking at is frmDataEntry5

R~