help on primary keys

There are several ways to do this. You would first create a relationship
between the two tables on the ProductID column. You would need a one to many
relationship, with the one side being on the Products table, and the many
side on your other table. Then you would go into your 2nd table, and in
design view of the form, go to the ProductID field, and at the bottom there
is a tab called lookup. Change the Display Control to Combo Box, then click
on Row Source and a ... will appear to the right. Click that and add your
products table, then your ProductID and any other fields you want to see in
your dropdown. If you add more than just the ProductID, then you will need
to set the Colum Count, and Column Widths to accomidate how many fields you
want to show in the dropdown. Once you have done this, when you add data to
the 2nd table and you get to the ProductID column, it will be a dropdown that
shows what you have told it to.

Hope this helps.
--
Please remember to mark this post as answered if this solves your problem.


"DCSC" wrote:

> I have 2 tables. Table 1 has ProductID as primary key. I want the same
> ProductID field to appear on Table 2. When data is entered/added in Table 1,
> how would that same data automatically show in Table 2?
>
> I am new to Access so I hope I made sense. Thanks to all those who can help
> me!

Perhaps it would be better if I described the exact scenario. I am creating
an inventory database. Each ProductID can have a number of licenses.

ProductsTable has the fields ProductID, Manufacturer, Version, #ofLicenses,
etc.
LicensesTable has the fields ProductID, Licenses, ExpDate, Staff, etc.

So I created a form with ProductsTable fields in the form header and
LicensesTable fields embedded as a subform in the detail section. Here are
the things I'd like my database to do:

1. If I enter, say "3" in the #ofLicenses field, I'd like for the
LicensesTable to only accept 3 records. Thus, the LicensesTable can only have
the number of records entered in the #ofLicenses field in the ProductsTable.

2. When I click the next record at the very bottom, I'd like for
LicensesTable in the detail section to "go with" the ProductsTable. The
problem I have now is, LicensesTable data stays when the ProductsTable is
clicked to go to the next record.

The relationship of the ProductID field between the ProductsTable and the
LicensesTable is one-to-many.

I couldn't get the right connection so that the records in the License form
stays with its ProductID when I click the form to add a new record.

I hope I was able to give a better description of my problem. Your help is
VEYR MUCH appreciated!

"DCSC" wrote:

> I have 2 tables. Table 1 has ProductID as primary key. I want the same
> ProductID field to appear on Table 2. When data is entered/added in Table 1,
> how would that same data automatically show in Table 2?
>
> I am new to Access so I hope I made sense. Thanks to all those who can help
> me!

Perhaps it would be better if I described the exact scenario. I am creating
an inventory database. Each ProductID can have a number of licenses.

ProductsTable has the fields ProductID, Manufacturer, Version, #ofLicenses,
etc.
LicensesTable has the fields ProductID, Licenses, ExpDate, Staff, etc.

So I created a form with ProductsTable fields in the form header and
LicensesTable fields embedded as a subform in the detail section. Here are
the things I'd like my database to do:

1. If I enter, say "3" in the #ofLicenses field, I'd like for the
LicensesTable to only accept 3 records. Thus, the LicensesTable can only have
the number of records entered in the #ofLicenses field in the ProductsTable.

2. When I click the next record at the very bottom, I'd like for
LicensesTable in the detail section to "go with" the ProductsTable. The
problem I have now is, LicensesTable data stays when the ProductsTable is
clicked to go to the next record.

The relationship of the ProductID field between the ProductsTable and the
LicensesTable is one-to-many.

I couldn't get the right connection so that the records in the License form
stays with its ProductID when I click the form to add a new record.

I hope I was able to give a better description of my problem. Your help is
VEYR MUCH appreciated!



"Jeff Boyce" wrote:

> First, what kind of data does table2 hold? That's an indirect way of asking
> why you need the ProductID from table1 to show up in table2. To put a
> sharper point on it, what is the relationship between the records in table1
> and the records in table2?
>
> FYI, if you are using an Access Autonumber for [ProductID] in table1, you
> will NOT be able to use an Access Autonumber for the 'matching' field in
> table2.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
>
> "DCSC" <DCSC RemoveThis @discussions.microsoft.com> wrote in message
> news:535BDBC3-A539-460D-BF49-69D77B45E24A@microsoft.com...
> >I have 2 tables. Table 1 has ProductID as primary key. I want the same
> > ProductID field to appear on Table 2. When data is entered/added in Table
> > 1,
> > how would that same data automatically show in Table 2?
> >
> > I am new to Access so I hope I made sense. Thanks to all those who can
> > help
> > me!
>
>
>

Perhaps it would be better if I described the exact scenario. I am creating
an inventory database. Each ProductID can have a number of licenses.

ProductsTable has the fields ProductID, Manufacturer, Version, #ofLicenses,
etc.
LicensesTable has the fields ProductID, Licenses, ExpDate, Staff, etc.

So I created a form with ProductsTable fields in the form header and
LicensesTable fields embedded as a subform in the detail section. Here are
the things I'd like my database to do:

1. If I enter, say "3" in the #ofLicenses field, I'd like for the
LicensesTable to only accept 3 records. Thus, the LicensesTable can only have
the number of records entered in the #ofLicenses field in the ProductsTable.

2. When I click the next record at the very bottom, I'd like for
LicensesTable in the detail section to "go with" the ProductsTable. The
problem I have now is, LicensesTable data stays when the ProductsTable is
clicked to go to the next record.

The relationship of the ProductID field between the ProductsTable and the
LicensesTable is one-to-many.

I couldn't get the right connection so that the records in the License form
stays with its ProductID when I click the form to add a new record.

I hope I was able to give a better description of my problem. Your help is
VEYR MUCH appreciated!

"Ryan" wrote:

> There are several ways to do this. You would first create a relationship
> between the two tables on the ProductID column. You would need a one to many
> relationship, with the one side being on the Products table, and the many
> side on your other table. Then you would go into your 2nd table, and in
> design view of the form, go to the ProductID field, and at the bottom there
> is a tab called lookup. Change the Display Control to Combo Box, then click
> on Row Source and a ... will appear to the right. Click that and add your
> products table, then your ProductID and any other fields you want to see in
> your dropdown. If you add more than just the ProductID, then you will need
> to set the Colum Count, and Column Widths to accomidate how many fields you
> want to show in the dropdown. Once you have done this, when you add data to
> the 2nd table and you get to the ProductID column, it will be a dropdown that
> shows what you have told it to.
>
> Hope this helps.
> --
> Please remember to mark this post as answered if this solves your problem.
>
>
> "DCSC" wrote:
>
> > I have 2 tables. Table 1 has ProductID as primary key. I want the same
> > ProductID field to appear on Table 2. When data is entered/added in Table 1,
> > how would that same data automatically show in Table 2?
> >
> > I am new to Access so I hope I made sense. Thanks to all those who can help
> > me!

As Ryan suggests, I would create a relationship between the 2 tables (one to
many - if that is the case). You can create a relationship by clicking on the
menu tab in your Access DB - the tab looks like 3 little monitors (they are
actually tables)connected by lines. make sure you have a ProductID field in
both tables and connect those fields with a relationship line.

One thing to add to Ryan's info. though - I think you may also need to go
into table 1 and designate the ProductID field as the Primary Key - go into
design view, right click on the ProductID field and select Primary Key.

Hope that helps.


"DCSC" wrote:

> I have 2 tables. Table 1 has ProductID as primary key. I want the same
> ProductID field to appear on Table 2. When data is entered/added in Table 1,
> how would that same data automatically show in Table 2?
>
> I am new to Access so I hope I made sense. Thanks to all those who can help
> me!

There are a number of things you need to do to your form and subform:

1. There is no real point putting the controls bound to the ProductTable's
fields in the form header. Because the form contains a subform it must be in
single form view, so the controls can be in the detail section. As the
subform scrolls independently of the parent there is no advantage in using
the parent form's header.

2. The LinkMasterFields and LinkChildFields properties of the subform
control (i.e. the control in the main parent form's Controls collection which
houses the subform) should both be ProductID. This will cause the subform to
show just the licences for the product in the parent form's current record.
It will also automatically insert the correct ProductID value when a new
licence record is inserted in the subform.

3. To control the maximum number of records per product which can be
inserted into the subform put the following code in its Current, AfterUpdate
and AfterDelConfirm event procedures:

Dim intMaxRecs As Integer

intMaxRecs = Nz(Me.Parent.[#ofLicences], 0)

Me.AllowAdditions = (Me.RecordsetClone.RecordCount < intMaxRecs)

4. In the AfterUpdate event procedure of the #ofLicences control on the
parent form put:

Dim frm As Form
Dim intMaxRecs As Integer

Set frm = Me.sfLicences.Form

intMaxRecs = Nz(Me.[#ofLicences], 0)

frm.AllowAdditions = (frm.RecordsetClone.RecordCount < intMaxRecs)

where sfLicences is the name of the subform control (i.e. again the control
in the main parent form's Controls collection which houses the subform)

This only provides control at form level over the number of licences per
product, so there is nothing to prevent additional rows being inserted into
the table by other means. You need to ensure, therefore, that data entry is
only available to users via the form/subform

Ken Sheridan
Stafford, England

"DCSC" wrote:

> Perhaps it would be better if I described the exact scenario. I am creating
> an inventory database. Each ProductID can have a number of licenses.
>
> ProductsTable has the fields ProductID, Manufacturer, Version, #ofLicenses,
> etc.
> LicensesTable has the fields ProductID, Licenses, ExpDate, Staff, etc.
>
> So I created a form with ProductsTable fields in the form header and
> LicensesTable fields embedded as a subform in the detail section. Here are
> the things I'd like my database to do:
>
> 1. If I enter, say "3" in the #ofLicenses field, I'd like for the
> LicensesTable to only accept 3 records. Thus, the LicensesTable can only have
> the number of records entered in the #ofLicenses field in the ProductsTable.
>
> 2. When I click the next record at the very bottom, I'd like for
> LicensesTable in the detail section to "go with" the ProductsTable. The
> problem I have now is, LicensesTable data stays when the ProductsTable is
> clicked to go to the next record.
>
> The relationship of the ProductID field between the ProductsTable and the
> LicensesTable is one-to-many.
>
> I couldn't get the right connection so that the records in the License form
> stays with its ProductID when I click the form to add a new record.
>
> I hope I was able to give a better description of my problem. Your help is
> VEYR MUCH appreciated!
>
> "DCSC" wrote:
>
> > I have 2 tables. Table 1 has ProductID as primary key. I want the same
> > ProductID field to appear on Table 2. When data is entered/added in Table 1,
> > how would that same data automatically show in Table 2?
> >
> > I am new to Access so I hope I made sense. Thanks to all those who can help
> > me!