How do I resort a cbo on a form?

[Dave]

I have a unbound cbo on a form that is used to find records in the form's
recordset.

When I make a change the affects the sort order of the current record on the
form, I want the cbo to be updated so that the position of the current
record in the cbo drop down list is changed based on the change I just made
to the sort order value.

I tried including cbo.requery in the afterupdate event of the text box that
contains the sort order value that I am changing but this does not seem to
work. I don't see any other cbo methods that might do what I need.

How can I resort the cbo on the fly?

 

[John Vinson]

I have a unbound cbo on a form that is used to find records in the form's
recordset.

When I make a change the affects the sort order of the current record on the
form, I want the cbo to be updated so that the position of the current
record in the cbo drop down list is changed based on the change I just made
to the sort order value.

I tried including cbo.requery in the afterupdate event of the text box that
contains the sort order value that I am changing but this does not seem to
work. I don't see any other cbo methods that might do what I need.

How can I resort the cbo on the fly?

What is the RowSource property of the combo? Could you post the code
in the afterorder event?

John W. Vinson[MVP]

 

[Dave]

Thanks John

The RowSource for the cbo called cboEssayID is:

SELECT Essay.EssayID, Essay.EssayRankID, Essay.EssayTopicShort
FROM Essay
ORDER BY Essay.EssayRankID, Essay.EssayTopicShort;

There is a textbox called txtEssayRankID that is bound to the EssayRankID
field that has the following event handler:

Private Sub txtEssayRankID_AfterUpdate()
cboEssayID.Requery
End Sub

I can step through the code and see the UpdateEvent fire and the requery
execute but the sort order of the cbo remains unchanged until I close and
reopen the form.

Thanks for any ideas.

Dave

 

[John Vinson]

There is a textbox called txtEssayRankID that is bound to the EssayRankID
field that has the following event handler:

Private Sub txtEssayRankID_AfterUpdate()
cboEssayID.Requery
End Sub

I can step through the code and see the UpdateEvent fire and the requery
execute but the sort order of the cbo remains unchanged until I close and
reopen the form.

I would guess that the txtEssayRankID field is bound to the
EssayRankID field in the table (used in the rowsource) - but that this
value has not yet been written to disk at the time you do the requery.
It's on the form control but not yet committed to disk.

Try (if it's appropriate) forcing a write to disk before requerying:
either

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

John W. Vinson[MVP]