repost - Fill Forms fields based on control value

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: CM (cm_at_.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 19:53:03 +0200

"CM" <cm@.com> wrote in message news:40a79bc6$0$704$5402220f
> 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 Data***" <spam@nospam.spam> wrote in message
> news:YjMpc.8693$zO3.6623@newsread2.news.atl.earthlink.net...
> > 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 Data***
> >
> > "CM" <cm@.com> wrote in message
> news:40a78c76$0$721$5402220f
> > > 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" <spam@nospam.spam> wrote in message
> > > news:cGtpc.7612$zO3.6756@newsread2.news.atl.earthlink.net...
> > > > 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 Data***
> > > > Your Resource For Help With Access, Excel And Word Applications
> > > > resource@pcdata***.com
> > > > www.pcdata***.com
> > > >
> > > > "CM" <cm@.com> wrote in message
> > > news:40a64351$0$722$5402220f@
> > > > > 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

Quantcast