I'm assuming you don't want the data in the referenced PostCodes table to
change if you change an address in the form from that looked up in the
PostCodes table. In this case you'll need to have columns in the form's
underlying table to store the address data and bind controls on the form to
that data. For the postcode column in the form's table use a combo box on
the form with a RowlSource such as:
SELECT PostCode, Street, Town, County
FROM PostCodes
ORDER BY PostCode;
Set the combo box's ColumnCount property to 4. make sure its AutoExpand
property is set to True (Yes) so that when a code is typed into the control
the first match will be selected from its list as each character is entered.
In its AfterUpdate event procedure assign values to the controls on the form
bound to the Street, Town and County columns in its underlying table:
Dim ctrl As Control
Set ctrl = Me.ActiveControl
Me.Street = ctrl.Column(1)
Me.Town = ctrl.Column(2)
Me.County = ctrl.Column(3)
The Column property is zero-based, so Column(1) is the second column
(Street) of the combo box's RowSource query, and so on.
If a postcode is not already in the postcodes table you can add one via the
combo box by putting code along these lines in its NotInList event procedure
to add the new code typed in and open a frmPostCodes form bound to the
PostCodes table to add the address data:
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add new post code to list?"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmPostCodes", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure postcode has been added
If Not IsNull(DLookup("PostCode", "PostCodes", "PostCode = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Post Codes table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
In frmPostCodes Open event procedure put:
If Not IsNull(Me.OpenArgs) Then
Me.PostCode.DefaultValue = """" & Me.OpenArgs & """"
End If
Ken Sheridan
Stafford, England
