Disappearing Combo Box Values

[dancole42]

So I have a subform that lists the various items on a particular
invoice.

First, the user selects a product class from the Class dropdown. This
is bound to the Class field in the InvioceLines table. The rowsource
for the combobox is:

ClassName, ClassID, ClassSort

from the Class table, with the bound column set to ClassID.

Depending on the class that the user selects, the options in the
Product dropdown change. The product dropdown is bound to the Product
field in the InvoiceLines table. The rowsource for the product
combobox is:

ProdName, ProdID, ProdSort, ClassID (where ClassID = [InvoiceLines].
[Form]![Class]), and I have ClassID related to ClassID in the Class
table in the query.

For the Class dropdown, I have the following AfterUpdate:

Me.Product = Null
Me.Price = Null
Me.Product.RowSource = "SELECT tblProduct.ProdName,
tblProduct.ProdID, tblProduct.ProdSort, tblProduct.ClassID FROM
tblProduct INNER JOIN tblProduct ON tblClass.ClassID =
tblProduct.ClassID WHERE (((tblProduct.ClassID) = [Forms]![Invoice]!
[InvoiceLines subform].[Form]![Class])) ORDER BY tblProduct.ProdSort;"

And the subform works just fine. When a user selects a class from the
class dropdown, it limits what appears in the product combobox. On the
next line, a user can select a different class, and then different
products appear in that line. Etc. Etc.

However, as soon as the subform loses focus, the Product field in the
subform gets blanked out except for the last record. Oddly, the values
for the blanked out fields are saved in the table, they simply do not
appear on the form. What you close out of the form and go back in, all
fields are blank except for the FIRST record.

Any ideas why this would be happening? It only seems to happen in
Access 2003, not in Access XP.

[Tom Clavel]

It sounds like you are assuming that the row source should be
different for the combo box for each record because there is a
different class selected for each record. In fact, each time you
update the class you are requerying the product subform for every
record. The combo box row source is the same for every record, and
changes every time you change the class. The combo box rowsource only
occasionally includes the value you have entered there.

It shows in the last record, because that's the record that was active
when you reset the query. When you open the form, it sounds like the
requery is operating on the first record.

try this: make the combo boxes unbound. Make a new text box bound to
the product in the afterupdate for the product combo box, set the
textbox = combo box. -tc

On Mar 7, 9:58 am, [e-mail] wrote:

So I have a subform that lists the various items on a particular
invoice.

First, the user selects a product class from the Class dropdown. This
is bound to the Class field in the InvioceLines table. The rowsource
for the combobox is:

ClassName, ClassID, ClassSort

from the Class table, with the bound column set to ClassID.

Depending on the class that the user selects, the options in the
Product dropdown change. The product dropdown is bound to the Product
field in the InvoiceLines table. The rowsource for the product
combobox is:

ProdName, ProdID, ProdSort, ClassID (where ClassID = [InvoiceLines].
[Form]![Class]), and I have ClassID related to ClassID in the Class
table in the query.

For the Class dropdown, I have the following AfterUpdate:

Me.Product = Null
Me.Price = Null
Me.Product.RowSource = "SELECT tblProduct.ProdName,
tblProduct.ProdID, tblProduct.ProdSort, tblProduct.ClassID FROM
tblProduct INNER JOIN tblProduct ON tblClass.ClassID =
tblProduct.ClassID WHERE (((tblProduct.ClassID) = [Forms]![Invoice]!
[InvoiceLines subform].[Form]![Class])) ORDER BY tblProduct.ProdSort;"

And the subform works just fine. When a user selects a class from the
class dropdown, it limits what appears in the product combobox. On the
next line, a user can select a different class, and then different
products appear in that line. Etc. Etc.

However, as soon as the subform loses focus, the Product field in the
subform gets blanked out except for the last record. Oddly, the values
for the blanked out fields are saved in the table, they simply do not
appear on the form. What you close out of the form and go back in, all
fields are blank except for the FIRST record.

Any ideas why this would be happening? It only seems to happen in
Access 2003, not in Access XP.

[Tom Clavel]

Typo: I dropped a period. Should read:

try this: make the combo boxes unbound. Make a new text box bound to
the product. In the afterupdate for the product combo box, set the
textbox = combo box. -tc