sub-forms and list choice

[JB]

Hello,

There is my problem. (using access 2000)

I've got a table TEST:
- ID numauto (primary key)
- Comment TEXT


To make easier the input of data, I want that the "Comment" field
present a list of pre-entered values
( select distinct Comment from TEXT order by Comment ).

I)

This almost works as I want.
When I open my table in data sheet mode , the field "Comment" contains
the list with former values.
But this list is not live updated.
If I enter a new value, then pass to another record, the list doesn't
contains my new value.
I have to close the table and reopen it to see it.

II)
Then I try with a form.
This works fine in form mode, but not in data sheet mode.

III)
I decide to use macro to correct this.
I had a macro on the "on click" event on my "Comment" control.
The macro executes an "update" on the control "Comment".
So, each time I click on my "Comment" cell, the list is updated.
Fine!
Is it the good way?


IV)
I decide to use this form as a sub-form of another form.
And it doesn't work anymore. My macro failed.
I think this is a problem of context, the macro is unable to find my
control "Comment" which is in the sub form.
What can I do?


thanks,

 

[Allen Browne]

So your Comment field is a combo box?

If so, you can requery it like this:
Me.Comment.Requery

You will want to do that in these two events:
- the AfterUpdate event of the *form* (not control), so it hears of edits
and inserts;
- the AfterDelConfirm event of the form (so it hears about deletions.)

 

[JB]

So your Comment field is a combo box?

If so, you can requery it like this:
    Me.Comment.Requery

Private Sub comment_Enter()
Me.comment.Requery
End Sub

Ok, this works fine, even in the sub-from. So code is better then
macro...?
I've noticed that using F9 shortcut seems to work too. ( but it isn't
as automatic ).


thanks a lot