Restricting data entry in one table until data has been entered in

[Cassi]

I have a Db that I am working on and I have several small tables related to
one main table. the smaller tables are each to capture one step of a several
step process in the cycle of a file through our office. An example would be:
1) File signed in and date related through client number to main table
2) File prepared and date related through client number to main table
3) File checked and date related through client number to main table
etc.
I would like to restrict access so that a file must be signed into one
table before it can be prepared or assembled (entries in other talbes). In
other words, I would like the Db set so that the data must be entered in the
logical order. You would not be able to enter data into the second related
table before entering it in the first one.

 

[Tim Ferguson]

several small tables
related to one main table. the smaller tables are each to capture one
step of a several step process in the cycle of a file through our
office.

Hmmm: I'd be keen to try to consolidate these "smaller" tables into one
table with lots of records...

I would like to restrict access so that a file must be signed into
one
table before it can be prepared or assembled (entries in other
talbes).

Just make the FK in the downstream table reference the PK in the next
upstream table instead.

But I would think very hard about this whole design: banks of one-to-one
relationships are almost certainly a sign of a house built of sand.

Hope it helps


Tim F

 

[Cassi]

Your idea works...I am only using this as a tracking system for files going
through our office so hopefully the one to one relationships don't make us a
house of sand. If I were to allow more than one record for each file, it
increases the chances of error because each file cycles through our office
only once per year. If for example john doe could be entered 2x, john c. doe
might get missed.

 

[PC Datasheet]

You don't need all the small tables assuming a file is signed once, prepared
once and checked once. You just need one table yhat looks like:
TblFile
FileID
FileName
DateSigned
SignedBy
DatePrepared
PreparedBy
DateChecked
CheckedBy
etc

On your data entry form you need the following code in the DatePrepared
Enter event:
If IsNull(DateSigned) Then
MsgBox "DateSigned Must Be Entered First",,"Missing DateSigned"
Me!DateSigned.SetFocus
End If

Use similar code throughout your form.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.

 

[Tim Ferguson]

Your idea works...I am only using this as a tracking system for files
going through our office so hopefully the one to one relationships
don't make us a house of sand.

I think it probably will, for several reasons. The biggest reason will
happen when someone invents a new step to come in between steps two and
three -- you will have an horrendous job satisfying all the integrity
constraints then.

What is the reason for not having all the steps in the same table? This
reduces your programming and checking overhead enormously. It is true that
"fields are expensive" but not nearly as expensive as storing data in table
names.

And yes, I do still think that all the checks should be in one table, with
a record for each check of each file, although of course I don't know much
about your business requirements.

All the best


Tim F