Full Version: Filter a combo box based on input in a text box
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form named customers, On this form i have a combo box named Suburb and a text box named postcode and another textbox named state. What i want to be able to do is to be able to filter the combo box - suburb, based on the postcode that is entered into the postcode text box and then automaticaly enter the state into the state textbox.
I have a table with the suburbs and its postcoded
if South Melbournes postcode is 1234 and is in the state of Victoria, I want to enter 1234 in the postcode textbox and it will show South Melbourne in the combo box and show Vic in the state textbox
Is this possible? and if so can someone please tell me how to do this. Im faiirly new to access but getting there
Thanks for your help in advance
Yes, it's possible. What you'd do is set the combobox's record source based on the postal code
If Len(Trim(Nz(Me.postcode, ""))) = 0 Then
    'No code entered, i.e. it was removed, set row source to all
    Me.Suburb.RowSource = "SELECT DISTINCT FROM SuburbTableNameHere ORDER BY Suburb;"
    Me.Suburb = Null
    Me.State = Null
    'Postal code entered
    Me.Suburb.RowSource = "SELECT DISTINCT FROM SuburbTableNameHere WHERE [postcode] = ' & Me.postcode & "' ORDER BY Suburb;"
End If
If Me.Suburb.ListCount = 1 Then
    'Only one suburb found for that postal code.  Use that as the suburb and enter the state based on it.
    Me.Suburb = Me.Suburb.Column(0,0)
    Me.State = DLookUp("[state]", "SuburbTaleNameHere", "[Suburb] = '" & Me.Suburb & "'")
End If

This assuming PostCode is of type TEXT. Also, replace SuburbTableNameHere with your real name(s).
where do i enter all this code?
when i went to the combobox's rowsource i went to the query builder then went into sql mode. I think im in the right place .
Then when i enter in your code it comes up with the error message "The SELECT statment includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
Any ideas
Thanks for your help by the way. I really appriciate it.
Hi Lee
Have a look at this it may help.
You say your want to be able to type in 3205 and return South Melbourne Vic it will also return Emerald Hill,Montague,City Road Post Offfice & South Melbourne DC.
In my option looking up the city using a combo box and having it fill in the state and post code is the best way.
For my "Customer Tables" I have the normal Name, Street and then have a combo to look up the city and return correct post code and state. in the cutomer table I only store the ID of the post code.
If you require any further help, post back.
Hi John
THere is a working attachement
Lee, that code goes into the PostCode AfterUpdate event procedure.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.