Using append queries and macros for a database

[Guest]

I'm trying to create a master form and use append queries to allow me to
enter new records into all the tables at once. All of the tables use the
same primary field and are connected to a main table that has a one to many
relationship with all of them. I'm trying to create a field in the form that
will allow me to insert a new record into all the tables at once with the use
of a command button. I've tried to do this through creating a series of
append queries and then putting those queries together on a macro using their
SQL statements. When I try to run the macro, I keep getting the message
'duplicate output destination (field name)'. Would it be better to try this
using visual basic and creating an SQL string? Or will I have to start
everything over from scratch and create one huge table to input the data on?
Thanks in advance for your help.

 

[Guest]

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks, Ken-

I did the subforms on the main form and set the Link child and Link master
fields, but I still can't input new records, even after setting all data
entry properties to yes. I have the primary key of the soldiers name on the
main form, and have it set as a foreign key to all of the other forms, and
then I use that to look up and input data into all of the other records. Do
I have it all backwards? Do I need to have the primary key as the soldiers
name in all of the other tables, and have it as the foreign key to the main
one and then allow updates? Or do I have it set up right and have to have
the name block in each of the subforms? I can do edits through a query, so I
just want to be able to input new records and then edit them through a query.
Thanks in advance for the help.
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

 

[Guest]

Corey:

Firstly names don't make good keys as they can be duplicated, even within a
small set of people; I once worked with two Maggie Taylors! Its best when
dealing with people to use a unique numeric primary key, and this can be an
autonumber field so whenever a new record is added a value will be entered
automatically. The actual values are immaterial, it’s the uniqueness that
matters. Call it something like PersonnelID. When dealing with values which
are unique, e.g. US states then as the names of the states are unique you can
use the name as the key. City names can be duplicated, though, so always use
a CityID numeric key for them, or you could end up confusing Paris, France
with Paris, Texas!

Any table which the Personnel table references in a many-to-one relationship
would also have a unique primary key. Lets say you want to record which
Company each soldier is in, then a Companies table would have a CompanyID
primary key field, and the Personnel table would have a CompanyID foreign key
field. The foreign key field must be a straightforward long integer number,
not an autonumber, however. The primary key of Companies can be an
autonumber though. So long as you only want to record which company a
soldier is currently in then you don't need a subform for this; on the main
Personnel form you'd have a combo box bound to the CompanyID field with a
RowSource property like this:

SELECT CompanyID, Company FROM Companies ORDER BY Company;

The BoundColumn property of the combo box would be 1, its ColumnCount
property 2 and its ColumnWidths property 0cm;8cm (or rough equivalent in
inches, but the first dimension must be zero as this hides the CompanyID so
you just see the text Company names in the combo box).

If, on the other hand there is a one-to-many relationship from Personnel to
another table then you would use a subform. Lets stick with the example of
Companies but assume that, rather than merely recording a soldier's current
company, you want to record all the companies they've served in. For this
you don't have a Companies foreign key in the Personnel table. The Companies
table remains the same as before. What you have here is a many-to-many
relationship between Personnel and Companies, i.e. each soldier can have
served in one or more companies, and each company will obviously have been
made up of more than one soldier. The way this is modelled is by creating a
third table, CompanyPersonnel say, which has two foreign keys, PersonnelID
and CompanyID. The many-to-many relationship has been resolved into two
one-to-many relationships, one from Personnel to CompanyPersonnel, the other
from Companies to CompanyPersonnel.

The CompanyPersonnel table would also have other fields such as the dates a
soldier joined and left a company, along with any others you might need to
record data relevant to a soldiers service in the company in question.

To incorporate this in your form, as the relationship from Personnel to
CompanyPersonnel is one-to-many, you'd use a subform based on the
CompanyPersonnel table (or better still a query on the table sorted by the
DateJoined field so the records are shown in date order). The
LinkMasterFields and LinkChildFields properties of the subform control would
be PersonnelID.

In this subform you'd have a combo box bound to the CompanyID field exactly
as in the example above in the main form where you are only recording a
soldier's current company. In addition you'd have text boxes bound to the
DateJoined and DateLeft fields etc. For each company a soldier has served in
a new record is entered in the subform.

I hope the above illustrates the general principles involved and will help
you with your own tables and forms, but if you need any clarification, or
there's any other aspect I can help with then don't hesitate to post back.

Ken Sheridan
Stafford, England

Thanks, Ken-

I did the subforms on the main form and set the Link child and Link master
fields, but I still can't input new records, even after setting all data
entry properties to yes. I have the primary key of the soldiers name on the
main form, and have it set as a foreign key to all of the other forms, and
then I use that to look up and input data into all of the other records. Do
I have it all backwards? Do I need to have the primary key as the soldiers
name in all of the other tables, and have it as the foreign key to the main
one and then allow updates? Or do I have it set up right and have to have
the name block in each of the subforms? I can do edits through a query, so I
just want to be able to input new records and then edit them through a query.
Thanks in advance for the help.
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

I'm trying to create a master form and use append queries to allow me to
enter new records into all the tables at once. All of the tables use the
same primary field and are connected to a main table that has a one to many
relationship with all of them. I'm trying to create a field in the form that
will allow me to insert a new record into all the tables at once with the use
of a command button. I've tried to do this through creating a series of
append queries and then putting those queries together on a macro using their
SQL statements. When I try to run the macro, I keep getting the message
'duplicate output destination (field name)'. Would it be better to try this
using visual basic and creating an SQL string? Or will I have to start
everything over from scratch and create one huge table to input the data on?
Thanks in advance for your help.

 

[Guest]

Thanks again, Ken. I had my relationships all goofed up with the primary
keys. I did manage to get it all working right again after taking a few
steps back. Now I'm on to splitting the database and putting it in a folder
so that multiple users can access it. A question on that, though. Can I,
using the same template for everyone, have a front end that connects to
multiple back-ends? For example, I set up a database that one unit uses and
copy it for all of the other units within the battalion. Each database has
it's own set of records so that those personnel within the unit can access
that information, but none of the other units can- they are all the same
set-up, though. Can I then have a front end that pulls the information from
all of those databases' back ends? I want it set up in a hierarchal style,
so that the higher HQ can pull records from all of those databases, and still
have it so the units can't access each other's records. Am I on the right
track with the splitting? Or can you only link to one back-end table?
Thanks in advance.
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

Firstly names don't make good keys as they can be duplicated, even within a
small set of people; I once worked with two Maggie Taylors! Its best when
dealing with people to use a unique numeric primary key, and this can be an
autonumber field so whenever a new record is added a value will be entered
automatically. The actual values are immaterial, it’s the uniqueness that
matters. Call it something like PersonnelID. When dealing with values which
are unique, e.g. US states then as the names of the states are unique you can
use the name as the key. City names can be duplicated, though, so always use
a CityID numeric key for them, or you could end up confusing Paris, France
with Paris, Texas!

Any table which the Personnel table references in a many-to-one relationship
would also have a unique primary key. Lets say you want to record which
Company each soldier is in, then a Companies table would have a CompanyID
primary key field, and the Personnel table would have a CompanyID foreign key
field. The foreign key field must be a straightforward long integer number,
not an autonumber, however. The primary key of Companies can be an
autonumber though. So long as you only want to record which company a
soldier is currently in then you don't need a subform for this; on the main
Personnel form you'd have a combo box bound to the CompanyID field with a
RowSource property like this:

SELECT CompanyID, Company FROM Companies ORDER BY Company;

The BoundColumn property of the combo box would be 1, its ColumnCount
property 2 and its ColumnWidths property 0cm;8cm (or rough equivalent in
inches, but the first dimension must be zero as this hides the CompanyID so
you just see the text Company names in the combo box).

If, on the other hand there is a one-to-many relationship from Personnel to
another table then you would use a subform. Lets stick with the example of
Companies but assume that, rather than merely recording a soldier's current
company, you want to record all the companies they've served in. For this
you don't have a Companies foreign key in the Personnel table. The Companies
table remains the same as before. What you have here is a many-to-many
relationship between Personnel and Companies, i.e. each soldier can have
served in one or more companies, and each company will obviously have been
made up of more than one soldier. The way this is modelled is by creating a
third table, CompanyPersonnel say, which has two foreign keys, PersonnelID
and CompanyID. The many-to-many relationship has been resolved into two
one-to-many relationships, one from Personnel to CompanyPersonnel, the other
from Companies to CompanyPersonnel.

The CompanyPersonnel table would also have other fields such as the dates a
soldier joined and left a company, along with any others you might need to
record data relevant to a soldiers service in the company in question.

To incorporate this in your form, as the relationship from Personnel to
CompanyPersonnel is one-to-many, you'd use a subform based on the
CompanyPersonnel table (or better still a query on the table sorted by the
DateJoined field so the records are shown in date order). The
LinkMasterFields and LinkChildFields properties of the subform control would
be PersonnelID.

In this subform you'd have a combo box bound to the CompanyID field exactly
as in the example above in the main form where you are only recording a
soldier's current company. In addition you'd have text boxes bound to the
DateJoined and DateLeft fields etc. For each company a soldier has served in
a new record is entered in the subform.

I hope the above illustrates the general principles involved and will help
you with your own tables and forms, but if you need any clarification, or
there's any other aspect I can help with then don't hesitate to post back.

Ken Sheridan
Stafford, England

Thanks, Ken-

I did the subforms on the main form and set the Link child and Link master
fields, but I still can't input new records, even after setting all data
entry properties to yes. I have the primary key of the soldiers name on the
main form, and have it set as a foreign key to all of the other forms, and
then I use that to look up and input data into all of the other records. Do
I have it all backwards? Do I need to have the primary key as the soldiers
name in all of the other tables, and have it as the foreign key to the main
one and then allow updates? Or do I have it set up right and have to have
the name block in each of the subforms? I can do edits through a query, so I
just want to be able to input new records and then edit them through a query.
Thanks in advance for the help.
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

:

I'm trying to create a master form and use append queries to allow me to
enter new records into all the tables at once. All of the tables use the
same primary field and are connected to a main table that has a one to many
relationship with all of them. I'm trying to create a field in the form that
will allow me to insert a new record into all the tables at once with the use
of a command button. I've tried to do this through creating a series of
append queries and then putting those queries together on a macro using their
SQL statements. When I try to run the macro, I keep getting the message
'duplicate output destination (field name)'. Would it be better to try this
using visual basic and creating an SQL string? Or will I have to start
everything over from scratch and create one huge table to input the data on?
Thanks in advance for your help.

 

[Guest]

Corey:

There is nothing to stop you linking to multiple back ends. What you can't
do, though, is create relationships between tables in different back ends.
You can join tables from different back ends in queries, but you can only
create relationships and enforce referential integrity within one file.

Another drawback is that when you need to pull information together from the
different back ends for the higher level HQ personnel to use you'd most
likely use UNION operations. A UNION query can't be updated, however, so
this would make the consolidated data read only. There are ways around that,
but it makes for more work in developing the front ends and can get messy.

What you can do, however, is use a single back end and control the rows in
tables each level of the hierarchy has access to by giving them access to the
data only via queries in the front ends which restrict the rows returned. A
very simple scenario would be to have a column in the main Personnel table
called AccessCode say. Lets say there are 5 units each requiring access to a
distinct set of rows from the table. You could have codes A, B, C,D, E, F.
So one unit has access via a query:

SELECT *
FROM Personnel
WHERE AccessCode = "A"

another:

WHERE AccessCode = "B"

and so on. If a level of the hierarchy requires access to A and B rows only
then:

WHERE AccessCode IN("A", "B")

For the top level, with access to all rows, then the WHERE clause is omitted
completely.

What this implies, of course, is that there is no way users can bypass this
very primitive security, which is impossible to guarantee in a non-secured
database, though there are steps you can take to make it as difficult as
possible. To achieve a good level of security, however, you'd really need to
implement user and group security, whereby you can assign permissions to
different user groups and then assign users to those groups. A user can be a
member of more than one group, so as you move up the hierarchy the users will
be members of more groups. You'll find details of user and group security at:


http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp


I'm not sure if there is a later version of the article, but I don't think
things have changed much since it was written. Whether you do need to
implement user and group security is something only you can decide in the
context of the sensitivity of the data and how far you think users might be
inclined and/or able to bypass less secure methods of restricting their
access to data.

Some time ago I did produce a little demo file which simulates how to
implement row level security in Access. This uses an integer value column in
each row of the table as a set of flags to indicate which groups that row is
available to, each bit representing a group. It could also be done by having
a related table with a foreign key column referencing the key of the main
table, so for each group a row was available to there would a row in the
related table referencing that row in the main table. You'll find it at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


Ken Sheridan
Stafford, England

Thanks again, Ken. I had my relationships all goofed up with the primary
keys. I did manage to get it all working right again after taking a few
steps back. Now I'm on to splitting the database and putting it in a folder
so that multiple users can access it. A question on that, though. Can I,
using the same template for everyone, have a front end that connects to
multiple back-ends? For example, I set up a database that one unit uses and
copy it for all of the other units within the battalion. Each database has
it's own set of records so that those personnel within the unit can access
that information, but none of the other units can- they are all the same
set-up, though. Can I then have a front end that pulls the information from
all of those databases' back ends? I want it set up in a hierarchal style,
so that the higher HQ can pull records from all of those databases, and still
have it so the units can't access each other's records. Am I on the right
track with the splitting? Or can you only link to one back-end table?
Thanks in advance.
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

Firstly names don't make good keys as they can be duplicated, even within a
small set of people; I once worked with two Maggie Taylors! Its best when
dealing with people to use a unique numeric primary key, and this can be an
autonumber field so whenever a new record is added a value will be entered
automatically. The actual values are immaterial, it’s the uniqueness that
matters. Call it something like PersonnelID. When dealing with values which
are unique, e.g. US states then as the names of the states are unique you can
use the name as the key. City names can be duplicated, though, so always use
a CityID numeric key for them, or you could end up confusing Paris, France
with Paris, Texas!

Any table which the Personnel table references in a many-to-one relationship
would also have a unique primary key. Lets say you want to record which
Company each soldier is in, then a Companies table would have a CompanyID
primary key field, and the Personnel table would have a CompanyID foreign key
field. The foreign key field must be a straightforward long integer number,
not an autonumber, however. The primary key of Companies can be an
autonumber though. So long as you only want to record which company a
soldier is currently in then you don't need a subform for this; on the main
Personnel form you'd have a combo box bound to the CompanyID field with a
RowSource property like this:

SELECT CompanyID, Company FROM Companies ORDER BY Company;

The BoundColumn property of the combo box would be 1, its ColumnCount
property 2 and its ColumnWidths property 0cm;8cm (or rough equivalent in
inches, but the first dimension must be zero as this hides the CompanyID so
you just see the text Company names in the combo box).

If, on the other hand there is a one-to-many relationship from Personnel to
another table then you would use a subform. Lets stick with the example of
Companies but assume that, rather than merely recording a soldier's current
company, you want to record all the companies they've served in. For this
you don't have a Companies foreign key in the Personnel table. The Companies
table remains the same as before. What you have here is a many-to-many
relationship between Personnel and Companies, i.e. each soldier can have
served in one or more companies, and each company will obviously have been
made up of more than one soldier. The way this is modelled is by creating a
third table, CompanyPersonnel say, which has two foreign keys, PersonnelID
and CompanyID. The many-to-many relationship has been resolved into two
one-to-many relationships, one from Personnel to CompanyPersonnel, the other
from Companies to CompanyPersonnel.

The CompanyPersonnel table would also have other fields such as the dates a
soldier joined and left a company, along with any others you might need to
record data relevant to a soldiers service in the company in question.

To incorporate this in your form, as the relationship from Personnel to
CompanyPersonnel is one-to-many, you'd use a subform based on the
CompanyPersonnel table (or better still a query on the table sorted by the
DateJoined field so the records are shown in date order). The
LinkMasterFields and LinkChildFields properties of the subform control would
be PersonnelID.

In this subform you'd have a combo box bound to the CompanyID field exactly
as in the example above in the main form where you are only recording a
soldier's current company. In addition you'd have text boxes bound to the
DateJoined and DateLeft fields etc. For each company a soldier has served in
a new record is entered in the subform.

I hope the above illustrates the general principles involved and will help
you with your own tables and forms, but if you need any clarification, or
there's any other aspect I can help with then don't hesitate to post back.

Ken Sheridan
Stafford, England

Thanks, Ken-

I did the subforms on the main form and set the Link child and Link master
fields, but I still can't input new records, even after setting all data
entry properties to yes. I have the primary key of the soldiers name on the
main form, and have it set as a foreign key to all of the other forms, and
then I use that to look up and input data into all of the other records. Do
I have it all backwards? Do I need to have the primary key as the soldiers
name in all of the other tables, and have it as the foreign key to the main
one and then allow updates? Or do I have it set up right and have to have
the name block in each of the subforms? I can do edits through a query, so I
just want to be able to input new records and then edit them through a query.
Thanks in advance for the help.
--
SSG Corey Hite
2/34th BSTB
IA ARNG


:

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

:

I'm trying to create a master form and use append queries to allow me to
enter new records into all the tables at once. All of the tables use the
same primary field and are connected to a main table that has a one to many
relationship with all of them. I'm trying to create a field in the form that
will allow me to insert a new record into all the tables at once with the use
of a command button. I've tried to do this through creating a series of
append queries and then putting those queries together on a macro using their
SQL statements. When I try to run the macro, I keep getting the message
'duplicate output destination (field name)'. Would it be better to try this
using visual basic and creating an SQL string? Or will I have to start
everything over from scratch and create one huge table to input the data on?
Thanks in advance for your help.

 

[Guest]

Ken,

Thanks again. Let me make sure I understand this right. What I need to do
is put it all on one database and create a table that assigns soldiers to
certain groups, say through unit assignment. I then create queries that pull
information for that particular group of soldiers, and grant user permissions
to those queries so they only access those soldiers as opposed to every
soldier in the database. Is that right? Or can I put it on one big query
and assign user permissions for a certain value in a particular row?
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

There is nothing to stop you linking to multiple back ends. What you can't
do, though, is create relationships between tables in different back ends.
You can join tables from different back ends in queries, but you can only
create relationships and enforce referential integrity within one file.

Another drawback is that when you need to pull information together from the
different back ends for the higher level HQ personnel to use you'd most
likely use UNION operations. A UNION query can't be updated, however, so
this would make the consolidated data read only. There are ways around that,
but it makes for more work in developing the front ends and can get messy.

What you can do, however, is use a single back end and control the rows in
tables each level of the hierarchy has access to by giving them access to the
data only via queries in the front ends which restrict the rows returned. A
very simple scenario would be to have a column in the main Personnel table
called AccessCode say. Lets say there are 5 units each requiring access to a
distinct set of rows from the table. You could have codes A, B, C,D, E, F.
So one unit has access via a query:

SELECT *
FROM Personnel
WHERE AccessCode = "A"

another:

WHERE AccessCode = "B"

and so on. If a level of the hierarchy requires access to A and B rows only
then:

WHERE AccessCode IN("A", "B")

For the top level, with access to all rows, then the WHERE clause is omitted
completely.

What this implies, of course, is that there is no way users can bypass this
very primitive security, which is impossible to guarantee in a non-secured
database, though there are steps you can take to make it as difficult as
possible. To achieve a good level of security, however, you'd really need to
implement user and group security, whereby you can assign permissions to
different user groups and then assign users to those groups. A user can be a
member of more than one group, so as you move up the hierarchy the users will
be members of more groups. You'll find details of user and group security at:


http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp


I'm not sure if there is a later version of the article, but I don't think
things have changed much since it was written. Whether you do need to
implement user and group security is something only you can decide in the
context of the sensitivity of the data and how far you think users might be
inclined and/or able to bypass less secure methods of restricting their
access to data.

Some time ago I did produce a little demo file which simulates how to
implement row level security in Access. This uses an integer value column in
each row of the table as a set of flags to indicate which groups that row is
available to, each bit representing a group. It could also be done by having
a related table with a foreign key column referencing the key of the main
table, so for each group a row was available to there would a row in the
related table referencing that row in the main table. You'll find it at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


Ken Sheridan
Stafford, England

Thanks again, Ken. I had my relationships all goofed up with the primary
keys. I did manage to get it all working right again after taking a few
steps back. Now I'm on to splitting the database and putting it in a folder
so that multiple users can access it. A question on that, though. Can I,
using the same template for everyone, have a front end that connects to
multiple back-ends? For example, I set up a database that one unit uses and
copy it for all of the other units within the battalion. Each database has
it's own set of records so that those personnel within the unit can access
that information, but none of the other units can- they are all the same
set-up, though. Can I then have a front end that pulls the information from
all of those databases' back ends? I want it set up in a hierarchal style,
so that the higher HQ can pull records from all of those databases, and still
have it so the units can't access each other's records. Am I on the right
track with the splitting? Or can you only link to one back-end table?
Thanks in advance.
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

Firstly names don't make good keys as they can be duplicated, even within a
small set of people; I once worked with two Maggie Taylors! Its best when
dealing with people to use a unique numeric primary key, and this can be an
autonumber field so whenever a new record is added a value will be entered
automatically. The actual values are immaterial, it’s the uniqueness that
matters. Call it something like PersonnelID. When dealing with values which
are unique, e.g. US states then as the names of the states are unique you can
use the name as the key. City names can be duplicated, though, so always use
a CityID numeric key for them, or you could end up confusing Paris, France
with Paris, Texas!

Any table which the Personnel table references in a many-to-one relationship
would also have a unique primary key. Lets say you want to record which
Company each soldier is in, then a Companies table would have a CompanyID
primary key field, and the Personnel table would have a CompanyID foreign key
field. The foreign key field must be a straightforward long integer number,
not an autonumber, however. The primary key of Companies can be an
autonumber though. So long as you only want to record which company a
soldier is currently in then you don't need a subform for this; on the main
Personnel form you'd have a combo box bound to the CompanyID field with a
RowSource property like this:

SELECT CompanyID, Company FROM Companies ORDER BY Company;

The BoundColumn property of the combo box would be 1, its ColumnCount
property 2 and its ColumnWidths property 0cm;8cm (or rough equivalent in
inches, but the first dimension must be zero as this hides the CompanyID so
you just see the text Company names in the combo box).

If, on the other hand there is a one-to-many relationship from Personnel to
another table then you would use a subform. Lets stick with the example of
Companies but assume that, rather than merely recording a soldier's current
company, you want to record all the companies they've served in. For this
you don't have a Companies foreign key in the Personnel table. The Companies
table remains the same as before. What you have here is a many-to-many
relationship between Personnel and Companies, i.e. each soldier can have
served in one or more companies, and each company will obviously have been
made up of more than one soldier. The way this is modelled is by creating a
third table, CompanyPersonnel say, which has two foreign keys, PersonnelID
and CompanyID. The many-to-many relationship has been resolved into two
one-to-many relationships, one from Personnel to CompanyPersonnel, the other
from Companies to CompanyPersonnel.

The CompanyPersonnel table would also have other fields such as the dates a
soldier joined and left a company, along with any others you might need to
record data relevant to a soldiers service in the company in question.

To incorporate this in your form, as the relationship from Personnel to
CompanyPersonnel is one-to-many, you'd use a subform based on the
CompanyPersonnel table (or better still a query on the table sorted by the
DateJoined field so the records are shown in date order). The
LinkMasterFields and LinkChildFields properties of the subform control would
be PersonnelID.

In this subform you'd have a combo box bound to the CompanyID field exactly
as in the example above in the main form where you are only recording a
soldier's current company. In addition you'd have text boxes bound to the
DateJoined and DateLeft fields etc. For each company a soldier has served in
a new record is entered in the subform.

I hope the above illustrates the general principles involved and will help
you with your own tables and forms, but if you need any clarification, or
there's any other aspect I can help with then don't hesitate to post back.

Ken Sheridan
Stafford, England

:

Thanks, Ken-

I did the subforms on the main form and set the Link child and Link master
fields, but I still can't input new records, even after setting all data
entry properties to yes. I have the primary key of the soldiers name on the
main form, and have it set as a foreign key to all of the other forms, and
then I use that to look up and input data into all of the other records. Do
I have it all backwards? Do I need to have the primary key as the soldiers
name in all of the other tables, and have it as the foreign key to the main
one and then allow updates? Or do I have it set up right and have to have
the name block in each of the subforms? I can do edits through a query, so I
just want to be able to input new records and then edit them through a query.
Thanks in advance for the help.
--
SSG Corey Hite
2/34th BSTB
IA ARNG


:

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

:

I'm trying to create a master form and use append queries to allow me to
enter new records into all the tables at once. All of the tables use the
same primary field and are connected to a main table that has a one to many
relationship with all of them. I'm trying to create a field in the form that
will allow me to insert a new record into all the tables at once with the use
of a command button. I've tried to do this through creating a series of
append queries and then putting those queries together on a macro using their
SQL statements. When I try to run the macro, I keep getting the message
'duplicate output destination (field name)'. Would it be better to try this
using visual basic and creating an SQL string? Or will I have to start
everything over from scratch and create one huge table to input the data on?
Thanks in advance for your help.

 

[Guest]

Corey:

Let's deal firstly with the simplest, but much less secure scenario, in
which user and group security is not implemented:

There are two types of groups involved here, firstly the distinct subsets of
soldiers to whose records access will be granted selectively to users,
secondly the groups of users which determines, by membership of a group or
groups, who will have access to objects in the database and the type of
access. For clarity I'll refer to the first type as sets, the second as
groups.

For the first type, the distinct sets of soldier records, in the table of
personnel, in which each soldier will be represented by one row, you'd have a
column which identifies which set the soldier is in.

For each user group you'd then create a query which gives access to one or
more of the sets of soldiers, so at the lowest level of the hierarchy this
will probably be just one set, a higher level might be several but not all
sets (using the IN operator in the query), the topmost level all sets (no
restriction in the query). You'd give each group of users an amended front
end so that they are using the relevant query or queries to access the
records.

The above approach, while easy to set up, is insecure on two counts.
Firstly a user might be able to use another user's front end which gives a
higher level of access than allowed to the user. This is less likely if the
front ends are physically remote from each other of course. Secondly, even
using the correct front end, a moderately knowledgeable user could bypass the
queries and gain access to the data.

If you do implement user and group security, which is far more secure, the
approach would be more like that in my demo, to which I gave you the link.
Each soldier's record would have a foreign key field referencing a table of
the sets of soldiers, identifying which set (e.g. unit) the soldier is in.
Each record in the table of sets would either have an integer field of flags
as in my demo to indicate which user groups can access that row in the table,
or would be related one-to-many to another table which would have rows
indicating which user groups the record for the set is available to. Queries
would then only return rows where the current user is a member of one or more
of the groups having access to that record, so all users would use the same
front end.

Ken Sheridan
Stafford, England

Ken,

Thanks again. Let me make sure I understand this right. What I need to do
is put it all on one database and create a table that assigns soldiers to
certain groups, say through unit assignment. I then create queries that pull
information for that particular group of soldiers, and grant user permissions
to those queries so they only access those soldiers as opposed to every
soldier in the database. Is that right? Or can I put it on one big query
and assign user permissions for a certain value in a particular row?
--
SSG Corey Hite
2/34th BSTB
IA ARNG

Corey:

There is nothing to stop you linking to multiple back ends. What you can't
do, though, is create relationships between tables in different back ends.
You can join tables from different back ends in queries, but you can only
create relationships and enforce referential integrity within one file.

Another drawback is that when you need to pull information together from the
different back ends for the higher level HQ personnel to use you'd most
likely use UNION operations. A UNION query can't be updated, however, so
this would make the consolidated data read only. There are ways around that,
but it makes for more work in developing the front ends and can get messy.

What you can do, however, is use a single back end and control the rows in
tables each level of the hierarchy has access to by giving them access to the
data only via queries in the front ends which restrict the rows returned. A
very simple scenario would be to have a column in the main Personnel table
called AccessCode say. Lets say there are 5 units each requiring access to a
distinct set of rows from the table. You could have codes A, B, C,D, E, F.
So one unit has access via a query:

SELECT *
FROM Personnel
WHERE AccessCode = "A"

another:

WHERE AccessCode = "B"

and so on. If a level of the hierarchy requires access to A and B rows only
then:

WHERE AccessCode IN("A", "B")

For the top level, with access to all rows, then the WHERE clause is omitted
completely.

What this implies, of course, is that there is no way users can bypass this
very primitive security, which is impossible to guarantee in a non-secured
database, though there are steps you can take to make it as difficult as
possible. To achieve a good level of security, however, you'd really need to
implement user and group security, whereby you can assign permissions to
different user groups and then assign users to those groups. A user can be a
member of more than one group, so as you move up the hierarchy the users will
be members of more groups. You'll find details of user and group security at:


http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp


I'm not sure if there is a later version of the article, but I don't think
things have changed much since it was written. Whether you do need to
implement user and group security is something only you can decide in the
context of the sensitivity of the data and how far you think users might be
inclined and/or able to bypass less secure methods of restricting their
access to data.

Some time ago I did produce a little demo file which simulates how to
implement row level security in Access. This uses an integer value column in
each row of the table as a set of flags to indicate which groups that row is
available to, each bit representing a group. It could also be done by having
a related table with a foreign key column referencing the key of the main
table, so for each group a row was available to there would a row in the
related table referencing that row in the main table. You'll find it at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


Ken Sheridan
Stafford, England

Thanks again, Ken. I had my relationships all goofed up with the primary
keys. I did manage to get it all working right again after taking a few
steps back. Now I'm on to splitting the database and putting it in a folder
so that multiple users can access it. A question on that, though. Can I,
using the same template for everyone, have a front end that connects to
multiple back-ends? For example, I set up a database that one unit uses and
copy it for all of the other units within the battalion. Each database has
it's own set of records so that those personnel within the unit can access
that information, but none of the other units can- they are all the same
set-up, though. Can I then have a front end that pulls the information from
all of those databases' back ends? I want it set up in a hierarchal style,
so that the higher HQ can pull records from all of those databases, and still
have it so the units can't access each other's records. Am I on the right
track with the splitting? Or can you only link to one back-end table?
Thanks in advance.
--
SSG Corey Hite
2/34th BSTB
IA ARNG


:

Corey:

Firstly names don't make good keys as they can be duplicated, even within a
small set of people; I once worked with two Maggie Taylors! Its best when
dealing with people to use a unique numeric primary key, and this can be an
autonumber field so whenever a new record is added a value will be entered
automatically. The actual values are immaterial, it’s the uniqueness that
matters. Call it something like PersonnelID. When dealing with values which
are unique, e.g. US states then as the names of the states are unique you can
use the name as the key. City names can be duplicated, though, so always use
a CityID numeric key for them, or you could end up confusing Paris, France
with Paris, Texas!

Any table which the Personnel table references in a many-to-one relationship
would also have a unique primary key. Lets say you want to record which
Company each soldier is in, then a Companies table would have a CompanyID
primary key field, and the Personnel table would have a CompanyID foreign key
field. The foreign key field must be a straightforward long integer number,
not an autonumber, however. The primary key of Companies can be an
autonumber though. So long as you only want to record which company a
soldier is currently in then you don't need a subform for this; on the main
Personnel form you'd have a combo box bound to the CompanyID field with a
RowSource property like this:

SELECT CompanyID, Company FROM Companies ORDER BY Company;

The BoundColumn property of the combo box would be 1, its ColumnCount
property 2 and its ColumnWidths property 0cm;8cm (or rough equivalent in
inches, but the first dimension must be zero as this hides the CompanyID so
you just see the text Company names in the combo box).

If, on the other hand there is a one-to-many relationship from Personnel to
another table then you would use a subform. Lets stick with the example of
Companies but assume that, rather than merely recording a soldier's current
company, you want to record all the companies they've served in. For this
you don't have a Companies foreign key in the Personnel table. The Companies
table remains the same as before. What you have here is a many-to-many
relationship between Personnel and Companies, i.e. each soldier can have
served in one or more companies, and each company will obviously have been
made up of more than one soldier. The way this is modelled is by creating a
third table, CompanyPersonnel say, which has two foreign keys, PersonnelID
and CompanyID. The many-to-many relationship has been resolved into two
one-to-many relationships, one from Personnel to CompanyPersonnel, the other
from Companies to CompanyPersonnel.

The CompanyPersonnel table would also have other fields such as the dates a
soldier joined and left a company, along with any others you might need to
record data relevant to a soldiers service in the company in question.

To incorporate this in your form, as the relationship from Personnel to
CompanyPersonnel is one-to-many, you'd use a subform based on the
CompanyPersonnel table (or better still a query on the table sorted by the
DateJoined field so the records are shown in date order). The
LinkMasterFields and LinkChildFields properties of the subform control would
be PersonnelID.

In this subform you'd have a combo box bound to the CompanyID field exactly
as in the example above in the main form where you are only recording a
soldier's current company. In addition you'd have text boxes bound to the
DateJoined and DateLeft fields etc. For each company a soldier has served in
a new record is entered in the subform.

I hope the above illustrates the general principles involved and will help
you with your own tables and forms, but if you need any clarification, or
there's any other aspect I can help with then don't hesitate to post back.

Ken Sheridan
Stafford, England

:

Thanks, Ken-

I did the subforms on the main form and set the Link child and Link master
fields, but I still can't input new records, even after setting all data
entry properties to yes. I have the primary key of the soldiers name on the
main form, and have it set as a foreign key to all of the other forms, and
then I use that to look up and input data into all of the other records. Do
I have it all backwards? Do I need to have the primary key as the soldiers
name in all of the other tables, and have it as the foreign key to the main
one and then allow updates? Or do I have it set up right and have to have
the name block in each of the subforms? I can do edits through a query, so I
just want to be able to input new records and then edit them through a query.
Thanks in advance for the help.
--
SSG Corey Hite
2/34th BSTB
IA ARNG


:

Corey:

You should certainly not create a single table, as this would introduce a
lot of redundancy, and the consequent risk of update anomalies. I don’t
really see a lot of point in adding rows into each table in one operation as
all you can insert into the referenced tables (those on the may side of the
relationships) would be the foreign key values corresponding to the primary
key of the referenced table. Also what happens when you want to add another
row to one of the referencing tables after the initial row has been inserted?

You can add a record to the main referenced table then as many or as few to
each of the referencing tables very simply with bound forms, either by using
subforms bound to each of the referencing tables in a parent form bound to
the main referenced table, or by opening linked forms from a form bound to
the referenced table.

If you use subforms then you don’t need any code at all. Just add a subform
to the main form for each referenced table and set the LinkMasterFields and
LinkChildFields properties of each subform control in the main form to the
name of the primary key of the main table and corresponding foreign key of
the referenced table. To keep the size of the form down you can put each
subform on a separate page of a tab control in the main form. If you want
the form and subforms only to be used for adding new records, not for viewing
or editing existing ones, set their DataEntry properties to True (Yes).

To use linked forms, each based on one of the referencing tables, you can
have on the main form a button to open each linked form with code like this,
assuming the primary key of the main form’s table and the corresponding
foreign key of the linked form’s table is called MyID and is a numeric data
type:

DoCmd.OpenForm "LinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

In each linked form's Open event procedure put code to set the DefaultValue
property of the MyID control in the linked form:

If Not IsNull(Me.OpenArgs) Then
Me.[MyID].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the MyID control in the linked form, so a new
record is only begun to be added once you start inserting other data. As
many new records as required for the current main record can be added in the
subform, and each will have the same MyID value.

If you wished you could open each linked form in turn with a single button
just by tacking together the code which calls the OpenForm method:

DoCmd.OpenForm "FirstLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "SecondLinkedLinkedFormName", _
WhereCondition:="[MyID] = " & Me.[MyID], _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=Me.[MyID]

DoCmd.OpenForm "ThirdLinkedLinkedFormName", _
<and so on>

Because each form is opened in dialogue mode by means of the acDialog
setting for the WindowMode argument, the next form won't open until the first
one has been closed. One or more records can be added in each as required,
or any of the linked forms can simply be closed without doing anything more
to move onto the next form without adding a record in the previous one.

Ken Sheridan
Stafford, England

:

I'm trying to create a master form and use append queries to allow me to
enter new records into all the tables at once. All of the tables use the
same primary field and are connected to a main table that has a one to many
relationship with all of them. I'm trying to create a field in the form that
will allow me to insert a new record into all the tables at once with the use
of a command button. I've tried to do this through creating a series of
append queries and then putting those queries together on a macro using their
SQL statements. When I try to run the macro, I keep getting the message
'duplicate output destination (field name)'. Would it be better to try this
using visual basic and creating an SQL string? Or will I have to start
everything over from scratch and create one huge table to input the data on?
Thanks in advance for your help.