Cool Search Toool problem

[edojanssen]

A while ago I found this Coos Search Tool db on the forum. I like the way it works very much. Trying to make it work for my own db fails. Please, can somebody help me out and make the damn thing work with my own table?

Edo

 

[rborob]

erm wouldnt details of your own use and tables be needed?

 

[edojanssen]

Cool Search Tool

Own table is included in the zip file.

 

[rborob]

apologies...ill take a look

 

[Luddite Lad]

First you will need to write a query similar to "Query1" the references your table. Then you will need to make a form similar to the "Search" form but based on your query you will need to examine and replicate the various events on the original "Search" form with reference to your own source data

 

[Luddite Lad]

I've been having a bit of a play with this form and have put an on change event on the text box which keeps the highlighted row of the list box at the top of the list, and also ensures that the data shown below the list box reflects the highlighted item.
it looks like this

Dim vSearchString As String

vSearchString = Text0.Text
Text5.Value = vSearchString
Me.List2.Requery
'Me.FRM_SrchAllDtlsSubform.Requery

Me.List2 = Me.List2.ItemData(1)
Me.List2.SetFocus

DoCmd.Requery
Me.RecordsetClone.findfirst "[WineID]=" & Me![List2]
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![List2] & "]"
End If

Me.Text0.SetFocus

It works fine however the problem is that when the focus is set back to Text0 the data already entered is selected. What is the code to set the focus at the end of the string without highligting it?

 

[boblarson]

Me.YourTextBoxName.SelStart = Len(Me.YourTextBoxName)

(My next post will hit the 1000 mark - FINALLY after almost 10 years on this board - I guess I haven't been too active at times)

 

[Keith Nichols]

Congrats Bob,

I hope there weren't too many of those posts spent taking me through Access 101

I appreciated everything you have posted that I have read (not just in reply to my questions!) and I'm sure you are appreciated by many others.

Kudos.

 

[Luddite Lad]

Thanks Bob, that's great. There is just one little side effect though, that I have not been able to overcome as yet, and that is that the Text box will no longer hold a space character. I'm guessing that has to do with the shift of focus from and to the text box. Is there a way around this?

 

[boblarson]

I'm not sure what would be going on. Could there be other code that is affecting the text box?

 

[Luddite Lad]

I've fixed the problem by testing if the last character is a space, and stopping the sub routine if it is.

As I suspected whenever a text box loses focus any trailing space character is dropped off the string.

I don't know why I didn't think of this solution until I went tot bed

 

[Luddite Lad]

Would it be possible to add some code that would highlight the area/s in which matches exsist? and what would it look like?

 

[rhett7660]

Hi.. I have been trying to get this one to work also. I have is setup, but the only problem I am having is this///

I have the query based off of three tables. tblMain, tblSpouse, tblVeh.. It works fine if I have an entry in tblMain AND tblSpouse.. If I don't have an entry in BOTH the entries to search will not come up..... Here is my sql for the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber, tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName, tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber, tblSpouse.EntryNumber, tblVeh.LicPlateNumber, tblVeh.Note, tblVeh.Current, *
FROM (tblMain INNER JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber) INNER JOIN tblVeh ON tblMain.EntryNumber = tblVeh.EntryNumber
WHERE ((([tblMain]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblVeh.LicPlateNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblVeh.Note) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblVeh.Current) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber;

the ORDER BY I had it setup with the following:

[tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And [tblVeh]![EntryNumber]

[tblMain]![EntryNumber] And [tblSpouse]![EntryNumber]

[tblMain]![EntryNumber]

Nothing seems to work.... I need it setup so that if there is an entry only in tblMain it shows it no matter what is in the other tables... But I want to be able to search all three tables.. does this make sense...?

Thanks
R~

 

[bigmon]

A while ago I found this Coos Search Tool db on the forum. I like the way it works very much. Trying to make it work for my own db fails. Please, can somebody help me out and make the damn thing work with my own table?

Edo

so edojanssen, had you solved your problem?

 

[Niniel]

Very nice.

I am trying to get this to work so that when I click on a record in the listbox [Search3 in my case], that record is loaded in another form.
I have a record ID field [ActivityID] that I want to use for this purpose.
I understand that the current setup is for the record to be displayed on the same form as the search box, but I'm not quite sure though how to send the ID of the record that was clicked to the other form.
"CIK" is one of the fields that can be searched; in the original version, it was called "Name".

Below is the slightly modified original code.

Private Sub Search3_AfterUpdate()

DoCmd.Requery
Me.RecordsetClone.findfirst "[CIK] = '" & Me![Search3] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![Search3] & "]"
End If

End Sub

Any assistance would be appreciated.
Thank you.

 

[Niniel]

Oh well, it doesn't matter. I found a different solution.

But thanks to whoever created the original Cool Search db. I found it very useful.

 

[stevo07]

Novice ALERT!!!!

A while ago I found this Coos Search Tool db on the forum. I like the way it works very much. Trying to make it work for my own db fails. Please, can somebody help me out and make the damn thing work with my own table?

Edo

hi guys

I have also had a look at this search function and it's just what i'm looking for. But being the fact that I am a complete retard when it comes to this sort of thing i have no idea where to start. Can someone have a look at my database and help me out.

Basically I work for a production company and in our archive we have a lot of footage tapes, pictures, music etc. Which all needs to be sorted into a database, which can be searched easily. For example, if I wanted to find a picture of a fountain in Greece, I would use a simple search function that shows every bit of footage and pictures which searches to this criteria. Have a look at the searchallform2 form and you'll see what I need to do.

cheers

stevo

 

[mike30x]

Is it possible to get a real small version of this DB query so i can try to understand and then copy it to my DB.

 

[kate10123]

I too am trying this search tool and at first I had it working (I was able to search one field for the desired result) as soon as I amended my query for other fields to be searchable it has stopped working.

Could someone possibly take a look at the attached version?

Much appreciated

 

[Luddite Lad]

kate10123 You've missed named the field in the criteria of your query;

You have

Like "*" & [Forms]![frmStudentSearch]![Search] & "*"

Whilst you should have

Like "*" & [Forms]![frmStudentSearch]![Search[B][SIZE="5"]2[/SIZE][/B]] & "*"

If you make that change it should work .