Ok. I'm on track now. I changed the property in the table index but
I'm not getting the desired results.
Going back to my original statement:
I need to assure that [Use] in a given recordset can never contain
more than one "CS", "UN" or "PT".
For example:
ID | Use
123 | CS
224 |
344 |
459 | UN
578 | PT
666 |
In other words, an ID's [Use] can be Null or CS, UN, or PT but there
can never be more than one ID as a CS, UN, or PT. [Use] serves as a
descriptor and "marker" in that a certain query will return other info
associated to an ID that has a [Use] value.
I see now that the IgnoreNulls doesn't directly address the challenge.
It will ignore nulls but it won't assure that only one ID is a CS. I'm
not suer that the autonumber field resolves this, either...?
Maybe write code that creates a string to check for existing [Use]
values...?
For the sake of curiosity here's the SQL for the subform. Not sure if
it helps.
SELECT tblPKWeightCalcs.Group, InStr("Product Unit Case UnitLoad",
[Category]) AS Cat, InStr("UN CS PT",[Use]) AS Us,
tblPKWeightCalcs.PKID, tblPKWeightCalcs.Category,
tblPKWeightCalcs.Use, tblProfiles.Description,
tblPKWeightCalcs.SubUnitQty, tblPKWeightCalcs.UnitQty,
tblPKWeightCalcs.txtProfileID,
Round(DLookUp("gConv","qryPKWeightsConvg","ID='" & [PKID] & "'"),4) AS
IDWtg, tblPKWeightCalcs.Value, tblPKWeightCalcs.UOM,
tblPKWeightCalcs.Weightg, tblPKWeightCalcs.Comments, Nz([IDWtg],
[Weightg]) AS WtNz, IIf(IsNull([UnitQty]),[Weightg],[IDWtg]*[UnitQty])
AS TotalWtg
FROM tblPKWeightCalcs LEFT JOIN tblProfiles ON tblPKWeightCalcs.PKID =
tblProfiles.txtProfileID
ORDER BY tblPKWeightCalcs.Group, InStr("Product Unit Case UnitLoad",
[Category]), InStr("UN CS PT",[Use]) DESC , tblPKWeightCalcs.PKID;
