Populating fields on a form using another.

[chantil]

I am currently designing a form for a database that I have been renovating. There are a number of fields in the database which define different geographical areas (such as Stategic Health Authority and Primary Care Trust) and I am trying to find out if there is a way to populate these fields by postcode when a postcode is entered onto the form.

I have a table connecting all the possible postcodes to these defined geographical areas but I do not know how to link this to the fields in the form.

I would be most grateful for any suggestions people can give me.

Chantil

 

[RivetHed]

If you can already link all the post codes to the relevant PCt or SHA then there is no reason to then enter those details into a table again. Just build a query linked by post code to show the SHA & PCT and have that as the record source of your form rather than just your main table.

 

[chantil]

Unfortunately for reasons beyond my control I have to have these fields included in my main table and so I want to make as little work as possible for our data entry clerk.

 

[fneily]

If you are saying that the words Stategic Health Authority and Primary Care Trust have to be in two tables, then your tables are not Normalized - you should not have duplicate data. In a relational database, such as Access, your tables must be normalized first. You would then use RivetHed's suggestion. If you're going to use Access, then there are rules you must follow, one of which is normalization.
As far as being beyond your control, then find someone in control to correct the database.
There will be no effect on your data entry clerk if it is constructed correctly.

 

[RivetHed]

Having worked with NHS datasets before I know that full normalisation isn't always doable with the frequently appaling data that is supplied to you and the at times bizarre demands from management.

You could check out the DLookup function and stick that into the after update event of the postcode field to auto populate the SHA and PCT fields.

However as fneily said you really should push to design the database within normal practices and only look to use the above solution if you have no other option.

 

[chantil]

Thanks for your help, I'll give your suggestions a go and hopefully I'll come up with an answer.