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

  • Thread starter Thread starter CM
  • Start date Start date
C

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
 
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

CM said:
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
Click to expand...
 
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



Steve said:
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
Click to expand...
Click to expand...
 
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

CM said:
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



Steve said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

PC Datasheet said:
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



Steve said:
Create a query named MyQuery based on Tbl-Postcodes. Put the fields in
Click to expand...
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
Click to expand...
to 3
and
column width 1;2;2. (you can set the column width to suit your data!)
Click to expand...
Set
the
autoexpand poperty to Yes.

You then need a textbox named Town and a textbox named County. They
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
to
no
avail.

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

I have tried the code listed underneath the original, but nothing
Click to expand...
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
Click to expand...
=[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
Click to expand...
"centralsystem"
and I
have made a table called "tbl-postcodes" which has the following
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
CM said:
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

PC Datasheet said:
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
Click to expand...
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
Click to expand...
that
will
Click to expand...
in
the
Click to expand...
count
to 3 data!)
Set
Click to expand...
allow
you
Click to expand...
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
Click to expand...
Click to expand...
=[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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top