Technical MS Access VBA stuff

[Jack]

Shot in the dark but google isn't helping me lol

I have a tabular form in an Access database with a load of records displayed. I would like to have a text box at the top that you can type a string into, and it searches all the fields in all the records for that string. How the feck can I do it?!

The old database that was built by someone who's long gone has it, so I know its possible... except thats been compiled and I can't access the forms to see the code :(

Halp.

[Southie]

Is this the sort of thing or have I gone way off track? >LINK (http://bytes.com/topic/access/answers/198201-how-make-search-engine-form)<

[Jack]

Hmmm, that looks kinda promising - I shall have a read through


I call your type of form a log form and my log forms are "continuous"
forms, not datasheet. I'm basically displaying a list of records in a
table filtered and sorted the way I want. Above each column I put a
text field in the form header. When a person enters a value in that
field above that column, I clear out any other values in the other
search fields (I use the Tag property to determine which fields to
clear). I associate each column with a field. I then create a search
string from it and store it to a public variable. When the person
presses Enter or Tab, it searches for that field. If it finds it, it
moves to that record and sets focus. If the F3 key is pressed, it
searches for the next record. It's a pretty generic form.

^thats sort of what I need... but instead of just highlighting the matching field/record in the list, I'd like it to hide all the non-matching records. So if I typed in "cheese" and two of 100 records had "cheese" in one of their fields, it would hide the 98 non-matching ones and only display the two records that matched.

I want to avoid having boxes pop up or additional forms to apply filters etc (as the people who will be using it are noddys and have gotten used to the old database), if it can update the data dynamically from input in a text box that would be ideal

[Stuart]

put it on a web server and nail php/sql to it and sorted lol

[flyin flea]

Just how familiar are you with Access?

Do you know how to create a query as a record source?

[flyin flea]

OR.... if you're 'ardcwore there is a VB solution!

[Jack]

Yeah, I can do queries on the fly. Came up with a solution using a filter though..


Private Sub txtFilter_KeyUp(KeyCode As Integer, Shift As Integer)

Me.Filter = "fldAsset like """ & "*" & Me.txtFilter & "*" & """ or fldNotes like """ & "*" & Me.txtFilter & "*" & """"
Me.FilterOn = True
txtFilter.SetFocus
pfPositionCursor txtFilter, Len(txtFilter & "")

End Sub


...which works with this function (which I nabbed from google) in my module, to set the cursor at the end of the text box rather than highlighting it all on setfocus...


Public Function pfPositionCursor(ctl As Control, lngWhere As Long)

Select Case ctl.ControlType
Case AcControlType.acTextBox, AcControlType.acComboBox

ctl.SelStart = lngWhere
ctl.SelLength = 0

Case Else
'Do Nothing
End Select

End Function

Win :d and it filters the data as you type, double win.

[Stuart]

more vba stuff. how do I select a particular page and insert an image in word with VBa....

I've googled and got a load of bullsheets lol


scratch that, worked it out

[flyin flea]

A bit long winded.... two lines do the job....


Me.Form.Filter = "Instr(1 , [FieldInDBYoureLookingThrough] , Forms![TheFileNameOfThisForm].Controls!NameOfTextBoxObjectWhereYouType )"
Me.Form.FilterOn = True


Then you'll need a button to turn the filter off.... so you can see everythin again. Or add a text box which says 'Right Click > Remove filter'

[Jack]

It is only two lines, the other two are simply to keep focus and allow the text box to update the filter as characters are entered ;)

I've got a button already to switch the filter off :)

[Hayley]

ctrl, alt delete..

or have you tried turning it off and on again??


im out. :)

[Jack]

Bloody hell, you going for a job in our place?!

[Hayley]

yeah im gonna be your boss

[Jack]

Pipe down, Kev is my boss.