Simple query/form help?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 5 columns AND 1,605,822 records in ACCESS 2003:

Postcodes X Y Postocode_A Postcode_Town

AB10 1AF 394100 806400 AB10 AB

PR4 5HL 349300 426200 PR4 PR

YO95 1BT 477900 448400 YO95 YO

I want to have a way of an end user typing a postcode in such as:
Enter postcode: AB10 1AF
And the form would then give the answer:
Postcode : AB10 1AF
X: 394100 Y: 806400

Is this possible? If so can you tell me how or where to go to get a
solution?

Thanks in anticipation
Liz
 
Look in the Help file for creating a recordset, and then using the Seek or
FindFirst methods of the recordset object. You will use Seek or FindFirst to
find the record containing AB10 1AF and then use:
XToFind = Rst!X
YToFind = Rst!Y
to get X and Y where Postcode = AB10 1AF.

Also look at the DLookup function in the Help file to see if you want to use
that.
 
This is pretty easy once you have an understanding of database
structure. A couple of points come to mind
- All Forms and Reports should be based on a Query, not a table
- Understanding and being familiar with Queries is vital to get things
like this done.

In this case, your tables would be structured like this

tblMain
------
..... all your usual fields ...
Postcode Number Type Foreign Key

tblPostcode
----------
Postcodes Number Primary Key
X Text
Y Text
Postcode_A Text
Postcode_Town Text

To get the two synchronised you use a query which has both tables in
the query with the field called Postcode coming from the *Main* table
and the other 4 fields coming from the *Postcode* table. You make
sure that the two Postcode Fields are joined with a Relationship Line
(you can drag and drop from one to the other)

You then transfer those fields to the Form. Then, as you enter the
postcode in the Form, you're entering it into the Main table's
Postcode field but the query is looking up the "related" fields for
that postcode in the Postcode table and filling them in. To stop
people messing with the filled in data, you make those
Controls(Fields) on the form Locked and not Enabled.

Brett



I have a table with 5 columns AND 1,605,822 records in ACCESS 2003:

Postcodes X Y Postocode_A Postcode_Town

AB10 1AF 394100 806400 AB10 AB

PR4 5HL 349300 426200 PR4 PR

YO95 1BT 477900 448400 YO95 YO

I want to have a way of an end user typing a postcode in such as:
Enter postcode: AB10 1AF
And the form would then give the answer:
Postcode : AB10 1AF
X: 394100 Y: 806400

Is this possible? If so can you tell me how or where to go to get a
solution?

Thanks in anticipation
Liz
Click to expand...

Cheers,
Brett
 
Back
Top