Preventing dup entries for any given date

[Patti]

I apologize for starting a new thread, but I'm afraid the previous won't be
looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In order to
prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form."

Assuming that the two fields that would determine a dup are "AppDate" &
"SSN", I'm guessing it be something like what I have below, but need help
with how to check the contents of the form controls against existing data in
table. Also, where would I put the code? In the On Lost Focus of SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an autonumber PK
and an SSN foreign key (so you can have a one to may relationships between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table being a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form.

HTH,
Nikos

 

[Alphonse Giambrone]

There are many ways to do this.
I generally check validation in the beforeupdate event of the form.

air code

If not isnull(dlookup("yourPK","yourtable", "yourPK<>" & me!yourPK &
"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

I apologize for starting a new thread, but I'm afraid the previous won't be
looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In order to
prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form."

Assuming that the two fields that would determine a dup are "AppDate" &
"SSN", I'm guessing it be something like what I have below, but need help
with how to check the contents of the form controls against existing data in
table. Also, where would I put the code? In the On Lost Focus of SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an autonumber PK
and an SSN foreign key (so you can have a one to may relationships between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table being a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form.

HTH,
Nikos

I'm creating a database for hiring/recruitment. There will be at least 3
tables in which each candidate will have a record - one for address/phone
info, one for assessment scores and evaluations, etc.

Not all of the data for each candidate will be entered at once (because of
the assessment process) so I need to make sure that we don't inadvertently
open a multiple records for each candidate. I want to make the SSN the
primary key in all three tables, since it's the only unique piece of data,
but I cannot set the field to "allow no duplicates" because the candidate
may apply again at a later date.

What I decided to do was join the SSN with the Application Date to

create

a
multiple field primary key. Before I proceed, I was wondering if anyone has
any arguments for or against this. Would I be setting myself up for
problems down the road? Any decent alternatives? I really don't feel
comfortable going with an autonumber for the primary key, since I can't
ensure what we won't create dupe records that way.

Thanks in advance,

Patti

 

[Patti]

Thanks Alphonse,

My primary key is the SSN, so I adapted your code as follows. I changed the
triple quotes around "& me!SSN" to double because they were causing a
syntax error.

It is stopping the entry, but rather than provide the message, I'm getting
run time error 2001 "you canceled the previous operation" in the code
window. It is highlighting everything up to the msgbox line.

If Not IsNull(DLookup("SSN", "Application Data", "SSN<>" & Me!SSN & _

"SSN="" & me!SSN& "" AND Date=#" & Me!Date & "#")) Then

MsgBox "This record already exists", vbExclamation, "Testing"

Cancel = True

End If


Any ideas?

Thanks again

There are many ways to do this.
I generally check validation in the beforeupdate event of the form.

air code

If not isnull(dlookup("yourPK","yourtable", "yourPK<>" & me!yourPK &
"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

I apologize for starting a new thread, but I'm afraid the previous won't be
looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In order to
prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form."

Assuming that the two fields that would determine a dup are "AppDate" &
"SSN", I'm guessing it be something like what I have below, but need help
with how to check the contents of the form controls against existing

data

in
table. Also, where would I put the code? In the On Lost Focus of SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an

autonumber

PK
and an SSN foreign key (so you can have a one to may relationships between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table being a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form.

HTH,
Nikos

least

3

(because

of create anyone
has

 

[Alphonse Giambrone]

Patti,

You need to explain your table structure a little more. You had said that
AppDate and SSN would determine a dupe, but if SSN is your PK, then only one
record for each SSN will be allowed in the table.
From my understanding, your table should have a minimum of 3 fields
AppID - PK autonumber
SSN - text
AppDate - Date/Time

Using the single quotes instead of the triple makes the enclosed terms
become literals instead of referring to the controls on the form.
The syntax error was due to my air code.
Based on the above structure the code should be (watch for wrapping also)
If Not IsNull(DLookup("AppID", "ApplicationData", "AppID<>" & Me!AppID & "
AND SSN=""" & Me!SSN & """ AND AppDate=#" & Me!AppDate & "#")) Then
MsgBox "Duplicate Entry"
Cancel = True
End If

Also be sure you are placing the code in the BeforeUpdate event of the form.
I tested this and it works without error.
Post back with more info if you still have a problem


--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Thanks Alphonse,

My primary key is the SSN, so I adapted your code as follows. I changed the
triple quotes around "& me!SSN" to double because they were causing a
syntax error.

It is stopping the entry, but rather than provide the message, I'm getting
run time error 2001 "you canceled the previous operation" in the code
window. It is highlighting everything up to the msgbox line.

If Not IsNull(DLookup("SSN", "Application Data", "SSN<>" & Me!SSN & _

"SSN="" & me!SSN& "" AND Date=#" & Me!Date & "#")) Then

MsgBox "This record already exists", vbExclamation, "Testing"

Cancel = True

End If


Any ideas?

Thanks again

There are many ways to do this.
I generally check validation in the beforeupdate event of the form.

air code

If not isnull(dlookup("yourPK","yourtable", "yourPK<>" & me!yourPK &
"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

I apologize for starting a new thread, but I'm afraid the previous

won't
be

looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In order to
prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form."

Assuming that the two fields that would determine a dup are "AppDate" &
"SSN", I'm guessing it be something like what I have below, but need help
with how to check the contents of the form controls against existing

data

in
table. Also, where would I put the code? In the On Lost Focus of SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an

autonumber

PK
and an SSN foreign key (so you can have a one to may relationships between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table being a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least, warn
about it), I would use a bit of code behind the application entry form.

HTH,
Nikos

I'm creating a database for hiring/recruitment. There will be at

least

3
tables in which each candidate will have a record - one for address/phone
info, one for assessment scores and evaluations, etc.

Not all of the data for each candidate will be entered at once

(because

of
the assessment process) so I need to make sure that we don't inadvertently
open a multiple records for each candidate. I want to make the SSN the
primary key in all three tables, since it's the only unique piece of data,
but I cannot set the field to "allow no duplicates" because the candidate
may apply again at a later date.

What I decided to do was join the SSN with the Application Date to create
a
multiple field primary key. Before I proceed, I was wondering if anyone
has
any arguments for or against this. Would I be setting myself up for
problems down the road? Any decent alternatives? I really don't feel
comfortable going with an autonumber for the primary key, since I can't
ensure what we won't create dupe records that way.

Thanks in advance,

Patti

 

[Patti]

Thanks for pointing that out the pk problem. Since I had no other way of
preventing dupes yet, I had set SSN to the pk in what _will_ be the many
side in order to prevent them in the interim. So now, there is only one pk
and it is in the "Application Data" table, but the error remains.

I'm guessing there's an issue with the AppDate. How do I qualify the table
where that resides? It is in table "Assessment Data".

Also, is there a place in help or online that explains each argument of a
procedure like this? I haven't had much success with Help, but maybe I'm
looking in the wrong place.

Patti,

You need to explain your table structure a little more. You had said that
AppDate and SSN would determine a dupe, but if SSN is your PK, then only one
record for each SSN will be allowed in the table.
From my understanding, your table should have a minimum of 3 fields
AppID - PK autonumber
SSN - text
AppDate - Date/Time

Using the single quotes instead of the triple makes the enclosed terms
become literals instead of referring to the controls on the form.
The syntax error was due to my air code.
Based on the above structure the code should be (watch for wrapping also)
If Not IsNull(DLookup("AppID", "ApplicationData", "AppID<>" & Me!AppID & "
AND SSN=""" & Me!SSN & """ AND AppDate=#" & Me!AppDate & "#")) Then
MsgBox "Duplicate Entry"
Cancel = True
End If

Also be sure you are placing the code in the BeforeUpdate event of the form.
I tested this and it works without error.
Post back with more info if you still have a problem


--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Thanks Alphonse,

My primary key is the SSN, so I adapted your code as follows. I changed the
triple quotes around "& me!SSN" to double because they were causing a
syntax error.

It is stopping the entry, but rather than provide the message, I'm getting
run time error 2001 "you canceled the previous operation" in the code
window. It is highlighting everything up to the msgbox line.

If Not IsNull(DLookup("SSN", "Application Data", "SSN<>" & Me!SSN & _

"SSN="" & me!SSN& "" AND Date=#" & Me!Date & "#")) Then

MsgBox "This record already exists", vbExclamation, "Testing"

Cancel = True

End If


Any ideas?

Thanks again

order

to

"AppDate"

&

"SSN", I'm guessing it be something like what I have below, but need help
with how to check the contents of the form controls against existing data
in
table. Also, where would I put the code? In the On Lost Focus of SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an autonumber
PK
and an SSN foreign key (so you can have a one to may relationships between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table

being

a SSN
the

 

[Alphonse Giambrone]

Patti,

You need to explain your table structure!!
If SSN is your PK, you will never be able to enter more that one application
for each SSN and I was under the impression that you needed to do that.
And if you have modified the code I posted, post exactly what you are using.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Thanks for pointing that out the pk problem. Since I had no other way of
preventing dupes yet, I had set SSN to the pk in what _will_ be the many
side in order to prevent them in the interim. So now, there is only one pk
and it is in the "Application Data" table, but the error remains.

I'm guessing there's an issue with the AppDate. How do I qualify the table
where that resides? It is in table "Assessment Data".

Also, is there a place in help or online that explains each argument of a
procedure like this? I haven't had much success with Help, but maybe I'm
looking in the wrong place.

Patti,

You need to explain your table structure a little more. You had said that
AppDate and SSN would determine a dupe, but if SSN is your PK, then only one
record for each SSN will be allowed in the table.
From my understanding, your table should have a minimum of 3 fields
AppID - PK autonumber
SSN - text
AppDate - Date/Time

Using the single quotes instead of the triple makes the enclosed terms
become literals instead of referring to the controls on the form.
The syntax error was due to my air code.
Based on the above structure the code should be (watch for wrapping also)
If Not IsNull(DLookup("AppID", "ApplicationData", "AppID<>" & Me!AppID & "
AND SSN=""" & Me!SSN & """ AND AppDate=#" & Me!AppDate & "#")) Then
MsgBox "Duplicate Entry"
Cancel = True
End If

Also be sure you are placing the code in the BeforeUpdate event of the form.
I tested this and it works without error.
Post back with more info if you still have a problem


--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Thanks Alphonse,

My primary key is the SSN, so I adapted your code as follows. I

changed
the

triple quotes around "& me!SSN" to double because they were causing a
syntax error.

It is stopping the entry, but rather than provide the message, I'm getting
run time error 2001 "you canceled the previous operation" in the code
window. It is highlighting everything up to the msgbox line.

If Not IsNull(DLookup("SSN", "Application Data", "SSN<>" & Me!SSN & _

"SSN="" & me!SSN& "" AND Date=#" & Me!Date & "#")) Then

MsgBox "This record already exists", vbExclamation, "Testing"

Cancel = True

End If


Any ideas?

Thanks again

There are many ways to do this.
I generally check validation in the beforeupdate event of the form.

air code

If not isnull(dlookup("yourPK","yourtable", "yourPK<>" & me!yourPK &
"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


I apologize for starting a new thread, but I'm afraid the previous won't
be
looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In

order

to
prevent double
application entries for an applicant on the same date (or, at least,
warn
about it), I would use a bit of code behind the application entry form."

Assuming that the two fields that would determine a dup are

"AppDate"

&
"SSN", I'm guessing it be something like what I have below, but need
help
with how to check the contents of the form controls against existing
data
in
table. Also, where would I put the code? In the On Lost Focus of
SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for
each
application; along these lines, I would suggest one table for applicants
with the SSN as PK, a different table fro applications with an
autonumber
PK
and an SSN foreign key (so you can have a one to may relationships
between
the two), another table for test results bound to the applications table
through the application number (the PK in the applications table

being

a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least,
warn
about it), I would use a bit of code behind the application entry form.

HTH,
Nikos

I'm creating a database for hiring/recruitment. There will be at
least
3
tables in which each candidate will have a record - one for
address/phone
info, one for assessment scores and evaluations, etc.

Not all of the data for each candidate will be entered at once
(because
of
the assessment process) so I need to make sure that we don't
inadvertently
open a multiple records for each candidate. I want to make the SSN
the
primary key in all three tables, since it's the only unique

piece

of don't
feel

 

[Patti]

Alphonse,

One table is for the Applicant's contact data and has the SSN (pk), name,
address, phone contact etc. Another table is for specific info from the
application such as education and certain other "survey" type data. A third
contains the results of the various assessments that the applicant must
undergo. The 2nd and 3rd tables are joined to the first by the SSN in a
one-to-many relationship.

The problems is that if the applicant should reapply in 6 months, I want to
only update their address/phone contact info (if necessary) but add a
complete new set of records in the other two tables. So I now have a main
form with the SSN, address, phone contact etc. data, and two subform for the
other table data.

The application will go through many hands before they are complete, so I
need to make sure that no one enters duplicate data for an individual for
the same date.

For example John Doe SSN 123-45-6789 applies on 12/1/04 and has a partial
record entered by one person. Then the results of another test come
through. The person doing the data entry needs to be alerted that a record
has already been started - they only need to update their portion.

When John Doe reapplies on 7/1/05, I do not want the user to be prevented
from starting a new record for with assessment results for 7/1/05.

I hope this makes sense. Thanks for the help!

Patti,

You need to explain your table structure!!
If SSN is your PK, you will never be able to enter more that one application
for each SSN and I was under the impression that you needed to do that.
And if you have modified the code I posted, post exactly what you are using.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Thanks for pointing that out the pk problem. Since I had no other way of
preventing dupes yet, I had set SSN to the pk in what _will_ be the many
side in order to prevent them in the interim. So now, there is only one pk
and it is in the "Application Data" table, but the error remains.

I'm guessing there's an issue with the AppDate. How do I qualify the table
where that resides? It is in table "Assessment Data".

Also, is there a place in help or online that explains each argument of a
procedure like this? I haven't had much success with Help, but maybe I'm
looking in the wrong place.






only
one

&

"

Focus

of

SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests for
each
application; along these lines, I would suggest one table for
applicants
with the SSN as PK, a different table fro applications with an
autonumber
PK
and an SSN foreign key (so you can have a one to may relationships
between
the two), another table for test results bound to the applications
table
through the application number (the PK in the applications table being
a
foreign key in the test results table) etc. In order to prevent double
application entries for an applicant on the same date (or, at least,
warn
about it), I would use a bit of code behind the application entry
form.

HTH,
Nikos

I'm creating a database for hiring/recruitment. There will be at
least
3
tables in which each candidate will have a record - one for
address/phone
info, one for assessment scores and evaluations, etc.

Not all of the data for each candidate will be entered at once
(because
of
the assessment process) so I need to make sure that we don't
inadvertently
open a multiple records for each candidate. I want to make

the
SSN

the
primary key in all three tables, since it's the only unique

piece

of
data,
but I cannot set the field to "allow no duplicates" because the
candidate
may apply again at a later date.

What I decided to do was join the SSN with the Application

Date

to up
for

since

I

 

[Alphonse Giambrone]

Patti,

Sounds basically correct.
Therefore, SSN is NOT the PK in ApplicationData.
You did not say what the PK's are in the other tables.
If you are not using an autonumber field for the PK in ApplicationData, make
it so.
You can always add a unique index on SSN/AppDate to unconditionally prevent
dupe entry.
The code I posted should be in the BeforeUpdate event of the ApplicationData
subform, not the main form and will work. I have tried it.
All you need do is change the field, control and table names to what you
actually have.

If you still have a problem, post your actual code.
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Alphonse,

One table is for the Applicant's contact data and has the SSN (pk), name,
address, phone contact etc. Another table is for specific info from the
application such as education and certain other "survey" type data. A third
contains the results of the various assessments that the applicant must
undergo. The 2nd and 3rd tables are joined to the first by the SSN in a
one-to-many relationship.

The problems is that if the applicant should reapply in 6 months, I want to
only update their address/phone contact info (if necessary) but add a
complete new set of records in the other two tables. So I now have a main
form with the SSN, address, phone contact etc. data, and two subform for the
other table data.

The application will go through many hands before they are complete, so I
need to make sure that no one enters duplicate data for an individual for
the same date.

For example John Doe SSN 123-45-6789 applies on 12/1/04 and has a partial
record entered by one person. Then the results of another test come
through. The person doing the data entry needs to be alerted that a record
has already been started - they only need to update their portion.

When John Doe reapplies on 7/1/05, I do not want the user to be prevented
from starting a new record for with assessment results for 7/1/05.

I hope this makes sense. Thanks for the help!

Patti,

You need to explain your table structure!!
If SSN is your PK, you will never be able to enter more that one application
for each SSN and I was under the impression that you needed to do that.
And if you have modified the code I posted, post exactly what you are using.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


one
pk

of

a

Me!AppID

&

causing

a

&

_

me!yourPK

&

"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


I apologize for starting a new thread, but I'm afraid the previous
won't
be
looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In
order
to
prevent double
application entries for an applicant on the same date (or, at least,
warn
about it), I would use a bit of code behind the application entry
form."

Assuming that the two fields that would determine a dup are
"AppDate"
&
"SSN", I'm guessing it be something like what I have below,

but
need

help
with how to check the contents of the form controls against existing
data
in
table. Also, where would I put the code? In the On Lost

Focus

of
SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several

tests
for

each
application; along these lines, I would suggest one table for
applicants
with the SSN as PK, a different table fro applications with an
autonumber
PK
and an SSN foreign key (so you can have a one to may relationships
between
the two), another table for test results bound to the applications
table
through the application number (the PK in the applications table
being
a
foreign key in the test results table) etc. In order to prevent
double
application entries for an applicant on the same date (or, at least,
warn
about it), I would use a bit of code behind the application entry
form.

HTH,
Nikos

I'm creating a database for hiring/recruitment. There will

be
at

least
3
tables in which each candidate will have a record - one for
address/phone
info, one for assessment scores and evaluations, etc.

Not all of the data for each candidate will be entered at once
(because
of
the assessment process) so I need to make sure that we don't
inadvertently
open a multiple records for each candidate. I want to make the
SSN
the
primary key in all three tables, since it's the only unique piece
of
data,
but I cannot set the field to "allow no duplicates" because the
candidate
may apply again at a later date.

What I decided to do was join the SSN with the Application

Date

to
create
a
multiple field primary key. Before I proceed, I was

wondering
if

anyone
has
any arguments for or against this. Would I be setting

myself

up since

 

[Patti]

Alphonse,

I'm assuming you mean adding an autonumber as the unique key. I like that
idea. I'll also try the code again tomorrow. I think I'm getting a
better handle on the situation.

Thanks for hanging in there with me!

Regards,

Patti

Patti,

Sounds basically correct.
Therefore, SSN is NOT the PK in ApplicationData.
You did not say what the PK's are in the other tables.
If you are not using an autonumber field for the PK in ApplicationData, make
it so.
You can always add a unique index on SSN/AppDate to unconditionally prevent
dupe entry.
The code I posted should be in the BeforeUpdate event of the ApplicationData
subform, not the main form and will work. I have tried it.
All you need do is change the field, control and table names to what you
actually have.

If you still have a problem, post your actual code.
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Alphonse,

One table is for the Applicant's contact data and has the SSN (pk), name,
address, phone contact etc. Another table is for specific info from the
application such as education and certain other "survey" type data. A third
contains the results of the various assessments that the applicant must
undergo. The 2nd and 3rd tables are joined to the first by the SSN in a
one-to-many relationship.

The problems is that if the applicant should reapply in 6 months, I want to
only update their address/phone contact info (if necessary) but add a
complete new set of records in the other two tables. So I now have a main
form with the SSN, address, phone contact etc. data, and two subform for the
other table data.

The application will go through many hands before they are complete, so I
need to make sure that no one enters duplicate data for an individual for
the same date.

For example John Doe SSN 123-45-6789 applies on 12/1/04 and has a partial
record entered by one person. Then the results of another test come
through. The person doing the data entry needs to be alerted that a record
has already been started - they only need to update their portion.

When John Doe reapplies on 7/1/05, I do not want the user to be prevented
from starting a new record for with assessment results for 7/1/05.

I hope this makes sense. Thanks for the help!


way
of of maybe
I'm then
only Me!AppID causing

Me!SSN

&

_

"SSN="" & me!SSN& "" AND Date=#" & Me!Date & "#")) Then

MsgBox "This record already exists", vbExclamation, "Testing"

Cancel = True

End If


Any ideas?

Thanks again

There are many ways to do this.
I generally check validation in the beforeupdate event of the form.

air code

If not isnull(dlookup("yourPK","yourtable", "yourPK<>" &

me!yourPK

&
"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


I apologize for starting a new thread, but I'm afraid the previous
won't
be
looked at anymore. Plus it is no longer a table design question.

In reply to my original post to .tabledesign, Nikos wrote: "In
order
to
prevent double
application entries for an applicant on the same date (or, at
least,
warn
about it), I would use a bit of code behind the application entry
form."

Assuming that the two fields that would determine a dup are
"AppDate"
&
"SSN", I'm guessing it be something like what I have below, but
need
help
with how to check the contents of the form controls against
existing
data
in
table. Also, where would I put the code? In the On Lost Focus
of
SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests
for
each
application; along these lines, I would suggest one table for
applicants
with the SSN as PK, a different table fro applications with an
autonumber
PK
and an SSN foreign key (so you can have a one to may relationships
between
the two), another table for test results bound to the applications
table
through the application number (the PK in the applications table
being
a
foreign key in the test results table) etc. In order to prevent
double
application entries for an applicant on the same date (or, at
least,
warn
about it), I would use a bit of code behind the application entry
form.

HTH,
Nikos

I'm creating a database for hiring/recruitment. There

will

be make
the because
the myself

 

[Alphonse Giambrone]

Patti,

I meant as the 'Primary Key' in your ApplicationData table. Other
configurations will work also, but I am trying to keep you on one track.
Open the table in design view.
If you don't already have it, add a new field of datatype Autonumber.
With your cursor still on that row, from the Edit menu, click Primary Key. A
key image will appear in the gray area on the left.
If you want to set up a unique 'Index' for SSN/AppDate, we can get into that
later.
First let's get your code working.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us

Alphonse,

I'm assuming you mean adding an autonumber as the unique key. I like that
idea. I'll also try the code again tomorrow. I think I'm getting a
better handle on the situation.

Thanks for hanging in there with me!

Regards,

Patti

Patti,

Sounds basically correct.
Therefore, SSN is NOT the PK in ApplicationData.
You did not say what the PK's are in the other tables.
If you are not using an autonumber field for the PK in ApplicationData, make
it so.
You can always add a unique index on SSN/AppDate to unconditionally prevent
dupe entry.
The code I posted should be in the BeforeUpdate event of the ApplicationData
subform, not the main form and will work. I have tried it.
All you need do is change the field, control and table names to what you
actually have.

If you still have a problem, post your actual code.
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


want
to for
the

so

I

need to make sure that no one enters duplicate data for an individual for
the same date.

For example John Doe SSN 123-45-6789 applies on 12/1/04 and has a partial
record entered by one person. Then the results of another test come
through. The person doing the data entry needs to be alerted that a record
has already been started - they only need to update their portion.

When John Doe reapplies on 7/1/05, I do not want the user to be prevented
from starting a new record for with assessment results for 7/1/05.

I hope this makes sense. Thanks for the help!


Patti,

You need to explain your table structure!!
If SSN is your PK, you will never be able to enter more that one
application
for each SSN and I was under the impression that you needed to do that.
And if you have modified the code I posted, post exactly what you are
using.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


Thanks for pointing that out the pk problem. Since I had no other way
of
preventing dupes yet, I had set SSN to the pk in what _will_ be the
many
side in order to prevent them in the interim. So now, there is

only
one

pk
and it is in the "Application Data" table, but the error remains.

I'm guessing there's an issue with the AppDate. How do I qualify the
table
where that resides? It is in table "Assessment Data".

Also, is there a place in help or online that explains each

argument
of

a
procedure like this? I haven't had much success with Help, but maybe
I'm
looking in the wrong place.






Patti,

You need to explain your table structure a little more. You had said
that
AppDate and SSN would determine a dupe, but if SSN is your PK, then
only
one
record for each SSN will be allowed in the table.
From my understanding, your table should have a minimum of 3 fields
AppID - PK autonumber
SSN - text
AppDate - Date/Time

Using the single quotes instead of the triple makes the enclosed terms
become literals instead of referring to the controls on the form.
The syntax error was due to my air code.
Based on the above structure the code should be (watch for wrapping
also)
If Not IsNull(DLookup("AppID", "ApplicationData", "AppID<>" & Me!AppID
&
"
AND SSN=""" & Me!SSN & """ AND AppDate=#" & Me!AppDate & "#")) Then
MsgBox "Duplicate Entry"
Cancel = True
End If

Also be sure you are placing the code in the BeforeUpdate event

of
the

form.
I tested this and it works without error.
Post back with more info if you still have a problem


--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


Thanks Alphonse,

My primary key is the SSN, so I adapted your code as follows. I
changed
the
triple quotes around "& me!SSN" to double because they were causing
a
syntax error.

It is stopping the entry, but rather than provide the message, I'm
getting
run time error 2001 "you canceled the previous operation" in the
code
window. It is highlighting everything up to the msgbox line.

If Not IsNull(DLookup("SSN", "Application Data", "SSN<>" &

Me!SSN

&
_

"SSN="" & me!SSN& "" AND Date=#" & Me!Date & "#")) Then

MsgBox "This record already exists", vbExclamation, "Testing"

Cancel = True

End If


Any ideas?

Thanks again

There are many ways to do this.
I generally check validation in the beforeupdate event of the
form.

air code

If not isnull(dlookup("yourPK","yourtable", "yourPK<>" & me!yourPK
&
"SSN=""" & me!SSN & """ AND AppDate=#" & me!AppDate & "#")) Then
msgbox "Duplicate Entry"
cancel = true
End If

HTH
--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


I apologize for starting a new thread, but I'm afraid the
previous
won't
be
looked at anymore. Plus it is no longer a table design
question.

In reply to my original post to .tabledesign, Nikos wrote: "In
order
to
prevent double
application entries for an applicant on the same date (or, at
least,
warn
about it), I would use a bit of code behind the application
entry
form."

Assuming that the two fields that would determine a dup are
"AppDate"
&
"SSN", I'm guessing it be something like what I have

below,
but

need
help
with how to check the contents of the form controls against
existing
data
in
table. Also, where would I put the code? In the On Lost
Focus
of
SSN?

If Me![AppDate] = ' ** WHAT TO PUT HERE ** Then
If Me![SSN] = ' ' ** WHAT TO PUT HERE **
Cancel = True '
End if
End if

TIA,

Patti

<ORIGINAL QUESTIONS FOLLOWS>

Patti,

An applicant may apply several times, and may take several tests
for
each
application; along these lines, I would suggest one table for
applicants
with the SSN as PK, a different table fro applications

with

an will at
once