Maureen :
You need to decompose the table so that the address data is in a separate
table. Firstly you need to establish to your satisfaction which columns
(fields) from the current table have the same values when the phone number is
the same. Lets say for example that the Company and Address columns will
always be the same for a particular phone number. You should create a table,
Companies say, with columns Phone Number, Company and Address.
With the situation you describe the Phone Number column is a 'candidate key'
of this table so can be defined as its primary key. Its unusual for a phone
number to be a key in this way as often a company will have more than one
phone number, but if you are happy with there being only one per company then
there is no reason not to make it the primary key.
Once you have this new empty Companies table you can fill it with an
'append' query based on your existing table (which I'll call YourTable for
this example) like so:
INSERT INTO Companies ([Phone Number], Company, Address)
SELECT DISTINCT [Phone Number], Company, Address
FROM YourTable;
This relies on the data in your current table being consistent, i.e. for
every value of Phone Number the values of Company and Address are always
exactly the same. If not then only one row per phone number will be
inserted, which is fine but the choice of which one will be arbitrary.
Create a form, frmCompanies say, bound to the Companies table.
Once you are happy with the contents of the new table you can delete the
redundant Company and Address columns from your current table. One other
thing you should do is index the Phone Number column in your current table
non-uniquely (duplicates allowed).
You can now create a relationship between the two tables on the Phone Number
column. Enforce referential integrity in the relationship.
Base your data entry form on your amended current table, and use a combo box
for the Phone Number, setting it up with properties like so:
Name: cboPhoneNumber
ControlSource: [Phone Number]
RowSource: SELECT [Phone Number], Company, Address FROM Companies ORDER
BY [Phone Number];
BoundColum: 1
ColumnCount: 3
LimitToList: Yes
AutoExpand: Yes
Now add two unbound text boxes to the form with ControlSource properties as
follows:
=[cboPhoneNumber].[Column](1)
=[cboPhoneNumber].[Column](2)
The Column property is zero-based so these reference the second (Company)
and third (Address) columns of the combo box.
When you select (or enter) an existing phone number in the combo box the
company and address for that phone number will automatically show in the two
unbound text boxes. To cater for new phone numbers, and hence new companies,
you can put the following code in the cboPhoneNumber control's NotInList
event procedure:
Dim ctrl As Control
Dim strMessage As String
Dim strCriteria As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"
strCriteria = "[Phone Number] = """ & NewData & """"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCompanies", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCcompanies closed
DoCmd.Close acForm, "frmCompanies"
' ensure city has been added
If Not IsNull(DLookup("[Phone Number]", "Companies", strCriteria))
Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Companies table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
In the Open event procedure of the frmCompanies form which you created put
the following code:
If Not IsNull(Me.OpenArgs) Then
Me.[Phone Number].DefaultValue = """" & Me.OpenArgs & """"
End If
When you enter a new phone number in the combo box on your form you'll be
prompted to confirm that you wish to add the new number, and then the
companies form will open at a new record with the phone number already in
place. You can then add the new Company and Address data, and when you close
the companies form you'll be returned to the combo box on the first form with
the new phone number in palace and the company and address data in the two
unbound controls.
One thing to be aware of is that you must enter some data besides the phone
number in the companies form to create the new record (even if you
immediately delete what you enter it will create the record with just the
phone number in it, but I doubt you'd want to do that).
Ken Sheridan
Stafford, England
