Re: Combo Box Design and/or Coding

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" <nospam@xxxxxxxxxxx> wrote in message
news:DmQhf.169588$zb5.98898@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> comments inline.
>
> "bw" <iamnu@xxxxxxxxxxxx> wrote in message
> news:11of9e7gnef5109@xxxxxxxxxxxxxxxxxxxxx
>> Tina,
>> I setup a test database, made the three tables, then created a Main form
>> with a data*** 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?
>
>>
>> Can you explain?
>>
>> Thanks,
>> Bernie
>>
>> "tina" <nospam@xxxxxxxxxxx> wrote in message
>> news:7HLhf.92125$qk4.27922@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> > you're very welcome :)
>> > (once i respond to a post, i usually "watch" it for at least a week)
>> >
>> >
>> > "bw" <iamnu@xxxxxxxxxxxx> wrote in message
>> > news:11of17jk2ho6p8b@xxxxxxxxxxxxxxxxxxxxx
>> >> 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" <nospam@xxxxxxxxxxx> wrote in message
>> >> news:r6Lhf.91964$qk4.89210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> >> > 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" <iamnu@xxxxxxxxxxxx> wrote in message
>> >> > news:11oeugjcg4jnl21@xxxxxxxxxxxxxxxxxxxxx
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>


.