Ensuring values in a subform are entered correctly

[Ron A.]

I have a database that tracks quality control inspections. The inspection
data is entered with a form and a subform is used to enter the mechanics Man
No on the work order. Is there a way to have access not accept man no's not
already in the employee tbl and give a warning message to that effect. My
tables are set up like this:

ShopCodedtbl:
[shopcode] <-- primary key -- one to many with [shopcode] in EmployeeManNotbl.
[shopname]

EmployeeManNotbl:
[ManNo] <-- primary key -- one to many with [ManNo] in EmployeeInsptbl.
[LastName]
[FirstName]
[Position]
[Section]
[ShopCode]

EmployeeInsptbl:
[ID] <-- one to many with [ID] in InspRecordstbl.
[ManNo]

InspRecordstbl:
[ID] <-- primary key
[RegNo]
[W/O No]
[DateInsp]
[W/O Closed]
[InspBy]
[Pass] <--Check Box
[Fail] <--Check Box
[Discrepancies]

I have reports based on date queries to show inspections and sorted by
Employee Man No and when I run them with Man No's in the EmployeeInsptbl that
are not in the EmployeeManNotbl, the report comes up blank or is short
records. Any help is greatly appreciated.

 

[John W. Vinson]

I have a database that tracks quality control inspections. The inspection
data is entered with a form and a subform is used to enter the mechanics Man
No on the work order. Is there a way to have access not accept man no's not
already in the employee tbl and give a warning message to that effect. My
tables are set up like this:

ShopCodedtbl:
[shopcode] <-- primary key -- one to many with [shopcode] in EmployeeManNotbl.
[shopname]

EmployeeManNotbl:
[ManNo] <-- primary key -- one to many with [ManNo] in EmployeeInsptbl.
[LastName]
[FirstName]
[Position]
[Section]
[ShopCode]

EmployeeInsptbl:
[ID] <-- one to many with [ID] in InspRecordstbl.
[ManNo]

Open the Relationships window and create a one to many relationship from
EmployeeManNotbl to EmployeeInsptbl on the ManNo field; check the "Enforce
Referential Integrity" checkbox. If you have that relationship set, you cannot
put in an invalid ManNo - you'll get an error message.

You can also use a Combo Box on the subform; the subform should be bound to
EmployeeInsptbl, using ID as the Master/Child Link Field. The subform should
have a combo box with its Limit to List property set to Yes, based on
EmployeeManNotbl. The combo can display the ManNo, or the name, or a
combination of fields - whatever is most meaningful for your users.

John W. Vinson [MVP]

 

[Ron A.]

I have 2 questions concerning your suggestions. Can I change the warning
message that shows when the data violates "Referential Integrity"? Also, can
I make the combo box accept multiple Man No's? There are several different
Man No's for one inspection. Thanks again.
--
Aloha,
Ron A.

I have a database that tracks quality control inspections. The inspection
data is entered with a form and a subform is used to enter the mechanics Man
No on the work order. Is there a way to have access not accept man no's not
already in the employee tbl and give a warning message to that effect. My
tables are set up like this:

ShopCodedtbl:
[shopcode] <-- primary key -- one to many with [shopcode] in EmployeeManNotbl.
[shopname]

EmployeeManNotbl:
[ManNo] <-- primary key -- one to many with [ManNo] in EmployeeInsptbl.
[LastName]
[FirstName]
[Position]
[Section]
[ShopCode]

EmployeeInsptbl:
[ID] <-- one to many with [ID] in InspRecordstbl.
[ManNo]

Open the Relationships window and create a one to many relationship from
EmployeeManNotbl to EmployeeInsptbl on the ManNo field; check the "Enforce
Referential Integrity" checkbox. If you have that relationship set, you cannot
put in an invalid ManNo - you'll get an error message.

You can also use a Combo Box on the subform; the subform should be bound to
EmployeeInsptbl, using ID as the Master/Child Link Field. The subform should
have a combo box with its Limit to List property set to Yes, based on
EmployeeManNotbl. The combo can display the ManNo, or the name, or a
combination of fields - whatever is most meaningful for your users.

John W. Vinson [MVP]

 

[John W. Vinson]

I have 2 questions concerning your suggestions. Can I change the warning
message that shows when the data violates "Referential Integrity"?

No, but you can use form events to trap the error before it happens. If you
have a Combo Box to select the ManNo, then the problem *simply cannot arise* -
the user gets a choice only of the valid ManNo values, so they cannot possibly
enter an invalid one!

Also, can
I make the combo box accept multiple Man No's? There are several different
Man No's for one inspection. Thanks again.

No. If you are trying to store multiple ManNo values in one field, your table
design is WRONG. There should not be a ManNo field in the Inspection table (if
there is such a field, it can have one and only one value)!

If each Inspection can involve multiple ManNo's, and each ManNo can
participate in multiple inspections, then you *need another table* to relate
the two. This table would have an ID field (indicating what's being inspected)
and a ManNo (indicating who's doing the inspection).

John W. Vinson [MVP]