Combo Box Requery

[Garry]

I have a combo box on a subform that is used to select an item from an
inventory table (row source is SQL on the table). I use the not-in-list
event to execute a macro that opens a form to add a record to the inventory
table. In the VBA code, after using the DoCmd to run the macro, I do a
requery on the combo box. The primary key for the inventory is a text field
containing the Item_No(Item_No may contain letters). The Item_No field is
the bound column. Everything works great unless the new added Item_No
becomes the "first" item - Item_No is first one if sorted. In this case, the
new added item does not appear in the combo box??? I have tried many things,
but cannot get it to work. I'm new to Access, but this is driving me crazy.
Any help would be greatly appreciated.


Thanks - Garry

 

[Garry]

I have a combo box on a subform that is used to select an item from an
inventory table (row source is SQL on the table). I use the not-in-list
event to execute a macro that opens a form to add a record to the inventory
table. In the VBA code, after using the DoCmd to run the macro, I do a
requery on the combo box. The primary key for the inventory is a text field
containing the Item_No(Item_No may contain letters). The Item_No field is
the bound column. Everything works great unless the new added Item_No
becomes the "first" item - Item_No is first one if sorted. In this case, the
new added item does not appear in the combo box??? I have tried many things,
but cannot get it to work. I'm new to Access, but this is driving me crazy.
Any help would be greatly appreciated.


Thanks - Garry

Additional info- Using Access 2000
Even though I don't see the new item in the combo
box, it is in the table
If I don't see the new item after adding it, then
close the form & re-open it, the new item is there.


Garry

 

[Wayne Morgan]

Rather than doing a Requery, try the built in Response argument.

Response = acDataErrAdded
will tell the combo box that the new item was added and that it should
requery itself.

Response = acDataErrContinue
will tell the combo box that you're handled the error and it doesn't need to
do anything. Use this if the user decides what they typed was a mistake and
they don't really want to add the item.

 

[Garry]

Wayne THANKS ALOT! It Works!!!

Garry

Rather than doing a Requery, try the built in Response argument.

Response = acDataErrAdded
will tell the combo box that the new item was added and that it should
requery itself.

Response = acDataErrContinue
will tell the combo box that you're handled the error and it doesn't need to
do anything. Use this if the user decides what they typed was a mistake and
they don't really want to add the item.