How can I search on a field and return all records that match 1

[Lhuffst]

I have a form and subform that are linked. The user selects the permit number on the main form and the form(s) display the related information.

Now the user wants to search on one of the subform fields using the find and goto the first record that meet that criteria. If that isn't the correct record, then they want to select next until they find the correct record.

They say they want this feature because there are so many parts that they cannot remember what permit number has what part. Since there are thousands of records, this seems reasonable but I am not sure of the best way to handle this. The person who coded this program used the recordclone method.

I have put the cursor on the field and selected the find (ctrl-f) and it always returns no records found. I know there are at least 5 swivels in the table. I did change the find box to match any part of the field.

I don't understand why that doesn't work and once it does work, how do I make sure that when they go to the next record, the subform and mainform records stay in sequence.

Any guidance would be greatly appreciated.
Thanks
Lhuffst

 

[Lhuffst]

Forgot to mention. I tried it on other fields and they seem to work. I am not even sure what I should be looking for to determine why it works in some places and not others.
thanks again. Lhuffst

 

[MajP]

I think you could probably make a better interface than search, find, go to next record. Could you explain a little clearer what the subform is and what the user needs to search for?

I have been demonstrating on this site a lot of class modules that turn a multi column listbox into a "find as you type" list box. It then returns the the records that are 'like" your search string. It is a pretty powerful way to search multiple fields and narrow your search.

However, if you want help on why your code does not work you will have to post the code.

Here is a demo of the multi list searc.

http://www.4shared.com/file/170681739/c0708f11/FAYT_Combo_and_List_10Dec.html

This requires the user to write two lines of code to get this functionality.

 

[Lhuffst]

MajP. I just downloaded your code. It looks great. I am going to try an use your method but in the interim, if a user puts their cursor in the textbox on a form and presses ctrl + f to get the access find box, fills in the info, how do I make it search the text field (seems to work on other fields). I dont' have any code to post because they are using the access find. Thanks again lhuffst

 

[MajP]

It should work fine. do you select?
Match: "any part of field

 

[Lhuffst]

yes. This works on other fields except this one. The only difference I can see is that the field is a memo field. Do you know if you have to do something special to use the search in a memo field? Thanks
lhuffst

 

[Lhuffst]

Majp
I tried your code but I get errors in 2 places.

Module - FindAsYouTypeListBox
the 1st 4 lines
Private withEvents mListbox as access.listbox
Private withEvents mForm as access.form
Private withEvents mSearchForm as access.form
Private withEvents mTextBox as access.Textbox

They show in red. I looked at your references and I had the same things so I'm not sure why they would be red.

Form_frmFAYTlistBoxDemo
Public fayLst as new FindAsYoutypeListBox says:
The expression On Open you entered as the event property setting produced the following error: A module is not a valid type

If I comment out the Public faylst as NewFindAsYoutypeListBox, then I don't get that particular error but then initalize doesn't work (to be expected). I'm not sure what I am missing to use your code. I thought that by defining them in the module that we could automatically reference them in code.
Thanks again
Lhuffst

 

[PHV]

FindAsYouTypeListBox should be a [!]Class[/!] module.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886