Message database

[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

 

[Arvin Meyer]

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

 

[frogsteaks]

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

What is your current table structure?

 

[Kit]

What is your current table structure?

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

 

[John W. Vinson]

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.

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.