Hello All,
I'm trying to have a selected record from a search form return that records' info back to another form. Hopefully the attached image helps explains what I'm attempting.
The pk used is Product_ID so I'd guess that a double click event of the record on the search form could "capture" the required info but I'm not familiar enough with the code required to close the search form and return that info to the Quote subform.
As always, any help is greatly appreciated.


Click to view attachment

Hi,

Subforms are usually linked to the main form, so updating what is being displayed in the subform might cause some problems or might not work at all.

We'll need to understand your form setup better to maybe give you a better solution.

Just my 2 cents...

Hopefully I can clarify what you're asking.

The Main form frmQuotes, is based on
tblQuotes
quote_ID
vendor_ID
quote_date
vendor_contact_ID

frmQuote_Detail uses quote_ID for Master / Child fields and links to
tblQuote_Details
quote_detail_ID
quote_ID
Product_ID
Catalog_no
Quote_qty
Quote_price

frmSearch_Products is a main form with an unbound search box at the top and a frmSearch_Orders_subform which has a record source of qryOrders_Search .
The query is based off
tblProducts
Product_ID
Product_desc
Category_ID
Part_no ( this may seem a duplicate but often a product has a part number and each vendor uses their own Catalog number so I need both text fields )

and from
tblCategory
Category_ID
Category_desc

I currently use the combobox in the subform to display the list of products but as the list gets longer, this becomes less effective.

I hope this info can help you lead me to the next steps.

Thanks.

Hi,

It might be better if you could post a zip copy of your db with test data.

Just my 2 cents...

Thanks for having a look.

The project is to keep track of purchased items, not for accounting purposes, but rather as a way to compare quotes from various vendors and search order history.

The intended flow of the project would usually start with the frmOrders. For a repeat order, I'd select the vendor on the main form and a list of the products they've previously quoted on shows in the sub-form combobox. If it's an item they haven't quoted on previously, I'd open the frmQuotes from a button in the sub-form.
Here's where I'd like to select the product to quote via the Search form, have it populate the quote subform, enter the vendors quoted price, and return to the order form and select the newly quoted item via the combobox.
If it's a new part that hasn't been quoted by any vendor, I'd start from the Quotes form to get pricing from a few vendors and then create an order based on price / delivery / etc.

I know there are a couple ( or perhaps more ) of glitches, for eg. some controls aren't refreshing when going from one record to another in the orders form, until the cboVendor_ID is used to reselect the vendor. (It's on my list of to-do fixes).


Click to view attachment

any suggestions from anyone?

Hi,

Sorry for the delay. I took a look at your db but had a hard time following what's going on since I don't have any idea about your business process.

The Search form does have a ProductID that we can grab but not yet sure how that would help you with what you want to do. It seems to me that there are other fields in the search form that are coming from additional tables that you might also need to send to the Quotes form.

By the way, after the user Double-clicks on the product on the Search form, are you trying to create a new record in the Quotes subform? If so, I think we'll also need to pass something for the following fields: QuoteID, CatalogNo, and QuotePrice. Adding the QuoteID might also need additional fields from the Quotes table.

It would be nice if you could sit down with someone to explain your db to make sure that you are going in the right direction.

Sorry if I misinterpreted your db.

Just my 2 cents...

Hello again,

I'm sitting now so will try to explain my db intent. I don't know anyone who I can discuss db strategies with but I have talked with my staff about what would help through the shop. This made sense at the time ?!?

In general I'm trying to create a 1 stop resource to be able to look-up past items Ive either bought or have pricing for so when the time comes to order / re-order an item I don't have to start going through catalogs or websites (or worse, my last year's worth or paper invoices) to find who I purchased something from , what the cat. number is and how much I paid last time.

There are quite a few specialty cutting tools and materials I need to buy over time that aren't common everyday items. Someone may remember who we bought something from but no catalog info, or may have in hand the box from the old part with a number but no idea who supplied it. The other use would be if I'm quoting to my customer on a project which would need special tools or materials etc., I'd record the quotes from my suppliers but wouldn't necessarily be placing an order. Perhaps months later, that quote request comes back as an order from my customer and I'd need to look up and refresh my vendor quotes to see if any price adjustments needed to be made and who I got the best price quotes from back then.

Since I'd be gathering price quotes from several vendors, the quote form with details on the sub-form would be used. In most cases, the part or product is not available from only 1 vendor so I may get prices for the same item from 3 or 4 vendors. It seemed normalization rules would have me not enter the same product description twice so the vendor_ID on the main quotes form in tblQuotes and the product_ID (or ID's ) and quote price(s) would be in the sub-form in the tblQuote_Details. ( 1 vendor : many products and 1 product : many vendors )
When I am placing an order, I'd use the Orders form and select which vendor I was purchasing from. A list of items they had supplied price quotes for would be in the Order Details subform combobox.

When it comes to entering a new quote from a Vendor for a certain product, the entire list of all products likely needs to be viewable and that makes for a huge list to scroll through. I had thought of filtering the list by somehow tagging each vendor with all the categories they may be suppliers of, but that one got away on me. In most cases, raw material suppliers don't supply cutting tools or office supplies, and tooling suppliers don't sell office supplies or raw material but then you get the McMaster-Carr's of the world who stock everything and would be in almost all categories.
The text based search and filtering of items in the search form gets me to the product quickly, but then I have to go back to the quotes sub-form and try to remember the full description or part number to select it in the combobox.


I also realize now that the 2 search forms I have are named in reverse. I'd copied one and removed fields I didn't need but renamed the wrong form. The search form that will display from the quotes sub-form (frmSearch_Orders) is only displaying Description, Part Number and Category. The Description & Part Number are all that get brought into a new record in the quotes sub-form and both are available from Product_ID. I'd then enter the price I get from the vendor (acquired by phone, fax or email) and any catalog number they use for that item.


Thanks for sitting with me. Any suggestions or comments would be appreciated. I may have suffered tunnel vision trying to get something in place with this db and missed the turn-off that would have been a short-cut to a better way.