Search/Edit form creation

[Hagridore]

I'm trying to create a form that allows a search for and edit of specific records based on the entries made by a user. I know about filters, and can use them myself. However, I'm trying to create this form with the goal that my Access-inexperienced users won't have to use any Access controls...I want them to be able to type in part of the entry in a field they want to use to search for their records, hit enter or click a button, and have the record(s) they're searching for show up. How do I set up the fields in a form to make this happen?

[June7]

Options:

1. http://www.datapigtechnologies.com/f...tomfilter.html

2. http://www.allenbrowne.com/ser-62.html

[Hagridore]

This looks really promising, especially the second option. I'm wondering, though, if I can use a similar approach which allows me to use a replica of my data entry form with 30+ fields, almost all of which need to be able to be searched (used in filters), instead of using a header/detail layout.

[June7]

What do you mean by 'full form instead of form header'?

Use a replica as long as the controls are UNBOUND.

How do you intend to show the results?

[Hagridore]

What do you mean by 'full form instead of form header'?

Use a replica as long as the controls are UNBOUND.

How do you intend to show the results?

First, I have to say I really appreciate the quickness of these replies. You are a big help!

Okay, so if I take the form I'm using for data entry and make all the controls unbound like in the form header in your example, then this should work? I'd like the results to be filtered individual forms that only show the requested/filtered responses, one form for each record. This instead of a datasheet. As there are so many fields, a datasheet with all of them would end up pretty cumbersome. I am creating other reports and queries, that return only some information, but this search form I want to be able to return everything about the requested records.

[June7]

Either of the suggested options can be used.

Other variations of the DataPig technique:
http://www.datapigtechnologies.com/f...earchform.html
http://www.datapigtechnologies.com/f...mtoreport.html

However, these examples all use text type fields, no number or date types. Can be adapted to handle them but not with LIKE and wildcard.

[Missinglinq]

I have to wonder about your data structure, here. A Table with 30+ fields is really pushing things, for a properly normalized database, and needing the ability to filter on all 30+ fields only reinforces the idea that the data scheme/normalization is off.

What, exactly, in the terms of business needs, are you trying to do, here?

Linq ;0)>

[Hagridore]

I have to wonder about your data structure, here. A Table with 30+ fields is really pushing things, for a properly normalized database, and needing the ability to filter on all 30+ fields only reinforces the idea that the data scheme/normalization is off.

What, exactly, in the terms of business needs, are you trying to do, here?

Linq ;0)>

I don't really need to be able to search on all of the fields, but most. I can't get too specific about my use, but the primary form allows entry of records that have unique identifiers, a possibility of one of four statuses, attachment to particular agencies and two different kinds of contractors, each with their own representatives (unfortunately not a one-to-one ratio), specific company resources being used, a listing of the legal and other regulatory information that is pertinent, different work scopes, different named company activities, and several different kinds of dates...around 30 fields, all of which are necessary for each item entered (each record). There will ultimately be in the neighborhood of a thousand of these records/items. Most of these fields need to be searchable...we may need to know all the items sent to a particular agency using a certain company resource by a certain date applied to a particular activity and the responsibility of a certain contractor representative as an example. To make matters more challenging, several of the fields need to be able to accept multiple entries, something else I'm not getting to work quite right yet. I really did think I knew Access pretty well before I bit off this'n. :-)

[June7]

What do you mean by 'multiple entries' - multi-value fields?

If you need multiple parameters on a single field, that is usually accomplished with a multi-select listbox. http://allenbrowne.com/ser-50.html

[Hagridore]

What do you mean by 'multiple entries' - multi-value fields?

If you need multiple parameters on a single field, that is usually accomplished with a multi-select listbox. http://allenbrowne.com/ser-50.html

Yes. I've got the multi-value multi-select listbox thing. I have been having a bit of a challenge making them put the information into the proper table field. I may have conquered this, though. If not, I'll be contacting y'all. Several of the fields I need to be able to search by are multi-value fields. In the search, I may need to select one or more value in one or more of these fields and filter by them.

[Hagridore]

Here's a somewhat related question, which may have the answer in some other place in these forums. I'm enough of an old-schooler to want to have a real hardcopy book as a reference. Do you have any that you recommend specifically?

[June7]

I don't use multi-value fields. I don't know if they can be searched or if filter can be applied to them.

I don't have any books dedicated to programming in Access but this one sounds interesting: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc. I expect there is a later edition.