Combo box doesn't always show data

[Darhl Thomason]

My main form is in single form view. I have a combo box control
(cboDistrict) that updates based on the value of another combo box
(cboRegion). As I page through the records in my db, the cboDistrict does
not always show the value, even though there is a value in the table for
that record. If I click in the combo box, the correct value does display.

And, to make it even more fun...it seems to be random. I haven't seen a
pattern of when it displays normally (about 1/2 the time) and when it does
not display.

The code associated with this combo box is listed below. My hunch on the
problem is that it's tied to the Got Focus event. I originally had the code
in the cboRegion's After Update event, instead of the cboDistrict's Got
Focus event, but that was causing issues with all of the Districts showing
unless I changed the cboRegion to cause the After Update event to fire.
When I moved it to the cboDistrict's Got Focus event it now correctly only
lists the appropriate Districts for the given Region, but it also has the
above listed problem of not always showing the data even though the data is
really there.

Private Sub cboDistrict_GotFocus()
strSQL2 = "SELECT DistrictID, DistrictName " _
& " FROM tblDistrict WHERE RegionID = " & Me.cboRegion _
& " ORDER BY DistrictName"
Me.cboDistrict.RowSource = strSQL2
End Sub

Thanks!

Darhl

 

[Wayne Morgan]

If you've selected a value for the District then change the Region in the
first combo box, the entry will no longer be in cboDistrict's drop down as a
selection, even though the previously selected value is still in the table.
As you run through the records, if you come across and entry where this has
happened then the textbox of the cboDistrict combo box will be empty. If you
change the Region, you need to clear the District selection and force the
user to make another selection from the list that is now available from the
new region.

 

[George Nicholson]

The following assumes you are storing DistrictID and displaying DistrictName
(which is the only thing I can think of that would cause the problem you
describe).

Placing your code in the GotFocus event would work fine *if* you were
displaying the stored value, because in that case you are only concerned
with what shows while the combo is in a drop-down state (stored values would
always display as-is, even if they weren't among the current Rowsource
values, but you wouldn't have the option to select a new value unless it's
in the Rowsource set).

However, since you are displaying something *other* than the stored value,
your code needs to be in the cboRegion AfterUpdate event *and* the Form's
Current Event (the code in GotFocus will then be redundant and can be
removed).

The way you have it now is that when you move from record to record,
cboDistrict.Rowsource is still using an "old" region as criteria. If the
current DistrictID doesn't exist in the "old" Region, cboDistrict can't
display the DistrictName because it doesn't "know" it: if it's not in the
Rowsource, it can't do the DistrictID to DistrictName translation.
Currently, that situation will only change when you give cboDistrict focus.
You want to change that so that cboDistrict's Rowsource changes (i.e.,
synchronizes to the current Region) every time you move to a new record or
edit cboRegion.

An alternative might be to add code in Form.Current and
cboRegion.AfterUpdate that allows cboDistrict.Rowsource to show all
Districts. Then your existing GotFocus code would constrain the selectable
options only when the user is in a position to make a selection. But that's
3 code snippets where 2 will do...

HTH,

 

[Darhl Thomason]

Thanks George,

You are correct about how I am storing and using DistrictID and
DistrictName. My tables are laid out like this (of course, there are plenty
of other fields in tblStoreData, but tblRegion and tblDistrict are exact):

tblStoreData
RegionID
DistrictID

tblRegion
RegionID
RegionName

tblDistrict
DistrictID
DistrictName
RegionID 'to identify which districts belong to which region

It sounds like I need to move the code from my GotFocus event into the
Form.Current event...is that right?

I think I understand why it would also need to be in the cboRegion
After_Update event, that's where the code originally was. I also understand
(at least it kind of makes sense) why it's not working currently. I did
some filtering on cboRegion and paged through some records and confirmed
that if the region does not change between records, the district shows
properly, but if the region does change between records, then the district
does not display until the GotFocus event is fired "refreshing" the list.

Darhl

 

[Darhl Thomason]

So here is what I changed my code to...but I must be missing something,
because it's still doing the same thing...


Private Sub cboRegion_AfterUpdate()
strSQL2 = "SELECT DistrictID, DistrictName " _
& " FROM tblDistrict WHERE RegionID = " & Me.cboRegion _
& " ORDER BY DistrictName"
Me.cboDistrict.RowSource = strSQL2
End Sub

Private Sub Form_Current()
strSQL2 = "SELECT DistrictID, DistrictName " _
& " FROM tblDistrict WHERE RegionID = " & Me.cboRegion _
& " ORDER BY DistrictName"
Me.cboDistrict.RowSource = strSQL2
End Sub