Message database

  • Thread starter Thread starter Kit
  • Start date Start date
K

Kit

Require some help please.
Just tried my hand at writing a simple database to store phone messages.
The records consist of the First name, Lastname, date and message.
The messages are entered in a sub form.
The problem I want Access to recognise if a name already exists and if
so go to that name and let me add a message. If not then a new name is
created. The trouble is it sees all entries as a new name resulting in
duplicates.

When advising please bear in mind that I am completely new to Access and
am trying to learn by writing a 'simple' database. Not so simple for me! :-(

Regards

Kit
 
Because it is possible for more than 1 person to have the same name, you may
have some legitimate duplicates. I would add a ContactID field to your table
and use that to look up a duplicate. Use a combo box with thed ContactID and
a contatenation of thed first aqnd last name field, like:

Select ContactID, FirstName & " " & LastName As FullName Order By LastName;

as the rowsource of the combo box. Then in the BeforeUpdate event of the
combo box, check for dupes:

Sub MyCombo_BeforeUpdate( Cancel As Integer)
If DCount("ContactID", "YourTable", "ContactID =" & Me.MyCombo) > 1 Then
If MsgBox("You have a possible duplicate. Proceed?", vbYesNo,
"Dupe?") = vbNo Then
Cancel = True
End If
End If
End Sub

Of course you need to substitute your own control, field, and table names.

Arvin Meyer, MCP, MVP
 
Require some help please.
Just tried my hand at writing a simple database to store phone messages.
The records consist of the First name, Lastname, date and message.
The messages are entered in a sub form.
The problem I want Access to recognise if a name already exists and if
so go to that name and let me add a message. If not then a new name is
created. The trouble is it sees all entries as a new name resulting in
duplicates.

When advising please bear in mind that I am completely new to Access and
am trying to learn by writing a 'simple' database. Not so simple for me! :-(

Regards

Kit
Click to expand...

What is your current table structure?
 
What is your current table structure?
Click to expand...
Hi

One table contains the firstname, lastname and phone number
Second table has firstname, lastname, message and date.

Then I made a form to show this.

Tried it with a Combo box but that's not satisfactory or else I haven't
written properly.

regards

Kit
 
Hi

One table contains the firstname, lastname and phone number
Second table has firstname, lastname, message and date.

Then I made a form to show this.

Tried it with a Combo box but that's not satisfactory or else I haven't
written properly.
Click to expand...

I'd suggest a different data structure. Names are NOT unique - when I was in
college there was a Professor John Vinson in the med school. He got one of my
paychecks; I never got one of his, alas.

I would suggest two tables:

People
PersonID <Autonumber, Primary Key>
LastName
FirstName
Phone
<other biographical data if needed to distinguish Prof. Vinson from that
obscure postdoc>

Messages
MessageID <Autonumber, Primary Key>
PersonID <Long Integer, link to People>
MessageTime <date/time, default value Now to get date and time>
Message <Text, or Memo if messages might run to more than 255 bytes>

You could use a Form based on People, with a Subform based on Messages, using
PersonID as the master/child link field. Put a Combo Box on the mainform using
the toolbox combo wizard - choose the option "Use this combo to find a
record", and base it on a query like

SELECT PersonID, LastName & ", " & FirstName FROM People ORDER BY LastName,
FirstName;

to display "Vinson, John" or "Jones, Ira" or whatever, sorted. You can use the
combo's not in list event to jump to the New Record to let you add a new
person.
 
Back
Top