Super Snazzy Search Functions Needed

[Purpleswanage]

This Forum is a God Send and everyone is so helpful. Ghudson's Audit Trail code saved me many hours of hair pulling Anyone out there who may be able to help with my Audit Trail Query?

Anyway, I've been developing a database for the last 2 weeks (night & day it seems!) to track "bugs" found in the code we (not me) develop, suggestions & new requirements and just when I thought I'd got it cracked they came up with the next requirement they'd forgotten to mention!!

I need a super snazzy search function. The top half of the screen is a form with various combo boxes of which some or all of the values can be selected i.e reference no. author, between dates and a keywords field (a bit like the search page at the beginning of this forum!). On clicking the 'Select' button all the records that meet the input criteria appear in the bottom of the screen in a data sheet view (Ref No. Description & Date). The user can the Double Click on the record they wish to view and they will be taken to the full record in the DataEntry Form.

I'm sure this isn't as complicated as it seems to me at the moment but I am so stumped I don't even know when to start. I've been writing databases for years using the wizards within Access but only recently started to experiment with code. I would really appreciate any assistance forum members can give me but as I am new to these complications please provide 'idiot proof' answers so I can understand what I am doing.

Thanks in anticipation

Helen

 

[padlocked17]

All you need to do is create a query that contains all of the possible information you want to show.

At that point in the criteria portion of the query, you can set it to look for selections made on your form.

[Forms]![nameofyourform]![NameOfControl]

Then whenever the query is run, it will take the values from each control that was selected on the form and "filter" the query based on the selections.

That help any at all?

 

[Purpleswanage]

Thanks I think I see what you are saying and I will have a play

Can I be really thick? Could you give me a definition / example of 'Control'? I keep seeing it in code but I can't really work out what it means. Sorry! As I said I am really new to this.

Thanks Again

Helen

 

[padlocked17]

No worries. A control is anything that manipulates data (text box, listbox, combo box, check box, radio button, etc.)

Let me know if you need some more guidance.

I'm sure myself or someone here can help.

 

[pbaldy]

There's a sample db in this FAQ that does that type of thing:

http://www.mdbmakers.com/forums/showthread.php?t=4895

 

[Purpleswanage]

Sorry for the delay in replying but I pretty much lost the will to live yesterday!

OK I have my Query (in the query builder) with all the fields I could possibly want. In the 'Criteria' of the first field I have

[Forms]![FmAdvancedSearch]![ARNValue]

In the 'Or' of the second field I have

[Forms]![FmAdvancedSearch]![ProgramValue]

And so on, dropping a line for each 'Or' of the 8 fields I might want to search.

When I Run the query I get a parameter value box for each of the fields. So I leave the first one blank, type a value into the second and leave the other 6 blank. Hey Presto! I get the results I expect. However, if I type values into more than one of the parameter boxes I get a whole load of spurious data returned.

The main table I am using has links to various other tables which form the combo boxes in the main data entry form and I am trying to search on the 'text' values of these smaller tables rather than the unique ID. I have tried changing [ProgramValue] to [ProgramText] thinking this might be the issue but I still encounter the same problems.

It occured to me that at the moment I'm asking it to look for ARN Or Program Or Catagory etc. when I should perhaps be asking ARN And/Or Program And/Or Catagory. Am I getting warm?

If so, would I then be putting the search parameters in both the Criteria for each field and the Or for each line?

Again any help (and patience) much appreciated.

Helen

P.S. Pbaldy - Thanks for the link to the database, it does exactly what I want. I'm trying to understand the code which will take time and patience (unfortunately not a luxury I have at the moment!!) but I will keep working on it. On the plus side, i did manage to get my reset button to work

 

[padlocked17]

If you can attached your db up here and I'll see about taking a look at it.

 

[padlocked17]

It occured to me that at the moment I'm asking it to look for ARN Or Program Or Catagory etc. when I should perhaps be asking ARN And/Or Program And/Or Catagory. Am I getting warm?

Yes. You want each criteria to be on the same line. If you have combo box's I feel as if you may need to include some IIF statements that take into account the possibility of a null value. Same with a Radio button or check box.

 

[Purpleswanage]

Russ,

Thank you for coming back to me and offering to help. I thought I might have cracked it but alas I was worong.

At the moment even the 'sanitised' version exceeds the upload size so I can't up load at the moment. I'll keep trying.

Helen

 

[DCrake]

Simple Software Solutions

Hi

Her is an example screen that may fulfill all your wishes

It won't work correctly as a couple of tables have not been included.

Just rem out the line that get the data

Code Master:http://www.icraftlimited.co.uk

 

[Purpleswanage]

Russ, Paul, DCrake,

I just wanted to thank you all for your extremely useful posts. Having struggled with the Query Builder for many days I finally took 'the bull by the horns' and had a stab at understanding the code posted. D'you know what? It wasn't that scary and I now have a functioning advance search system

One last question (on this thread anyway):

I currently have a field called Keyword, this successfully searches the 'Description' field for a single word using the following code:

If Len(Me.SearchKeywords & vbNullString) Then

strSQLWhere = "WHERE [BriefDescriptionofRequirementProblem] Like '*' & " & Chr$(39) & Me.SearchKeywords & Chr$(39) & " &'*'"

strSQLWhere = strSQLWhere & strJoin

End If

The actual user requirement is to be able to enter a number of keywords (2, 3 , 4 ....) into the 'Keyword' field. Any ideas how I can amend / add to what I already have to achieve this?

As always thoughts and suggestions are most gratefully received.

Helen

 

[DCrake]

Simple Software Solutions

Hi Helen

I have attempted to create you final request and here goes with my solution

You will need 2 functions


Function BuildLikeCriteria(AnyString As String, StrFieldName As String) As String
'Here is a function that allows for multiple keywords
'It builds up a Like Or statement based on spaces in a string
'It treats spaces as delimiters between keywords
'Takes in 2 parameters Keywords and the name of the field being filtered


'If there is only one keyword the
'create the simple string and exit function

If InStr(AnyString, " ") = 0 Then
BuildLikeCriteria = StrFieldName & "Like '* " & AnyString & "*'"
Exit Function
End If

Dim strPos As Integer
Dim StrLike As String
Dim intStrLen As Integer

intStrLen = Len(Trim(AnyString))
'Loop until nothing left to check

Do Until intStrLen = 0

For strPos = 1 To Len(Trim(AnyString))
'Is there a string in the remaining string
If Mid(AnyString, strPos, 1) = " " Then
'Build the Like OR condition
StrLike = StrLike & " Like '*" & Mid(AnyString, 1, strPos) & "*' OR " & StrFieldName
Exit For
End If

Next
'Left Trim off the current keyword
AnyString = Mid(AnyString, strPos + 1)

'Is there any more spaces left in the search string
'If so, add the final like to the string and drop the last OR command
'Exit the main Do command Loop
If InStr(AnyString, " ") = 0 Then
StrLike = StrLike & " Like '*" & Mid(AnyString, 1, strPos) & "*'"
Exit Do
End If
If AnyString = "" Then Exit Do
intStrLen = Len(AnyString)

Loop
Debug.Print StrLike

BuildLikeCriteria = StrLike

End Function

Function TestKeywords(AnyKeywords As String, FldName As String) As String
Dim Rs As DAO.Recordset
Dim sSql As String
sSql = "Select * From tbdDischarges Where " & FldName & BuildLikeCriteria(AnyKeywords, FldName)

Set Rs = CurrentDb.OpenRecordset(sSql)

If Not Rs.EOF And Not Rs.BOF Then

'place code here

End If
Rs.Close
Set Rs = Nothing


End Function


Ran the TestKeywords function throught the immediate window with one of my sample tables. Simply change the table name and the field name and the search criteria to test yourself.

I have left the Debug.Print line in to let you see what is has built

Good Hunting

David
Code Master:http://www.icraftlimited.co.uk

 

[Purpleswanage]

WOW!

David, thank you so much this works like a dream.

Thank you to everyone who has helped me with this problem.

Helen