Combobox - refresh problem?

[EManning]

Using A2003 w/ tables linked to SQL Server. All users have their own
copy of the mdb. I have a combobox whose rowsource is a query. This
query is based on a table and has a field in it that indicates if the
record has been selected. When user1 selects the record from the
combobox, the field is updated that the record has been selected. If
user2 tries to select that same record, they get a warning that user1
has selected it and they need to wait until user1 is finished. When
user1 is finished, the field is updated and user2 should now be able
to select the record.

My problem is getting the combobox to refresh after the field has been
updated. When user1 is finished with the record, the updated field
indicates they're not on that record anymore. But for user2, the
field indicates that user1 is still using the record.


This query is a union query that displays names in both "last name,
first name" order and "first name, last name" order.


I've tried requerying the combobox in the Enter, Click, and GotFocus
events. The only thing that works is closing the form and
reopening.


I assume this is a refresh problem since closing and opening the form
solves the problem. Where else can this combobox be refreshed?


Thanks for any help or advice.

[mandanarchi]

You could try a 'requery' instead of a refresh.

[EManning]

My apologies. I am using "requery", not refresh. I say both words but use only "requery" in coding.

[mandanarchi]

Fair enough. =)

I had a similar problem a while back and having a macro to Requery a certain control solved it.

Would be easier to have the query only show those that aren't locked by the other user?

[EManning]

That's not a bad idea but the 2nd user might freak when they try to select a record they know should be there. I could display a message that the record *is* there, just being used by user1.

However! I may have found the answer to my original post. After receiving the warning message that user1 has the record, if user2 clicks on another control other than the combobox, then clicks back on the combobox, it gets requeried. Now I have to figure out where to put the coding to set focus to another control after user2 gets the warning message. My first attempt gave me error #2108 - "You must save the field....."

Thanks for your time and replies