Re: Access combo box-show name, not ID, in table?
- From: BruceM <BruceM@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 11:37:25 -0700
I'm just going to chime in here, realizing that I am running the risk of
repeating something. There are occasions when you will want to store the
name. For instance, if you need to know a person's name at the time they
created a record you would store that person's name. We use a database to
create a sort of certificate, and it needs to contain the person's name at
the time they signed the certificate. If, however, you need to keep track of
an account through several changes of company name you will want to store the
ID. Think of your social security number, which is associated with you no
matter what name you use. It is your primary key, so to speak, with the
Social Security Administration.
If, however, you want an account to remain through a name change, you MUST
store the primary key field. For instance, you would want payments into the
company's 401(k) plan to be associated with you if you change your name,
without having to alter every payroll record containing your old name.
Try using autoform (a sort of lightning bolt icon on the toolbar) to make a
form based on a table. It will give you an idea how a form can display the
data. Now use the Combo Box Wizard to create a combo box to find a record
based on what you select.
A combo box (or a text box, etc.) can be bound or unbound. If it is bound
it means that the record source is linked to a field in an underlying table.
If you created a combo box with the wizard, right click the combo box and
select Properties. Click the Data tab and look at the Bound Column, which is
probably 1. Now click on the Format tab and look at the column widths, which
will be 0";1.5" or something like that if you followed the suggestions in the
wizard to hide the key field. Now click Row Source on the Data tab, and
click the three dots. What you are looking at could be called the row source
query (maybe that IS what it's called).
Now that you have a form, you can make a query based on the form's Record
Source table. Sort by something convenient in the query design grid, save
the query, then go back to the form and change its record source from the
table to the new query. It will be just the same as before, except the
records will be sorted. You can also use a query to combine first name and
last name, and things like that. Once you have done something like that you
can add the field to the form without having to store it as you would if it
were a table field.
I'm not trying to be comprehensive here, just trying to add a piece to the
puzzle if I can.
I
"write on" wrote:
> Dear Albert,
>
> But I tried started with the Control Wizard, and it dsplayed the (Supplier)
> ID instead of the name in the (Products) table. That's when I started taking
> Northwinds apart with a fine-toothed comb. I never did figure out what was
> wrong with mine. Sigh.
>
> I won't have time to mess with this again until tomorrow. I'll try again,
> this time with a new database that I haven't had time to screw up yet, and
> following your instructions instead of the Idiot's Guide. I'll let you know.
>
> Thanks,
>
> write on
>
>
> "Albert D. Kallal" wrote:
>
> > > But then, I could
> > > just open the query, rather than even opening the table, which I guess is
> > > the
> > > point after all. Sorry I'm rambling; I'm "thinking out loud" here.
> >
> > Correct. And, in fact, if you do things right..then you will open a nice
> > form, or whatever. Think of using a application...you don't want to "guess"
> > what table to open etc...but provide a nice menu that opens up a nice form.
> > Users don't care about tables...and stuff like that. Of course..now that you
> > are starting to wear the developers hat..then a different view of things
> > needs to be taken.
> >
> > > I'm sorry, but I have to ask: If the normal and recommended way is to
> > > store and display the [Supplier] ID# in the [Products] table, WHY is the
> > > sample
> > > database written to display [Supplier] Names in the [Products] tables,
> > > which is the ABnormal and NOT recommended way? This is confusing to us
> > > newbie's
> >
> > Ah, the got the above wrong!!
> >
> > >If the normal and recommended way is to store
> > > and display the [Supplier] ID#
> >
> > No, the normal and recommend way is to start the ID, but display the
> > suppler name!!! Your users will NEVER EVER see the internal ID. So, store
> > the id, but display the suppler name.
> >
> > To make such a control on a form, you simply use the wizard, and drop in a
> > combo box into the form. The wizard will guide you thought the rest of this
> > process..but just make sure the ID is the first field you select for the
> > combo box..and the 2nd field the "text description", or supplier name in
> > this case. The result will be a combo box that searches, and displays by
> > suppler name..but SAVES THE ID in a appropriate (long integer) field.
> > Ms-access will thus do (solve) this common solution to your problem.
> >
> > > Also, I'm afraid I don't quite follow. 1) Are you implying that I should
> > > be
> > > looking at the form within the table somehow?
> >
> > For you end users, you most certainly do want a nice form, and a nice comb
> > box solves this for you.
> >
> > However, if you must display and "open" the data table directly (which is
> > not
> > a good idea for end users), then you have to use the query builder, and join
> > in those additional fields from the suppler table that you want. And perhaps
> > you want a few more then just the suppler name to be available in this
> > query.
> > Once you make this query, you save it, and then as you stated open this
> > query in place of the table..and you will see the "text" suppler name in the
> > table, along with any other fields that you want from the "other" table.
> >
> > 2) Are you saying that the
> > > combo box in the form, which displays Supplier Names to choose from for
> > > data
> > > entry, but stores the ID in the table, is itself a kind of query?
> >
> > It does in fact behind the scenes use a query. However, just let the combo
> > box wizard make it for you. You can take a look at the settings for the
> > combo box, and you will note that some query is used "inside" the combo
> > box...but you don't have to make this query if you use the wizard.
> >
> > 3) I don't
> > > understand what you mean by "set the ID." 4)
> >
> > You got the above idea right. When I said "id", I meant suppler id. However,
> > for consistency sake, 100% of my tables use "ID" as the key field, and then
> > I never have to "guess" as what the primary key id used for a table is.
> >
> >
> > > The table is apparently not MEANT for viewing;
> > > it's just a storage closet from which to pull out whatever I want to view
> > > somewhere else. Did I finally get it?
> >
> > Yes...and of course you "can" use a query in place of a table anyway..and it
> > will/can display data from other tables.
> >
> >
> > --
> > Albert D. Kallal (Access MVP)
> > Edmonton, Alberta Canada
> > pleaseNOOSpamKallal@xxxxxxx
> > http://www.members.shaw.ca/AlbertKallal
> >
> >
> >
> >
.
- References:
- Access combo box-show name, not ID, in table?
- From: write on
- Re: Access combo box-show name, not ID, in table?
- From: Albert D. Kallal
- Re: Access combo box-show name, not ID, in table?
- From: write on
- Re: Access combo box-show name, not ID, in table?
- From: Albert D. Kallal
- Re: Access combo box-show name, not ID, in table?
- From: write on
- Access combo box-show name, not ID, in table?
- Prev by Date: RE: List box V/s Combo populating form fields(thanks in Advance)
- Next by Date: RE: Query export excel: save format
- Previous by thread: Re: Access combo box-show name, not ID, in table?
- Next by thread: Re: Access combo box-show name, not ID, in table?
- Index(es):
Relevant Pages
|
Loading
