Answers embedded.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Perplexed said:
Thanks Allen for the reply.
This info was a good start in assisting with my table relationships but
doesn't exactly fit my scenario. I didn't provide enough clarification
trying to keep my post short; however. . .
I'm actually trying to setup a database of Customers (who are passengers
on
a bus). As explained before, in order to not duplicate customers we want
to
use the phone number as a "unique" identifier. (However, there will be
times
when someone in the same household is a unique customer as well.) I
established this table as:
CustomerName:
Address: (which will be a mailing address and always a PO Box)
City: (I have a limited number of entries that will be
utilized)
ZipCode: (I would like to have this data automatically filled in
AND STORED in the customer table) from a lookup from another table (which
I
get to work on the form but it doesn't enter the data into the table
Phone:
Date of Suspension:
Date of Reinstatement: (would like this to calculate 30 days after the
date
of suspension)
Add a CustomerID (AutoNumber) field. Primary key.
You cannot use Phone as primary key, because it is not unique.
Regarding whether to store the date of reinstatement and how to do that,
see:
Calculated fields
at:
http://members.iinet.net.au/~allenbrowne/casu-14.html
Use the AfterUpdate event of City to lookup the zip code. Use DLookup().
Details in:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html
BTW, we usually split the CustomerName into 2 fields (first name and
surname). Searches, filters, and sorting are more powerful and efficient
that way.
I have created second table called Appointments -- consisting of:
Phone:
Date:
Time:
Cancelled: (as a yes/no checkbox)
Reason: (lookup list)
I have created a relationship between the two based on the phone number
fields (also made the phone number the primary key in the Customer table).
The relationship based on phone number is no good. It does not uniquely
identify the customer. Remove the Phone field from the Appointments table,
and replace it with CustomerID (Number), so you have a relationship that
uniquely identifies the person.
Even the name + phone number is not unique: it's not unusual to have a
father and son with the same name, at the same address, with the same phone
number.
After doing that, open the Relationships window (Tools menu), and create a
relationship between the two tables based on CustomerID. Check the box for
referential integrity.
In your form, you can use a combo box to *display* the phone number, but
store the CustomerID. Give the combo these properties:
Control Source CustomerID
Row Source SELECT CustomerID, Phone, CustomerName
FROM Customers ORDER BY Phone;
Bound Column 1
Column Count 3
Column Widths 0;1;0
Because the CustomerID column is zero-width, Access will display the next
column.
I made a form with the appointments as a subform. Any existing data and
corresponding appointments display IF the two tables already have data
entered. I can input a new record for each table IF the phone number is
unique. However, IF I display each record and type into any field, it
just
changes the data and then I get an error if I've duplicated the phone
number.
I'd also like the form screen to start with a "new record" or blank rather
than record #1 of the customer table.
To open the Appointment for to a new record, set its Data Entry property to
Yes. Alternatively (if you want the old data to be available also), add this
to the Load event procedure of the form:
Private Sub Form_Load()
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
End Sub
You need to distinguish between *finding* data and *entering* data. Don't
try to use the same text boxes for both. Typically what we do is to add some
unbound boxes to the Form Header section (View menu when in form design),
and make the a different color (yellow) so it is really obvious to the user
that these are for navigation and the white ones in the detail section are
for editing.
There is an example of how use an unbound combo in this link:
Using a Combo Box to Find Records
at:
http://members.iinet.net.au/~allenbrowne/ser-03.html
This combo could display and by sorted by phone number if you wish (like the
example given above).
You mentioned you have a subform for Appointments? You could even put the
combo's name into the LinkMasterFields property of the subform control, and
CustomerID into the LinkChildFields. Access will then just show the
appointments for the customer in the combo, no code needed.
