Automatically populate fileds from combo row source value

[morlan]

Hi all,

On one of my forms I have a post code finder. You type in a postcode and click search. I have a piece of VB code which queries a web server for all addresses within that postcode.

The web server returns a result and then the VB code parses the result and returns it to a format suitable for the row source property of a combo box.

This means that all address results are listed in the combo box.
As an example, here are five results for the EH3 9DW postcode that are returned to the row source property of the combo box..

"EH3 9DW 50/1 Lady Lawson Street, EDINBURGH";
"EH3 9DW 40-44 Lady Lawson Street, EDINBURGH";
"EH3 9DW 46-54 Lady Lawson Street, EDINBURGH";
"EH3 9DW 50/2 Lady Lawson Street, EDINBURGH";
"EH3 9DW 50/3 Lady Lawson Street, EDINBURGH";


When I click the dropdown and then (for example) click on the first result I want VB to populate txtPostcode with the post code from the result, it should then populate txtStreet with "50/1 Lady Lawson Street" and then populate txtTown with "Edinburgh".

Anyone haven any clever ideas, or can point me the right direction


THANKS!

 

[ColinEssex]

On the AfterUpdate of the ComboBox put this code

Me.TxtPostcode = Me.ComboBox.Column(0)
Me.TxtStreet = Me.ComboBox.Column(1)
Me.TxtTown = Me.ComboBox.Column(2)

Thats assuming of course that your addresses are split into columns for Postcode,Street and Town.

Col

 

[raskew]

If the Post Code returns are as consistent as your example, i.e.:
Post Code = ### ###
and the entire listing contains only one comma (just prior to the city name), the following may work for you.

From the debug window:

x = "EH3 9DW 50/1 Lady Lawson Street, EDINBURGH"
y = instr(x, ",")
myStreet = mid(x, 9, y-9)
mycity = mid(x, y + 2)
? myStreet
50/1 Lady Lawson Street
? mycity
EDINBURGH

 

[morlan]

On the AfterUpdate of the ComboBox put this code

Me.TxtPostcode = Me.ComboBox.Column(0)
Me.TxtStreet = Me.ComboBox.Column(1)
Me.TxtTown = Me.ComboBox.Column(2)

Thats assuming of course that your addresses are split into columns for Postcode,Street and Town.

Col

If I click the dropdown box there is more than one result. How do I get access to fill the various text boxes with the reult that I click?

 

[Fizzio]

Do you want to store this value in the client's record?

If so, you need to decide a couple of things.

Theoretically, you only need to store the ID of that postcode - you have in fact got a lot of duplicates that are unneccessary - (postcodes, streets, towns, city). If you go into any store all they ask for is your house number and postcode - this is how you should structure your Db.
In relation to this, the blinkin' post office occasionally changes the boundaries of postcodes (I suspect that so people have to keep 'updating' their postcode reference) so that if you do only store the ID, it may change in future!

If this is not what you want then your sting format will be a nightmare to split. Raskew has had a good shot at this but this will not work for postcodes such as NE29 8NH or S1 3XX. I would consider having a better formatted string eg
EH3 9DW, 50/1 Lady Lawson Street, EDINBURGH

then you can at least use the instr function to get the relevant chunks.

If you do not want to restructure things to a more normalised structure then look at your string structure and parse this into separate columns, then you can use the method that Col originally suggested.

hth