Product Search

[torquay]

Thank you in advance for any assistance you be able to provide.

I would like some help on searching for product codes or serial numbers and formatting this into an order form a current customer form.
I have 4 main tables

CustomersID (PK)– Name, Address, City, Post Code, Tel Number
OrdersID(PK) – CustomersID(FK), Order Date,
OrderDetailsID(PK) - OrdersID(FK), ProductID(FK), Quantity
ProductsID(PK) - Code Number, Serial Number, Description, Color, Unit Price

I think I have all of these linked correctly

Here's what I am trying to achieve (so far with little success)
I have a customer form that I fill in name address etc; I then have a button that will take me to a customer order form, easy so far.

In my product table there are about 2000 separate products (spare parts for electrical appliances) so what I would like is for a popup box to appear when the order form is loaded (as a drop down or combo box isn’t practical ) so I can search for the product by code number or serial number only for a particular spare part. The description and price are fixed in the products table and should not be changed on the customer order form, I would then like the selection of the code to transpose to the order form.

In other words I would like when the code number has been entered and the product found that the info should then be added to the order form (model or serial number, part description, color and price all taken from the products table) I would like it that when the product has appeared on the order form that it cannot be changed, only by being deleted.

I know this appears to be rather basic but for some reason I am hitting a brick wall, I have looked at many db's and even tried to dissect Northwind to do what I want but cannot make it happen, now after 8 days in the wilderness I need help or guidance.

Thanks in advance
Kim

 

[gemma-the-husky]

Firstly,
are you seeing your combo box on the popup form?, and can you pick the item you want? Is it just returning it to the order form that's giving you problems.

Secondly, how is the order items form presented - is it a continuous form or single form, and it is bound to a query somewhere. (the style of form is an observation, not critical), but how the form is bound is important to see the results.

What you need from the popup form is the productid, as this is the foreign key in the order items, and the orderid that you are currently working with. Then after you pick an item you need to insert it into the order items table with a SQL statement. If your order items table requires a quantity, then you need to get your quantity at this point, or the insert will fail.

If you want to add items one at a time you could select an item, close the form ,and then enter the quantitiy. If you want to select multiple items you may be better staying in the popup until all the items are entered. Then when you have added all the items you want you can close the form, and then requery the order items form to display the items you have added.

Now you are only storing the item code in the order line key, as you say the price is taken from a price list. It is reasonable for the description etc to come from the parts list, but you should think about the price in your order items table - firstly, if it always and only taken from the table, you can never vary the price for any customer, and secondly you may have very difficult maintenance issues when you change the usual selling price at some point.

 

[torquay]

Thank you Gemma.
As there are so many model numbers or serial numbers it would take the user too long to scroll thru so I am looking for a way to do a search for the code number and then return all of the components for that particular code number.
The order items are in continuous form and no it is not bound, which maybe the problem.
Only 1 item at a time would be selected.
Thanks
Kim

 

[gemma-the-husky]

I would be inclined to save the pk of the item you pick in a global variable, then close the popup.

You now have the value of the pk available in your order items form to do whatever you want.

If you want to see all the order items so far though, for this order, the easiest
way would probably be to show them with a continuous form for this order.

I actually tend to use a temporary table to store my order lines. If I throw the order away, nothing happens. If I save the order, I can then write the temporary order details into the main dbs, and issue a sequential order ref etc at that point. You can also have a form bound to the temp table, without having to mess around with filters and things.

 

[gemma-the-husky]

one other thought - finding a needle in a haystack isnt so easy. I have implemented a search before by letting a user key in up to three search strings and then finding matches for all three. Its not indexed but it reduces the final number of things to find.

eg search for 8 2 screw would find all 2.5 inch and 2inch, No8 screws, but hopefully it gets the selection down to a manageable size.