Form: Change in Product Name does not cause ID to change.

[JGalt90]

Hello,


I have attached a screenshot of the form in question as well as the zipped database. Right now a change to the OrderDetails_ProductID (17 in this case) in the OrderDetails subform, cascades an update and changes the correct Product name (Pr8 in this case). My issue is that I would like the opposite to happen as well. For a change to Product name to cascade update to the correct ID number. I have looked into after update changes in the properties sheet but have not had success. Example: Jim selects ProductX, the OrderDetails_ProductID correctly changes to X. Right now, no change in ID occurs if I change the product name.

Thank you very much.

-J BreadDB2.zip

[CJ_London]

No code required

set both controls as comboboxes to have the same controlsource (ProductID)

1. for the productname combo the rowsource would be something like

SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName

set number of columns=2, bound column=1, and column widths =0


2. for the productID combo the rowsource would be something like

SELECT ProductID FROM tblProducts ORDER BY ProductID

set number of columns=1, bound column=1, and column widths leave blank

[NTC]

As Product Name and Product ID are side-by-side with a 1:1 then typically this is an easy task. Generally one or the other is a combo box of the Product list, and then in the AfterUpdate event it enters in all the other fields. If your user may need to select from either there is no problem in having them both be combo boxes.

You should right click and change them to combo box; then set their row source to the Product table including both fields - and then put in your code in the AfterUpdate event to enter the appropriate field info.

[keviny04]

Hello,
I have attached a screenshot of the form in question as well as the zipped database. Right now a change to the OrderDetails_ProductID (17 in this case) in the OrderDetails subform, cascades an update and changes the correct Product name (Pr8 in this case). My issue is that I would like the opposite to happen as well. For a change to Product name to cascade update to the correct ID number. I have looked into after update changes in the properties sheet but have not had success. Example: Jim selects ProductX, the OrderDetails_ProductID correctly changes to X. Right now, no change in ID occurs if I change the product name.

Thank you very much.

-J BreadDB2.zip

I'm sensing a major issue here. I looked at your OrderDetails subform and noticed that its record source links the OrderDetails table to the Products table. In other words, the ProductName field you see on the subform actually belongs to the Products table, and therefore must be uneditable. If you edit it, you essentially edit the corresponding entry in the Products table, which is surely not what you intend. For instance, you enter product id 17, and the product name Pr8 shows up; but as soon as you change Pr8 to something else, you change the corresponding entry in the Products table. You can easily verify this by opening up the Products table in table view. The SupplierID field in the subform should also be uneditable because it, too, belongs to the Products table. The only editable fields should be the fields that are actually in the OrderDetails table (see below):




As to your original question of how to look up by product id and by product name, obviously you can't have two bound textboxes the way you set them up above, for reasons I stated above. And you can't use an unbound box for searching either, because unbound boxes are USELESS in datasheet view (whatever you type in an unbound box, the WHOLE COLUMN will show the same thing).

Your goal may be achieved with a "search as you type" combobox, although this method requires some advanced coding. I just assisted someone in another thread on this. It works like this: the user types whatever into the combobox, and the combobox's dropdown will show any ID or any product name that contains text that the user just entered, and the dropdown list will keep updating as the user types. This combobox is bound to OrderDetails's product ID field, which makes it perfectly editable. The user entry's bound field will, of course, be a product ID value, which fits the editable field. So the integrity of the OrderDetails table is maintained. Whatever form you design, you need to make sure the integrity of the underlying table(s) is always maintained.

Some minor observations: why are price and quantity "text" fields? Also, using ProductID as a co-primary key makes it impossible for you enter the same ProductID twice on the same order. A customer may order a ProductID at one price, and the same ProductID at a different price within the same order. Even if you think it won't ever happen, it is wise to include that possibility in your table design, since it may be tough to re-design your table down the road. I suggest you use a generic "line number" field as the co-primary key instead of ProductID: