Re: Combo Box Design and/or Coding

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


.

Quantcast