Main form and sub-form search results

[scmattson]

I'll hopefully make this as clear and concise as I can.

BACKGROUND: I have a scheduling database with multiple tables. One table holds the requests, and one table holds the contacts that are associated with a request. Relationship between requests and contacts is 1 to many.

Requests are entered on a main form which also contains a subform for the contacts. Requests can be edited on the EditRequest form which is a carbon copy of the main form, only with a few more buttons.

A search form uses VBA code to build search parameters querying several fields (first name, last name, event title, topic, event data, etc). If a result is found, the EditRecord form will open with the request matching the criteria.

ISSUE: When searching fields that are on the main EditRecord form, the search works properly, and displays the records matching the criteria. However when I search for fields from the SubForm (first and last name), no records are generated.

I need help figuring out how to search and display the records found in either the subform or the main form. I've attached a ZIP of the database. Any ideas, suggestions, solutions, or alternatives will be GREATLY appreciated. Thank you!

 

[June7]

Exactly what method are you using for search?

A linked subform can only show records that are associated with parent record on main form. Normally search is done on main form and subform just tags along.

Since don't seem to be displaying or otherwise using fields from Contacts and Organizations on NewRecord and EditRecord forms, why include those tables in RecordSource?

Maybe this will give you ideas http://allenbrowne.com/ser-62.html

Using DISTINCT in query results in a non-editable dataset.

 

[arnelgp]

relink your db and test.
check new code on the module.

 

[scmattson]

relink your db and test.
check new code on the module.

arnelgp, Thank you so much for providing a solution for me! It is definitely beyond my skill set at the moment, and would have taken me weeks to figure out. I really appreciate your time and expertise!

 

[arnelgp]

just google those words that are not familiar to you.
good luck!