Search Table

[Danny729]

I have a form that has 10 different fields to list document numbers.


These fields are named DocNo1, DocNo2, etc.

If like to create another form in this database that has a text box and when I type a document number it provides a list of all the IDs of the records where the typed in document number is on one of the 10 fields describe in the first sentence of this post.

Is this possible, if so, how do I do this?

[Welshgasman]

Sounds like your data is not normalised?
If not, you will jump through hoops getting what should be simple.
I thought allen browne had a function to search through fields in a record, but that might have just been min and max.
http://allenbrowne.com/tips.html

[Welshgasman]

Duplicate post?
Reply was still in reply field?

[Bulzie]

Just for the exercise and if I am understanding your description, on the 2nd form with the search DocNo field "SearchDocNo" in form header, add all the other fields to the detail section of form, make the recordsource for that form a query based on the data table, list all the 10 fields and in criteria for each field put something like "= Forms![Form2]![SearchDocNo]" then for 2nd and subsequent fields in the OR row put same "= Forms![Form2]![SearchDocNo]" in the OR box and do that for all 10 fields (each new field you have to put it in the OR row below the previous one) so basically it is saying if whatever you typed into SearchDocNo is in any of the 10 fields, grab that record. You would need to put an event with Forms!Form2!Requery in the AfterUpdate event in that SearchDocNo field to do the action.

You could also do it in code say on a button to select those records based on the search field value and put that as the recordsource and then repaint or requery Form2.

Not the best way but maybe a way .