Question multiple questions!

[themastertaylor]

Sorry for loading so many questions into one post but i'm getting inundated with problems. I basically devised a system for work to log orders for materials from numerous suppliers. as i've used it i've improved it, then re-written it and now i'm halfway through creating the third version.

in my earlier systems i had an orders table with space for 6 items, in the form of category, item, quantity, unit, rate - category2, item2, quantity2, unit2, rate2 etc etc. however this was prohibitive in only having 6 items per order and also made queries to search for prices very complicated. as such i've adopted the northwind style order form with a sub form for the details and the generic aspects e.g. supplier, phone number etc in the main form. the problem i have now is that due to the number of items i have to order i'd grouped them by category , whereby the category was selcted and the afterupdate field generated the row source for item.

using the sub form method when i enter the second category it updates the item field and removes my selection in item 1 (well clears the display but the data remains in the orders table) i need to be able to flick through the orders and see what has been ordered etc so this isn't suitable. is it possible to categorise my items any other way bearing in mind that it will be in a sub form later on, i considered putting my items in different tables according to category however i'm unsure if this would be in keeping with a good structure for my database.

my second issue is with my query to look up telephone numbers. for example in my order form i input supplier a and site a - i hit a button and it runs a query searching for previous instances of this combination and returns the phone number used (some suppliers will have different numbers for different sites etc). the trouble is if i place an order with the same supplier for the same site more than once i get a mass of identicle phone numbers returned - is there a way to filter out identicle results?

finally with my old system i created a query to find orders relating to specific sites, in the results i got a one line response for every order. with the sub table system as described i now get a line for each item ordered. i.e. if order no. 003 had 12 items on it i'd now get 12 lines in the results relating to the same order - is there a way to make the results collapasable, whereby they expanded to show all the items if clicked etc.

 

[Pat Hartman]

1. By your description, it sounds like you are using a combo box to make the selection and each row in the table may have a different set of values for the combo's RowSource. Is this your situation?
2. Change your query to group by all the selected columns except the telephone and OrderID
3. Assuming that Site is in the order header table, do not include the order detail table in your query.

 

[themastertaylor]

1. By your description, it sounds like you are using a combo box to make the selection and each row in the table may have a different set of values for the combo's RowSource. Is this your situation?
2. Change your query to group by all the selected columns except the telephone and OrderID
3. Assuming that Site is in the order header table, do not include the order detail table in your query.

1. - sounds like you understand, but for clarity i have a materials table with three fields, id, category and description. on the orders form/table, i select category from a combo box and the combo box for item is populated with the items in that category - basically reducing the size of my list in the combo-box for ease of use. works fine as one field but getting it to work in a sub form is prooving tricky, i also noticed northwind uses a bulk list of items rather than sorting by category so it may be that its not possible?

2. works a treat thank you,

3- almost there - thats a handy starting point as it will give me an order history, however i'm often called to ask "when did i order item a, could you try and add this to the order so i get delivery at the same time?" so i need a query that shows the order detail too (which i already have but on multiple lines etc) is there a way i can use your query and from the results fire my own from your results table to get detail on specific orders or would this be very slow/impossible?

 

[Pat Hartman]

1. I attached a sample database that should explain the problem and provide a way to solve it. It does require that your subform be in continuous form view rather than datasheet view because it relies on manipulating form controls which is not possible in datasheet view.
2. Great
3. You can include the details table and use selection criteria against it but as long as you use group by and do not show any of the columns (uncheck the show box) from the detail table, you will get back only one row per order.

 

[themastertaylor]

1. I attached a sample database that should explain the problem and provide a way to solve it. It does require that your subform be in continuous form view rather than datasheet view because it relies on manipulating form controls which is not possible in datasheet view.
2. Great
3. You can include the details table and use selection criteria against it but as long as you use group by and do not show any of the columns (uncheck the show box) from the detail table, you will get back only one row per order.

After trying to get the system working on point 1 for the last few days I finally had a eureka moment and found the simple solution. i've taken the category out of the order details, so that i now have a simple combobox listing every single material - allowing me to keep the dayasheet view.

i've kept the categories table and the links with materials, as such i've made the combo-box a dual column box with the item and its category. so now i have the combo-box listing all my materials and the relevant category, sorted by category. this works perfectly except for one problem, the 3 columns in the combo-box are category, item and itemID (hidden & bound). the thinking being that i can start typing the category and the list will jump to that point and i can select the item without wading through the whole list.

the problem is that i need the category to be in the first column to make the system function, however this results in the selection from column one being displayed in the datasheet view, as such when i look through the orders all i see is the category and i'd prefer to see the item itself. obviously the item id is the stored data but the category is displayed, how to i manipulate it to display the item description?

 

[Pat Hartman]

With minimal effort, you can make the continuous form look like a datasheet if you are intent on that look. You do loose the ability for the user to reorder and resize the columns on the fly but you can still use the shift-F2 key combo to bring up a zoom box to view long fields.

Your solution to keep the full list in the second combo defeats the purpose of having the first combo.

If you go with a single combo, you will need to concatenate the two description fields so that they both stay visible.

 

[themastertaylor]

With minimal effort, you can make the continuous form look like a datasheet if you are intent on that look. You do loose the ability for the user to reorder and resize the columns on the fly but you can still use the shift-F2 key combo to bring up a zoom box to view long fields.

Your solution to keep the full list in the second combo defeats the purpose of having the first combo.

If you go with a single combo, you will need to concatenate the two description fields so that they both stay visible.

I've now got it working by adding an unbound text box to display the second column, obviously it takes a split second to update but it'll do the job perfectly.

Tha main reason i rewrote the system was to allow me to order more than 6 items, on top of that my query to look up historical prices was huge as it had to search for 6 items that could be in 6 different locations on historical orders. basically all the suppliers have different prices and the same supplier will have different prices for different locations due to transport etc. what i'm trying to achieve is a query to look up the historical orders to find the items in the current order, where site and supplier match and the invoice has been checked then to return the prices charged.

i currently have the following

SELECT orders.Site, orders.Supplier, category.Category, materials.Description, orderssub.Quantity, Units.Unit, orderssub.Rate, orderssub.Item, orders.[Invoice Checked], orderssub.Item
FROM Units INNER JOIN (orders INNER JOIN (category INNER JOIN (materials INNER JOIN orderssub ON materials.[Material ID] = orderssub.Item) ON category.categoryID = materials.CategoryID) ON orders.orderid = orderssub.orderid) ON Units.Unitid = orderssub.Unit
WHERE (((orders.Site)=[Forms]![orders]![Site]) AND ((orders.Supplier)=[Forms]![orders]![Supplier]) AND ((orderssub.Item)=[Forms]![orderssub subform]![Item]) AND ((orders.[Invoice Checked])=True));

this seems to be failing on the actual looking up of the items. if i remove the criteria on the items so that i'm looking up site, supplier and invoice checked? i get the results perfectly. however my criteria to look up the item seems to be failing and giving me a parameter box. if i enter the item id it works but i can't seem to get it to look up the id from the combobox.

hopefully its something simple i'm missing but a pointer in the right direction would be very handy!

 

[Pat Hartman]

If Access is prompting you, then the names are different. Make sure that the number of spaces between words is not different. PS - using spaces or special characters in your names is poor practice and is/will be a serious annoyance when working with VBA.