Help - Search function.

[kacey8]

Hi Guys.

So I am writing a access DB for some of my staff to use (I haven't used Access in years)

It is pretty straight forward and has one table with less than 20 fields.

I have a data entry form set up and works nicely but I am trying to add a Search function in the header.

I have currently tried two methods.

One was with a textbox and a search button. It works but only displays the FIRST result but I require it to be able to find the next and the next and so on (there might be 20 plus results with the same name.

I am searching for the "Name" realistically I would like them to be able to search for the first couple of letters and matches to appear

code I used for this (Which I found online) and which worked is below

[SIZE=3][FONT=Calibri]Private Sub cmdSearch_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim bkmk As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strField As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.RecordsetClone.MoveFirst[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'Find the first record that matches what[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'is in the search text box.[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.RecordsetClone.FindFirst "Name Like " _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]& Chr(34) & Me.txtSearch & "*" & Chr(34)[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]If Me.RecordsetClone.NoMatch Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "No Match"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]bkmk = Me.RecordsetClone.Bookmark[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Recordset.Bookmark = bkmk[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]

The next one I tried is the following, it was just a text box with an auto update once you hit enter (which I like) but it seems to search by field name typed into the box not by the name (ie if I type a name in it gives an error saying it is an invalid "field name" rather than searching for that entry in the "name" field

[SIZE=3][FONT=Calibri][/FONT][/SIZE]

[FONT=Courier New]'Find record based on contents of txtSearch.[/FONT]
[FONT=Courier New]  Dim strSearch As String[/FONT]
[FONT=Courier New]  On Error GoTo errHandler[/FONT]
[FONT=Courier New]  'Delimited for text search.[/FONT]
[FONT=Courier New]  'strSearch = "Name = " & Chr(39) & Me!txtSearch.Value & Chr(39)[/FONT]
[FONT=Courier New]  'Delimited for numeric values.[/FONT]
[FONT=Courier New]  strSearch = "Name = " & Me!txtSearch.Value[/FONT]
[FONT=Courier New]  'Find the record.[/FONT]
[FONT=Courier New]  Me.RecordsetClone.FindFirst strSearch[/FONT]
[FONT=Courier New]  Me.Bookmark = Me.RecordsetClone.Bookmark[/FONT]
[FONT=Courier New]  Exit Sub[/FONT]
[FONT=Courier New]errHandler:[/FONT]
[FONT=Courier New]  MsgBox "Error No: " & Err.Number & "; Description: " & _[/FONT]
[FONT=Courier New]   Err.Description[/FONT]
[FONT=Courier New]End Sub[/FONT]

I am sure I am missing something simple but as I said I haven't used access in years (around Acess 2007) I was miffed to find they removed the user logon function from 2013

 

[kacey8]

Okay,

So I have used the Dynamically Search from this thread
Dynamically search multiple fields

(Sorry I can't post links)

Which works perfectly. but I want to be able to edit the results I get returned. How could I do this? I have a form set up as follows. but how do I, once I select a result get it to fill the fields bellow it (so it can be edited)

Screenshot of the form I have
ht tp://s25.pos timg.org/vsk2tqpsv/Untitled.png

I would very much appreciate some help here.

 

[olek_w1]

Hello kacey8,

As I understand you have some table with many records and a form set in form mode as single view.... and at the top of the form is a search text box which is not bound with any data in the table... so at the begining all the records in the table are available and you wish to filter all the records to match criteria given in the search field... am I right.

For instance you wish to find all the guys with name "John" - so I advise you to use filter properties of the form.....

In the event "after update" of the search text box just type below code:

if len(me.txtSearch.value) = 0 or isnull(me.txtSearch.value) = true then
  me.filter = ""
  me.filteron = false
else
  Me.Filter = "Name Like "'*" &  Me.txtSearch.value & "*'"
  ME.Filteron = true
end if

After that number records shown in form should be reduced to only these which match the search criteria....

(please note that I'm not super expert in access - so maybe there are some more porper and accurate ways to solve this problem - maybe some other more experienced member can give you better solution - but this one I'm using in my Access forms searching and filtering routines and works for me well)

best regards

 

[kacey8]

Thanks, I just came back as I worked it out myself (used a different method than above) and is probably a bigger work around. but works well thanks.

Just need to get reports working (never used reports before)