Re: Combo Box Design and/or Coding

Tech-Archive recommends: Speed Up your PC by fixing your registry

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
>>
>>
>
>


.

Relevant Pages

  • Re: Combo Box Design and/or Coding
    ... > HTH?? ... >> LocationID (foreign key from tblLocations) ... >> you may use the two fields as a combination primary key, ... >>> I want to have a table of Hotels. ...
    (microsoft.public.access.formscoding)
  • Re: Combo Box Design and/or Coding
    ... LocationID (foreign key from tblLocations) ... primary keys, you can add another field to serve as a surrogate primary key, ... tblHotelLocations ... > I want to have a table of Hotels. ...
    (microsoft.public.access.formscoding)
  • Re: FAQ? factors influencing choice of data type for primary key
    ... >>example, an Autonumber as a primary key, are there ... >>it is a foreign key? ... >don't need to bring the lookup table or any indexes into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: OT: SQL & Dave
    ... A&E broadcast ... My initial thought was to have the "show number" be the primary key, ... so that it'll be the foreign key in other tables (guests, staff, ... could I set up those other tables (guest, ...
    (alt.fan.letterman)
  • Re: Data changes but reverts.
    ... > trust my data to a wizard-built form. ... > 3) What is the primary and foreign key in your relationship ... I did a TABLEUPDATE() and it's now working! ... The symbol field is the primary key in the Symbols table and it is ...
    (microsoft.public.fox.programmer.exchange)