Combo not updating from change on form

[CJ]

Hi:

I have Access 2003

I have a combo box on a subform that shows an inventory of in stock items,
Model, Serial Number and InStock (Y/N). Only items that are in stock show
up.
When a user chooses an item, the subform populates with the item selected.
(So far, no problem).

What I need to happen is this:

When the user selects the product and then selects the Y/N box on the
subform and changes it to No, then I need the combo box for the next record
to not show the item as still being in stock. I have tried all kinds of
events for the Yes/No box and the subform with no success.

This is what I have at the moment:

Private Sub ysnInStock_AfterUpdate()
Me.cmbHeaterLookup.Requery
End Sub

Could sombody please correct this for me?
Thanks in Advance

 

[Rob Oldfield]

I'd suspect that it's because the change in the Y/N box hasn't been written
back to the table when you run the requery, and moving to the next record
also doesn't trigger a requery.

Try changing your sub to

Private Sub ysnInStock_AfterUpdate()

docmd.runcommand accmdselectrecord
Me.cmbHeaterLookup.Requery
End Sub

and if that doesn't work add a requery of the combo into the current event
of the form (so that it requeries when you move to the next record)

 

[CJ]

Thanks for the input Rob.

The code you suggested works fine when I try it with just the subform open.
However, if I have the main form open with the subform, it doesn't work. The
item still shows up as in stock.

I tried both of your suggestions.

Any other thoughts?

 

[Rob Oldfield]

Curious. I would have expected that to work. Could you just expand on what
you mean by "When the user selects the product and then selects the Y/N box
on the subform and changes it to No"? They select the product on the main
or subform? How do they select it? What tables are the main and sub form
bound to? Try posting some code and more detail and I'll try to recreate
the situation here.

 

[CJ]

The Main form is the customer information, address, phone etc, it is based
on a table. The subform in question shows the data for orders of a
particular product, it is based on a query. The combo box on the subform is
based on a portion of sql from the same query as the subform but it only
shows model, number and instock. The criteria for this sql has instock set
to yes.

When a user places a new order for a product, they select an instock item
from the combo box.....the product information then populates the subform. I
would like them to then be able to click on the y/n box on the subform to
place the product out of stock because it has now been ordered.

I hope that straightens it out a little bit.

Thanks for your help Rob.

 

[Rob Oldfield]

Hmm. I think you might have something a bit wrong with your logic. If
choosing the item enters it onto a subform that relates to a particular
client, then it must be going onto a different table than the table
containing the list of items. The only way then to get the combo to requery
as you require would be for its rowsource as a query reference the list of
products plus also the table recording what items each customer has.

Is that what you have?

 

[CJ]

Not exactly, the subform is for the product chosen from the combo box. When
the order is placed, then the Order ID becomes associated with the client.

However, I think I have figured it out........haven't had a chance to try it
yet though.

I think the problem is that the combo is getting it's data from the same
query as the subform. I think if I just make a different query for the
combo, I will be able to requery more efficiently.

Thanks for your help Rob. As mentioned I haven't had a chance to try it yet,
but no worries.