key postcode - retrieve suburbs

[BarryDalHerbert]

New to Access, and using Access 2003 to enter names and addresses from paper
forms hand-written by people becoming members. To speed entry, I want the
data entry operator to be able to key a four digit numeric Australian
postcode, and have just the matching suburb names displayed for the data
entry operator to make the appropriate selection. I have a table of postcodes
and suburbs. Am I completely daft, or does the built-in wizard's powers not
extend this far? It seems like a basic requirement to me, but does it need
special coding to make it happen?

 

[John Spencer]

Does any one postcode have only one suburb (Case one) or can a postcode
refer to more than one suburb (Case 2)?

The first case is very simple - just store the postcode and don't store
the suburb in the member record.

The second case is not too difficult. I would add an unbound combobox
to the entry form and populate it after the entry of the postcode.

Then I would code the after update event of the postcode control with

Dim StrSQL
StrSQL = "SELECT Suburb From PostCodeTable WHERE Postcode = " & Chr(34)
& Me.TxtPostCodeControl & Chr(34)

Me.PostcodeCombobox.RowSource = strSQL
Select case Me.PostcodeCombobox.rowCount
Case 0 'No match found
MsgBox "Invalid or missing postcode"
Case 1 'Only one match
Me.SuburbControl = me.combobox.ItemData(0)
Me.Suburbcontrol.setfocus
Case else
'If multiple choices set focus to combobox
'And optionally set a default value in the
'Suburbcontrol
Me.SuburbControl = me.combobox.ItemData(0)
Me.PostCodeCombobox.setfocus
End Select


You would need to code the after update event of the combobox to
populate the SuburbControl.
Me.SuburbControl = me.PostcodeComboBox


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[BarryDalHerbert via AccessMonster.com]

Your "case 1" would have been one of the intelligent options available when
planning the system, but "they" left the decision making to dimwits, so your
"case 2" is they way that it is John.

Your solution is jumping in at the deep end for me, but I am going to have to
get in there sooner or later, so here goes...

And very many thanks for the astoundingly fast response too!

BdH
Canberra

Does any one postcode have only one suburb (Case one) or can a postcode
refer to more than one suburb (Case 2)?

The first case is very simple - just store the postcode and don't store
the suburb in the member record.

The second case is not too difficult. I would add an unbound combobox
to the entry form and populate it after the entry of the postcode.

Then I would code the after update event of the postcode control with

Dim StrSQL
StrSQL = "SELECT Suburb From PostCodeTable WHERE Postcode = " & Chr(34)
& Me.TxtPostCodeControl & Chr(34)

Me.PostcodeCombobox.RowSource = strSQL
Select case Me.PostcodeCombobox.rowCount
Case 0 'No match found
MsgBox "Invalid or missing postcode"
Case 1 'Only one match
Me.SuburbControl = me.combobox.ItemData(0)
Me.Suburbcontrol.setfocus
Case else
'If multiple choices set focus to combobox
'And optionally set a default value in the
'Suburbcontrol
Me.SuburbControl = me.combobox.ItemData(0)
Me.PostCodeCombobox.setfocus
End Select

You would need to code the after update event of the combobox to
populate the SuburbControl.
Me.SuburbControl = me.PostcodeComboBox

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

New to Access, and using Access 2003 to enter names and addresses from paper
forms hand-written by people becoming members. To speed entry, I want the

[quoted text clipped - 4 lines]

extend this far? It seems like a basic requirement to me, but does it need
special coding to make it happen?