Form(Combo Box) to Form Data Select (Access 2007)

As you will see shortly, I am new to the site, I was referred here by Ratcat.
I am currently using Access 2007. I am creating this database for where I work.

Since I read the Rules I know to post the following link:
http://forums.techonthenet.com/showthread.php?t=881

This post is very close to the question I am about to ask. Only this one is more complex imo.

Since I consider this a somewhat complex question. I am going to relate it to the Northwind Database. I have a form very similar to the Order Details form in the 2007 Northwind Database. Problem is with the number of "Products" in the drop down list located in the subform. I would like it so when you double click the Product ID line on the Order Details form it opens another form I have created which has filters and a search box to help go from 40000 products down to a few for quick finding of the products. I have a field similar to the "Open/New" that reads select. I want when I click select next to the "Filtered Product I want" I want that Product to be entered into the combo box on the Order Details form, and then the after update macro to run. It doesn't necessarily have to happen like that. I could also have a button on the form that says Add Product or something like that. When you click it, it opens that same form, when you select a product it adds the product to the next spot on the form, ie a new spot.

Simpler Description,
Use combo box to select from list, or double click it to open a new form.
On the new form use search box to filter results, click select on new form to select product.
New product that was selected is entered into the combo box on Order Details subform.
New form closes, the world is great again.

Any and all advice is appreciated. I am a mid level access user/low to mid level vba. more on the low side.

Thanks in advance for any time you spend trying to help me, and any suggestions you may provide. If you need more data please ask!

Dan

Since I haven't had much luck with this idea. I am wondering if I could get some help with the same problem slightly different idea behind it though.

I am not posting another question because it is the same problem.
Just like on the order details form in northwind, this is an inventory program.

If I create a new record in my recordset Order Details with the form I made, how do I get it to check if inventory is available for the item i just added and perform the same checks as in the Northwind database.

Any thoughts? All and any advice is appreciated.

Thanks again,

Dan

Hi there Split_Atom18

I think I may be able to put something together like the first idea you have!
I currently have a text box set up to open a separate Calendar form that parses the date back into the text box on double-click. The thing is, I would need to have access to a dummy version of your database to see if I can code it for you - would you be able to up a version here for me to work with - or, if you prefer, I can upload a base version of my current form if you wish - then you can see if you can alter it to fit your needs!

My database as a whole is way to large, however I will try and pull out what is needed and upload it. Thanks much for your help in advance.

Dan

Assuming you have the northwind database for access 2007.
Add this to the northwind database, and i want to be able to doubleclick the order details subform and add a product.

Posted file as requested. Let me know if you need more!!

I'll Have a look over the weekend and see if I can get something to work, OK!

This should be fun!

I am anxiously awaiting some direction I greatly appreciate any time you spend helping me. I have a reason to look forward to Monday now

Any luck? Let me know if you need any more information or files.

Thanks

Dan

I solved this, just in case anyone was wondering. At the time of this post I new very little vba.I just opened a Searchable form with all our products on it, and created a button in the recordset that would add that record to the table using ADO.