Address validation

[systemx]

Hi all,

Just a little advice on how to approach a problem.

I have some simple address validation in an unbound form. When the user types in a suburb, I have a DLOOKUP function that returns the appropriate postcode for that suburb.

My problem is that most suburbs have more than one postcode (standard and PO BOX).

How can I return both values?

My intention is to then allow the user to select the appropriate postcode.

Thanks

Robert

 

[john471]

It might be more efficient to have the user type in the postcode (easier to type correctly than a suburb name), then use a cascading combo box to select the suburb from the list of matching results. You could also do it the other way around, but this might result in frustration with difficult to spell or long suburb names.

Search the forum for Cascading Combo Boxes - there are many threads and examples.

HTH

Regards

John

 

[systemx]

Hi John,

Thanks for the suggestion - the cascading combobox would make a lot of sense and may be the sensible way forward if I cannot achieve the desired effect.

I have the same function working on both the suburb and postcode - and to make things more difficult I have just realised that each postcode may have multiple suburbs (up to five in some cases).

I am hoping to achieve a certain look and feel with this form. The concept I had in mind, was when the text lost focus (either postcode or suburb), a small modal form would expand from the relevant control (postcode if suburb is typed, and vice versa) listing any matches. The user would then be able to select the relevant value from the list.

I'm thinking this is probably most easily achieved with a query. If you can use DLOOKUP or even ADO to pull multiple records (I have only used ADO to find single records) I am keen to learn how this can be done!

Thanks

Rob

 

[gemma-the-husky]

i think this is a deceptively difficult project, depending on the geographical area you need to cover.

personally, i would have thought starting from the suburb name would make the most sense. A user is more likely to know a city/suburb as a starting point, than a partial post code.

so you probably want a suburbs table, maybe linked to a main city, eg if you are in the uk - start with Birmingham, then pick an appropriate suburb from the birmingham list, and then offer the appropriate postcode(s) for that suburb.

where it gets complex though, is that there are rural areas that wont easily fit into this model - eg you may need to start with warwickshire, rather than birmingham, and this may not immediately apparent.

--------------------------
i think you need to be quite sure therefore, before you start that the suburb/city/postcode data structure will work in all cases.

 

[systemx]

Well I have had some early success.

I have two basic select queries picking SUBURB and POSTCODE from tblPostalValidation (one for each control)

Each query has a LIKE expression referring back to the control on the form -

Like "*" & Forms!MyControl & "*"

I have made a small form, no control box, no borders which opens when my control loses the focus.

The form is modal, forcing the user to select the appropriate suburb/postcode. The original customer form is then populated with the details from the selection.

Once I get the form animations, positioning, etc to it looks 'semi-professional' I will post an example (perhaps on the weekend).

Cheers,

Rob

 

[neileg]

Where are you based? Postal codes work differently in various countries.

 

[systemx]

Hi,

I'm in Western Australia. Our postal codes nationwide are 4 digit numbers...so very easy to work with

Cheers.