Form shows incorrect quantity in text box

[sprtrmp]

bear with me, I am a relatively new Access user. I have a simple table that has a "quantity" field that I have filled with test data. That table is tied to a form (through a query). when I open the form, there is a combo box that looks up records based on the selection in the combo box. Most of the selections in the combo box result in the correct quantities being shown in the "quantity" textbox on the form, but some of them are incorrect. in other words, what shows up in the text box is not the quantity for that record in the table. Any ideas?? Thanks in advance!

[June7]

Need to review data structure and form design and combobox properties. If you want to provide db for analysis, follow instructions at bottom of my post.

[sprtrmp]

Inventory.accdb
Alrighty, db is attached, and I have stripped it down to only include the table/query/form I am having trouble with. On the "miscAdd" form, there is a combo box labeled "Miscellaneous Items" choosing an item in the dropdown SHOULD cause the quantity in the "quantity" box to change to the quantity on the "Misc" table. However, for the first 2 items the quantity does not change from the default value of "0" until you select the 3rd, 4th, or 5th item on the list. if you select either of the last 3 items on the list, the quantity changes, but if you select one of the first 2 on the list AFTER either of the last 3, the quantity does not change. It seems to be a problem JUST with the first 2 items. I appreciate any help you can give. THANKS!!

[pbaldy]

As a hint, I'll restate the problem. You say it doesn't work for the first two, I say it doesn't work for the two with apostrophes in the description. It's not that the quantity doesn't change, it's that the macro to go to the specified record fails because of the apostrophe.

[June7]

The WHERE condition is like the WHERE clause of an SQL statement. Apostrophe and quote marks in data can be an issue when programmatically constructing SQL statements because they have special meaning in SQL. Handle the apostrophe with Replace().

="[Nomenclature] = " & "'" & Replace([Screen].[ActiveControl], "'", "''") & "'"

Doubling the apostrophe is called 'escaping' special character. It forces the SQL to treat it as simple text, not a special character.

However, if you referenced the ID instead of Nomenclature, this would not be an issue. Use a multi-column combobox, review http://datapigtechnologies.com/flash...combobox3.html

Should also review http://allenbrowne.com/AppInventory.html

[sprtrmp]

Are you kidding?? well... my face is red!!!
All I can say is "DUH"

Thank you both for your help, I figured it would be something simple I was missing.

[pbaldy]

No problem and welcome to the site by the way!