one field to control the other

[uapak90]

Hey
I am making a table in which I have to record times if a sample was found at a certain time.
I have the following fields
Date
Room/Location
NumberOfTimes
Chest
LeftFinger
RightFinger
LeftWrist
RightWrist
LeftForearm
RightForearm

Right now, the default value in all the body locations is "0". The thing is, when they do find a sample, they will change the 0 to however many they found, but then I want another field that would be like:

ChestTime
LeftFingerTime
RightFingerTime
etc..

What I wanted to do was, make it required for them to enter a value in the chesttime, leftfingertime, etc fields if the value in the corresponding chest, leftfinger, field was not 0.

How do I do this?

 

[Remou]

There are a number of ways, but you may wish to use the Before Update event for the form to check each of the controls and then cancel the update with a message if the data is incomplete.

 

[dhookom]

I wouldn't go any further until the table structure was normalized. You are using data values as field names. Every number associated with a body part should create a new record.

Duane
Hook'D on Access
MS Access MVP

 

[JBinQLD]

To clarify answer above, I'd suggest these tables:

tblExaminations
ExamID
ExamDate
ExamLocation
ExamPerson
ExamExaminer

tblBodyParts
PartID
Part

tblExamResults
ResultID
ExamID
PartID
ResultsFound
ResultTime

This is what the previous poster means by normalisation - try to store as little redundant info as possible. normally i dont go into these things because many posters have inherited a database that is already in use and can't change but it sounds as though you're doing something new here so try and do it well from the start, it will save you heartache and pain later! Promise!

Take it easy, JB

 

[MajP]

To add it also appears that you have a calculated field.
numberOftimes
By the name I am guessing that is the total number of times that samples were found. If that is the case you would not save that value in the table, but dynamically calculate it in the query based on the suggested normalized table structure.