Automatic Postcode Fill

[evilcowstare via AccessMonster.com]

Hi,
I have a form which has a Address drop-down box and a seperate postcode box.
what I want to happen is that whenever an address is selected from the
dropdown box, the postcode is automatically entered with the one stored
against the address id.

A quick run through of how its set up..

address table (id,address,postcode,contactname,contactno ---> Query (
contains Address, postcode) ----> Form (address dropdown box using row source
to query)
Postcode is in a txt box.

Everything is saved to a Main table after

any ideas?

Thank You

 

[Guest]

Hi

Use the build option (...) to create a query to base the combo on. Include
the postcode in the query.

Set the control source of the text box as =[ComboName].column.2

Note - I have use "2" (the 3rd column) only as an example use whatever
column the postcode is in

 

[evilcowstare via AccessMonster.com]

Hi, I think I have done everything you said but its not working I just get
#Name? appearing in the postcode box.

I have a query that contains "Address Name" and "Postcode" only

The Combo "AddressCombo" gets its info from the "Address Name" of the query.

The postcode had a control source pointing to "postcode" but in my main table
of data, I changed this to =[AddressCombo].column.2

but didnt work, I have also tried column.1, tried changing around the dots
incase. but still nothing

Anything im missing?

Hi

Use the build option (...) to create a query to base the combo on. Include
the postcode in the query.

Set the control source of the text box as =[ComboName].column.2

Note - I have use "2" (the 3rd column) only as an example use whatever
column the postcode is in

Hi,
I have a form which has a Address drop-down box and a seperate postcode box.

[quoted text clipped - 14 lines]

Thank You

 

[Anthos]

This is also a good place to use the DLookup Command.

In this instance.

=Dlookup("[postcode]", "address table", "[id]=" &
Me.AddressDropDown.Value)

Then run a requery on the Control when the Address Drop down is
loaded.
(This is assuming the ID is the primary key, and you are using this as
the bound value in the Drop Down list)

 

[evilcowstare via AccessMonster.com]

Im still getting the same in the postcode box, it isnt finding anything. I do
have an ID as a primary key in the main Address table.

Thank you for replying

This is also a good place to use the DLookup Command.

In this instance.

=Dlookup("[postcode]", "address table", "[id]=" &
Me.AddressDropDown.Value)

Then run a requery on the Control when the Address Drop down is
loaded.
(This is assuming the ID is the primary key, and you are using this as
the bound value in the Drop Down list)

 

[Anthos]

What is the Drop Down box called on your form?
What is the Postcode Field Called on your form?

 

[evilcowstare via AccessMonster.com]

Hi

The Drop Down is called "AddressCombo"

Postcode field in the form is called "postcode box"

 

[Allen Browne]

There's an example similiar to what you are doing here:
Combos with Tens of Thousands of Records - Postcodes example
at:
http://allenbrowne.com/ser-32.html

 

[Anthos]

Private Sub AddressCombo_AfterUpdate()
Me.PostCode_Box.Value = DLookup("[postcode]", "address table",
"[id]=" & Me.AddressCombo.Value)
End Sub

Put that code into the AddressCombo's after Update procedure.

This should work.
(Providing the table name, address table, is correct)

 

[evilcowstare via AccessMonster.com]

I tried, it just comes up all red and keeps saying error, it really seems to
not like the [id] part?

Does it matter that the drop-box is getting its info via a query rather then
directly from the table.

The main table is called "Site Addresses"
The Query is called "Site Addresses Query"
The postcode text area in the form is called "postcode box"
The Drop-down box that contains the main address info is called
"AddressCombo"

The query contains just "Site Address" and "Postcode"
The main table contains ID, Site Address, Postcode, Contact Name, Contact
Number

The drop down "AddressCombo" gets its info from "Site Adresses Query"

I need "postcode box" to display the postcode from the same ID which was
selected from "AddressCombo"

Would it be better for me to add the ID also to the query "Site Addresses
Query"


Just trying to write it as simple as poss as at this time of night, its easy
to miss something so hopefully I would have explained it better this time
round. If it still doesnt help then thanks for the time you have put to help
me, hopefully itll work at some point

Private Sub AddressCombo_AfterUpdate()
Me.PostCode_Box.Value = DLookup("[postcode]", "address table",
"[id]=" & Me.AddressCombo.Value)
End Sub

Put that code into the AddressCombo's after Update procedure.

This should work.
(Providing the table name, address table, is correct)

 

[evilcowstare via AccessMonster.com]

I'll have a look, thank you.
Im really a complete novice when it comes to codes so I get lost fairly
quickly but hopefully might pick something up ;-)

There's an example similiar to what you are doing here:
Combos with Tens of Thousands of Records - Postcodes example
at:
http://allenbrowne.com/ser-32.html

Hi,
I have a form which has a Address drop-down box and a seperate postcode

[quoted text clipped - 16 lines]

Thank You

 

[Anthos]

The reason for the error is this.

The query contains just "Site Address" and "Postcode"
The main table contains ID, Site Address, Postcode, Contact Name,
Contact
Number


Becuase there the drop down list isn't actually bound to the ID, the
DLookup function isn't working.

If you put in the ID field in the drop down list as well, bound the
drop down list to that, but make the first column invisible (column
widths = 0;3;2) then this Dlookup function will work.

 

[evilcowstare via AccessMonster.com]

Hi I have tried, I keep getting Syntax Error.
I now have the ID in the drop down, just hidden.

The postcode field in the SiteAddress table is called "postcode"
and the combo in the form which contains the addresses is called
"AddressCombo"
The box to contain the postcode in the form is called "PostCode Box"

I put the following code into the VBE for After Update in "AddressCombo"

Private Sub AddressCombo_AfterUpdate()
Me.PostCode_Box.Value = DLookup("[postcode]", "SiteAddress",
"[id]=" & Me.AddressCombo.Value)
End Sub

Not sure what Im doing wrong still.

Thank You for all your help!

 

[Guest]

I just noticed Wayne's post had a typo.... Since you said you have two fields
in the combo box, it should be:

=[AddressCombo].column(1)


Or (what I would use), you could use:
'-----beg code-----------
Private Sub AddressCombo_AfterUpdate()
Me.PostCode_Box = Me.AddressCombo.column(1)
End Sub
'-----end code-----------


Change "Me.PostCode_Box" to the name of your text box.
Notice the underscore in the above line. You really souldn't use spaces in
object names


You don't need to use the "DLOOKUP()" function - that is what the query in
the combo box rowsource does.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi I have tried, I keep getting Syntax Error.
I now have the ID in the drop down, just hidden.

The postcode field in the SiteAddress table is called "postcode"
and the combo in the form which contains the addresses is called
"AddressCombo"
The box to contain the postcode in the form is called "PostCode Box"

I put the following code into the VBE for After Update in "AddressCombo"

Private Sub AddressCombo_AfterUpdate()
Me.PostCode_Box.Value = DLookup("[postcode]", "SiteAddress",
"[id]=" & Me.AddressCombo.Value)
End Sub

Not sure what Im doing wrong still.

Thank You for all your help!

The reason for the error is this.

The query contains just "Site Address" and "Postcode"
The main table contains ID, Site Address, Postcode, Contact Name,
Contact
Number

Becuase there the drop down list isn't actually bound to the ID, the
DLookup function isn't working.

If you put in the ID field in the drop down list as well, bound the
drop down list to that, but make the first column invisible (column
widths = 0;3;2) then this Dlookup function will work.

 

[evilcowstare via AccessMonster.com]

Hi Steve,
Thanks a million, the beg code worked 1st time.
Really straight forward and easy to read and change for future bits.

Thanks Again, thats taken away a lil stress for a while

Also thanks to everyone else that helped, it was really appreciated !

I just noticed Wayne's post had a typo.... Since you said you have two fields
in the combo box, it should be:

=[AddressCombo].column(1)

Or (what I would use), you could use:
'-----beg code-----------
Private Sub AddressCombo_AfterUpdate()
Me.PostCode_Box = Me.AddressCombo.column(1)
End Sub
'-----end code-----------

Change "Me.PostCode_Box" to the name of your text box.
Notice the underscore in the above line. You really souldn't use spaces in
object names

You don't need to use the "DLOOKUP()" function - that is what the query in
the combo box rowsource does.

HTH

Hi I have tried, I keep getting Syntax Error.
I now have the ID in the drop down, just hidden.

[quoted text clipped - 28 lines]