Is this possible? Please suggest.

[mozart66]

Hi,

I am developing a simple database to add data and print
reports through Access. I am using Access 2000. My table
structure is as follows:

Product Code Text
UPC Code Text
Component Code Text
Pack Size Text
Lot #1 Text
Lot #2 Text

See the following sample data:

Product Code UPC Code Component Code
789 123-45-01 AB1234
789 123-45-01 AB8756
789 123-45-05 AB5478
789 123-45-05 JK8767
789 123-45-05 AB1234
675 657-67-09 AB1234
675 657-67-09 JK8767
675 657-67-03 AB1234

In short Product Code, UPC Code, component codes can be
duplicated. As can be observed, component codes can be
repeated for any combination of Product Code and UPC Code.
My question is:

On a form I have made a suform out of the same table. The
common fields such as Product Code and UPC Code were on
the main form and another subform containing Component
Code and other fields was put on the main form, linking
the two forms by Product Code and UPC Code fields. I got
the form working to display the data correctly. However,
on the subform it allows me to add a record for the given
Product Code, UPC Code combination on the main form but on
the main form itself it does not allow me to add new data.
So how do I do it? Is it that in Access a main form and a
subform SHOULD be built off TWO tables linking them by
common key or can I build a main form and a subform off
ONE table, for new data entry?

Please suggest.

Thank you!

Mozart66

 

[Bruce]

I don't understand the point of the subform. I am fairly
new to this, but from my admittedly limited knowledge I
would have to say that the subform should be based on a
different table. Several controls on the same form can
have the same field as a control source, or they can be
text boxes referencing other controls on the same form.
Why don't you open the table and try click Autoform on the
toolbar to see what happens?
I suspect there could be problems with your database
structure. That the same product code can be associated
with several component codes and UPC codes suggest that
these should all be in different tables, but without
knowing more about the purpose of the database it is
difficult to tell.