help on primary keys

  • Thread starter Thread starter DCSC
  • Start date Start date
D

DCSC

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!
 
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
 
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.
 
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!
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
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!
 
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.
 
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
 
Back
Top