Search form

Question

Hi.  I'm a noob for the most part. I need a search form that allows the user to select a record to edit.  I got the basic search functionality from here: http://www.youtube.com/watch?v=CTiA_4Me0cI.   I placed multiple unbound fields at the top of the form with a button to run a "like" query.  (I read that there are pitfalls to using "like" queries, but for now it meets my needs.)

Next I need a combo or subform or other control to display multiple rows returned by the search query.  Then I would like the user to select one of the returned records, and from there jump into a data entry form just for that record.  This could all be on one for, or multiple forms as needed.

I'd appreciate your guidance.  Of course the more detail, the better.

Thanks for your help!

Answer 1

See if this helps...

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

Answer 2

First, you don't have fields on a form, you have controls that may or may not be bound to a field in a table. The search controls you placed at the top (they should go in a header) are such controls. 

Second, If you use Continuous Form mode on your form, and use a button to requery the form once your criteria is entered, then you will have multiple records to match the criteria.

Finally, you can use the following to launch your edit form using the Double Click event of the form.

DoCmd.OpenForm "formname", ,, "[PrimaryKey] = " & me.PKcontrol

An alternative to this is to you a split form. In a split form, one part (usually the top) displays a single record in form view. the other part displays a list of records in datasheet view. The user can use datasheet filtering to filter the list until they can find the record they want. Then select that record and it will display in the single record part of the form for editing. The advantage of this is you don't have to do any search coding. The user has the freedom to build a filter using any criteria they want and they can build it in steps. For example, they can filter for the last querter then see that they need to filter further by dept so add that each time drilling down to fewer records.

Answer 3

Need to clarify...

The search form runs a query which joins two linked SQL tables.  Query returns multiple rows.  The user selects a row from the result set.  That displays a record from a local Access table in a single-record form.  The local Access table has fields in common with the linked SQL tables.  Relationships are defined in the Access Relationship Editor.