Full Version: Postcode Local Authority Look Up Form
UtterAccess Forums > Microsoft® Access > Access Forms
Kento
Hi All,
’m building a lookup form – where on entering a postcode the Local Authority (and a few other fields) are automatically populated.
SELECT tblPostCodeData.PostCode, LocalAuthorityLookUp.LocalAuthority, tblCountyDistrictLookUp.CountyDistrict, tblLocalAuthorityDescriptionLookUp.Description
FROM tblLocalAuthorityDescriptionLookUp RIGHT JOIN (tblCountyDistrictLookUp RIGHT JOIN (LocalAuthorityLookUp RIGHT JOIN tblPostCodeData ON LocalAuthorityLookUp.LocalAuthorityCode=tblPostCodeData.LocalAuthorityCode) ON tblCountyDistrictLookUp.CountyDistrictCode=LocalAuthorityLookUp.CountyDistrictCo
de) ON tblLocalAuthorityDescriptionLookUp.AreaTypeCode=LocalAuthorityLookUp.AreaTypeCod
e;
The text box where the postcode is entrerd is called: [txtEnterPostCode] and where I want the local authority name to appear: [txtLocalAuthority]
My VBA skills are v. limited - but I'm keen to learn. Any further info required/copy of dbase let me know.
Cheers,
Sam.
dashiellx2000
The easiest way to do this would be to change the text box were you enter the post code to a combo box. Set up the row source of the combo box to include the the post code and then the other fields containing the data you want populated into the text boxes. Then on the after update event of the combo box put code as follows:
!--c1-->
CODE
Me.txtTextBoxName = Me.cboComboBoxName.Column(x)

Change the x to match the column of the combo box you want the data to populate (note: combo box columns start numbering from 0, so the third column is actually column 2).
HTH.
Kento
Thank you dashiellx2000,
This works a treat.
Omay have a further problem now. I want to populate lots more fields (on tabbed sheets) based on the Local Authority and another field selected from a combo (grant type). The table I'm going to do this with houses the information relating to individual grants and the fields on tabbed sheets refer to each grant.
I'm really struggling to think how this can be done as one grant can span many local authority areas and thus there's not local authority code associated with each individual grant. I have however made a column for each local authroity area and if the grant is operating in that area is has a -1 and if not a 0. Could this be referenced somehow and the new fields populated by the table depedning on the local authority name listed in the text box and the grant type combo?
Any ideas?
Let me know if any more detail is required,
Cheers,
Sam.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.