Cascading Combos in Datasheet View

[bruced3846]

OK, here is the problem. I have the following tables:

Customers
Consignment
ConsignDetails
Employees
Products
Suppliers
CompanyColors
CompanySizes

and I am creating a form to view the items currently on consignment with the customer. I have the main form 'Consignment' that holds the CustomerID, ConsignID, DateStarted, Employee (SalesPerson), Inventory Interval, and Closed (Y/N)... The subform lists in datasheet view the records in form 'ConsignDetails' that have the same ConsignID. The controls on the subform are :

ProductID
UnitPrice
Quantity
Color
Size

The ProductID is selected in a combobox from all products. The UnitPrice defaults to a DLookup() from the products table based on the ProductID, but can be changed and stored. The Quantity is a manual entry field. Color and Size are both supplier-dependent fields, in other words, the supplier of the product has unique Size and/or color codes or descriptions. I have no problem creating a combobox either in the AfterUpdate event of the ProductID or in the MouseDown event of the Color or Size field to limit the drop-down list to only those Colors/Sizes from the CompanyColors / CompanySizes tables with the same SupplierID as the Product in the first control.

So far so good, but here is where I am beating my head against the wall. Every time I choose a product from a different supplier, the Color and Size controls display ONLY the items from the current Supplier, not the items from the Supplier of the Product on their individual records. I have seen ways to make the combo popluate a locked textbox, hide the combo, and display only the text, but in a data entry application the user will be primarily on the keyboard, and the solutions seem to make it almost impossible to do without using the mouse over a continuous form.

I am using Access 2002 (ver 10.6501.6735) SP3 to create an ADP using MSDE to link to SQL7 tables....

Any help will be appreciated, I have spent almost two weeks trying different approaches and may be too close to the forest to see the trees.

Thanks,

Bruce Davis

 

[Tim L]

Not sure whether I followed your description properly (you lost me about to whom the colours belonged in the paragraph starting "So far so good") but it sounds to me as if there may be a problem with the query rather than the method of populating the combo box.

I would call a refresh of the querys that populate the Colour and Size fields(combo boxes?) from an AfterUpdate on the ProductID combo, which I presume is what you are doing. So that begs the question, do the queries return the right information?

Tim

 

[bruced3846]

Tim,

Thanks for the quick reply, but according to microsoft in the article, http://support.microsoft.com/default.aspx?scid=kb;en-us;208866 , the resolution is: "Do not hide the BoundColumn field. If you want to see a different column, use the DLookup() function or the AutoLookup technique to display the information that you want in another control on the form. "

I just can't make it work for me.

Bruce

Yes, I am requerying on AfterUpdate of ProductID, also tried most otehr events, still having problem.

Thx

 

[Tim L]

I couldn't see where you mentioned that you had a hidden column in your original text - the main problem came across as being the population of the color and size combos.

I've got several combo boxes displaying data with the bound column hidden by setting the column width of it to 0 in the Format\Column Widths property and they work fine.

If you have a combo, say cboColours, with three columns and want to hide the first and display the second and third, I'd have a second control, a text box, locked, say txtCboColourCol3. Then make the recordsource for the text box = cboColours.Column(2) - for some reason the column number starts at 0. When you change the value in the combo box the value in the txt box will change automatically. (But, re-reading your post you probably know about this method.)

If I understand you correctly now, the sales person can change the Unit price but gets the default given to them on selecting the product from the Product combo. In which case the text box solution won't work as you won't be able to change the value stored for the sale. In this case, on the AfterUpdate event of the Product combo have the Unit value (from the appropriate column) transferred into the Unit price control.

me.txtUnitPrice.value = me.cboProduct.column(x)

where x is the appropriate column (adjusted as above)

Tim

ps: I'd edited my previous reply to include more detail and then again to change some grammatical errors, and the new version may have crossed paths whilst you were replying.

 

[bruced3846]

OK, I am probably not describing this correctly, let me try again. The problem is NOT poplulating the 2nd and 3rd combo boxes. I can do that several different ways, nor is the problem the unit price, that works fine. I probably gave too much info at first. The problem is the when the user adds a record to the consignment inventory by selecting a product off the dropdown on a new line, the color and size fields blank out unless the supplier of the product on their line is the same as the supplier of the product on the new line. For example, if I add "Hat" as a product, and "Hat" has a link to the Supplier "Nike" in the Products table, the color and size combo boxes work fine and display the information for Nike's colors and sizes. If on the next line I add "Pens" as a product, and "Pens" has a link to the Supplier "Bic" in the Products table, the color and size combo boxes now display only the colors and sizes associated with the supplier "Bic", and the display blanks out the color and size for the record for "Hat".

Make any more sense?

Thx, Bruce

 

[Tim L]

Bruce your last comments make much more sense now. I must say that what you describe is perhaps the behaviour that I would have been expecting/trying for.

What colours are you expecting to be in the list, obviously not just the ones associated with the individual the item, 'pen' or 'hat'? Do you wish the list to include all the (suppliers) colours associated with all the items associated with the current parent form? If this is so I'd go back to my earlier comment and suggest that the problem is with the query that is used to populate the color and size drop-downs.

There said, I think that there is also an issue with how you are storing the color and size for each item; if the boxes are going blank it appears that they both reference to a combo box that is being requeried in the OnCurrent event, rather than being stored for the item record.

Tim

 

[bruced3846]

Pat,

Thank you for the reply, I arrived at this forum via a link to that very post that someone else gave me on a different forum (with a note saying how great YOU are, by the way). I see where the solution you propose is useful in continuous form view, but I still have a problem with datasheet view. Also, my bound columns are GUID, having attempted to create a normalized table structure with a table identity column for each table, and then storing that identity in any other location where I need to reference the table value. Microsoft (http://support.microsoft.com/default.aspx?scid=kb;en-us;208866) says the bound coulmn must be unhidden, so I may have to restructure my tables. Not a pleasant thought at this late date... Oh I miss the "good ole days" of DOS programming! I used to write Clipper code years and years ago, and with recent unemployment am trying to understand VB... or at least VBA. Thanks for any help you can provide. It appears VBA will not do what I need with the structure I have, so I need to modify the design.

Bruce Davis

 

[bruced3846]

Tim,

Thank you again for the continuing replies.

You asked: "What colours are you expecting to be in the list, obviously not just the ones associated with the individual the item, 'pen' or 'hat'?" -

No, all colors associated with the Supplier of that product.

Do you wish the list to include all the (suppliers) colours associated with all the items associated with the current parent form?

No, I only wish to include the colors associated with the supplier of the item on the current record, not the parent form. The parent form is the consignment form itself, identifying the customer to whom we have consigned the inventory. The consigndetails subform is where I am working, with each record (line) showing a product, price, quantity, color, and size. (image attached)

If this is so I'd go back to my earlier comment and suggest that the problem is with the query that is used to populate the color and size drop-downs.

I have tried several different events and several different queries to populate the dropdowns. I just replied to Pat Hartman with a link to a Microsoft article describing this behavior which MS seems to says is by design and a good thing.

There said, I think that there is also an issue with how you are storing the color and size for each item; if the boxes are going blank it appears that they both reference to a combo box that is being requeried in the OnCurrent event, rather than being stored for the item record.

The boxes do go blank, and they do both reference a query (I've tried putting it in OnCurrent event for the color/size control as well as in the AfterUpdate event for the ProductID control, among other contol/events...) that uses the product on that record to identify the supplier, then to identify the color. The fields in the ConsignDetail record are CompanyColorID and CompanySizeID, the GUID identity fields in each of the two tables. I store the bound column of each dropdown in the field, rather than storing the actual color. This table design is what I thought best for normalization, but now I may have to change that structure.

Thanks again for the assistance.

Bruce Davis

 

[Tim L]

Just had a sudden thought. In the Product Table, have you set the Lookup for the Color and Size fields Display Control to Textbox or Combo/List box. I've not tried anything, yet, but I presume that if they are not set to Text box in the Table then it may introduce problems when you come to produce the form, especially in datasheet view.

Right, I've now had a chance to have a play around. Please find attached the db that I created (it would really be too much to explain in text...), which appears to be working as you specify.

Hopefully this works the way you want it to. It did require a fudge or two; like additional combo boxes and hiding columns in the consignment (sub)form, which your users may just figure out about, so if this works but you need more security I'd suggest that you see if you can't use continuous forms rather than datasheet view. Because the queries for the colours and sizes are based on sfmConsignment whilst it is contained within frmOrders they will not work properly if you open the subform as standalone.

HTH

Tim

 

[bruced3846]

Tim,

Thanks for the test database... I am using an ADP, not an MDB, so a lot of what you did has no exact corollary.

In the Product Table, have you set the Lookup for the Color and Size fields Display Control to Textbox or Combo/List box.

In ADP's there is no "Lookup" tab on the table level. I have defined the fields as Combo box on the form.

It did require a fudge or two; like additional combo boxes and hiding columns in the consignment (sub)form, which your users may just figure out about, so if this works but you need more security I'd suggest that you see if you can't use continuous forms rather than datasheet view.

I may have to use continuous, but would rather not.

Because the queries for the colours and sizes are based on sfmConsignment whilst it is contained within frmOrders they will not work properly if you open the subform as standalone.

I see you have colours and sizes for each PRODUCT in your DB. In mine I have only a single list of colours and sizes for each SUPPLIER. Adds a level to the query, since I am not looking up a product in the table and selecting a size or colour, I am looking up a supplier based on the product and then looking up size or color. Each supplier has only one table of sizes and/or colours, so rather than make the user enter for each product, they justhave to enter one time for the supplier.

Still trying to make it work on my side.

Thanks again,

Bruce D

 

[bruced3846]

Pat,

I am probably guilty of over-enthusiasm and under-education. I thank you for your comments.

If you are talking about a form in datasheet view, it works the same as in continuous view. If you are talking about viewing a table or query directly, you can't use cascading combos because you don't have any form events to use for requery.

I am talking about a form in datasheet view. I have created forms for all views. I am using an ADP, not an MDB, which seems to make quite a difference. I am using SQL7 as the database engine.

I may be making this more complicated than it needs to be, but this is where I am coming from. I used to be a Clipper programmer (heavy on the "used to be") many years ago in the DOS world. Have been out of coding for years, in customer service and management. Had my own small software company (out of business 3 years now) doing practice management stuff for doctors offices, and had two VB programmers on payroll. They convinced me that Access databases were not stable enough for most multi-user office applications, and preferred SQL. Now that I am un-employed a couple of friends have agreed to pay me a MINIMAL hourly wage to create an application for their promotional products company. I decided to use SQL7 as DB engine, since the app can be run using the no-fee MSDE on the the user side.

Why would you use GUIDs rather than Autonumbers? Even when using replication, autonumbers set to generate random numbers are the norm.

Another hand-me-down from my former employees. I did not think it mattered what the index or ID column was since it would never be seen, only stored.

Thanks again for listening to my prattle,

Bruce D.

 

[Tim L]

Pleased to be of assistance

I see you have colours and sizes for each PRODUCT in your DB. In mine I have only a single list of colours and sizes for each SUPPLIER. Adds a level to the query, since I am not looking up a product in the table and selecting a size or colour, I am looking up a supplier based on the product and then looking up size or color. Each supplier has only one table of sizes and/or colours, so rather than make the user enter for each product, they justhave to enter one time for the supplier.

I suppose that it's your choice on how you do it

This method was just my natural assumption; once you have selected the product the supplier is automatically determined and hence the colours and sizes available are too. I admit though that my method does require more information to be entered from the outset ("you pays your money you takes your choice").

You could, I suppose, adapt my method to give the sizes and colours for the supplier rather than the product, however I would question whether all products are available in all sizes and whether size names are valid for all products. You, I presume, have the answer to this whilst I do not, and you will, I am sure, take the most appropriate route to your solution.

Good luck and let us know how you get on.

Tim

 

[bruced3846]

Workaround

Pat,

I am way too far in to recreate the wheel using an MDB. I tried converting to VB6, but the best program I can find is not too good... 17 pages of uncoverted code. Here is what I ended up doing:

I created a form for all the fields and call it in add mode from a command button on the original form. The single query per form problem is ok here, because it only adds one record at a time. I requery the original subform on close, so the datasheet view shows everything I need. I remembered that even my high-priced programmers used pop-up forms from buttons rather than trying to use cascading combos, so I followed thier example, seems to work pretty well. Assigned hot keys so the user can do ALMOST eveything from the keyboard...

At this point I am two weeks away from alpha, thanks again.

Bruce Davis