Listbox Selection Not Letting Me Select Other Controls 2

[BSSME]

I have the following code on my form to assist with autofilling other controls on my form. This works great, however, once the county field value has changed I cannot move to other fields on my form.

*******Begin Code*******

Private Sub County_AfterUpdate()

If IsNull(Me.County) = True Or Len(Trim(Me.County)) = 0 Then
Me.DistrictNum.RowSource = "County?"
Else
Me.DistrictNum.RowSource = "SELECT Distinct TaxCode From Counties WHERE Abbrev = '" & Me.County & "' ORDER BY TaxCode"
Me.EEO_Region.RowSource = "SELECT Distinct EconArea From Counties WHERE Abbrev = '" & Me.County & "' ORDER BY EconArea"
Me.Craft.RowSource = "SELECT Distinct Craft From Counties WHERE Abbrev = '" & Me.County & "' ORDER BY Craft"

Me.DistrictNum.Requery
Me.EEO_Region.Requery
Me.Craft.Requery

Me.DistrictNum.Selected(0) = True
Me.EEO_Region.Selected(0) = True
Me.Craft.Selected(0) = True
End If

End Sub

*********End Code*********

I know I am missing something simple but I am stumped. Any ideas?

 

[HandsOnAccess]

Are you getting any error message,
If so, kindly post the same...

Regards,

 

[BSSME]

No error message, it just won't let the focus go.

 

[HandsOnAccess]

What does this mean Me.DistrictNum.RowSource = "County?"?

What are these controls?
Are they Combo Box or List Box?
Me.DistrictNum
Me.EEO_Region
Me.Craft

The code you have written will not work properly for text box controls.

Let me know about the control types.

Regards,

 

[BSSME]

Me.DistrictNum.RowSource = "County?" is just a value statement stating that if there is nothing there then say "County?" to alert the user of missing info.

The controls for Me.DistrictNum, Me.EEO_Region, and Me.Craft are listbox controls and the county is a combobox control.

Thanks for your patience.

 

[snyperx3]

Private Sub County_AfterUpdate()

If IsNull(Me.County) = True Or Len(Trim(Me.County)) = 0 Then
Me.DistrictNum.RowSource = "County?"
Else
Me.DistrictNum.RowSource = "SELECT Distinct TaxCode From Counties WHERE Abbrev = '" & Me.County & "' ORDER BY TaxCode"
Me.EEO_Region.RowSource = "SELECT Distinct EconArea From Counties WHERE Abbrev = '" & Me.County & "' ORDER BY EconArea"
Me.Craft.RowSource = "SELECT Distinct Craft From Counties WHERE Abbrev = '" & Me.County & "' ORDER BY Craft"

Me.DistrictNum.Requery
Me.EEO_Region.Requery
Me.Craft.Requery

Me.DistrictNum.Selected(0) = True
Me.EEO_Region.Selected(0) = True
Me.Craft.Selected(0) = True
End If

End Sub
==================
I notice you're not putting a requery after the initial result of an empty county.

Does the error happen whether you select a county or not? Like can you select a county and it work correctly?


-Pete

 

[BSSME]

All fields on the form will function correctly and I can change my focus without any problems until I select a county in the county combobox. As soon as I update the combobox it acts like the focus is stuck on it after the procedure has updated the three listbox fields. The only way out is to completely close the form.

 

[snyperx3]

comment out these lines
Me.DistrictNum.Selected(0) = True
Me.EEO_Region.Selected(0) = True
Me.Craft.Selected(0) = True
and see if it will let you set the focus on something else


-Pete

 

[BSSME]

I figured it out. It has something to do with the procedure action call itself. I changed it to act upon the combobox changing instead of OnUpdate and now it works fine.

 

[BSSME]

Just out of curiosity, why would it act up like that OnUpdate but not OnChange?

Thak you all for all your help!

 

[HandsOnAccess]

It is like this
When we put that code in County_AfterUpdate Event
It wass executing same code each time we tried to get the focus out of County field.

Obviously as snypex3 mentioned the problem is due to these lines of code

Me.DistrictNum.Selected(0) = True
Me.EEO_Region.Selected(0) = True
Me.Craft.Selected(0) = True

The correct way of assiging the value is

Me.DistrictNum = Me.DistrictNum.ItemData(0)
Me.EEO_Region = Me.EEO_Region.ItemData(0)
Me.Craft = Me.Craft.ItemData(0)

This code will work fine in AfterUpdate Event too...

Thanks,

 

[BSSME]

Didn't get a chance to check back until just now. Thanks for explaining what was going on and for helping me out. Stars for both!