Combo Box with RowSource based on Textbox Value won't "refresh"

[HLCruz via AccessMonster.com]

I have a form that uses an unbound text box and unbound combo box as a search
process for users.

In the unbound text box, [txtFilterZip] the user enters the zip code. This
Zip code becomes the criteria for the SELECT statement in the RowSource of
the combo box [Combo83]. Users then are able to search by street address
within the combo box filtered for addresses just within that Zip code.

This works perfectly except when the user would like to enter a *new zip code
in the text box. In that case, the combo box results stay filtered under the
previous zip code. If the user closes the form and re-opens it then they can
enter a new zip code and get accurate combo box results. However, that
process isn't very efficient.

It all my attemps I have been unable to set up some code that would
accurately requery the combo box without closing the entire form.

Can anyone help? This is the code I've been working on lately but it returns
no values in the combo box when the user tries another zip code:

Private Sub Combo83_AfterUpdate()

Dim strZip As String
Dim strSource As String

strZip = [txtFilterZip]
strSource = "SELECT sysqryResidentListByZip.Pkey FROM sysqryResidentListByZip
WHERE sysqryResidentListByZip.Zip = " & strZip & """"

Combo83.RowSource = strSource
Combo83.Requery

Dim strWhere As String
Const strcHead = "SELECT * FROM sysqryResidentListByZip WHERE "
Const strcTail = ";"

If Me.Dirty Then Me.Dirty = False
If IsNull(Me.Combo83) Then
strWhere = "False"
Else
strWhere = "[Pkey] = """ & Me.Combo83 & """"
End If

Me.RecordSource = strcHead & strWhere & strcTail
End Sub

Thank you! Heather

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200908/1

[Damon Heron]

Your code requeries the combobox rowsource in the afterupdate event- which
fires after user makes a selection. Put the code in the textbox on exit
event and see if that fixes the problem.

Damon

"HLCruz via AccessMonster.com" <u27207@uwe> wrote in message
news:9b2e45ec363aa@uwe...