Forms and Subforms

To all you MS-Access gurus out there, thank you very much in advance for your
help.

I am trying to create a Form containing a Subform. The Form is based on a
table (Vendors) with the fields VendorID (primary key) and VendorName. The
Subform I am trying to create contains the fields contained in a query that
combines at least 3 other linked tables The first table (Contracts) contains
a ContractID (primary key) and the VendorID (foreign key). The second table
(Repname) contains sales rep ID (primary key), sales rep names ( first name
and last name), ContractID (foreign key) and VendorMgrSubGrpID (foreign key)
and the third table (VendorMgrSubGrp) contains VendorMgrSubGrpID (primary
key) and the sales rep team description (VendorMgrSubGrpCode).

All the tables are linked as follows:
Vendors:Contracts = 1:many
Contracts: Repname = many:many via a Junction table
RepName:VendorMgrSubGrp = many:1

I am experiencing the following difficulties:

1) I am unable to change (edit) values of any of the fields in the subform
2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?
3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?
4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

Unfortunately I am not a coder, so would like to avoid resorting to this if
at all possible.

Your sage advice would be greatly appreciated.

P.S. I have created a small example database containing all these elements
if you need to analyse it.

Kind Regards,

vykc00

Provided that you have set all the sub form attributes correctly, I doubt
whether the inability to edit the records is to do with the sub form. Go
back to your query and see if you can edit the fields there. If not then you
need to redesign your SQL - probably the joins.

Regards,

Rod

PS Let's solve #2 when #1 is working.

"vykc00" wrote:

To all you MS-Access gurus out there, thank you very much in advance for your
help.

I am trying to create a Form containing a Subform. The Form is based on a
table (Vendors) with the fields VendorID (primary key) and VendorName. The
Subform I am trying to create contains the fields contained in a query that
combines at least 3 other linked tables The first table (Contracts) contains
a ContractID (primary key) and the VendorID (foreign key). The second table
(Repname) contains sales rep ID (primary key), sales rep names ( first name
and last name), ContractID (foreign key) and VendorMgrSubGrpID (foreign key)
and the third table (VendorMgrSubGrp) contains VendorMgrSubGrpID (primary
key) and the sales rep team description (VendorMgrSubGrpCode).

All the tables are linked as follows:
Vendors:Contracts = 1:many
Contracts: Repname = many:many via a Junction table
RepName:VendorMgrSubGrp = many:1

I am experiencing the following difficulties:

1) I am unable to change (edit) values of any of the fields in the subform
2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?
3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?
4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

Unfortunately I am not a coder, so would like to avoid resorting to this if
at all possible.

Your sage advice would be greatly appreciated.

P.S. I have created a small example database containing all these elements
if you need to analyse it.

Kind Regards,

vykc00

Thanks very much for your help Rod.

I have followed your advice, and it seems that the query is not editable
either.

If the table joins is the cause, then perhaps I have set up the Junction
table between the Contracts and Repname tables incorrectly?

The Contracts and Repname tables have a Many:Many relationship so I have
created a Junction Table containing each table's primary key (i.e. ContractID
and RepNameID), with each table having a 1:Many relationship with the
Junction Table (i.e. TblContract:TblJunction = 1:Many, TblRepName:TblJunction
= 1:Many).

Is this correct?

It's a pity attachments aren't permitted in this forum. Pictures (and
sample d/bases) are worth a thousand words.

Kind Regards,

vykc00

"Rod Plastow" wrote:

Provided that you have set all the sub form attributes correctly, I doubt
whether the inability to edit the records is to do with the sub form. Go
back to your query and see if you can edit the fields there. If not then you
need to redesign your SQL - probably the joins.

Regards,

Rod

PS Let's solve #2 when #1 is working.

"vykc00" wrote:

To all you MS-Access gurus out there, thank you very much in advance for your
help.

I am trying to create a Form containing a Subform. The Form is based on a
table (Vendors) with the fields VendorID (primary key) and VendorName.

The Subform I am trying to create contains the fields contained in a query
that
combines at least 3 other linked tables.

The first table (Contracts) contains a ContractID (primary key) and the
VendorID (foreign key).
The second table (Repname) contains sales rep ID (primary key), sales rep
names ( first name and last name), ContractID (foreign key) and
VendorMgrSubGrpID (foreign key),

and the third table (VendorMgrSubGrp) contains VendorMgrSubGrpID (primary
key) and the sales rep team description (VendorMgrSubGrpCode).

All the tables are linked as follows:
Vendors:Contracts = 1:many
Contracts: Repname = many:many via a Junction table
RepName:VendorMgrSubGrp = many:1

I am experiencing the following difficulties:

1) I am unable to change (edit) values of any of the fields in the subform
2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?
3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?
4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

Unfortunately I am not a coder, so would like to avoid resorting to this if
at all possible.

Your sage advice would be greatly appreciated.

P.S. I have created a small example database containing all these elements
if you need to analyse it.

Kind Regards,

vykc00

Hi,

I agree with your comment about attachments - you cannot even paste a
picture in-line, or at least it won't work for me - but I do understand the
reasons for this restriction.

OK, we've taken a step forward and demonstrated that your problem has
nothing to do with subforms but is the query itself. From your description
there is nothing wrong with the way you have set up your junction table, it
is in fact the classic way of resolving a many:many relationship. Also,
reading between the lines, I suspect you have - correctly - used the
Relationships functionality to define the relationships between your tables
to the database manager. If so, one of the consequences is that Access
retrieves these relationships when you define a query - and this is not
always what you want! Sounds strange? Well it's not when you really think
about it. Very often Access will report it can't run the query because of
ambiguous joins, at other times it simply locks the fields and keeps mum.

I suggest you delete each join in your query (don't worry this does not
affect the database relationship) and redefine them. Pay particular
attention to the direction of the join.

If you can't get the query working then zip your mdb and sent it to me at



Regards,

Rod

"vykc00" wrote:

Thanks very much for your help Rod.

I have followed your advice, and it seems that the query is not editable
either.

If the table joins is the cause, then perhaps I have set up the Junction
table between the Contracts and Repname tables incorrectly?

The Contracts and Repname tables have a Many:Many relationship so I have
created a Junction Table containing each table's primary key (i.e. ContractID
and RepNameID), with each table having a 1:Many relationship with the
Junction Table (i.e. TblContract:TblJunction = 1:Many, TblRepName:TblJunction
= 1:Many).

Is this correct?

It's a pity attachments aren't permitted in this forum. Pictures (and
sample d/bases) are worth a thousand words.

Kind Regards,

vykc00

"Rod Plastow" wrote:

Provided that you have set all the sub form attributes correctly, I doubt
whether the inability to edit the records is to do with the sub form. Go
back to your query and see if you can edit the fields there. If not then you
need to redesign your SQL - probably the joins.

Regards,

Rod

PS Let's solve #2 when #1 is working.

"vykc00" wrote:

To all you MS-Access gurus out there, thank you very much in advance for your
help.

I am trying to create a Form containing a Subform. The Form is based on a
table (Vendors) with the fields VendorID (primary key) and VendorName.

The Subform I am trying to create contains the fields contained in a query
that
combines at least 3 other linked tables.

The first table (Contracts) contains a ContractID (primary key) and the
VendorID (foreign key).
The second table (Repname) contains sales rep ID (primary key), sales rep
names ( first name and last name), ContractID (foreign key) and
VendorMgrSubGrpID (foreign key),

and the third table (VendorMgrSubGrp) contains VendorMgrSubGrpID (primary
key) and the sales rep team description (VendorMgrSubGrpCode).

All the tables are linked as follows:
Vendors:Contracts = 1:many
Contracts: Repname = many:many via a Junction table
RepName:VendorMgrSubGrp = many:1

I am experiencing the following difficulties:

1) I am unable to change (edit) values of any of the fields in the subform
2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?
3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?
4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

Unfortunately I am not a coder, so would like to avoid resorting to this if
at all possible.

Your sage advice would be greatly appreciated.

P.S. I have created a small example database containing all these elements
if you need to analyse it.

Kind Regards,

vykc00

Hi Rod,

Thank you for your response.

Your suggestion was a good one, as even in my limited experience I have
found that MS-Access is prone to some quirky errors.

Over the weekend I removed and then re-instated the joins in my query.
Unfortunately this doesn't seem to have had any effect, so I hope you don't
mind if I take up your kind offer to send you a copy of my sample database
to have a look at directly?

Many Thanks.

"Rod Plastow" wrote:

Hi,

I agree with your comment about attachments - you cannot even paste a
picture in-line, or at least it won't work for me - but I do understand the
reasons for this restriction.

OK, we've taken a step forward and demonstrated that your problem has
nothing to do with subforms but is the query itself. From your description
there is nothing wrong with the way you have set up your junction table, it
is in fact the classic way of resolving a many:many relationship. Also,
reading between the lines, I suspect you have - correctly - used the
Relationships functionality to define the relationships between your tables
to the database manager. If so, one of the consequences is that Access
retrieves these relationships when you define a query - and this is not
always what you want! Sounds strange? Well it's not when you really think
about it. Very often Access will report it can't run the query because of
ambiguous joins, at other times it simply locks the fields and keeps mum.

I suggest you delete each join in your query (don't worry this does not
affect the database relationship) and redefine them. Pay particular
attention to the direction of the join.

If you can't get the query working then zip your mdb and sent it to me at



Regards,

Rod

"vykc00" wrote:

Thanks very much for your help Rod.

I have followed your advice, and it seems that the query is not editable
either.

If the table joins is the cause, then perhaps I have set up the Junction
table between the Contracts and Repname tables incorrectly?

The Contracts and Repname tables have a Many:Many relationship so I have
created a Junction Table containing each table's primary key (i.e. ContractID
and RepNameID), with each table having a 1:Many relationship with the
Junction Table (i.e. TblContract:TblJunction = 1:Many, TblRepName:TblJunction
= 1:Many).

Is this correct?

It's a pity attachments aren't permitted in this forum. Pictures (and
sample d/bases) are worth a thousand words.

Kind Regards,

vykc00

"Rod Plastow" wrote:

Provided that you have set all the sub form attributes correctly, I doubt
whether the inability to edit the records is to do with the sub form. Go
back to your query and see if you can edit the fields there. If not then you
need to redesign your SQL - probably the joins.

Regards,

Rod

PS Let's solve #2 when #1 is working.

"vykc00" wrote:

To all you MS-Access gurus out there, thank you very much in advance for your
help.

I am trying to create a Form containing a Subform. The Form is based on a
table (Vendors) with the fields VendorID (primary key) and VendorName.

The Subform I am trying to create contains the fields contained in a query
that
combines at least 3 other linked tables.

The first table (Contracts) contains a ContractID (primary key) and the
VendorID (foreign key).
The second table (Repname) contains sales rep ID (primary key), sales rep
names ( first name and last name), ContractID (foreign key) and
VendorMgrSubGrpID (foreign key),

and the third table (VendorMgrSubGrp) contains VendorMgrSubGrpID (primary
key) and the sales rep team description (VendorMgrSubGrpCode).

All the tables are linked as follows:
Vendors:Contracts = 1:many
Contracts: Repname = many:many via a Junction table
RepName:VendorMgrSubGrp = many:1

I am experiencing the following difficulties:

1) I am unable to change (edit) values of any of the fields in the subform
2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?
3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?
4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

Unfortunately I am not a coder, so would like to avoid resorting to this if
at all possible.

Your sage advice would be greatly appreciated.

P.S. I have created a small example database containing all these elements
if you need to analyse it.

Kind Regards,

vykc00

Hi Rod,

Further to my response below, I have revisited the tables and have noticed
(to my chagrin) that in the 'Contracts' table, I have neglected to specify
'ContractID' as the primary key. This seems to be why the Query and Subform
were uneditable.

Now that Problem 1 appears to have been resolved, if you have time I wonder
if you might be able to provide any recommendations regarding the other
problems below:

2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?

3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?

4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

many Thanks.

"vykc00" wrote:

Hi Rod,

Thank you for your response.

Your suggestion was a good one, as even in my limited experience I have
found that MS-Access is prone to some quirky errors.

Over the weekend I removed and then re-instated the joins in my query.
Unfortunately this doesn't seem to have had any effect, so I hope you don't
mind if I take up your kind offer to send you a copy of my sample database
to have a look at directly?

Many Thanks.

"Rod Plastow" wrote:

Hi,

I agree with your comment about attachments - you cannot even paste a
picture in-line, or at least it won't work for me - but I do understand the
reasons for this restriction.

OK, we've taken a step forward and demonstrated that your problem has
nothing to do with subforms but is the query itself. From your description
there is nothing wrong with the way you have set up your junction table, it
is in fact the classic way of resolving a many:many relationship. Also,
reading between the lines, I suspect you have - correctly - used the
Relationships functionality to define the relationships between your tables
to the database manager. If so, one of the consequences is that Access
retrieves these relationships when you define a query - and this is not
always what you want! Sounds strange? Well it's not when you really think
about it. Very often Access will report it can't run the query because of
ambiguous joins, at other times it simply locks the fields and keeps mum.

I suggest you delete each join in your query (don't worry this does not
affect the database relationship) and redefine them. Pay particular
attention to the direction of the join.

If you can't get the query working then zip your mdb and sent it to me at



Regards,

Rod

"vykc00" wrote:

Thanks very much for your help Rod.

I have followed your advice, and it seems that the query is not editable
either.

If the table joins is the cause, then perhaps I have set up the Junction
table between the Contracts and Repname tables incorrectly?

The Contracts and Repname tables have a Many:Many relationship so I have
created a Junction Table containing each table's primary key (i.e. ContractID
and RepNameID), with each table having a 1:Many relationship with the
Junction Table (i.e. TblContract:TblJunction = 1:Many, TblRepName:TblJunction
= 1:Many).

Is this correct?

It's a pity attachments aren't permitted in this forum. Pictures (and
sample d/bases) are worth a thousand words.

Kind Regards,

vykc00

"Rod Plastow" wrote:

Provided that you have set all the sub form attributes correctly, I doubt
whether the inability to edit the records is to do with the sub form. Go
back to your query and see if you can edit the fields there. If not then you
need to redesign your SQL - probably the joins.

Regards,

Rod

PS Let's solve #2 when #1 is working.

"vykc00" wrote:

To all you MS-Access gurus out there, thank you very much in advance for your
help.

I am trying to create a Form containing a Subform. The Form is based on a
table (Vendors) with the fields VendorID (primary key) and VendorName.

The Subform I am trying to create contains the fields contained in a query
that
combines at least 3 other linked tables.

The first table (Contracts) contains a ContractID (primary key) and the
VendorID (foreign key).
The second table (Repname) contains sales rep ID (primary key), sales rep
names ( first name and last name), ContractID (foreign key) and
VendorMgrSubGrpID (foreign key),

and the third table (VendorMgrSubGrp) contains VendorMgrSubGrpID (primary
key) and the sales rep team description (VendorMgrSubGrpCode).

All the tables are linked as follows:
Vendors:Contracts = 1:many
Contracts: Repname = many:many via a Junction table
RepName:VendorMgrSubGrp = many:1

I am experiencing the following difficulties:

1) I am unable to change (edit) values of any of the fields in the subform
2) If I actually were able to amend the VendorMgrSubGrpCode displayed in the
subform, how can I present a list of the available VendorMgrSubGrpCodes (held
in VendorMgrSubGrp table) for the user to select from, and how can the
VendorMgrSubGrpID corresponding to the selected VendorMgrSubGrpCode replace
the original VendorMgrSubGrpID stored in the Subform, the query behind the
Subform, and the Repname table sitting behind the query?
3) When I wish to select another VendorID in the parent form, how can I make
the VendorName (also displayed in the parent form) change to the new
corresponding Vendor Name?
4) How can I change the format of the subform (presently datasheet) to
another format (e.g. column/tabular)?

Unfortunately I am not a coder, so would like to avoid resorting to this if
at all possible.

Your sage advice would be greatly appreciated.

P.S. I have created a small example database containing all these elements
if you need to analyse it.

Kind Regards,

vykc00