Table/Subform Autonumber and merge

[Viki]

I have a main table for groups of inventoried items: [Package ID], which is
primary, [Name], [Account Number], and multiple fields for serial numbers
(battery, modem, device, etc.). I have a secondary table for returns with
[Package ID], [Item returned], [Reason], etc. I want to create a unique
numbering format for the [Package ID] field which will be linked between the
two tables and ultimately a form/subform.

Also, on several subform setup attempts, when I enter data into the Returns
subform (a sub of the main inventory group form), I get the message, "You
can't assign a value to this object". This happens when I select from a combo
box I set up with about 6 values (battery, modem, device, etc.). What does
this mean and how to I fix it? I've verified field length, nothing is open
in design view, and the form is not read-only (per suggestions on error
message). Thanks.

 

[pietlinden]

I have a main table for groups of inventoried items: [Package ID], which is
primary, [Name], [Account Number], and multiple fields for serial numbers
(battery, modem, device, etc.). - Repeating fields - violates 1NF.

I have a secondary table for returns with

[Package ID], [Item returned], [Reason], etc. I want to create a unique
numbering format for the [Package ID] field which will be linked between the
two tables and ultimately a form/subform.

Also, on several subform setup attempts, when I enter data into the Returns
subform (a sub of the main inventory group form), I get the message, "You
can't assign a value to this object". This happens when I select from a combo
box I set up with about 6 values (battery, modem, device, etc.). What does
this mean and how to I fix it? I've verified field length, nothing is open
in design view, and the form is not read-only (per suggestions on error
message). Thanks.

Viki,
Things would probably work better if you had a slightly different
design. Maybe this would be better:

tblPkg(PackageID*, PackageName, AccountNumber)

tblPkgItems(SerialNumber*,PackageID (FK-tblPkg), ItemType)

tblReturns(ReturnID,ReturnedSerialNumber (FK tblPkgItems),Reason)

Having a PackageID in the Returns table looks redundant. You can get
that information by joining Returns to PkgItems.

 

[Viki]

Thank you so much. I created 3 separate tables per your suggestion. It
looks much cleaner now. I believe my [Package ID] fields are now aligned. I
created a main form and added a tab control for the Items and Returns.
However, I am still getting an error when I try to enter data in one of my
subforms. Error, "You can't assign a value to this object. *The object may
be a control on a read only form, *The object may be on a form that is open
in Design view, *The value may be too large for this field". I have checked
all of these scenarios and can't find the problem. Any suggestions?
Again...I thank you.

I have a main table for groups of inventoried items: [Package ID], which is
primary, [Name], [Account Number], and multiple fields for serial numbers
(battery, modem, device, etc.). - Repeating fields - violates 1NF.

I have a secondary table for returns with

[Package ID], [Item returned], [Reason], etc. I want to create a unique
numbering format for the [Package ID] field which will be linked between the
two tables and ultimately a form/subform.

Also, on several subform setup attempts, when I enter data into the Returns
subform (a sub of the main inventory group form), I get the message, "You
can't assign a value to this object". This happens when I select from a combo
box I set up with about 6 values (battery, modem, device, etc.). What does
this mean and how to I fix it? I've verified field length, nothing is open
in design view, and the form is not read-only (per suggestions on error
message). Thanks.

Viki,
Things would probably work better if you had a slightly different
design. Maybe this would be better:

tblPkg(PackageID*, PackageName, AccountNumber)

tblPkgItems(SerialNumber*,PackageID (FK-tblPkg), ItemType)

tblReturns(ReturnID,ReturnedSerialNumber (FK tblPkgItems),Reason)

Having a PackageID in the Returns table looks redundant. You can get
that information by joining Returns to PkgItems.

 

[pietlinden]

Thank you so much. I created 3 separate tables per your suggestion. It
looks much cleaner now. I believe my [Package ID] fields are now aligned. I
created a main form and added a tab control for the Items and Returns.
However, I am still getting an error when I try to enter data in one of my
subforms. Error, "You can't assign a value to this object. *The object may
be a control on a read only form, *The object may be on a form that is open
in Design view, *The value may be too large for this field". I have checked
all of these scenarios and can't find the problem. Any suggestions?
Again...I thank you.

If I understand your description of the problem correctly, your design
is still off. the hierarchy of entities in your problem description is
like this:

tblPkg---(1,M)---tblPkgItems---(1,M)---tblReturns
So tblPkg would be the source for your main form,
then tblPkgItems would be the source for the main form's subform.
Insid the subform would be another form, based on tblReturns.

What it looks like you're doing is trying to combine the two child
tables in a single form. and you can't really do that very well,
because you have to save the record for tblPkgItems before you add a
related record to tblReturns. So when you try, you get a violation and
the record doesn't save.

If you build the bottommost form (on tblReturns) and then on
tblPkgItems, you can drop the child form into the parent, and the
database should work fine. And you should set up the relationships
between the 3 tables to make sure the database records will be properly
related.