Postcodes/ZipCodes

[prmitchell]

I wish to add to a form the interface that allows the user to type in a postcode and the system then displays the suburbs that match that postcode and the user then selects the correct suburb (for where they live).

The structure I have is a PostcodesAndSuburbs table with the fields
Postcode
Suburb
State
PCode (PK)

PCode being necessary because neither postcode and suburb are unique. The values stored in PCode are the concatenation of the postcode and the suburbs initial(s). So on a form I have a combo box looking up the values in PCode,. So if the user types in the four digits of the postcode and then clicks the drop-down arrow a list will show and the suburbs with that postcode will appear at the top of the list and if the user selects the correct suburb then fine - all working.

But this is just a little different from the interface that exists now in many places (webpages) where you type in the postcode - and on entering the fourth digit a list appears - and the list only contains the suburbs that match the four digits.

So my pseudocode for this is
OnChange (Or OnEnter)
when fourth digit entered
run a query
Where Me.PCode = [PostcodesAndSuburbs].[Postcode]
which is behind the combo box allowing the user to select the suburb

but I am not sure how to put this into reality.

Thank you in anticipation.

 

[llkhoutx]

Postal codes are published regularly by the USPS. They might be available free. New urban areas often require new postal codes. Are you allowing for the +4 digits?

 

[John Big Booty]

The sample here may do the sort of thing you are after.

 

[prmitchell]

Thanks for the reply. The sample here doesn't do what I was seeking. I wish to make able to make an interface where the user types in the postcode and then suburbs are shown in the list matching that postcode. As per many webpages these days when you enter a postcode.

The .dropdown property does help to force the dropdown list to appear - so thank you for that.

 

[RainLover]

I would have thought Big John's answer to be what you need.

If all you want is to have the second Dropdown to open then there is code for that.

 

[prmitchell]

Big John helped as the .dropdown forces the list to appear - so that is useful. But the interface that he provided has choosing the state which populates the second dropdown with all the suburbs of that state and then the user chooses the suburb. Whereas the more common process these days is to type in the postcode and suburbs with that postcode appear - which saves choosing the state - so there is only one selection, albeit with some typing first

 

[RainLover]

So what is the problem in changing this to suit your needs.

 

[John Big Booty]

Big John helped as the .dropdown forces the list to appear - so that is useful. But the interface that he provided has choosing the state which populates the second dropdown with all the suburbs of that state and then the user chooses the suburb. Whereas the more common process these days is to type in the postcode and suburbs with that postcode appear - which saves choosing the state - so there is only one selection, albeit with some typing first

If you look at the sample carefully you will see that the user can in fact simply type a know post code into the post code box. This will then be validated and push the relevant data back into the two combo's, if there are numerous suburbs that share the same postcode then the user will be prompted to make a selection from the list.

 

[prmitchell]

Thank you John. Yes it does as you suggest of course. Now I have to work out how. Something to do with the text box(es). But the query behind the suburbs gets the value from Combo0 which is the state not the postcode. I shall keep looking.

 

[prmitchell]

Thank you again John. I have put a text box in the form and when after the user enters a postcode into the text box the focus is set to the combo box and the .dropdown property forces that list to appear and the query behind that combo includes the criteria
[Forms]![PostcodeAndSuburbs]![pc]
where pc is the name of the text box.

I was trying to accomplish the whole process in one combo box rather than splitting into text box and combo box.

Wonderful!

 

[prmitchell]

Re: Postcodes

I have run into trouble when trying to implement the Postcode and Suburbs into another form, eg. Clients, and I am getting an error
Update or CancelUpdate without AddNew or Edit
when I try and close the form

ideas please

 

[RainLover]

Go to your query and check to see if that is updatable or not.

I have not seen your code so this is an assumption. The answer could be quite different.

Post back after testing, please.

 

[John Big Booty]

If you have no joy with RainLover's suggesting post a copy of your DB.