Newbie Question: Limit Input Value baseon previouse input

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
Sorry if this is in the wrong group but Im a newbie on Access and am not sure under what are to ask this question.

So I have a database (In creation) that records the time spent on certain tasks carried out on a daily basis. As each task is entered the user enters a duration in this case in 1/4 days (eg 0.25, 0.5. 0.75 or 1 Day).

What i need to do is stop the user entering more than one days worth of time, but the user can enter anywhere up to 4 tasks per day, or any combination as long as it does not exceed 1 day.

How do I do this as the validation rule on the table wont allow refs to fields etc.

Is this a querie or some thing similar.

Any suggestions most greatfully recieved, but please remember Im a Newbie and will need to have the solution spelt out if it more than a little bit complicated.

Many thanks
GreenBoy
 
well, your user(s) should be entering data via forms,
never directly into a table. and in a form, you can
control the data entry completely.
my first thought would be to run a Totals query in VBA
from the form's BeforeUpdate event, to sum the time for
all records where the user and date match the current
record. then add that sum to the time entered in the
current record (in VBA). if the total is greater than 1
(day), cancel the update event and giver the user an error
message; otherwise, roll on.

hth

-----Original Message-----
Hi
Sorry if this is in the wrong group but Im a newbie on
Access and am not sure under what are to ask this question.
So I have a database (In creation) that records the time
spent on certain tasks carried out on a daily basis. As
each task is entered the user enters a duration in this
case in 1/4 days (eg 0.25, 0.5. 0.75 or 1 Day).
What i need to do is stop the user entering more than one
days worth of time, but the user can enter anywhere up to
4 tasks per day, or any combination as long as it does not
exceed 1 day.
How do I do this as the validation rule on the table wont allow refs to fields etc.

Is this a querie or some thing similar.

Any suggestions most greatfully recieved, but please
remember Im a Newbie and will need to have the solution
spelt out if it more than a little bit complicated.
 
Tina
Thanks for the responce.

I agree that the users should / and will enter data via a form (with Sub-form).

I have a little experience of VBA for excel , but none for Access, so this may be a chalange.

Any tips appreciated. Also if you know of any good web sites that have some info on this sort of coding I would be greatfull of the URL.

Again, Many thanks
GreenBoy
 
Hi,

If you post more info about the table structure, I could help you with the
code.
Also, if you preffer, you can contact me on e-mail, and send me in a MDB the
table you have and I could make a small form with the required code for what
you need.

Regards,
Bogdan
(e-mail address removed)
________________________________
Freelance programmer


GreenBoy said:
Tina
Thanks for the responce.

I agree that the users should / and will enter data via a form (with Sub-form).

I have a little experience of VBA for excel , but none for Access, so this may be a chalange.

Any tips appreciated. Also if you know of any good web sites that have
some info on this sort of coding I would be greatfull of the URL.
 
Bogdan

Thank you very much for your kind offer. As I believe in these newsgroups, I will post details here instead of emailing you, as then perhaps other Newbies will benefit also.

This is set up as a test bed - the rest of my application is quite simple (fingers crossed for luck!) and will revolve around this central section. Therefore I have stripped this to the bare essentials to focus on this one issue.

I am using two main tables - DateTable and DurationTable

DateTable includes the following fields at present but will have other things added in due course.

DateID - Autonumber - unique key
Date - Date/Time - this will take the date in dd/mmm/yyyy format
Notes - Memo

DurationTable

TaskID - Autonumber - Unique key
Task - Number - this will be linked by a lookup to another table of approx 150 tasks for the user to choose from.
Duration - Number - Allowed inputs are restricted to 0.25; 0.50; 0.75; 1.00 - these are fractions of a day.

Both of these tables are included as part of a Form - DailyTaskForm

Date and notes are in the main form and the Task and Duration are in a sub-form as a table.

What I need to do is restrict the user to only inputing as many Tasks with a duration that are equal or less than 1.00 as soon as they reach this no more entries may be made uness a deletion is made first.

From Tinas response this would apear to be by code calling a Total querie, but I am unsure of how to do this - I gues what this means is after the user adds data to the sub-form for the duration of a task the querie runs and sees if the total for tasks added that day exceed 1 if so 'block' more entries - but this would require that you could delete an existing entry or modfy it.

Any help you can give most greatfully received.

If you need any more info let me know.

GreenBoy
 
not sure why you have the fields below in 2 separate
tables, but....
you need to tie each transaction record to a specific date
(and to a specific user, but that's another issue). then,
when the user enters a new transaction record you can run
a function as follows

***AIR CODE***

Private Sub Form_BeforeUpdate()

Dim dblSubTotal As Double, dblTotal As Double

DblSubTotal = DSum("Duration","TransactionTable","User
= Forms!FormName!User And Date = Forms!FormName!Date")

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled. You've entered more than one
day's worth of time for this date."
End If

End Sub

this code pulls all existing records that match the user
and date entered in the new record, and adds up the
duration times. (the new record won't be included because
it has not been saved to the table yet.)

then the code adds the duration time in the new record to
the above total. if it equals more than 1, the record is
not saved to the table and a message box informs the user
and explains why.

as a newbie, you'll want to look up domain aggregate
functions in Help, and the BeforeUpdate event for forms,
and also MsgBox, perhaps. this will help you understand
how to use the air code above in your db.

also suggest you take a good look at your tables design as
well, and make sure you're following normalization rules
and setting table relationships correctly.

hth

-----Original Message-----
Bogdan

Thank you very much for your kind offer. As I believe in
these newsgroups, I will post details here instead of
emailing you, as then perhaps other Newbies will benefit
also.
This is set up as a test bed - the rest of my application
is quite simple (fingers crossed for luck!) and will
revolve around this central section. Therefore I have
stripped this to the bare essentials to focus on this one
issue.
I am using two main tables - DateTable and DurationTable

DateTable includes the following fields at present but
will have other things added in due course.
DateID - Autonumber - unique key
Date - Date/Time - this will take the date in dd/mmm/yyyy format
Notes - Memo

DurationTable

TaskID - Autonumber - Unique key
Task - Number - this will be linked by a lookup to
another table of approx 150 tasks for the user to choose
from.
Duration - Number - Allowed inputs are restricted to
0.25; 0.50; 0.75; 1.00 - these are fractions of a day.
Both of these tables are included as part of a Form - DailyTaskForm

Date and notes are in the main form and the Task and
Duration are in a sub-form as a table.
What I need to do is restrict the user to only inputing
as many Tasks with a duration that are equal or less than
1.00 as soon as they reach this no more entries may be
made uness a deletion is made first.
From Tinas response this would apear to be by code
calling a Total querie, but I am unsure of how to do this -
I gues what this means is after the user adds data to the
sub-form for the duration of a task the querie runs and
sees if the total for tasks added that day exceed 1 if
so 'block' more entries - but this would require that you
could delete an existing entry or modfy it.
 
Back
Top