Combo selection comes up WRONG

[davidbodhi]

I have a form with combo boxes for selecting products to be purchased.

When a product is selected, the price appears in a text box. This is working fine by making the bound column of the combo box the field containing the price.

When I have two different products that have the same price, however, the combo box always displays the name of the first item of that price.

For example, a jar of Lavendar Oil costs $12 and a Small Exercise Ball costs $12. The Combo list is sorted alphabetically. When I select the Small Exercise Ball, I get the $12 in the text box, but Lavendar Oil shows up in the combo.

How do I make the combo show what I'm selecting? I assume it's something with the bound column, but can't find the resolution.

I hope someone is able to help me with this................

 

[ScottGem]

The RowSource of your combo should look like this:

SELECT PRODUCTID, ProductName, Price FROM table ORDER BY ProductName;

The bound column should be column 1, the Column count 3 and the column widths 0";2";0" (you can change 2 to as wide as you need for the product name).

In the After Update event of the combo run code like:

Me!txtPrice = Me!cboProduct.Column(2)

The combo will display the product name the txtPrice control, its price.

 

[davidbodhi]

Scott - Thanks for the help. I can see how this is supposed to work. The bound column is set to the unique identifier and the After Update is supposed to tell the text box to reflect the price column.

Unfortunately, when I do this, it doesn't work. With no AfterUpdate event, I always get the autonumber ID of the item selected in the combo box as the price. (Or whatever value is in the column I've bound)

When I put AfterUpdate code in place, I get blank fields, both in the combo and the text box.

I'm using this, based on your suggestion and what little I understand of VB:
Private Sub Therapy_Type_AfterUpdate()
Me!Therapy_Cost = Me!Therapy_Type.Column(4)
End Sub

where "Therapy_Cost" is the name of the text box where I want the price to show up, "Therapy_Type" is the name of the combo box and column 4 is the field with the price.

Using this AfterUpdate, I get blanks, as I mentioned above. I'm sure it's my VB code that sucks, here, but I can't see what to change, it's such simple code...

 

[ScottGem]

You have to count the columns starting with 0. So if you are using column 4, that would be the fifth column in your Rowsource. If you only have 4 columns that would explain the blanks. Also you have to make sure the column count corresponds with the actual number of columns in the Rowsource (counting from 1). If you notice the sample I gave you, Price was the third column but I used Column 2 in the VBA code.

What other columns do you have? can we see your RowSource?

 

[davidbodhi]

Scott -

Let me say "DUH", since I KNEW that the columns start with zero...

So I've changed the AfterUpdate VB code and I'm apparently getting the $ values I want in my text box, but my combo box is still coming up blank, no matter what item I pick, so I'm not sure if it's defaulting to the first item of cost X, still, or not.

Here's my complete Row Source:

SELECT Purchase_Items.ItemID, Purchase_Items.Item_Type, Purchase_Items.Item_Name, Purchase_Items.Item_Cost
FROM Purchase_Items
WHERE (((Purchase_Items.Item_Type)="2"));

Column Count = 4
Column Widths = 0";0";1";0"
Bound Column = 1

If I clear the AfterUpdate procedure, though, my combo box shows the selection, so there's something about the VB that's at fault. Below is that code:

Private Sub Therapy_Type_AfterUpdate()
Me!Therapy_Cost = Me!Therapy_Type.Column(3)
End Sub

Do I need some sort of refresh of the combo or something? My blatant ignorance is really shining, here... Still perusing the forum, looking, so far to no avail...

 

[ScottGem]

Everything looks correct now. Have you checked the Fore & Back colors? While everything looks like it should work, I would have done it slightly differently. I would move the Item_Type column to the 4th column and uncheck the Show box so its not included in the column count, just used for the WHERE clause. Then change your column count to 3 and the widths to:0";1";0".

If that doesn't help, can you attach a copy of the database with just the relevant tables, forms and queries?

 

[davidbodhi]

Scott - The colors are fine. I did check them, but when I remove the AfterUpdate, the text shows just as it should.

I also made the column changes you suggested and that did no good either, other than slightly cleaning up the programming.

I'm attaching the database without the column changes. I removed what I am pretty sure is irrelevant to the current issue, but it doesn't seem to reduce the size of the file, for some reason. I've had to zip it twice, so you'll need to unzip it twice, sorry.

I appreciate the help. Seems like this ought to be simple, totally simple. MS ought to have the interface set up so you can merely tell it "make this field show up in that box" and have the computer do it all for you.

 

[ScottGem]

Ok, I see the problem. The first problem was you had the control, therapy_Type bound to the Therapy_Cost field. It needs to be bound to the TherapyID field. BTW, you shouldn't use the same names for controls as their bound fields. Read up on naming conventions.

Second, I'm not sure if this is part of the problem but I rarely use a named query for my RowSources. I build the query and store the SQL as the RowSource.

Finally your database is not properly normalized. Having fields like Purchase1, Purchase2, etc. constitutes a repeating group which violates normalization rules. You should have a separate purchases table and use a subform to enter purchases.

 

[davidbodhi]

Scott - I am confused, for various reasons.

My combo Therapy_Type is bound to the Therapy_ID field. (bound column = 1) The Control Source is Therapy_Cost, but that's the field the price pops up in. Now that I think about it, I had Control Source set that way prior to using the AfterUpdate function. One thing that's confused me is that upon deleting Control Source, everything seems to be working fine.

I'll read up on naming conventions. Does Access ever choke on having controls and their bound fields named the same? Ah... I see that *I* get screwed up, though. I thought I'd set Control Source to the text box Therapy_Cost, but I'd set it to the FIELD Therapy_Cost. That leaves me mystified as to how I got prices popping up in that text box in the first place.

You're right. The "purchase items" fields are redundant. And I'd thought I'd cleansed myself of all my bad structure. Clearly not. On the other hand, I have not tackled subforms at all and am clueless as to how to implement them AND I need something functional ASAP (as usual).

I do have a table listing all items that get paid for, from therapies to products. You're saying I need another table, relating Transactions and Purchase_Items, correct?

OK. Thank you very much for your help. My inital problem is resolved and I even think I understand why. If you feel like offering suggestions for tackling subforms, I'll appreciate it greatly. In any case, I'll look into them now. If I go live with this, as is, I'll have to jump through a lot of hoops to move data from these purchase fields to a new table and re-coordinate everything. &#$*@& I really didn't want to add another thing to learn before I could make this functional....

Thanks, again, Scott.

 

[ScottGem]

Sorry, I didn't make it clearer. The combobox is bound to column1 of the query, but its also bound to a field in the underlying table. The Bound Column property is a number indicating the column number in the query. The ControlSource property indicates where the value is stored. You had the Controlsource set to the wrong field. Because it was set to the wrong field, there was a datatype mismatch that was causing the problem. Deleting the Controlsource eliminates the mismatch, but also eliminates the value being stored in your table.

Not using field names for controlnames is more to keep the developer from confusing things, but it could cause problems with Access.

Subforms are not difficult once you have your relations correct. Your Transactions table is analagous to an Order table. You still need a lineitem or detail table for the specific items attributable to the transaction. That table would look like this:

tblTransIterms
TransItemID (PK Autonumber)
TransactionID (FK to Transactions)
ProductID (FK)
UnitCost
Quantity

you don't need the subtotal since that's a calculation and I'm not sure what the Purch_For field is for. Also, relooking at it, The Therapy info should be a line item as well. I can see the deductible info applying to the whole transaction, but each individual service should be a line item.

From there you would create a form bound to tblTransItems similar to what you set up for each set of Purchase controls. You then place the form on your main form and link it on TransactionID. Check the Northwinds sample for how subforms are used.

 

[davidbodhi]

Thanks, Scott... All that is very helpful.

I note you said "Deleting the Controlsource eliminates the mismatch, but also eliminates the value being stored in your table." This sounds like a bad thing.

If Control Source is where the data is stored, (and why not use an intuitive term for this, Microsoft???) shouldn't that be stored in a field related to the transaction, rather than the therapy? By implication, you are telling me that any data I want to store needs to have Control Source set to the table.field I want it stored in.

Actually, the Therapy Cost is already stored in the Purchase_Items table and is only being used in this form for doing calculations. I shouldn't need to re-store it anywhere, it seems to me.

Any other thoughts, besides get my ass out and take a class?

By the way, the "Purchased For" field is in cases where a gift certificate is purchased for someone else or where one person pays for someone else's therapy: such as for a child or a spouse with a different last name. They're all in the same Contacts table though! Honest!!

 

[davidbodhi]

Scott - I do have other questions regarding the new table you're suggesting.

I already have Unit Cost field in the table of items and I have quantity, now, in Transactions. ItemID and TransactionID already exist in their tables.

Isn't my Transactions table doing the job of this new table? I can't get my brain around how things are cleaner by inserting another table in the mix.

I'm not keeping lists of repeated items now. What I *am* doing is keeping fields whether items have been purchased or not. A way not to do that would be good, as well as a way to allow more items, just in case that ever happened. But I'm struggling to see how a new table will do that for me. It's what I wanted my Transactions table to do: to relate my table of purchasers (Contacts) and my table of things to be purchased (Purchase_Items) via each transaction. Why can't Transactions serve the purpose of this new table, though I may have designed it lamely?

I thought the sin was having someone typing in items each time there is a purchase, so the same data is kept in multiple places. Here I have pointers to items and prices in another table and quantities, in this table, for calculation, that's all.

I guess I need to study some order processing samples.

 

[ScottGem]

Actually Controlsource is pretty clear. To give you some background on how forms work. You don't put fields on forms, you put controls on them. Every object on a form (or a report for that matter) is a control. Controls may or may not be bound to a field in a table, just as a form may or may not be bound to a table or query. So the control source is the source of what's displayed in a control.

Now there is nothing wrong with having an unbound control. There are a variety of reasons you might use one. But if the control is unbound, then the value displayed in the control will not be saved to your table. I would think you would want to save the ProductID.

As for storing the UnitCost, this is one of the few exceptions to the rule of not duplicating data. Do your unit costs ever change? If you don't store the unit cost, what happens if a customer comes in and asks for an invoice from 6 months ago and your unit cost changed 3 months ago! You need to provide for that. There are two ways to do it. Either you store a history of all unitcost changes and then pull the unit cost effective on the date of the transaction or you store the current unit cost with the transaction.

In an order processing app there are generally 2 tables, the order and the order details. Unless you have a situation where a customer will only order ONE item per order (which doesn't seem to be your situation) then you need the two tables because you have a 1-many relation. The order table holds info specific to the order itself; date, customer, shipping info, taxes, etc. The order details table holds info about each line item; OrderID as a foreign key, product, unit cost, quantity, etc. Again take a look at Northwinds.

 

[davidbodhi]

Thanks, Scott. I appreciate all the explanations and the time they've taken.

I am looking into the Northwind samples. I've stabbed at subforms since my last post and gotten more errors than, well... than I'd like. The really unhelpful ones, too.

Rather than leaning on you more, right now, (which I'd rather do but feel guilty for <smile>) I'll crack some books and study the samples further.

 

[ScottGem]

Not sure why you are having a problem with the subform. If you create the subform as a tabular form and set it to Continous form mode. Then use the controlbox wizard to embed it on your main form there should be no problem. Its very straight forward.

 

[davidbodhi]

Me neither, Scott.

If there exists a test record when I place the subform, I get a massive choke telling me I'm trying to do coding that's too complex, and only get a big white box in the middle of the parent form.

Using the same flavor code to display my combo box and place the price in the field where I want it as in the parent form (what you started out helping me with) gives me all kinds of Object Not Found messages.

I was at a client's today and never got back into coding mode. Hopefully I shall tomorrow. I tackled the subform after 8 or 9 solid hours of work and, likely, my brain was not firing on all cylinders.

I can say, though, that I'm dipped if I know when, yet, to use square brackets, square with parenthesis, Me, or Forms, ! or . when referring to things. I'm sure there's some logic to it, but having dived into the doing of this with no training whatsoever, it's beyond me. So far, I'm just convinced that these commands and syntax are determined by programmers, rather than users. The logic is under the hood, I know, but for a situation like mine (and I'm no idiot) more logic on the dashboard would be better.

I have a text on VB programming for the rank beginner, but have delayed studying in favor of inching forward on the project itself.

 

[ScottGem]

You can attach a zipped copy if you want us to take a look