Limitting Values

[LindaBee]

I have a field which I need to enter numbers in for each record on teh
databse. each record can have an instance of 1,2,3,4,5,6,7,8 once only.

But the table can allow the values to be entered several times

for example
Record A can have instances of

Record A date1 - 1
Record A date2 - 2
Record A date3 -3 etc

but not
Record A date4 - 1 or
Record A date4 - 3

as both numbers have alredy been used by that record


I cannot work out how to make it so that users do not enter the same numer
twice against the same record

Thanks in advance

 

[Jeanette Cunningham]

Hi LindaBee,
you can use an index to do this.
Select the field where you want unique numbers and in the lower pane of the
table, scroll down to the Indexed property and set it to Yes ( No
duplicates).



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Douglas J. Steele]

Actually the index would need to include both the date field and the number
field.

 

[LindaBee]

Hi
Thanks for that but I tried and it will only let me enter 1 instance of a
value so if I enter 1 for Mr A, I cannot enter 1 for Mr B and I need to be
able to enter the same value for different people but only 1 occurence for
each individual.


I am not sure if this makes sense

Linda

 

[Douglas J. Steele]

As I implied in my add-on to Jeanette's response to you, you need to ensure
that the unique index includes all the fields that need to be unique, not
just the numeric field.

 

[LindaBee]

Hi Again
Thanks but if 2 people have that information entered on the same date and
the number is the same it is allowing entry then. If both need the number 1
entering against them today that creates duplicate entries. I have tried the
same number but on a different date for a different person still no joy

 

[Douglas J. Steele]

So what fields do you have in your table, which combination of fields has to
be unique and what fields do you have in your index?

 

[Jeanette Cunningham]

This is kind of an unusual requirement.
I suspect there is an easier way to do what you want, but with a different
setup.

Would you post more details of what this database is trying to achieve and
what tables you have in this database. If people can only enter the data
from forms, there are more options for showing them only allowed values to
choose from.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Hi Doug,
I was aware that my answer wasn't going to be the final solution. I offered
it as a step along the way of discovering what the user really was trying to
do. Hope that makes sense.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[LindaBee]

The database is for the re-ablement team they try to get eldery residnet who
are recently discharged from back on their feet and get them back into the
community. The maximum number of weeks a person can stay on this program is
8 weeks hence the limit.

Foe example if a Mr smith comes out of hospital he starts on week 1
and Mr Jones also comes out of hospital he will also start at week 1 but
they both may come out at the same time so the date and week number for both
will be the same.

I have created a table to record the week number, date and all related
activities being monitored each week.

The fields are
PLID - Primary key
ASSID - Secondary key links to another table
Week Number
Date
Activity1
Activity1 etc

 

[Jeanette Cunningham]

The database can calculate which date is 8 weeks time from the date the
person joins the program.
That is a simple calculation and takes away the difficult requirement of
juggling the numbers.
Once that date has been reached, you know this person must leave the
program.
The table needs to have a field for the date each person joins the program.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[LindaBee]

Jeanette
Could you kindly explain how that works I have never done it before

Thanks

 

[Jeanette Cunningham]

Linda,
The key point is what is going to happen once a client has been on the
program for 8 weeks.
What do you want the database to do when that point is reached for each
client?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[LindaBee]

After 8 weeks whatever the outcome the process ends, they are either reabled
and can leave the service or they get moved onto another service for long
term care.

Thanks

 

[Jeanette Cunningham]

We are still discussing what fields to put in the table.
Here is the info posted so far on the fields in the table

The fields are
PLID - Primary key
ASSID - Secondary key links to another table
Week Number
Date
Activity1
Activity1 etc

I am still not sure how this relates to the original question about-->
Please explain more about what you want this database to do.




don't allow the values to be entered several times

for example
Record A can have instances of

Record A date1 - 1
Record A date2 - 2
Record A date3 -3 etc

but not
Record A date4 - 1 or
Record A date4 - 3

as both numbers have alredy been used by that record
I cannot work out how to make it so that users do not enter the same numer
twice against the same record




Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[LindaBee]

I want the database to display an error message if a user tries to add a
ninth week or disable the weeks fields to stop users from entering more that
8 weeks

 

[Jeanette Cunningham]

Here's an example form.

Create a new form based on the table we were discussing.
Use default view = single form.
Put a text box bound to the field StartDate on the form.
As you click the nav button to go to the next record, you will get a warning
when you are 8 weeks past the start date.

Use the code below-->

----------------------------
Option Compare Database
Option Explicit

Private Sub DateFinish()
Dim dteStart As Date
Dim dteFinish As Date
Dim blnStop As Boolean


dteStart = Me.DateStart
dteFinish = DateAdd("d", 56, dteStart)

Debug.Print dteFinish

If DateDiff("d", dteFinishDate, Date) > 1 Then
blnStop = True
MsgBox "stop"
End If

End Sub


Private Sub Form_Current()
Call DateFinish
End Sub