Using seek to find if a record exists

[Kevin Brammer]

I'm trying to use seek to check for the existence of a record before
saving, so there are no duplicate entries (is there another way?). I
have a "groups" table, which has

GroupID
Island
GroupName

My code right now is giving me a "type mismatch" error, and this is what
it looks like:

Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("tblGroups")

rsGroups.Seek Form_frmGroups.txtGroupName

If rsGroups.EOF Then
MsgBox "Value not found"

' DoCmd.Save
' Form_frmGroups.AllowAdditions = False
' Form_frmGroups.AllowEdits = False

Else
MsgBox "Value already found in database"

End If

Can anyone offer some suggestions or hints?

Kevin

[Allen Browne]

To use Seek, you must specify the index name (which must be on the field you
are seeking), and then you must specify the operator (probably "=".)

But that approach is not very flexible, e.g. if fails if you split the
database later, because Seek works only on recordsets of type dbOpenTable
(i.e. not attached tables.) A DLookup() would be simpler and more flexible.

This example assumes tblGroups has a primary key named GroupID, and it
avoids the search if the value has not changed (so an existing record does
not find itself):
Dim strWhere As String
Dim varResult As Variant

With Me.txtGroupName
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
strWhere = "[GroupName] = """ & .Value & """"
varResult = DLookup("GroupID", "tblGroups", strWhere)
If Not IsNull(varResult) Then
MsgBox "Group" & varResult & " has the same value."
End If
End If
End With

Note: If GroupName is a Number field (not a Text field), lose the extra
quotes:
strWhere = "[GroupName] = " & .Value

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin Brammer" <kd*****@msn.com> wrote in message
news:MP****************@tornado.socal.rr.com...

I'm trying to use seek to check for the existence of a record before
saving, so there are no duplicate entries (is there another way?). I have
a "groups" table, which has

GroupID
Island
GroupName

My code right now is giving me a "type mismatch" error, and this is what
it looks like:

Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("tblGroups")

rsGroups.Seek Form_frmGroups.txtGroupName

If rsGroups.EOF Then
MsgBox "Value not found"

' DoCmd.Save
' Form_frmGroups.AllowAdditions = False
' Form_frmGroups.AllowEdits = False

Else
MsgBox "Value already found in database"

End If

Can anyone offer some suggestions or hints?

Kevin

[(PeteCresswell)]

Per Allen Browne:

To use Seek, you must specify the index name (which must be on the field you
are seeking), and then you must specify the operator (probably "=".)

But that approach is not very flexible, e.g. if fails if you split the
database later, because Seek works only on recordsets of type dbOpenTable
(i.e. not attached tables.) A DLookup() would be simpler and more flexible.

Actually, you can .Seek an attached table.

The trick is to Dim/Set a pointer to the database that the table is in.

e.g.
-------------------------------------------------------------------
Public Function EmployeeExist(byVal theEmployeeNumber As Long) As Boolean

Dim myDB as DAO.Database
Dim myRS As DAO.Recordset

Set myDB = dbEngine(0).OpenDatabase("M:\Whatever\Payroll.mdb" )

Set myRS = myDB.OpenRecordset("tblEmployee", dbOpenTable)
With myRS
.Index = "PrimaryKey"
.Seek "=", theEmployeeNumber
If .NoMatch = False Then
EmployeeExist = True
End If
(or, if you want to be cute: EmployeeExist = Not .NoMatch
.Close
End With

Set myRS = Nothing
set myDB = Nothing
End Function

-------------------------------------------------------------------
--
PeteCresswell

[Lyle Fairfield]

(PeteCresswell) wrote:

Per Allen Browne:

To use Seek, you must specify the index name (which must be on the field you
are seeking), and then you must specify the operator (probably "=".)

But that approach is not very flexible, e.g. if fails if you split the
database later, because Seek works only on recordsets of type dbOpenTable
(i.e. not attached tables.) A DLookup() would be simpler and more flexible.

Actually, you can .Seek an attached table.

The trick is to Dim/Set a pointer to the database that the table is in.

e.g.
-------------------------------------------------------------------
Public Function EmployeeExist(byVal theEmployeeNumber As Long) As Boolean

Dim myDB as DAO.Database
Dim myRS As DAO.Recordset

Set myDB = dbEngine(0).OpenDatabase("M:\Whatever\Payroll.mdb" )

Set myRS = myDB.OpenRecordset("tblEmployee", dbOpenTable)
With myRS
.Index = "PrimaryKey"
.Seek "=", theEmployeeNumber
If .NoMatch = False Then
EmployeeExist = True
End If
(or, if you want to be cute: EmployeeExist = Not .NoMatch
.Close
End With

Set myRS = Nothing
set myDB = Nothing
End Function

-------------------------------------------------------------------

When ducks honk they are geese.

--
Lyle Fairfield

[summerwind]

Hi Kevin

I wouldn't use Seek at all.

Just open your recordset based on "WHERE GroupName = " & QUOTE &
Me.txtGroupName & QUOTE

The QUOTE is a global that I use which is simply a double set of
quotation marks. When using a string as a parameter for an SQL
statement it needs to be enclosed in these double quotes. That is why
you are getting your mismatch error.

After opening the recordset, your next line is:

If rsGroups.BOF and rsGroups.EOF then
'You've got an empty recordset and therefore the group isn't in
the table
' Do what you wanndo
end if

[Steve]

Kevin, I'm going to join this mishmash of suggestions, although you
have probably already implemented one. What I would do is simply make
the GroupName a primary key (No Duplicates) in your underlying table.
You would just have an extra primary key (as opposed to making
GroupName your ONLY primary key). In this approach, any effort to add a
record with the same GroupName as an existing one would result in an
error. On Error Resume next works well, but you could trap that
specific error if you wanted to.

[David W. Fenton]

Kevin Brammer <kd*****@msn.com> wrote in
news:MP****************@tornado.socal.rr.com:

I'm trying to use seek to check for the existence of a record
before saving

Why not just do a SELECT on the values that you're trying to match,
and if the result has a non-zero number of records, you know it's a
duplicate?

I never use SEEK. Ever.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Steve" <th*********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

Kevin, I'm going to join this mishmash of suggestions, although
you have probably already implemented one. What I would do is
simply make the GroupName a primary key (No Duplicates) in your
underlying table. You would just have an extra primary key (as
opposed to making GroupName your ONLY primary key). In this
approach, any effort to add a record with the same GroupName as an
existing one would result in an error. On Error Resume next works
well, but you could trap that specific error if you wanted to.

This response confuses me.

A primary key, by definition, can only be "primary" if there's only
one of them in a table. Suggesting adding another primary key would
be like saying "this is the first, and this is the other first."

I believe what you mean is adding a unique index on the other field.
The downside of this is that for that to work, it has to prohibit
Nulls.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Br]

David W. Fenton wrote:

"Steve" <th*********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

Kevin, I'm going to join this mishmash of suggestions, although
you have probably already implemented one. What I would do is
simply make the GroupName a primary key (No Duplicates) in your
underlying table. You would just have an extra primary key (as
opposed to making GroupName your ONLY primary key). In this
approach, any effort to add a record with the same GroupName as an
existing one would result in an error. On Error Resume next works
well, but you could trap that specific error if you wanted to.

This response confuses me.

A primary key, by definition, can only be "primary" if there's only
one of them in a table. Suggesting adding another primary key would
be like saying "this is the first, and this is the other first."

I believe what you mean is adding a unique index on the other field.
The downside of this is that for that to work, it has to prohibit
Nulls.

Hehe, yes you can only have one primary key :)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Kevin]

On Thu, 22 Dec 2005 15:16:35 -0600, David W. Fenton wrote:

Kevin Brammer <kd*****@msn.com> wrote in
news:MP****************@tornado.socal.rr.com:

I'm trying to use seek to check for the existence of a record before
saving

Why not just do a SELECT on the values that you're trying to match, and if
the result has a non-zero number of records, you know it's a duplicate?

I never use SEEK. Ever.

I saw that in one of my searches as well. I may have to give it a try,
thanks!

Kevin

[Kevin]

On Thu, 22 Dec 2005 06:31:46 -0800, summerwind wrote:

Hi Kevin

I wouldn't use Seek at all.

Just open your recordset based on "WHERE GroupName = " & QUOTE &
Me.txtGroupName & QUOTE

The QUOTE is a global that I use which is simply a double set of quotation
marks. When using a string as a parameter for an SQL statement it needs to
be enclosed in these double quotes. That is why you are getting your
mismatch error.

After opening the recordset, your next line is:

If rsGroups.BOF and rsGroups.EOF then
'You've got an empty recordset and therefore the group isn't in
the table
' Do what you wanndo
end if

Yet another great suggestion, I'll try this as well. :)

Kevin

[Kevin]

On Thu, 22 Dec 2005 10:47:20 -0800, Steve wrote:

Kevin, I'm going to join this mishmash of suggestions, although you have
probably already implemented one. What I would do is simply make the
GroupName a primary key (No Duplicates) in your underlying table. You
would just have an extra primary key (as opposed to making GroupName your
ONLY primary key). In this approach, any effort to add a record with the
same GroupName as an existing one would result in an error. On Error
Resume next works well, but you could trap that specific error if you
wanted to.

Nope, I haven't implemented one yet. I'm a few hours behind all of you so
it takes me like 12 hours after my post to actually get to _try_ something. :)

Thanks,

Kevin

[Kevin Brammer]

summerwind wrote:

Hi Kevin

I wouldn't use Seek at all.

Just open your recordset based on "WHERE GroupName = " & QUOTE &
Me.txtGroupName & QUOTE

The QUOTE is a global that I use which is simply a double set of
quotation marks. When using a string as a parameter for an SQL
statement it needs to be enclosed in these double quotes. That is why
you are getting your mismatch error.

After opening the recordset, your next line is:

If rsGroups.BOF and rsGroups.EOF then
'You've got an empty recordset and therefore the group isn't in
the table
' Do what you wanndo
end if

Any idea why this:

Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups WHERE
GroupName =" & txtGroupName)

If rsGroups.BOF And rsGroups.EOF Then
'You've got an empty recordset and therefore the group isn't in

MsgBox "Value not found"

Else

MsgBox "Value already found in database"

End If

would produce "Too few parameters. Expected 1" error?

Kevin

[John Mishefske]

Kevin Brammer wrote:

summerwind wrote:

Hi Kevin

I wouldn't use Seek at all.

Just open your recordset based on "WHERE GroupName = " & QUOTE &
Me.txtGroupName & QUOTE

The QUOTE is a global that I use which is simply a double set of
quotation marks. When using a string as a parameter for an SQL
statement it needs to be enclosed in these double quotes. That is why
you are getting your mismatch error.

After opening the recordset, your next line is:

If rsGroups.BOF and rsGroups.EOF then
'You've got an empty recordset and therefore the group isn't in
the table
' Do what you wanndo
end if

Any idea why this:

Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups WHERE
GroupName =" & txtGroupName)

If rsGroups.BOF And rsGroups.EOF Then
'You've got an empty recordset and therefore the group isn't in

MsgBox "Value not found"

Else

MsgBox "Value already found in database"

End If

would produce "Too few parameters. Expected 1" error?

Kevin

The variable txtGroupName most likely needs to be in quotes:

Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups WHERE" & _
" GroupName = '" & txtGroupName & "'")

or needs to be converted to the appropriate type (Date, Boolean, etc...) in the SQL statement.

--
'---------------
'John Mishefske
'---------------

[Wayne Gillespie]

On Fri, 23 Dec 2005 07:35:01 GMT, Kevin Brammer <kd*****@msn.com> wrote:

summerwind wrote:

Hi Kevin

I wouldn't use Seek at all.

Just open your recordset based on "WHERE GroupName = " & QUOTE &
Me.txtGroupName & QUOTE

The QUOTE is a global that I use which is simply a double set of
quotation marks. When using a string as a parameter for an SQL
statement it needs to be enclosed in these double quotes. That is why
you are getting your mismatch error.

After opening the recordset, your next line is:

If rsGroups.BOF and rsGroups.EOF then
'You've got an empty recordset and therefore the group isn't in
the table
' Do what you wanndo
end if

Any idea why this:

Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups WHERE
GroupName =" & txtGroupName)

If rsGroups.BOF And rsGroups.EOF Then
'You've got an empty recordset and therefore the group isn't in

MsgBox "Value not found"

Else

MsgBox "Value already found in database"

End If

would produce "Too few parameters. Expected 1" error?

Kevin

If GroupName is text it needs to be surounded by quotes in your WHERE clause.

Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups
WHERE GroupName = " & chr(34) & txtGroupName & Chr(34) & ")"

Wayne Gillespie
Gosford NSW Australia

[Lyle Fairfield]

To prevent duplicates I create a unique index on the field or fields
that are not to be duplicated.
After that I let Access and Jet worry about duplicates.
When a user attempts to insert a duplicate, a quite "nice" message
comes up by default and the insert is denied.
There is no muss and no fuss. Perhaps you would prefer something more
user-friendly but this is sufficient for me.
It is also, TTBOMK, fool proof.

[Kevin]

On Fri, 23 Dec 2005 03:30:49 -0800, Lyle Fairfield wrote:

To prevent duplicates I create a unique index on the field or fields that
are not to be duplicated.
After that I let Access and Jet worry about duplicates. When a user
attempts to insert a duplicate, a quite "nice" message comes up by default
and the insert is denied. There is no muss and no fuss. Perhaps you would
prefer something more user-friendly but this is sufficient for me. It is
also, TTBOMK, fool proof.

I was trying to figure something like this out. When I first started, I
went to the Index property in the database table design view. I can't
recall now why I opted against it, I'll have to look again.

Kevin

[Steve]

Actually, by definition, a "primary key" can be composed of more than
one field. Yes, there is only one primary key in each table, but it
doesn't have to be a single field, in Access. For example, using a
combination of LastName, FirstName, and Birthdate would still
constitute a primary key.

[David W. Fenton]

"Steve" <th*********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

Actually, by definition, a "primary key" can be composed of more
than one field. Yes, there is only one primary key in each table,
but it doesn't have to be a single field, in Access. For example,
using a combination of LastName, FirstName, and Birthdate would
still constitute a primary key.

Well, two comments about that:

1. in the context of the discussion, the suggestion was not to add
the other field to the existing PK, but to add another PK, which is
nonsense.

2. a compound primary key cannot have Nulls in any of the fields, so
your candidate compound key would be an extremely poor choice for a
PK.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Kevin Brammer]

John Mishefske wrote:

The variable txtGroupName most likely needs to be in quotes:

Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups WHERE" & _
" GroupName = '" & txtGroupName & "'")

or needs to be converted to the appropriate type (Date, Boolean, etc...)
in the SQL statement.

Worked for the SQL query, but now I'm getting a type mismatch. This
really boggles my mind..

[David W. Fenton]

Kevin Brammer <kd*****@msn.com> wrote in
news:VL*****************@tornado.socal.rr.com:

John Mishefske wrote:

The variable txtGroupName most likely needs to be in quotes:

Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups
WHERE" & _
" GroupName = '" & txtGroupName & "'")

or needs to be converted to the appropriate type (Date, Boolean,
etc...) in the SQL statement.

Worked for the SQL query, but now I'm getting a type mismatch.
This really boggles my mind..

Try this:

Dim strSQL As String

strSQL = ""SELECT * FROM tblGroups WHERE "
strSQL = strSQL & " GroupName = '" & txtGroupName & "'"
Debug.Print strSQL
Set rsGroups = CurrentDb.OpenRecordset(strSQL)

If it fails, you can then look in the Debug/Immediate window at
exactly what string was actually passed to your OpenRecordset.

You might also try replacing the single quotes with double quotes.
Probably the easiest way to do this on the fly is:

strSQL = strSQL & " GroupName = " & Chr(34) & txtGroupName & Chr(34)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Kevin Brammer]

David W. Fenton wrote:

Try this:

Dim strSQL As String

strSQL = ""SELECT * FROM tblGroups WHERE "
strSQL = strSQL & " GroupName = '" & txtGroupName & "'"
Debug.Print strSQL
Set rsGroups = CurrentDb.OpenRecordset(strSQL)

That worked! Now, what's different besides you defined the SQL ahead of
time?

[Thelma Lubkin]

Kevin Brammer <kd*****@msn.com> wrote:
: David W. Fenton wrote:

:> Try this:
:>
:> Dim strSQL As String
:>
:> strSQL = ""SELECT * FROM tblGroups WHERE "
^
|second quote is missing in statement below.
*******************************************

:> strSQL = strSQL & " GroupName = '" & txtGroupName & "'"
:> Debug.Print strSQL
:> Set rsGroups = CurrentDb.OpenRecordset(strSQL)
:>

: That worked! Now, what's different besides you defined the SQL ahead of
: time?

This is what you had in another post:

Set rsGroups = CurrentDb.OpenRecordset("SELECT * FROM tblGroups WHERE" & _
" GroupName = '" & txtGroupName & "'")

tiny, tiny details can make lots of trouble.
--thelma

[David W. Fenton]

Kevin Brammer <kd*****@msn.com> wrote in
news:N7*****************@tornado.socal.rr.com:

David W. Fenton wrote:

Try this:

Dim strSQL As String

strSQL = ""SELECT * FROM tblGroups WHERE "
strSQL = strSQL & " GroupName = '" & txtGroupName & "'"
Debug.Print strSQL
Set rsGroups = CurrentDb.OpenRecordset(strSQL)

That worked! Now, what's different besides you defined the SQL
ahead of time?

If you pasted as is, it shouldn't have worked. The double quote at
the beginning is wrong.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Kevin]

On Sat, 24 Dec 2005 13:39:26 -0600, David W. Fenton wrote:

Kevin Brammer <kd*****@msn.com> wrote in
news:N7*****************@tornado.socal.rr.com:

David W. Fenton wrote:

Try this:

Dim strSQL As String

strSQL = ""SELECT * FROM tblGroups WHERE " strSQL = strSQL & "
GroupName = '" & txtGroupName & "'" Debug.Print strSQL
Set rsGroups = CurrentDb.OpenRecordset(strSQL)

That worked! Now, what's different besides you defined the SQL ahead of
time?

If you pasted as is, it shouldn't have worked. The double quote at the
beginning is wrong.

I took it out before I ran the code. :)

Thanks again,

Kevin

[Steve]

>. in the context of the discussion, the suggestion was not to add

the other field to the existing PK, but to add another PK, which is
nonsense.
2. a compound primary key cannot have Nulls in any of the fields, so
your candidate compound key would be an extremely poor choice for a
PK.

1. You are absolutely correct; I used the wrong terminology.

2. Using a Birthdate field as part of a primary key would not be a poor
decision if the birthdate were required for all records. An example of
a situation where a birthdate would be required is a hospital keeping
track of birth rates.

[Randy Harris]

"Steve" <th*********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

. in the context of the discussion, the suggestion was not to add
the other field to the existing PK, but to add another PK, which is
nonsense.
2. a compound primary key cannot have Nulls in any of the fields, so
your candidate compound key would be an extremely poor choice for a
PK.

1. You are absolutely correct; I used the wrong terminology.

2. Using a Birthdate field as part of a primary key would not be a poor
decision if the birthdate were required for all records. An example of
a situation where a birthdate would be required is a hospital keeping
track of birth rates.

IMO - birthdate is still a poor choice for a primary key. Yes, combined with
other attributes it could be part of a unique PK. The PK, however, should do
nothing but guarantee unique identification of a record. "Smart keys" are a
bad idea.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[David W. Fenton]

"Steve" <th*********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

. in the context of the discussion, the suggestion was not to add
the other field to the existing PK, but to add another PK, which
is nonsense.

2. a compound primary key cannot have Nulls in any of the fields,
so your candidate compound key would be an extremely poor choice
for a PK.

1. You are absolutely correct; I used the wrong terminology.

2. Using a Birthdate field as part of a primary key would not be a
poor decision if the birthdate were required for all records. An
example of a situation where a birthdate would be required is a
hospital keeping track of birth rates.

But one of the names almost always need to be blank in some small
number of records. I have never encountered a single application
where the compound key you describe could possibly have been used.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Steve]

In what scenario would a program recording birth records not have the
birthdate available? If a user is entering information about the births
at a hospital, the very nature of the work forces said user to already
have a birthdate to enter.

[Wayne Gillespie]

On 27 Dec 2005 13:57:13 -0800, "Steve" <th*********@gmail.com> wrote:

In what scenario would a program recording birth records not have the
birthdate available? If a user is entering information about the births
at a hospital, the very nature of the work forces said user to already
have a birthdate to enter.

But they may not yet know the baby's first name.

Wayne Gillespie
Gosford NSW Australia

[David W. Fenton]

"Steve" <th*********@gmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

In what scenario would a program recording birth records not have
the birthdate available? If a user is entering information about
the births at a hospital, the very nature of the work forces said
user to already have a birthdate to enter.

I did *not* say there were *no* scenarios where it would work. I
just said there are plenty of them where it would *not*.

And, as Wayne points out, there can be a time be a time period, even
in a hospital, where you have a surname and a birthdate, but no
first name. It may not last long, but it means you couldn't always
enter a child in your database at birth if the parents hadn't yet
settled on the name.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Thelma Lubkin]

David W. Fenton <XX*******@dfenton.com.invalid> wrote:
: And, as Wayne points out, there can be a time be a time period, even
: in a hospital, where you have a surname and a birthdate, but no
: first name. It may not last long, but it means you couldn't always
: enter a child in your database at birth if the parents hadn't yet
: settled on the name.

My son came home from the hospital with the first name of
'Boy Baby', so the hospital was ready to enter him into the
database, even if the entry would be suboptimal and already
in need of update.
--thelma
: --
: David W. Fenton http://www.dfenton.com/
: usenet at dfenton dot com http://www.dfenton.com/DFA/

[Steve]

And I did *not* say that it would work in every scenario. I just said
there are some in which it would.

Even if there is a nameless time period as you describe, the child's
first name could first be entered as an ambigious placemarker as
Thelma's experience indicates, until a name was decided upon. I would
think that the number of incidents where the parents have not yet
decided on a name by the time the birth is over with is relatively low.
If the hospital were to enter birth records in their system after the
birth certificate were issued, the problem becomes a non-issue.

[David W. Fenton]

"Steve" <th*********@gmail.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

And I did *not* say that it would work in every scenario. I just
said there are some in which it would.

Even if there is a nameless time period as you describe, the
child's first name could first be entered as an ambigious
placemarker as Thelma's experience indicates, until a name was
decided upon. . . .
Any database design that requires the entry of fake data is a bad
design, in my opinion.
. . . I would
think that the number of incidents where the parents have not yet
decided on a name by the time the birth is over with is relatively
low. If the hospital were to enter birth records in their system
after the birth certificate were issued, the problem becomes a
non-issue.

The point is that names are very often an extremely poor candidate
for inclusion in a compound unique index.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/