Repost - Forms Fill Fields automatically on form based on a control's value

[CM]

Hi Repost.... Urgent help required...Thxs !

Hi everyone,

I am trying to use the code pasted below, which is designed to allow you to
automatically select the town and county based on what postcode you enter. I
have tried to revise the code based on the database that I have but to no
avail.

I have made a table called "tbl-postcodes" which I have three fields, first
one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing seems to
work.

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tbl-postcodes", "Postcode =[Postcode] ")
varCity = DLookup("City", "tbl-postcodes", "Postcode =[Postcode] ")
If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub


My form contains the information from one table called "centralsystem" and I
have made a table called "tbl-postcodes" which has the following sample data
which I will finish off once I know it works

Field 1 is named Postcode
Field 2 is named Town
Field 3 is named County

In the rows it would be something like this...

SW1A 1AA Westminster London
PL1 1AA Plymouth Devon
BH1 1AA Bournemouth Dorset

However, when I try the postcodes in the form, I want it to automatically
pick the town and county, which is a selection in a combo box. It doesn't
save much time, I know but it all helps!

Thanks

Colin

 

[Steve]

Create a query named MyQuery based on Tbl-Postcodes. Put the fields in the query
in the order of PostCode, Town, County. Set the sort on Postcode ascending.

Create an unbound combobox Named MyCombobox on your form and set its rowsource
property as MyQuery. Set the bound column property to 1, column count to 3 and
column width 1;2;2. (you can set the column width to suit your data!) Set the
autoexpand poperty to Yes.

You then need a textbox named Town and a textbox named County. They can be bound
or unbound depending on the nature of your form.

Put the following code in the AfterUpdate event of the combobox:
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)

You will now be able to start typing a postcode in the combobox and the list
will scroll to the first postcode beginning with the characters you entered.
When you select a postcode, the town and county textboxes will automatically
fill with the town and county corresponding to the postcode you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi Repost.... Urgent help required...Thxs !

Hi everyone,

I am trying to use the code pasted below, which is designed to allow you to
automatically select the town and county based on what postcode you enter. I
have tried to revise the code based on the database that I have but to no
avail.

I have made a table called "tbl-postcodes" which I have three fields, first
one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing seems to
work.

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tbl-postcodes", "Postcode =[Postcode] ")
varCity = DLookup("City", "tbl-postcodes", "Postcode =[Postcode] ")
If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub


My form contains the information from one table called "centralsystem" and I
have made a table called "tbl-postcodes" which has the following sample data
which I will finish off once I know it works

Field 1 is named Postcode
Field 2 is named Town
Field 3 is named County

In the rows it would be something like this...

SW1A 1AA Westminster London
PL1 1AA Plymouth Devon
BH1 1AA Bournemouth Dorset

However, when I try the postcodes in the form, I want it to automatically
pick the town and county, which is a selection in a combo box. It doesn't
save much time, I know but it all helps!

Thanks

Colin

 

[CM]

Thanks Steve,

How could I do this without losing the field Town & County in the main
table, I know its not normal practice with Access to have fields
unnecessary, but I just want a simple solution for one of my forms that will
speed up input.

Therefore if they enter a postcode the first 3 or 4 digits will be
recognised and automatically fill in the fields Town and County on the form,
which will in turn fill the table with the information. I dont want it to
relate to the whole postcode otherwise I will be months inputting every
postcode....

Thanks for any advice with this...

Cheers

Colin

Create a query named MyQuery based on Tbl-Postcodes. Put the fields in the query
in the order of PostCode, Town, County. Set the sort on Postcode ascending.

Create an unbound combobox Named MyCombobox on your form and set its rowsource
property as MyQuery. Set the bound column property to 1, column count to 3 and
column width 1;2;2. (you can set the column width to suit your data!) Set the
autoexpand poperty to Yes.

You then need a textbox named Town and a textbox named County. They can be bound
or unbound depending on the nature of your form.

Put the following code in the AfterUpdate event of the combobox:
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)

You will now be able to start typing a postcode in the combobox and the list
will scroll to the first postcode beginning with the characters you entered.
When you select a postcode, the town and county textboxes will automatically
fill with the town and county corresponding to the postcode you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi Repost.... Urgent help required...Thxs !

Hi everyone,

I am trying to use the code pasted below, which is designed to allow you to
automatically select the town and county based on what postcode you enter. I
have tried to revise the code based on the database that I have but to no
avail.

I have made a table called "tbl-postcodes" which I have three fields, first
one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing seems to
work.

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tbl-postcodes", "Postcode =[Postcode] ")
varCity = DLookup("City", "tbl-postcodes", "Postcode =[Postcode] ")
If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub


My form contains the information from one table called "centralsystem" and I
have made a table called "tbl-postcodes" which has the following sample data
which I will finish off once I know it works

Field 1 is named Postcode
Field 2 is named Town
Field 3 is named County

In the rows it would be something like this...

SW1A 1AA Westminster London
PL1 1AA Plymouth Devon
BH1 1AA Bournemouth Dorset

However, when I try the postcodes in the form, I want it to automatically
pick the town and county, which is a selection in a combo box. It doesn't
save much time, I know but it all helps!

Thanks

Colin

 

[PC Datasheet]

Colin,

Try this ----

Rather than put any code in the combobox's Afterupdate event, put the following
code in the combobox'x On Change event:

If Len(Me!MyCombobox) >= 3 Then
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)
End If

Nothing will happen when you enter the first two characters of the postcode.
When you enter the third character, Town and County will automatically fill with
the Town and County associated with the first postcode that begins with the
three characters that were entered. If the Town and County are not the ones you
want, enter a fourth character and Town and County will automatically fill with
the Town and County associated with the first postcode that begins with the four
characters that were entered. You can continue to enter characters for the
postcode and Town and Country will keep changing at each character.

Steve
PC Datasheet

Thanks Steve,

How could I do this without losing the field Town & County in the main
table, I know its not normal practice with Access to have fields
unnecessary, but I just want a simple solution for one of my forms that will
speed up input.

Therefore if they enter a postcode the first 3 or 4 digits will be
recognised and automatically fill in the fields Town and County on the form,
which will in turn fill the table with the information. I dont want it to
relate to the whole postcode otherwise I will be months inputting every
postcode....

Thanks for any advice with this...

Cheers

Colin

Create a query named MyQuery based on Tbl-Postcodes. Put the fields in the query
in the order of PostCode, Town, County. Set the sort on Postcode ascending.

Create an unbound combobox Named MyCombobox on your form and set its rowsource
property as MyQuery. Set the bound column property to 1, column count to 3 and
column width 1;2;2. (you can set the column width to suit your data!) Set the
autoexpand poperty to Yes.

You then need a textbox named Town and a textbox named County. They can be bound
or unbound depending on the nature of your form.

Put the following code in the AfterUpdate event of the combobox:
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)

You will now be able to start typing a postcode in the combobox and the list
will scroll to the first postcode beginning with the characters you entered.
When you select a postcode, the town and county textboxes will automatically
fill with the town and county corresponding to the postcode you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi Repost.... Urgent help required...Thxs !

Hi everyone,

I am trying to use the code pasted below, which is designed to allow you to
automatically select the town and county based on what postcode you enter. I
have tried to revise the code based on the database that I have but to no
avail.

I have made a table called "tbl-postcodes" which I have three fields, first
one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing seems to
work.

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tbl-postcodes", "Postcode =[Postcode] ")
varCity = DLookup("City", "tbl-postcodes", "Postcode =[Postcode] ")
If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub


My form contains the information from one table called "centralsystem" and I
have made a table called "tbl-postcodes" which has the following sample data
which I will finish off once I know it works

Field 1 is named Postcode
Field 2 is named Town
Field 3 is named County

In the rows it would be something like this...

SW1A 1AA Westminster London
PL1 1AA Plymouth Devon
BH1 1AA Bournemouth Dorset

However, when I try the postcodes in the form, I want it to automatically
pick the town and county, which is a selection in a combo box. It doesn't
save much time, I know but it all helps!

Thanks

Colin

 

[CM]

Hi Steve,

Tried this, but how do I ensure I keep the postcode typed in?? at the moment
the record source is set to the postcode from the main table. If I change
this to a combobox, would it lose this information?

Thanks for all your help!

I am interested to find a solution, as I could use the code for similar
things on other forms...

Cheers

Colin

Colin,

Try this ----

Rather than put any code in the combobox's Afterupdate event, put the following
code in the combobox'x On Change event:

If Len(Me!MyCombobox) >= 3 Then
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)
End If

Nothing will happen when you enter the first two characters of the postcode.
When you enter the third character, Town and County will automatically fill with
the Town and County associated with the first postcode that begins with the
three characters that were entered. If the Town and County are not the ones you
want, enter a fourth character and Town and County will automatically fill with
the Town and County associated with the first postcode that begins with the four
characters that were entered. You can continue to enter characters for the
postcode and Town and Country will keep changing at each character.

Steve
PC Datasheet

Thanks Steve,

How could I do this without losing the field Town & County in the main
table, I know its not normal practice with Access to have fields
unnecessary, but I just want a simple solution for one of my forms that will
speed up input.

Therefore if they enter a postcode the first 3 or 4 digits will be
recognised and automatically fill in the fields Town and County on the form,
which will in turn fill the table with the information. I dont want it to
relate to the whole postcode otherwise I will be months inputting every
postcode....

Thanks for any advice with this...

Cheers

Colin

Create a query named MyQuery based on Tbl-Postcodes. Put the fields in

the
query

in the order of PostCode, Town, County. Set the sort on Postcode ascending.

Create an unbound combobox Named MyCombobox on your form and set its rowsource
property as MyQuery. Set the bound column property to 1, column count

to 3
and

column width 1;2;2. (you can set the column width to suit your data!)

Set
the

autoexpand poperty to Yes.

You then need a textbox named Town and a textbox named County. They

can be
bound

or unbound depending on the nature of your form.

Put the following code in the AfterUpdate event of the combobox:
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)

You will now be able to start typing a postcode in the combobox and

the
list

will scroll to the first postcode beginning with the characters you entered.
When you select a postcode, the town and county textboxes will automatically
fill with the town and county corresponding to the postcode you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi Repost.... Urgent help required...Thxs !

Hi everyone,

I am trying to use the code pasted below, which is designed to allow

you
to

automatically select the town and county based on what postcode you enter. I
have tried to revise the code based on the database that I have but

to
no

avail.

I have made a table called "tbl-postcodes" which I have three

fields,
first

one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing

seems
to

work.

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tbl-postcodes", "Postcode

=[Postcode]
")

varCity = DLookup("City", "tbl-postcodes", "Postcode =[Postcode] ")
If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub


My form contains the information from one table called

"centralsystem"
and I

have made a table called "tbl-postcodes" which has the following

sample
data

which I will finish off once I know it works

Field 1 is named Postcode
Field 2 is named Town
Field 3 is named County

In the rows it would be something like this...

SW1A 1AA Westminster London
PL1 1AA Plymouth Devon
BH1 1AA Bournemouth Dorset

However, when I try the postcodes in the form, I want it to automatically
pick the town and county, which is a selection in a combo box. It doesn't
save much time, I know but it all helps!

Thanks

Colin

 

[CM]

Hi Steve,

Tried this, but how do I ensure I keep the postcode typed in?? at the moment
the record source is set to the postcode from the main table. If I change
this to a combobox, would it lose this information?

Thanks for all your help!

I am interested to find a solution, as I could use the code for similar
things on other forms...

Cheers

Colin

Colin,

Try this ----

Rather than put any code in the combobox's Afterupdate event, put the following
code in the combobox'x On Change event:

If Len(Me!MyCombobox) >= 3 Then
Me!Town = Me!MyCombobox.Column(1)
Me!County = Me!MyCombobox.Column(2)
End If

Nothing will happen when you enter the first two characters of the postcode.
When you enter the third character, Town and County will automatically fill with
the Town and County associated with the first postcode that begins with the
three characters that were entered. If the Town and County are not the ones you
want, enter a fourth character and Town and County will automatically

fill

with
the Town and County associated with the first postcode that begins with the four
characters that were entered. You can continue to enter characters for the
postcode and Town and Country will keep changing at each character.

Steve
PC Datasheet

that

will

in

the

count
to 3 data!)

Set

allow

you

but

to

no
avail.

I have made a table called "tbl-postcodes" which I have three fields,
first
one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing seems
to
work.

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tbl-postcodes", "Postcode =[Postcode]
")
varCity = DLookup("City", "tbl-postcodes", "Postcode

=[Postcode]

")

If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub


My form contains the information from one table called "centralsystem"
and I
have made a table called "tbl-postcodes" which has the following sample
data
which I will finish off once I know it works

Field 1 is named Postcode
Field 2 is named Town
Field 3 is named County

In the rows it would be something like this...

SW1A 1AA Westminster London
PL1 1AA Plymouth Devon
BH1 1AA Bournemouth Dorset

However, when I try the postcodes in the form, I want it to
automatically
pick the town and county, which is a selection in a combo box. It
doesn't
save much time, I know but it all helps!

Thanks

Colin