Combo Box Design and/or Coding

[bw]

I don't know if this is a design problem, or coding. In any case, I don't
know how to do what I want.

I want to have a table of Hotels.
I want to have a table of Locations.

Hotels may have many Locations.
Locations may have many Hotels.
A Hotel/Location combination must be unique.

So I would like to know how to set up the Table(s) to accomplish this, and
maybe an explanation as to what the relationships are here.

From a practical stand point, I want to be able to allow for selection of a
Hotel/Location combination, on a Form, AND to be able to update these tables
from the Form, if the Hotel/Location combination does not currently exist.

I'm appreciative of any help received.

Thanks,
Bernie

 

[tina]

you're describing a many-to-many relationship between tblHotels and
tblLocations. that is resolved with a "linking" table, as

tblHotelLocations
HotelID (foreign key from tblHotels)
LocationID (foreign key from tblLocations)
you may use the two fields as a combination primary key, which will ensure
that each combination is unique. if you prefer to work with single-field
primary keys, you can add another field to serve as a surrogate primary key,
such as HLID (data type Autonumber). in that case, you can still create a
unique (though not primary) index of the two foreign key fields, to ensure
that each combination is unique.
note: strongly recommend that you do NOT use Lookup fields in
tblHotelLocations (or in any other table in your database, for that matter).

the relationships are
tblHotels (parent) 1:n tblHotelLocations (child)
tblLocations (parent) 1:n tblHotelLocations (child)

in a form, you can use combo boxes to allow selections of specific hotels
and locations from those parent tables. (suggest you look up the following
combo box control Properties in Access Help, to learn how they work:
RowSourceType, RowSource, ColumnCount, ColumnWidths, BoundColumn, ListWidth,
LimitToList, and NotInList.) there are several ways to add records to the
parent tables while in the form, utilizing the NotInList event. see
http://www.mvps.org/access/forms/frm0015.htm for one solution; you can find
others by doing a search on these newsgroups (this is a common question).

hth

 

[bw]

HTH??
You're kidding of course.
Tina, this is one of the most comprehensive answers I have ever received on
any news group. I really do appreciate the time you took composing your
answer. I have not tried any of this, but I'll spend the weekend checking
it out to see what problems I may run into. Maybe you can check back on
Monday (or so) to see if I was successful.

Again, thank you so much for a great answer.
Bernie

 

[tina]

you're very welcome
(once i respond to a post, i usually "watch" it for at least a week)

 

[bw]

Tina,
I setup a test database, made the three tables, then created a Main form
with a datasheet subform. After some problems understanding and getting the
linkage set, it is now working. I can add new hotels and new locations from
the form.
Two problems though...
1. I can add the same Hotel with the same Location, and I don't know why.
2. I tried to add a combo box on the main form instead of the txtcontrol,
but I have no idea how to get this thing linked properly.

Can you explain?

Thanks,
Bernie

 

[tina]

comments inline.

Tina,
I setup a test database, made the three tables, then created a Main form
with a datasheet subform. After some problems understanding and getting the
linkage set, it is now working. I can add new hotels and new locations from
the form.
Two problems though...
1. I can add the same Hotel with the same Location, and I don't know why.

i'm assuming you used a surrogate Autonumber primary key field in
tblHotelLocations. you need to go back to the table, open it in Design view,
and create a index of the two foreign key fields, setting the Index to
unique (though NOT primary). on the menu bar, click View | Indexes to open
the Indexes box. go to the first *completely blank row* below the existing
indexes. enter a number for the new index in the first column. in the second
column, select the HotelID field from the droplist. stay in the second
column and move to the row directly below; select the LocationID from the
droplist. move back up to the HotelID row, then look at the Index Properties
in the bottom half of the box. change the Unique property from No to Yes.

2. I tried to add a combo box on the main form instead of the txtcontrol,
but I have no idea how to get this thing linked properly.

i can probably help you sort this out, but i need to know exactly how the
mainform/subform is set up. what table is bound to the main form? what table
is bound to the subform? what are the names of the fields in the subform
control's LinkChildFields and LinkMasterFields properties? in the main form,
what is the ControlSource of the combo box? what is the RowSource?

 

[tina]

oops! in the instructions for creating a unique table index, the phrase

enter a number for the new index in the first column.

should read

"enter a *name* for the new index in the first column."

hth

 

[bw]

Before getting into the Combo boxes, here are the details for my tables.

tblHotels
Primary Key: HotelID, Indexed, Yes (No Duplicates), Data Type: AutoNumber
Field Name: txtHotel, Indexed, Yes (Duplicates Ok), Data Type: Text

tblLocations
Primary Key: LocationID, Indexed, Yes (No Duplicates), Data Type: AutoNumber
Field Name: txtLocation, Indexed, Yes (Duplicates Ok), Data Type: Text

tblHotelLocations
Primary Key: HLID, Indexed, Yes (No Duplicates), Data Type: AutoNumber
Field Name: HotelID, Indexed, Yes (No Duplicates), Data Type: Number
Field Name: LocationID, Indexed, Yes (No Duplicates), Data Type: Number
In the indexes box,
Index Name1 is Primary Key, Field Name is HLID , and Index Properties:
Primary=Yes, Unique = Yes
Index Name2 is Tina, Field Name is HotelID, and Index Properties: Primary
=No, Unique = Yes
Index Name 3 is empty, Field Name is LocationID, and Index Properties are
not available

Relationships
tblHotels>HotelID is one-to-many related to tblHotelsLocations>HotelID
tblHotels>LocationID is one-to-many related to tblHotelsLocations>LocationID

MainForm=frmHotels
SubForm=frmLocationsSubForm1
Link Master Fields=HotelID
Link Child Fields = LocationID

Execution
In this form, I can enter hotel names and location names, both of which are
stored in tblHotels and tblLocations respectively.
However, nothing is being updated in tblHotelLocations.

So before going on to combo boxes, how to I get the form to work properly,
and get tblHotelLocations working the way it should?

Thanks for your patience.
Bernie

 

[tina]

comments inline.

Before getting into the Combo boxes, here are the details for my tables.

tblHotels
Primary Key: HotelID, Indexed, Yes (No Duplicates), Data Type: AutoNumber
Field Name: txtHotel, Indexed, Yes (Duplicates Ok), Data Type: Text

if i were going to index field txtHotel, i'd set it to No Duplicates. this
table is simply a listing of all hotels. you don't want or need the exact
same hotel name listed twice.

tblLocations
Primary Key: LocationID, Indexed, Yes (No Duplicates), Data Type: AutoNumber
Field Name: txtLocation, Indexed, Yes (Duplicates Ok), Data Type: Text

if i were going to index field txtLocation, i'd set it to No Duplicates.
this table is simply a listing of all locations. you don't want or need the
exact same location listed twice.

tblHotelLocations
Primary Key: HLID, Indexed, Yes (No Duplicates), Data Type: AutoNumber
Field Name: HotelID, Indexed, Yes (No Duplicates), Data Type: Number

if you're going to index field HotelID, set it to Duplicates OK. remember,
one hotel may have many locations, so you need to be able to list the same
HotelID many times.

Field Name: LocationID, Indexed, Yes (No Duplicates), Data Type: Number
In the indexes box,

if you're going to index field LocationID, set it to Duplicates OK.
remember, one location may have many hotels, so you need to be able to list
the same LocationID many times.

Index Name1 is Primary Key, Field Name is HLID , and Index Properties:
Primary=Yes, Unique = Yes
Index Name2 is Tina, Field Name is HotelID, and Index Properties: Primary
=No, Unique = Yes
Index Name 3 is empty, Field Name is LocationID, and Index Properties are
not available

your two-field index is set up correctly (though i'd have picked another
index name <g>). with the changes on the individual field index settings
noted above, the table will accept the same HotelID multiple times, and the
same LocationID multiple times, but each *combination* of HotelID and
LocationID must be unique.

Relationships
tblHotels>HotelID is one-to-many related to tblHotelsLocations>HotelID
tblHotels>LocationID is one-to-many related to tblHotelsLocations>LocationID

MainForm=frmHotels
SubForm=frmLocationsSubForm1
Link Master Fields=HotelID
Link Child Fields = LocationID

it's not clear what tables are bound to the forms. i assume that tblHotels
is the RecordSource of frmHotels. but what table is used as the RecordSource
of frmLocationsSubForm1?

standard setup would be:

frmHotels.RecordSource = tblHotels
frmLocationsSubForm1.RecordSource = tblHotelLocations

the subform has combo box in it, with the ControlSource set to the
LocationID field in tblHotelLocations. the RowSource of the combo box is
tblLocations.

result: in the main form, you move to a specific hotel record. in the
subform, you add a record for every location that hotel is found at, using
the combo box "droplist" to choose each location from tblLocations.

obviously, you can add new hotel records directly in the main form. to add a
location to tblLocations during data entry in the subform, you can add code
to the combo box's NotInList event to update the locations table and the
combo box list - it's a commonly used process.

hth

 

[bw]

Great Tina!
My tables are now all linked and allowing me to add hotels in tblHotels,
locations in tblLocations, or I can add them in tblHotelLocations. No
duplicates when they are not allowed, and duplicates when they are. Cool!

I have added code to the combo box's NotInList event to update the locations
table and the combo box list in other applications. While I don't think I
should have a problem with this, this linkage thing MAY cause me some
consternation. I'll let you know later.

One of the reasons I was having a problem is because I did not use a lookup
table in tblHotelLocations to show the actual names of the Hotels and
Locations. Maybe stupid on my part, put in one of your earlier posts, you
said NOT to use lookup tables in this table. Why did you say that?

Thanks much for sticking with me on this. I never could have done it
without you.
Bernie

 

[tina]

One of the reasons I was having a problem is because I did not use a
lookup

table in tblHotelLocations to show the actual names of the Hotels and
Locations. Maybe stupid on my part, put in one of your earlier posts, you
said NOT to use lookup tables in this table. Why did you say that?

whoa. any of the problems you experienced in setting up your forms, were NOT
caused by omitting Lookup fields from a table, i can guarantee you that.
using combo box controls in *forms* is standard, and when the control's
properties are set correctly, they work fine and cause no problems. but the
only advantage i can think of to putting a Lookup field in a table, is that
the combo box is automatically created in a form bound to that table (in
certain circumstances). this is a minor advantage, at best, that is much
more than cancelled out by the problems that Lookup fields cause in forms,
queries, and reports. see http://www.mvps.org/access/lookupfields.htm for
details. if you browse these newsgroups, you'll find that the overwhelming
(not total, but overwhelming) consensus is *against* using Lookup fields in
tables. if you added Lookup fields to any of your tables, i strongly
recommend that you remove them.

While I don't think I
should have a problem with this, this linkage thing MAY cause me some
consternation. I'll let you know later.

it shouldn't. the setup i described to you is a standard user interface for
a many-to-many relationship with a "linking" or "resolver" table solution.
if you find yourself still having a problem with your form setup, or the
NotInList event update code, post back and we'll work on solving it.

hth

 

[bw]

Okay, I'll remove them (I promise), but not until you explain how to create
a combo box that works in my main form. I can't get the links to work right
at all.

I have a main form, and a subform. The main form has a text box for Hotel.
The subform is a datasheet subform with Locations.

How do I replace the text box in the main form with a combo box? Sure I can
create the combo box, but it will not "link" to the Locations properly.

Step by step, please. How is this done?
Bernie

 

[tina]

Bernie, i specifically outlined the form/subform setup in a post yesterday
(see below, between the "starred lines"). generally speaking, a combo box is
bound to a foreign key field in a form. you said your main form is bound to
tblHotels, which doesn't have any foreign key fields in it. so why do you
need a combo box in the main form? what field in tblHotels are you wanting
to bind it to? also, i've asked you twice what table your subform is bound
to; i can't help you sort this out without specific information.

************
it's not clear what tables are bound to the forms. i assume that tblHotels
is the RecordSource of frmHotels. but what table is used as the RecordSource
of frmLocationsSubForm1?

standard setup would be:

frmHotels.RecordSource = tblHotels
frmLocationsSubForm1.RecordSource = tblHotelLocations

the subform has combo box in it, with the ControlSource set to the
LocationID field in tblHotelLocations. the RowSource of the combo box is
tblLocations.

result: in the main form, you move to a specific hotel record. in the
subform, you add a record for every location that hotel is found at, using
the combo box "droplist" to choose each location from tblLocations.

obviously, you can add new hotel records directly in the main form. to add a
location to tblLocations during data entry in the subform, you can add code
to the combo box's NotInList event to update the locations table and the
combo box list - it's a commonly used process.

************

hth

 

[bw]

Tina,
The Record Source for the main form (frmHotels) is tblHotels.
The record Source for the subform (frmHotelLocationsSubform1) is
tblHotelLocations.
This relations are specified in the relations window with tblHotels
(HotelsID) one-to-many with tblHotelLocations (HotelID), AND
tblLocation(LocationID) one-to many with tblHotelLocations (LocationID).

The main form has a text box with a Control Source of Hotel (the only other
available choice in the Control Source drip down would be HotelID). When I
use page down on the main form, or the record selector to move to another
hotel, the locations change also. It works as it should. I can add new
locations to any hotel visible on the main form.

I would think it would be an easy thing to change this control to a combo
box, BUT IT's NOT! Nothing I do makes the combo box work correctly. I have
set up the combo box using the Combo Box Wizard, answering the questions as
follows:
1. Yes, I want the combo box to lookup values in a table or query.
2. Which table or query should provide the values for your combo
box?....Table: tblHotels
3. Selected fields which become columns in your combo box...are HotelID and
Hotel
4. How wide? Hide key column is checked, and the Hotels appear in the Hotel
column
5. Remember the value for later use. If I choose store the values in this
field (HotelID or Hotel), Access won't allow me to access the control).
When I go to the main form, the combo box is empty. The drop down displays
the hotels, but when I select one, the locations displayed are not correct.
It is very weird to say the least.

I have also tried to setup the combo box by right clicking on the text
control, selecting "Change to" and then combo box, then modifying to control
as normal. The result is the same action as produced by the Combox Box
Wizard.

There is something basic I'm overlooking I guess, but I just don't know what
it could be. Seems simple. If it works with the text box, why not the combo
box?

Do you see anything wrong here?
Bernie

 

[tina]

comments inline.

Tina,
The Record Source for the main form (frmHotels) is tblHotels.
The record Source for the subform (frmHotelLocationsSubform1) is
tblHotelLocations.
This relations are specified in the relations window with tblHotels
(HotelsID) one-to-many with tblHotelLocations (HotelID), AND
tblLocation(LocationID) one-to many with tblHotelLocations (LocationID).

okay, sounds like the tables/relationships are correct.

The main form has a text box with a Control Source of Hotel (the only other
available choice in the Control Source drip down would be HotelID).

okay, here you lose me completely. the main form should have *two* textbox
controls: one control bound to field HotelID, and the other control bound
to field HotelName.

When I
use page down on the main form, or the record selector to move to another
hotel, the locations change also. It works as it should. I can add new
locations to any hotel visible on the main form.

I would think it would be an easy thing to change this control to a combo
box, BUT IT's NOT! Nothing I do makes the combo box work correctly.

what control are you talking about? a control on the main form? you have no
use for a bound combo box control on the main form, that i can see. neither
the HotelID field or the HotelName field can be populated from a combo box.

if you want to use an UNBOUND combo box control to find specific records in
the form, that makes sense, and the wizard will do that for you. the key
point here is that the combo box control must be unbound - that is, leave
the ControlSource blank.

hth

 

[bw]

Hi Tina!
Everything is working well now. I couldn't have done it without you. The
tblHotelLocations was a big deal for me, as you may be able to imagine. I
have never used this kind of relationship before.

With regards to the combo box, I think I just misunderstood something you
were trying to explain.

okay, here you lose me completely. the main form should have *two* textbox
controls: one control bound to field HotelID, and the other control bound
to field HotelName.

Yes, what you've said is true, and that's the way it is...except that I
don't display the HotelID control on the form

what control are you talking about? a control on the main form? you have
no
use for a bound combo box control on the main form, that i can see.
neither
the HotelID field or the HotelName field can be populated from a combo
box.

The crux of the problem. I thought they could.

So I think we are finished. I'm happy. My application is functional and
the Forms and Reports look quite pleasing to the eye.

You've been patient with me. Thanks.
You've been a big help. Thanks.
You've given me an education. Thanks.
You've helped me to finish my project. Thanks.

I hope you come to visit me again on the next question I post.
Bernie

 

[tina]

you're welcome, Bernie, i'm glad we were able to get it all sorted out. who
knows if i'll see your next post, but no worries - there are numerous
skilled and expert developers answering questions here, many
head-and-shoulders above me, so it's very likely you'll get whatever help
you may need.

 

[JethroUK©]

you dont need any additional tables

take a look here:

http://tinyurl.com/bjfz6

 

[tina]

i agree that the concept described in your link is definitely useful,
Jethro, and can be very handy in various situations. in this thread, the
original post states

"I want to have a table of Hotels.
I want to have a table of Locations.

Hotels may have many Locations.
Locations may have many Hotels.
A Hotel/Location combination must be unique."

Bernie says he wants those two tables. what he is describing is a
many-to-many relationship between them, and that can only be resolved with a
third, linking table. to implement the alternate solution you suggest, he
would not need a third table, but he would have to completely discard one of
his two original tables. that may be a valid option for his particular
database design; i can't say yea or nay because i don't know nearly enough
about the underlying business process - so i just gave him a valid solution
to support his stated goal.

as for combo boxes, they're powerful tools and very handy for a variety of
uses, including the solution in your link, and also for working with
supporting tables. there are times when using a combo box with an "outside"
table, as opposed to a self-populating list, is necessary - so it's a good
idea to gain a thorough understanding of how they work, so as to be "ready"
when the time comes. once you get the hang of them, combo boxes are pretty
easy to work with, really. (the exception being reports, i'd say. i can't
really think of a good reason to use a combo box control in a report - since
by their nature they're intended for user interaction - and i don't use them
in reports at all.)

hth

 

[JethroUK]

i know Bernie says he wants a table structure, but most posters dont really
know what they want, they just want to acheive their goal - in Bernies case
he's made his goal ery clear and this is a possible solution