Copying record found from a search form's results to another form

[CliffACS]

Forgive me if this question isn't detailed enough, but here's my problem:
I have a Main Form (frmBundles) with a Subform(frmBundleDetails). This form allows the user to build a list of products together in the Subform (using combo boxes in continuous forms view) and give this group a name with a description on the Main form. The result is a package of products with a total cost; much like building an invoice.

Since my list of products to select in the combo boxes on the subform is so large, it makes it cumbersome for the user to scroll through and find the product they want to select. Therefore, I added a Product Search button which opens up a search form which allows the user to enter criteria to find the product. When the results of the search are displayed, I would like the user to double click on the product description of the item they want to add to the list on the other form's subform. Is there code out there or a way to make this happen? Thanks for your time!--Cliff

 

[missinglinq]

So, you bring up a search form, where presumably you enter a search term, click on a button and search for the item, then bring it up? Just so that you don't have to scroll thru the combobox? This doesn't really make a lot of sense, since comboboxes, by default, have their AutoExpand Property set to Yes! Which means as the user starts to enter a term in the combobox Access moves (think search for) to the matching item!

This combobox could be a bound field in the subform and everything would be done in a much simpler fashion.

 

[CliffACS]

Thanks, missinglinq, for the reply. Unfortunately, the product description records aren't listed well enough for the autoexpand search capability of the combo box. For example, the user will look for any product description that has the word "gloves" in it. The results will show like 30 products listed with "gloves" in the description but won't always be the first word in the description (i.e. Leather work gloves, driver's gloves, etc...). Thus, this is why I'm adding a search form. So is there any way to transfer the product that the user wants from the search results to the other form by an event such as double-clicking on the product description?

 

[MarkK]

There are a bunch of ways to do this. Perhaps the simplest is don't bother communicating between forms, just save the result of the users selection directly to the table and requery the SubForm during the MainForm_Activate() event.
But you can also write a Public Sub or Function or Property on a form and call it using a reference to that form, for instance, if Form1 exposes ...

Public Sub GoToID(id as long)
  With Me.RecordsetClone
    .FindFirst "id = " & id
    if not .nomatch then me.bookmark = .bookmark
  End With
End Sub

Then you can run this Sub from Form2 with code like...

Private Sub cmd_Click()
  Forms("Form1").GoToID someID
End Sub

and if the record having the specified ID exists in Form1, it will become the current record.
Using these concepts you can execise a lot of control over what happens where and when.