Combo Boxes - linking of

[Nikkinovice]

I know there is probably a simple answer to this, but am self taught on
access and am stuck :-(

Am using access 2003

Have 3 tables as follows:
Supplier Goods (id=primary key, supplier name, product desc) (- linked to
supplier info)
Supplier Info (Supplier Name=primary key), contact number etc (linked to
goods ordered)
Goods ordered(id=primary key, supplier, product desc etc (linked to an order
table)

on a form i have created 2 combo boxes, one to look up the supplier name,
then one to look up their products. the first combo works spot on, but the
second doesn't. i want the second combo box to only show the products for
the supplier selected in the first combo box. however at the mo it is
showing all products for all suppliers. i have tried changing the control
source of the second combo to 'supplier' but still not working.

Please treat me like an absolute beginner in any reponses and please help!

Thanks
Nikki

 

[Douglas J. Steele]

Fortunately, we don't have to treat you like an absolute beginner, because
Sal Ricciardi walks you through this at
http://office.microsoft.com/en-ca/access/HA011730581033.aspx?pid=CH063650361033
<g>

 

[Nikkinovice]

Hi Douglas

Just managed to re-find my post! Thanks for trying but I'm still
struggling, spend 12 hours yesteray and now 5 today trying to sort. Do you
know of somewhere where I can email my dbase to for checking. There probably
isn't anywhere but worth a shot!

I can get my second combo either to show no records or records from each
supplier - feeling a homer simpson moment coming on - doh!

 

[Douglas J. Steele]

Sorry, I'm not aware of any service that will let you email a database for
review (other than ones that charge)

 

[KenSheridan via AccessMonster.com]

Nikki:

For the first combo box, which I'll call cboSupplier for this example, use a
RowSource property of:

SELECT [supplier name] FROM [Supplier Info] ORDER BY [supplier name];

Its ControlSource property will be [supplier].

For the second combo box, which I'll call cboProduct for this example, use a
RowSource property of:

SELECT [product desc] FROM [Supplier Goods] WHERE [supplier name] = Form!
[cboSupplier] ORDER BY [product desc];

Its ControlSource property will be [product desc].

In the AfterUpdate event procedure of cboSupplier requery cboProduct with:

Me.[cboProduct].Requery

If you are unfamiliar with entering code in event procedures you do this by
selecting the control in form design view and opening its properties sheet if
its not already open. Then select the relevant event property in the
properties sheet. Click on the 'build' button; that's the one on the right
with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines already
in place. Enter the line of code between these two existing lines.

Ken Sheridan
Stafford, England

 

[Nigel]

Hi Nikki,

You should find this useful:

http://msdn.microsoft.com/en-us/library/bb404901.aspx

Cheers,

 

[AccessVandal via AccessMonster.com]

The Supplier Info table - The primary should be the SupplierID, you should
avoid using Supplier Name as a primary key it will be more difficult to
search one with more than ten letters Like "MicroSoft Corporation......" or
"MicroSoft blah blah....." you get the idea. The Supplier Goods table should
include the SupplierID instead of Supplier Name. You than can link these two
tables (SupplierIDs) in your query for your Report or even your combobox.

 

[Ray C]

Hi Nikki
I feel very shy in coming back to you on this because I am very much a
novice myself and I will probably get this quite wrong. I had this issue some
short while ago and turned myself inside out in trying to find a working
solution. I don't know if you are past the problem now but you should have
something like a Suppliers table and a Products table. Suppliers should have
something like "SupplierID" (auto number Primary Key), "SupplierName" (text),
"Addr1" etc,etc. The Products table should have a "ProductID" (auto number
Primary Key), "SupplierID" (number). "ProductCode", "Product description" etc
etc
Your first combo box you seem to have working but your second combo box
should have something like the following in the program (using cmb_ComboBox2
as the name of the combo) :-
Me.ComboBox2.RowSource = "SELECT tbl_Products.ProductCode FROM
tbl_Products WHERE ((tbl_Suppliers.SupplierID) = (tbl_Products.ProductID)))
ORDER BY tbl_Store.Name"

I am not sure if I need that many sets of brackets but thgis should give you
an idea. I appologise if it does not work but that will probably be due to me
changing stuf arround to get the concept over to you.

If it helps, I have a whole block od code that deals with just exactly what
you are trying to do. If it helps at all I would be happy to e-mail it to you
if you drop me a line on rclethro at Bowshaw dot co dot uk. I have not
written this out in the normal format as I dont want various machines picking
up my e-mail and sending me any more junk than I already get.

Hope that this helps

Regards Ray C

 

[KenSheridan via AccessMonster.com]

While the use of a surrogate key does have advantages, in the context of this
thread the use of a natural key does have a significant advantage in that it
enables correlated combo does to be used in continuous form view. Where a
surrogate key is used in this context, in those rows in the form where the
value of the hidden bound column in the second combo box is not included in
the set of values returned by the control's RowSource query by virtue of the
restriction imposed by the referenced value of the first combo box, then the
row will not show the value of the visible column. Where a natural key is
used, however, the value will be visible regardless of the restriction. This
is the very reason I didn't raise this point in my reply to the OP.

A natural key is not always possible (or strictly speaking a single-column
natural key isn't) where the relevant column is not a candidate key of course,
e.g. a city name, in which case surrogate key is the only realistic solution.
It is still possible to use the same approach of top-down selection via
correlated controls by means of a 'hybrid' control superimposing a text box
on a combo box to give the appearance and apparent functionality of a single
combo box control. You'll find my demo of this at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Another point to be considered is that with the model as posted, having both
supplier name and product desc columns in the Goods Ordered table as the OP
is doing might mean the table is not normalized to 3NF if product desc
determines supplier name, i.e. if product desc is a candidate key of Supplier
Goods. I do wonder, however, whether we don't have the full picture and
Supplier Goods is in fact modelling a many-to-many relationship between Goods
and Suppliers tables, in which case Goods Ordered is normalized to 3NF.

I'm afraid that I don't follow your point about searching on the string
expressions. In a developed application searching by either supplier or
product would generally be via combo boxes with the AutoExpand property True,
so the user can either scroll and click, or be progressively taken to the
first matches as they type. Whether a surrogate or natural key is used would
make no difference from the user's point of view. Even with a crude search
facility in which the text value has to be entered in full a user would have
to type in the string expression as the values of the surrogate key would not
even be known to the user, the only exception being if the key is a
structured value such as a code number where it can be easier sometimes for
users to enter the code rather than the description, but I see no indication
of that in this case.

Ken Sheridan
Stafford, England

The Supplier Info table - The primary should be the SupplierID, you should
avoid using Supplier Name as a primary key it will be more difficult to
search one with more than ten letters Like "MicroSoft Corporation......" or
"MicroSoft blah blah....." you get the idea. The Supplier Goods table should
include the SupplierID instead of Supplier Name. You than can link these two
tables (SupplierIDs) in your query for your Report or even your combobox.

I know there is probably a simple answer to this, but am self taught on
access and am stuck :-(

[quoted text clipped - 20 lines]

Thanks
Nikki

 

[AccessVandal via AccessMonster.com]

Hi Ken,

Thanks for your information.

I do use search string expression as User don't understand the purpose if
having IDs. It possible to pick a same name supplier without knowing that it
has a different address, region, country, etc. One same name supplier may
come from Canada and the same name supplier may also come from US. How is the
typical User knows which one to pick? Because I do encounter this scenario
unless of course the supplier company has a naming policy.

As you have already point out the table normalization. I did not want to go
through with the OP about this and the surrogate and natural keys and as a
beginner, I doubt the OP would pick up the skills quickly.

You should post to the OP if need, I wouldn't oppose even if you disagree
with mine. You're always welcome to post, it would be meaningless if no one
would give an alternative suggestion in a forum.

 

[KenSheridan via AccessMonster.com]

Clearly if there can be multiple suppliers with the same name the supplier
name is not a candidate key, so there are two options; either use a surrogate
key such as an autonumber, or a multi-column key. The latter can be
cumbersome, however. If we take the Customers table in the sample Northwind
database as an example, if this table as it stands were normalized it would
have to reference a Regions table with a multi-column key of City, Region and
Country. The Cities table would reference a Regions table with a key of
Region and Country and the Regions table would reference a Countries table
with a key of Country. This is cumbersome, so I would normally use a
surrogate key, in this case a CityID, dispensing with the redundant Region
and Country columns in Customers.

When it comes to both data entry and searching whether a single column
surrogate key or a multi-column 'natural' key is used is immaterial. The
user would have to be presented with all of the columns which make up the
candidate 'natural' key. For entering data in the Customers table the user
would be presented with a combo box with three columns like this:

Aachen NRW Germany
Albuquerque NM USA
Anchorage AK USA
Ã…rhus East Jutland Denmark

and so on. My demo file does include such a multi-column combo box, but it
is sorted by (the equivalent of) Country then Region, then City to provide a
more logical ordering of the list.

Or by correlated combo boxes from which the user can select the country, then
the region, then the city. My demo also includes this. Either way the user
can distinguish between cities and/or regions of the same name.

There is no fundamental disagreement between us here. All I am pointing out
is that, in the context of this thread, if a surrogate key is used it makes
the use of correlated combo boxes significantly more difficult, particularly
in a continuous form. But it can be done, as my demo file shows. In the
OP's case, however, the model seems to preclude the possibility of two
suppliers of the same name, so the use of a single-column natural key would
make it far easier to set up the correlated combo boxes in the way I
described in my original response. But there is no reason why they can't use
a surrogate key as you suggest if they are prepared to go to the trouble of
setting up the controls and the code behind them accordingly. I was
conscious of their novice status in not referring them to my demo, however.

Ken Sheridan
Stafford, England

Hi Ken,

Thanks for your information.

I do use search string expression as User don't understand the purpose if
having IDs. It possible to pick a same name supplier without knowing that it
has a different address, region, country, etc. One same name supplier may
come from Canada and the same name supplier may also come from US. How is the
typical User knows which one to pick? Because I do encounter this scenario
unless of course the supplier company has a naming policy.

As you have already point out the table normalization. I did not want to go
through with the OP about this and the surrogate and natural keys and as a
beginner, I doubt the OP would pick up the skills quickly.

You should post to the OP if need, I wouldn't oppose even if you disagree
with mine. You're always welcome to post, it would be meaningless if no one
would give an alternative suggestion in a forum.

While the use of a surrogate key does have advantages, in the context of this
thread the use of a natural key does have a significant advantage in that it

[quoted text clipped - 40 lines]

Ken Sheridan
Stafford, England

 

[AccessVandal via AccessMonster.com]

Hi Ken,

In my case, there many same name companies each with different addresses. I
have comboboxes that will filter base on the user location. The combobox will
never pick more than one Company name and will have a unique ID which also
print a report base on it so that it will print out the correct address.

I have yet to look into your demo, but I believe the demo would be a good
sample for the OP to try out and from what you have discripe in your post, I
guess the demo will work for the OP.

 

[KenSheridan via AccessMonster.com]

When you say 'user location' are you saying that it’s the location of the
customer which determines which supplier companies are returned by the combo
box, not directly the location of the companies? Though telatter would
indirectly determine it of course. For instance if I as a user select a
company I'd get a list of the suppliers in the Stafford area? If so, that's
an interesting variation on the usual correlated combo box approach; it’s a
bit like the 'store finder' facility one often sees on company web sites
where by entering your post code you get a list of local branches.

You are quite right, the two correlated combo box solutions in my demo (one
for single forms, the other for continuous forms) would work for the OP, but
it does require a certain amount of expertise to implement them, which was
why I didn't recommend it to the OP, who posts as a 'novice'. The additional
complexity arises from the fact that its not only the correlation of the
combo boxes as such which the file demonstrates, but also, and this is really
the raison d'être of the demo, it does so in the context of a set of
normalized tables. I've found that mostly people don't normalize to 3NF in
such cases, e.g. having a CityID and StateID in a table of locations. CityID
determines StateID of course, so the latter is transitively dependent on the
key. Its hard to know if the OP's Supplier Goods table is normalized to 3NF
or not as its unclear whether it models a Goods entity type or a many-to-many
relationship type between a Goods table and the Supplier Info table. If the
latter then it is normalized to 3NF, as is the Goods Ordered with its
composite foreign key.

Ken Sheridan
Stafford, England

Hi Ken,

In my case, there many same name companies each with different addresses. I
have comboboxes that will filter base on the user location. The combobox will
never pick more than one Company name and will have a unique ID which also
print a report base on it so that it will print out the correct address.

I have yet to look into your demo, but I believe the demo would be a good
sample for the OP to try out and from what you have discripe in your post, I
guess the demo will work for the OP.

Clearly if there can be multiple suppliers with the same name the supplier
name is not a candidate key, so there are two options; either use a surrogate

[quoted text clipped - 41 lines]

Ken Sheridan
Stafford, England

 

[AccessVandal via AccessMonster.com]

Hi Ken,

When you say 'user location' are you saying that it’s the location of the
customer which determines which supplier companies are returned by the combo
box, not directly the location of the companies? Though telatter would
indirectly determine it of course. For instance if I as a user select a
company I'd get a list of the suppliers in the Stafford area? If so, that's
an interesting variation on the usual correlated combo box approach; it’s a
bit like the 'store finder' facility one often sees on company web sites
where by entering your post code you get a list of local branches.

Yes, that's right. That was an easy fix as it was determined by the Users
location. That's one problem fix on one form............

You are quite right, the two correlated combo box solutions in my demo (one
for single forms, the other for continuous forms) would work for the OP, but
it does require a certain amount of expertise to implement them, which was
why I didn't recommend it to the OP, who posts as a 'novice'. The additional
complexity arises from the fact that its not only the correlation of the
combo boxes as such which the file demonstrates, but also, and this is really
the raison d'être of the demo, it does so in the context of a set of
normalized tables. I've found that mostly people don't normalize to 3NF in
such cases, e.g. having a CityID and StateID in a table of locations. CityID
determines StateID of course, so the latter is transitively dependent on the
key. Its hard to know if the OP's Supplier Goods table is normalized to 3NF
or not as its unclear whether it models a Goods entity type or a many-to-many
relationship type between a Goods table and the Supplier Info table. If the
latter then it is normalized to 3NF, as is the Goods Ordered with its
composite foreign key.
Ken Sheridan
Stafford, England

but a problem in another form I had was that the Form has only one
field/control. Having more fields was not what they wanted (ugly). In this
case, you can't use Users location where companies does not fall into this
category. The Company had two addresses in one Country in two City. The
combobox was not good enough as Users might still pick the wrong one.

I had to create a Popup Filter Form with 4 auto cascade unbound comboboxes,
CompanyName bound to ID, City, Country, Region and one ListBox. When the
Users pick the combos, the Listbox will show the Companies. All the Users had
to do was to narrow down the list to one company from the combos and the
Popup will return the value to the field/control. At least it was acceptable
the Users.

 

[KenSheridan via AccessMonster.com]

That's a very interesting technique. Thanks for sharing it with us.

You might be interested in a routine I developed many years ago (I think it
was in dBASE originally) for finding locations in a particular area. It
won't be of practical use in many commercial situations as it was done in the
context of my own work in environmental planning and does require that each
location be identified by map references (an easting and northing value in
the UK national grid) and a site radius value indicating the nominal extent
of the site. The way it worked was that the user entered a grid reference
and search radius, and the routine then located any sites which fell within
the intersections of the two circles defined by the site's grid
reference/radius and the user entered grid reference/radius. This is the
snippet of the relevant code from the Click event procedure of a button on
the dialogue form:

Dim strEast As String, strNorth As String

If RadialValue = "" Or IsNull(RadialValue) Then
'Display message if no radius entered
MsgBox "No radius entered", vbExclamation, "Invalid
Operation"
Me!RadialValue.SetFocus
Else
'Calculate filter string

' Call Easting and Northing functions and convert results to
strings
strEast = Str(Easting(RadialNgr))
strNorth = Str(Northing(RadialNgr))

strFilter = strFilter & "(Sqr((Easting - " & strEast & _
")^2 + (Northing - " & strNorth & ")^2) < " & _
RadialValue & " + Radius) "

strFilterText = "Radial search of " & RadialValue & _
"m on " & RadialNgr & " "
End If

As you can see its really just a simple application of Pythagoras's theorem.
The strFilter variable is then used to filter a form or report. The code
calls these functions:

Public Function Easting(strNgr As String) As Double

Dim lngEastSquare As Long, lngEastDetail As Long

' Get numeric value of easting 100km square
Select Case Left(strNgr, 2)

Case "SJ"
lngEastSquare = 300000
Case "SK"
lngEastSquare = 400000
Case "SO"
lngEastSquare = 300000
Case "SP"
lngEastSquare = 400000
Case Else
lngEastSquare = 0

End Select

lngEastDetail = Val(Mid(strNgr, 3, 5))
Easting = Val(lngEastSquare + lngEastDetail)

End Function


Public Function Northing(strNgr As String) As Double

Dim lngNorthSquare As Long, lngNorthDetail As Long

' Get numeric value of northing 100km square
Select Case Left(strNgr, 2)

Case "SJ"
lngNorthSquare = 300000
Case "SK"
lngNorthSquare = 300000
Case "SO"
lngNorthSquare = 200000
Case "SP"
lngNorthSquare = 200000
Case Else
lngNorthSquare = 0

End Select

lngNorthDetail = Val(Mid(strNgr, 8, 5))
Northing = Val(lngNorthSquare + lngNorthDetail)

End Function

In each case the grid square letters are those for our particular area, but
if it were adapted to cover the whole of the UK these could be in a separate
table and looked up rather than hard-coded, which is of course bad technique
as data should only be held in tables, but I was young and green then! These
functions are used to enable the user to enter a grid reference in the
conventional format with which people here are familiar, of two letters
followed by a string of digits, whereas the grid references are stored in the
table as two completely numeric values, which are much better for doing
calculations.

Using geometry like this only works with relatively small areas of course.
In our case we were only interested in distances of 200km or less, but it
would work over the whole UK satisfactorily within an acceptable margin of
error. For global use using latitude and longitude values it would be
necessary to use spherical trigonometry rather than geometry. I've seen code
to do this, but never had occasion to use it.

The above and other associated routines became largely redundant when we
started using a GIS, where we simply clicked on a map on the screen, but they
served us well for a long time.

Ken Sheridan
Stafford, England

Hi Ken,

When you say 'user location' are you saying that it’s the location of the
customer which determines which supplier companies are returned by the combo

[quoted text clipped - 4 lines]

bit like the 'store finder' facility one often sees on company web sites
where by entering your post code you get a list of local branches.

Yes, that's right. That was an easy fix as it was determined by the Users
location. That's one problem fix on one form............

You are quite right, the two correlated combo box solutions in my demo (one
for single forms, the other for continuous forms) would work for the OP, but

[quoted text clipped - 13 lines]

Ken Sheridan
Stafford, England

but a problem in another form I had was that the Form has only one
field/control. Having more fields was not what they wanted (ugly). In this
case, you can't use Users location where companies does not fall into this
category. The Company had two addresses in one Country in two City. The
combobox was not good enough as Users might still pick the wrong one.

I had to create a Popup Filter Form with 4 auto cascade unbound comboboxes,
CompanyName bound to ID, City, Country, Region and one ListBox. When the
Users pick the combos, the Listbox will show the Companies. All the Users had
to do was to narrow down the list to one company from the combos and the
Popup will return the value to the field/control. At least it was acceptable
the Users.

 

[AccessVandal via AccessMonster.com]

Hi Ken,

That's a very interesting design. Sounds like a web page where you search a
road, building, etc. Just like a street directory page portal.

Thank you for sharing your experience with us.

That's a very interesting technique. Thanks for sharing it with us.

You might be interested in a routine I developed many years ago (I think it

snip.........