Sub Forms

[Ray C]

I Have two Tables. the first holds various information with it's own Primary
Key and also a Supplier ID "SW_Opp" that identifies a Supplier. The second
"Suppliers" Table holds the "SupplierNo" number as the primary key and lists
the "SupplierName" plus other info about the supplier. The first table
"SW_Opp" is the same as the second table "SupplierID" and they are linked in
the Relationship.
I have a Sub Form that has its record source from the First Table, so all
the Text boxes have their control Souce based on that Table. One of the Text
Boxes in that sub form is called "SW_OPp and returns the number of the
supplierlinked to that particular record in the first table. What i want to
do is to have the supplier Name (held in the Suppliers Table) appear in the
sub form depending on the Supplier number held in first table. I thought of
using an unbound List Box and have tried to set the Row Source to get the
result I want.
I have tried many combinations but they all come up with an error. The one
that I thought should work is as follows :-
SELECT tbl_Suppliers.SupplierName FROM tbl_Suppliers WHERE
(((tbl_Suppliers.SupplierNo)=[sub_frm_StoreAddressExtra].[SW_Opp]));

It still comes up with an error and I must be doing something fundamentally
wrong.

Any Help please?

RayC

 

[Ray C]

Hi Ken
Thank you so much for coming back to me. I have tried and tried to
understand and implement both your alternatives but neither to any avail.
First of all, i am an absolute beginer with Accesss and I am begining to
think that I have some fundamental misunderstandings about how Access works.
I may not have explained the way I have things set up too well to you. I
have a "frm_Main" and that form has 4 unbound "Find Combo Boxes" that are
used to search the various tables. it also has 2 "unbound" Sub Form controls
that are used to display the information that is requested by the user and
held in diffrent "Sub Forms that are based on the table in which the
information is held.
e.g. if the User wants to view Supplier names and address information that
is held in the "suppliers" table, they select Suppliers from the appropriate
combo box in The "Frm_Main" and this populates the appropriate "Find"
Combo's with options available to that particular request (supplier name,
Supplier ID, Contract No etc). The selection of the correct record from one
of these find boxes will search the Table and set the Record with a Bookmark.
The program then sets the Source Object of the Sub Form Control as the
appropriate Sub Form, make it visible and that is job done.
All that works OK other than the situation I was trying to resolve when I
originaly made the enquiry. One of the Tables (the Customers Table) has 3
different fields that hold the Supplier Primary Key number from the Suppliers
Table as an indication of the supplier that provides that particular service.
this could be any combination of one supplier that supplies all 3 services;
through to the different suppliers that supply the 3 different services. If
the Sub Form is based on the Customers table, all that will be shown is the
Supplier Number that is held in the Customer Table. I wanted to use that
number to Look up the supplier name. (e.g. I wanted Supplier number 254 held
in the appropriate Field of the Customers Table to Look up the Number
(primary key) in the "Suppliers" Table and return the name of the supplier
associated with that record. I did this with a List Box where the Row Source
was set with the statement in the message below.
That did not work as the Lisy Box always remained Blank. I changed the line
to simply reference the Form Property with Form!SW_Opp but that did not work.
I changed the Sub Form to be based on a Query as you suggest but that does
not work as there seems to be nothing to link the seloection of the Customer
that is selected with its information shown in the first Sub form Control
(based on the Customer Table) with the second Sub form that is based on the
query.
Also, by using the query I can generate the Text Box that controls the Name
of the Supplier for one of the 3 (Potentially) different suppliers held in
the Customers Table but I acn not generate a Text Box that will display the
names of the other two. Surely this information has to be displayed by a
Lookup?
Sorry this is so long. but any help will be usefull.

Thanks Ray C

Simply base the subform on a query which joins the two tables and include the
SupplierName in the query's columns. You can then include a text box control
in the subform bound to the SupplierName column. To prevent users trying to
edit the name set the control's Locked property to True (Yes) and its Enabled
property to False(No).

The reason for the error in your query BTW was that you cannot reference a
subform by the name of the underlying form object; it has to be referenced
via the Form property of the subform control in the parent form. As the
query is the RowSource of a control in the subform, however, you can simply
reference the Form property, Form!SW_Opp without the need to qualify it by
the name of the parent form's subform control.

Ken Sheridan
Stafford, England

I Have two Tables. the first holds various information with it's own Primary
Key and also a Supplier ID "SW_Opp" that identifies a Supplier. The second
"Suppliers" Table holds the "SupplierNo" number as the primary key and lists
the "SupplierName" plus other info about the supplier. The first table
"SW_Opp" is the same as the second table "SupplierID" and they are linked in
the Relationship.
I have a Sub Form that has its record source from the First Table, so all
the Text boxes have their control Souce based on that Table. One of the Text
Boxes in that sub form is called "SW_OPp and returns the number of the
supplierlinked to that particular record in the first table. What i want to
do is to have the supplier Name (held in the Suppliers Table) appear in the
sub form depending on the Supplier number held in first table. I thought of
using an unbound List Box and have tried to set the Row Source to get the
result I want.
I have tried many combinations but they all come up with an error. The one
that I thought should work is as follows :-
SELECT tbl_Suppliers.SupplierName FROM tbl_Suppliers WHERE
(((tbl_Suppliers.SupplierNo)=[sub_frm_StoreAddressExtra].[SW_Opp]));

It still comes up with an error and I must be doing something fundamentally
wrong.

Any Help please?

RayC

--
Message posted via AccessMonster.com


.

 

[Ray C]

Hi Ken
I originally used a "List Box" because I wanted the box to look like a
normal "Text Box" even though it obtained it's data from a diferent form. I
thought that List boxes and Text boxes worked nearly the same way but it
seems not.
However, I have changed the box to a Combo box and I now have the rather
ugly combo box selection arrow. It does work but is quite useless in that
the Combo is Blank amd I have to klick the Combo select arrow to get the box
to show it's drop down containing the Supplier name. I then select the
dropdown to have the supplier name showing in the Combo itself. As this is
just supposed to be a dispay of the information, that process rather defeats
the object of the exercise. Do I need to execute a requery or something?
I have tried to show a text box that is set up as you also suggest but that
displays nothing either.

Thank you Ray C

Ray:

I think the pertinent point is:

"If the Sub Form is based on the Customers table, all that will be shown is
the Supplier Number that is held in the Customer Table"

By making the control in the subform bound to the field containing the
Supplier Number a combo box, and hiding the bound column, you can show the
supplier name from another column. To do this set up the combo box as
follows:

RowSource: SELECT [Supplier Number], [Supplier Name] FROM [Suppliers]
ORDER BY [Supplier Name];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

This is the standard way of showing meaningful data form a column of a
referenced table where the keys are arbitrary values, usually numeric.
Although the value of the control is the number, what you see is the
corresponding text value from the Supplier Name column in the Suppliers table.
As it's a combo box, selecting another supplier from the drop down list would
change the value of the bound Supplier Number column in the Customers table
of course if the subform is updatable. This can be prevented by setting the
combo box's Locked property to True (Yes) and its Enabled property to False
(No) as I described earlier.

You can extend this if necessary to show other data from the Suppliers table
in the subform by including the relevant columns in the combo box's RowSource
and referencing the columns in an unbound text box in the subform; say for
instance you also wanted to show data from a column Supplier Address you'd
set up the combo box like this:

RowSource: SELECT [Supplier Number], [Supplier Address], [Supplier Name]
FROM [Suppliers] ORDER BY [Supplier Name];

BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;0cm;8cm

Then add an unbound text box to the subform, with a ControlSource property of:


=cboSupplier.Column(1)

where cboSupplier is the name of the combo box. The column property is zero-
based, so Column(1) is the second column, Supplier Address.

Ken Sheridan
Stafford, England

Hi Ken
Thank you so much for coming back to me. I have tried and tried to
understand and implement both your alternatives but neither to any avail.
First of all, i am an absolute beginer with Accesss and I am begining to
think that I have some fundamental misunderstandings about how Access works.
I may not have explained the way I have things set up too well to you. I
have a "frm_Main" and that form has 4 unbound "Find Combo Boxes" that are
used to search the various tables. it also has 2 "unbound" Sub Form controls
that are used to display the information that is requested by the user and
held in diffrent "Sub Forms that are based on the table in which the
information is held.
e.g. if the User wants to view Supplier names and address information that
is held in the "suppliers" table, they select Suppliers from the appropriate
combo box in The "Frm_Main" and this populates the appropriate "Find"
Combo's with options available to that particular request (supplier name,
Supplier ID, Contract No etc). The selection of the correct record from one
of these find boxes will search the Table and set the Record with a Bookmark.
The program then sets the Source Object of the Sub Form Control as the
appropriate Sub Form, make it visible and that is job done.
All that works OK other than the situation I was trying to resolve when I
originaly made the enquiry. One of the Tables (the Customers Table) has 3
different fields that hold the Supplier Primary Key number from the Suppliers
Table as an indication of the supplier that provides that particular service.
this could be any combination of one supplier that supplies all 3 services;
through to the different suppliers that supply the 3 different services. If
the Sub Form is based on the Customers table, all that will be shown is the
Supplier Number that is held in the Customer Table. I wanted to use that
number to Look up the supplier name. (e.g. I wanted Supplier number 254 held
in the appropriate Field of the Customers Table to Look up the Number
(primary key) in the "Suppliers" Table and return the name of the supplier
associated with that record. I did this with a List Box where the Row Source
was set with the statement in the message below.
That did not work as the Lisy Box always remained Blank. I changed the line
to simply reference the Form Property with Form!SW_Opp but that did not work.
I changed the Sub Form to be based on a Query as you suggest but that does
not work as there seems to be nothing to link the seloection of the Customer
that is selected with its information shown in the first Sub form Control
(based on the Customer Table) with the second Sub form that is based on the
query.
Also, by using the query I can generate the Text Box that controls the Name
of the Supplier for one of the 3 (Potentially) different suppliers held in
the Customers Table but I acn not generate a Text Box that will display the
names of the other two. Surely this information has to be displayed by a
Lookup?
Sorry this is so long. but any help will be usefull.

Thanks Ray C

Simply base the subform on a query which joins the two tables and include the
SupplierName in the query's columns. You can then include a text box control

[quoted text clipped - 37 lines]

RayC

--
Message posted via AccessMonster.com


.

 

[Ray C]

Yipppppeeee !!! Great. Smashin. Fabulous. Magic our Mourice. Tha Reet Up our
Street kid.

Seriously Ken, Thank you so much, I would never have got there without your
invaluable help. The Text Box with the DLookUp has done the trick and I will
spent the next few days reading the whole content of your post and trying to
work out why they did not work for me.

I don't know what we are going to do now that Microsoft look as though they
are closing this forum.

Many thanks anyway.

Regards Ray C

The combo box needs to be bound to the Supplier Number column in the
subform's underlying table or query. But if you want a text box then you can
either do as I originally advised and as the subform's RecordSource property
use a query which joins the Suppliers table to the Customers table and bind
the text box to the Supplier Name column, or use an unbound text box whose
ControlSource is an expression which calls the DLookup function:

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = " & [Supplier
Number 1])

or if Supplier nuber is a text data type rather than a number;

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = """ &
[Supplier Number 1] & """")

I've called the column in the subform's underlying table Supplier Number 1 in
this instance as you said you have three columns each referencing the
Suppliers table, so you'd do the same with each. This also means that if you
do use a query to pull in the supplier names the joins would probably have to
be LEFT OUTER JOINS to allow for a Null in any of the three columns.

The fact that you have three columns referencing Suppliers does suggest
however that the logical model is flawed. What you seem to be doing here is
representing different attributes of the relationship type (the type of
service provided in your case) between Customers and Suppliers as column
headings in Customers. Attributes are data, and data should only be stored
as explicit values at column positions in rows in tables. This was Codd's
Rule 1 (the Information Rule) when he first proposed the database relational
model back in 1970. The way to model this relationship would be as a table
with foreign key columns referencing the primary keys of Customers and
Suppliers, and a third column representing the nature of the realtionship.
This third column would also be a foreign key, in this case referencing a
table of the different types of relationship (i.e. the services) between
customers suppliers. The relationship type is therefore a ternary one
between the three entity types. So a customer with all three types of
relationship to suppliers would be represented as three rows in this table,
each with the same customer number, different values in the service type
column and either the same or different values in the supplier depending on
whether the services to that customer are provided by the same or different
suppliers. If a customer cannot receive the same service from more than one
supplier at any one point in time this should be enforced by means of a
unique index on the Supplier number and service type columns, in combination,
not individually.

Ken Sheridan
Stafford, England

 

[Larry Linson]

I don't know what we are going to do now
that Microsoft look as though they
are closing this forum.

Actually, Microsoft has officially announced that this newsgroup will close
as of 6/1/2010, so it's more than "look as though". If you have a news
server, or are willing to sign up for one (there are many, both fee and
free), the USENET newsgroup comp.databases.ms-access still exists and many
MVPs and others who provide excellent answers have indicated they will be
returning there.

Microsoft is providing some online forums that include some "answerers" who
are paid and also accept answers from others (including MVPs). So far, there
has been no groundswell of acceptance for those, though some find them
usable. Access is lumped in with some other software in one of the sections
of social.answers.microsoft.com -- nothing like the number of newsgroups has
been discussed, AFAIK.

 

[Ray C]

Hi Ken
Access bing Access, I was probably a little premature in my excitement at
getting the thing to work.
I now have a dialogue box that pops up as Access is first opening up and
prior to the first screen being displayed saying
"The exprexssion is typed incorrectly or is too complicated, For example, a
numeric expression may containtoo many complicated elemements. Try
simplifying the expression by assigning parts of the expression to variables."
If I OK that, the first forms opens up and the database seems to work OK
from there on in.
Incidentally, if I completely remove the "Text Boxes" and the Dlookup
formula that you suggest, I still get the error message on opening.
Could you throw any light on the reason for this?

Yipppppeeee !!! Great. Smashin. Fabulous. Magic our Mourice. Tha Reet Up our
Street kid.

Seriously Ken, Thank you so much, I would never have got there without your
invaluable help. The Text Box with the DLookUp has done the trick and I will
spent the next few days reading the whole content of your post and trying to
work out why they did not work for me.

I don't know what we are going to do now that Microsoft look as though they
are closing this forum.

Many thanks anyway.

Regards Ray C

The combo box needs to be bound to the Supplier Number column in the
subform's underlying table or query. But if you want a text box then you can
either do as I originally advised and as the subform's RecordSource property
use a query which joins the Suppliers table to the Customers table and bind
the text box to the Supplier Name column, or use an unbound text box whose
ControlSource is an expression which calls the DLookup function:

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = " & [Supplier
Number 1])

or if Supplier nuber is a text data type rather than a number;

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = """ &
[Supplier Number 1] & """")

I've called the column in the subform's underlying table Supplier Number 1 in
this instance as you said you have three columns each referencing the
Suppliers table, so you'd do the same with each. This also means that if you
do use a query to pull in the supplier names the joins would probably have to
be LEFT OUTER JOINS to allow for a Null in any of the three columns.

The fact that you have three columns referencing Suppliers does suggest
however that the logical model is flawed. What you seem to be doing here is
representing different attributes of the relationship type (the type of
service provided in your case) between Customers and Suppliers as column
headings in Customers. Attributes are data, and data should only be stored
as explicit values at column positions in rows in tables. This was Codd's
Rule 1 (the Information Rule) when he first proposed the database relational
model back in 1970. The way to model this relationship would be as a table
with foreign key columns referencing the primary keys of Customers and
Suppliers, and a third column representing the nature of the realtionship.
This third column would also be a foreign key, in this case referencing a
table of the different types of relationship (i.e. the services) between
customers suppliers. The relationship type is therefore a ternary one
between the three entity types. So a customer with all three types of
relationship to suppliers would be represented as three rows in this table,
each with the same customer number, different values in the service type
column and either the same or different values in the supplier depending on
whether the services to that customer are provided by the same or different
suppliers. If a customer cannot receive the same service from more than one
supplier at any one point in time this should be enforced by means of a
unique index on the Supplier number and service type columns, in combination,
not individually.

Ken Sheridan
Stafford, England

 

[Larry Linson]

Microsoft cannot "end these newsgroups"; Microsoft can remove their servers
from the Internet, so you will have to find a new server. Microsoft can also
send an "authenticated something-or-other" to other servers requesting they
no longer carry these groups, but few news servers follow those directives,
I hear, because they have wreaked havoc in the past when forged.

I'll be moving the microsoft.public.access groups that I follow to my new
news server and hope they will not remove the groups.

So, except for the minimal trouble of changing news servers if you read
these groups at "news.microsoft.com" and the fact that the microsoft website
will no longer direct you to the groups or to their UI for the groups, you
may not see all that much drop-off of activity in the microsoft.public
newsgroups. There are some USENET newsgroups that were "officially replaced
by others broken down by subject" around 15 years ago that are still hanging
on in some servers -- comp.lang.basic.visual, for example.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

Ken,
<smile> You're always helpful and provide useful information. I read a
post
that said the termination of the Microsoft newsgroups would affect 3rd
party
sites. I thought this would include Access Monster. I hope it doesn't.
Thank
you so much for the information and the links. While I'm not particularly
enamored with the MS forum to date, there's always the hope that it will
turn
out better than expected.

Aria:

You don't need to use a newsreader if you prefer a web-based interface as
there are a number of the latter which will enable you to access the comp.
databases.ms-access newsgroup to which Larry referred. I've only just
realised that this is what the 'General 1' section does in AccessMonster,
which I'm using at the moment to access this group. Its other sections
access this newsgroup. I'm assuming that it will continue to operate
after
this newsgroup is consigned to the dustbin of history. You can find it
at:

http://www.accessmonster.com

Its free to use and setting up an account is only a few minutes work.

Google Groups is another web-based option which seems to be quite popular,
though I'm not too enamoured of it myself. You'll find the
comp.databases.ms-
access group at:

http://groups.google.com/group/comp.databases.ms-access/topics

I'm sure you'll find most of the names you've become familiar with here at
comp.databases.ms-access in the near future if not already, and there will
be
no diminution in the level of help you'll receive. From the evidence to
date
the new MS forums don't look promising.

Ken Sheridan
Stafford, England

Well, maybe I shouldn't jump in here but I've been concerned ever since I
read the posts regarding the termination of these newsgroups. I'm really
sad

[quoted text clipped - 14 lines]

of social.answers.microsoft.com -- nothing like the number of newsgroups
has
been discussed, AFAIK.

 

[Ray C]

Hi Ken
Thanks for your advice.and help anyway.

Regards Ray C

It sounds like it's not the text box's expression that's the problem, but
something else. Without being able to do any hands-on debugging, however, I
can't hazard a guess as to what it might be.

Ken Sheridan
Stafford, England

Hi Ken
Access bing Access, I was probably a little premature in my excitement at
getting the thing to work.
I now have a dialogue box that pops up as Access is first opening up and
prior to the first screen being displayed saying
"The exprexssion is typed incorrectly or is too complicated, For example, a
numeric expression may containtoo many complicated elemements. Try
simplifying the expression by assigning parts of the expression to variables."
If I OK that, the first forms opens up and the database seems to work OK
from there on in.
Incidentally, if I completely remove the "Text Boxes" and the Dlookup
formula that you suggest, I still get the error message on opening.
Could you throw any light on the reason for this?

Yipppppeeee !!! Great. Smashin. Fabulous. Magic our Mourice. Tha Reet Up our
Street kid.

[quoted text clipped - 57 lines]

Ken Sheridan
Stafford, England