issue with bound combo box values...

[Brad Pears]

I have a project I recently converted to Access 2000 from dbaseIV. The user
has been collecting data since the 70's (before dbase, it was obviously
something else). This particular system is fairly straight forward. They are
simply collecting data and storing codes relating to various circumstances
and incidents in a database on individuals supported by the agency. Recently
they went through a "code/description" which saw them do away twith some
codes, create new codes etc... etc.. They do still need to retain the old
codes as they existed in the database though.

To facilitate this type of thing (as it is something they will likely go
through every once in a while) I added a boolean field called "Show In List"
to the system "codes" table and it's default is set to true for each code.
As codes become irrelevant, they simply uncheck the "ShowInList" field and
that code is no longer displayed in the combo box when the user is entering
new data into the main entry form. The old code is still maintained as it
was in the database but they no longer are able to add that old code when
entering new data. For reporting however, I DO give them the ability to
select ALL codes, whether the SHOWINLIST indicator is set or not. This
allows them to still report on old data.

Ok, now that you have the jist of what the application is doing, here is the
problem. Because the various bound "codes" combo boxes have a select clause
that excludes all codes where SHOWINLIST is false, each time a record is
displayed that has a code in it that does not exist in the combo box, the
code is "blanked" out and of course confuses the user as they think that
niothging has been entered there...

To get around this I tried changing the select clause so that all codes are
by default selected until that control has focus. Then, in the "got focus"
event for each combo box, I reset the record source so that it then
excludes the old codes by only selecting those rows where "showinlist" is
true. On the "lost focus" event for each combo box, I then reset the
recordsource to select all records again (exclude the "showinlist = true"
where clause) . This works ok but causes some crazy behaviour if the user
happens to click on existing rows in the combo box.

I'd like to rethink this whole thing and redo it. There must be a better
way. Should I not make the combo boxes "bound" possibly and instead handle
the displaying and saving of the code data within the combo boxes manually
using the "on current" and various "update" events of the form perhaps?

What would be the best way to approach this issue??

Thanks,

Brad

 

[Guest]

Hi Brad,

The fact that the comboboxes are bound or not is unlikely to make too much
difference to handling the rowsource of the combobox. I would try moving the
code from the gotfocus to the on_enter procedure, as the combobox loses focus
during code execution in certain circumstances, and may well be the cause of
your odd behaviour at times.
Even better, would be to set the select code and the value for the
combobox/es in the after_update event of whichever field first selects the
record (only change code if it has 'Show In List' = 0).

good luck,

TonyT..

 

[Marshall Barton]

I have a project I recently converted to Access 2000 from dbaseIV. The user
has been collecting data since the 70's (before dbase, it was obviously
something else). This particular system is fairly straight forward. They are
simply collecting data and storing codes relating to various circumstances
and incidents in a database on individuals supported by the agency. Recently
they went through a "code/description" which saw them do away twith some
codes, create new codes etc... etc.. They do still need to retain the old
codes as they existed in the database though.

To facilitate this type of thing (as it is something they will likely go
through every once in a while) I added a boolean field called "Show In List"
to the system "codes" table and it's default is set to true for each code.
As codes become irrelevant, they simply uncheck the "ShowInList" field and
that code is no longer displayed in the combo box when the user is entering
new data into the main entry form. The old code is still maintained as it
was in the database but they no longer are able to add that old code when
entering new data. For reporting however, I DO give them the ability to
select ALL codes, whether the SHOWINLIST indicator is set or not. This
allows them to still report on old data.

Ok, now that you have the jist of what the application is doing, here is the
problem. Because the various bound "codes" combo boxes have a select clause
that excludes all codes where SHOWINLIST is false, each time a record is
displayed that has a code in it that does not exist in the combo box, the
code is "blanked" out and of course confuses the user as they think that
niothging has been entered there...

To get around this I tried changing the select clause so that all codes are
by default selected until that control has focus. Then, in the "got focus"
event for each combo box, I reset the record source so that it then
excludes the old codes by only selecting those rows where "showinlist" is
true. On the "lost focus" event for each combo box, I then reset the
recordsource to select all records again (exclude the "showinlist = true"
where clause) . This works ok but causes some crazy behaviour if the user
happens to click on existing rows in the combo box.

I'd like to rethink this whole thing and redo it. There must be a better
way. Should I not make the combo boxes "bound" possibly and instead handle
the displaying and saving of the code data within the combo boxes manually
using the "on current" and various "update" events of the form perhaps?

What would be the best way to approach this issue??

What I do in this kind of situation is to place a text box
directly on top of the combo box's text part. Bind the text
box to the the same field as the combo box so it will
display the field value regardless of the combo box's row
source. To prevent users from editing data in the text box,
use it's GotFocus event to set the focus to the combo box.
The combo box will automatically be brought to the front
when it receives the focus.