Access combo box-show name, not ID, in table?

[Guest]

I have read through some old posts relative to my question, but came away
three times as confused and intimidated. Here is a simple and common example
of the equivalent of what I want to do:

Let's say I have a Suppliers table and form, and a Products table and form.
On the Products form I want to create a combo box for the Supplier field that
will use the Supplier table as a source to look up data. I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table. When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Is there a REASONABLE way for a non-programmer to accomplish this?

I notice that the sample Northwinds database shows names (as opposed to IDs)
in tables routinely. I have carefully studied design, field properties, and
relationships until my eyes glazed over. But for the life of me I cannot
duplicate this.

Then there's the question of expressions vs. SQL statements. How do I tell
the difference? How do I know which I need? How do I learn to write either
one?

I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000.
(That's where I started, but didn't get far. This time I mean it--but I'm
beginning to wonder if I'm crazy.)

 

[Wayne Morgan]

Yes, you can set up the equivalent of a combo box in the table. However,
tables are just to store data. They shouldn't be used by regularly going to
the table. As you indicate, the combo box on the form does show the name and
it is possible to have the name show in the results of a report with just
the ID being stored in the table.

What you are looking for is setting up the Lookup tab for that field in the
table. You would set it up just as you do the combo box on the form. The
problem with doing this is that if you do look at the table, what you see
isn't what is really there and this can cause confusion and problems later.

See item #2 at this link.
http://www.mvps.org/access/tencommandments.htm

 

[Albert D. Kallal]

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:

I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).

When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Yes...you build query to do this. You then build combo box on the form that
displays that returns the id..and this comb box can search/display by any
column you want..but you always still store the id.

Is there a REASONABLE way for a non-programmer to accomplish this?

You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the other
table. once done, then no code at all needs to be written here...

 

[Guest]

Thanks, Wayne.

OK, you've convinved me to do things the "normal" way--a rarity for me. That
list of the Evils of Lookup Fields is pretty scary, all right. Between you,
the Ten Commandments you linked me to, and the other response to this post,
some (not all) of my "Why?" questions have been answered. (See my response to
Albert Kallal).

I love the Ten Commandments, but I don't understand all of them. To wit:

#4 What's a "procedure"--as in "write comments in your procedures and
explain each variable"?
#5 What's error handling?
#6 What does it mean to split a database?
#9 I'm not even going to touch it.
#10 I dread to ask, although this sounds like the really important one--
*Back-up my database? I regularly backup my documents (to a zip disk). I
assumed my Access files (databases) would be included in that. Am I wrong?
Does this mean something different?
*Work only on the Production Database? What? How?
*Prototype copy? What? How?

Thanks for your help.

 

[Guest]

Thanks for the response.

You wrote,

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

First, viewed how? I find subdatasheets to be too cumbersome. (I am visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I guess my
reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can set up
a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if I can
just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. 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.

You say:

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

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 newbies (at
least this newbie), who presumably use the sample to help us understand the
normal way to use a database.

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? 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? 3) I don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:

Also, it is not clear:

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).

Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list box (on
the Products Form) to choose the Supplier Name (so I don't have to memorize
all Supplier ID#). I understand the norm is to then store the Supplier ID in
the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My thinking
was, "If I view the data in the Products Table, what good is the Supplier ID
to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a form, or
in a query, or in a report. 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?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.

 

[Fred Boer]

Dear "write on":

It caught my eye that you say you are visually impaired. I teach at a school
for blind and visually impaired students. I have taught computer courses
involving Access to totally blind and low vision students. If you have
problems with Access related to your vision, perhaps I might be of
assistance; or, perhaps I can learn from you!

Fred Boer

Thanks for the response.

You wrote,

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

First, viewed how? I find subdatasheets to be too cumbersome. (I am visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I guess my
reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can set up
a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if I can
just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. 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.

You say:

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

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 newbies (at
least this newbie), who presumably use the sample to help us understand the
normal way to use a database.

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? 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? 3) I don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:

Also, it is not clear:

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).

Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list box (on
the Products Form) to choose the Supplier Name (so I don't have to memorize
all Supplier ID#). I understand the norm is to then store the Supplier ID in
the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My thinking
was, "If I view the data in the Products Table, what good is the Supplier ID
to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a form, or
in a query, or in a report. 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?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).



Yes...you build query to do this. You then build combo box on the form that
displays that returns the id..and this comb box can search/display by any
column you want..but you always still store the id.


You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Fred Boer]

Dear "write on":

While I am not an expert like Wayne or Albert, perhaps I can suggest some
answers:

I love the Ten Commandments, but I don't understand all of them. To wit:

#4 What's a "procedure"--as in "write comments in your procedures and
explain each variable"?

A procedure is a piece of programming code. Access uses Visual Basic for
Applications as its programming language. A procedure might look like this:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Errorhandler
Me.cboAuthor.SetFocus
ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

This procedure, which runs when a form is opened, sets the focus to a
combobox on the form called "cboAuthor".

#5 What's error handling?

Error handling is programming code which deals with errors that might occur
with your program. In the procedure above, the lines 2,4,6,7, and 8 are
error handling code.

#6 What does it mean to split a database?

To actually have two MDB files: one which has only data tables, (the back
end), and one with all the forms, queries and reports (the front end). The
front end links to the tables in the back end. Usually the back end is on a
server, and the front end on individual workstations.

#9 I'm not even going to touch it.
#10 I dread to ask, although this sounds like the really important one--
*Back-up my database? I regularly backup my documents (to a zip disk). I
assumed my Access files (databases) would be included in that. Am I wrong?
Does this mean something different?

You back up a database by copying the database file to a backup location. No
one should be using the file when this is done. If you mean that you copy
the files in the folder "My Documents" to a zip disk, you may or may not be
backing up your Access database, depending on whether those files are in
that folder.

*Work only on the Production Database? What? How?
*Prototype copy? What? How?

Production Database=finished database application actually in use.
Prototype=unfinished database application


HTH
Fred Boer

 

[Jeff Conrad]

in message:

I love the Ten Commandments, but I don't understand all of them. To wit:

#6 What does it mean to split a database?

Just to add some additional information to Fred's excellent response,
here are some great links on splitting databases which should help
explain the purpose:

http://www.granite.ab.ca/access/splitapp/index.htm

http://allenbrowne.com.au/ser-01.html

http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18

http://www.access.qbuilt.com/html/gem_tips.html#SplitDB

How to manually split a Microsoft Access database in Access 2002 or in Access 2003:
http://support.microsoft.com/kb/304932/

 

[Guest]

Fred,

Most of that is pretty scary, but thanks for the enlightenment. At least it
tells me what I need to worry about, and what I don't. That's helpful.

write on

 

[Guest]

Dear Fred,

Wow! That's pretty cool!

I use ZoomText for screen magnification, and I love it. The downside is, it
doesn't play well with my graphics card (or visa versa), so mouse control can
be a hassle. The mouse can be difficult to position exactly where I need it;
it's hard to control highlighting of text, and may not stop highlighting when
I let go of the mouse button; mouse pointer may jump around the screen
out-of-control.

This has led me to learn and use keyboard shortcuts as much as I can. But
that presents its own set of challenges. First of all, There are cases where
the ZoomText hotkeys conflict with Microsoft shortcuts. ZoomText always wins.
Then I have to do the MS function another way--usually ALT+menu keys. But
even that doesn't always work. (Case in point: If I open a new message window
in Outlook Express, there is an address book icon next to the "To:" box
label. The idea is, you click that icon to open your address book and select
an address to put in the "To:" field. I cannot find a way to open that icon
without a mouse. I cannot even get it to receive focus so I can try to open
it. And this function can't be done with ALT+menus either. Do screen readers
have a way around this?)

Receiving focus is often a problem when trying to navigate. ZoomText has a
function that lets me ALT+TAB through all open windows, until I get to the
one I want. But the window I stop at doesn't always receive focus the way it
should, so that my next keyboard command will work there. I particularly have
trouble with Outlook in this regard.

Navigating within a window is usually a pain. The F6 key doesn't work most
of the time (to move between panes) the way MS shortcut key lists say it is
supposed to, and I don't even know why. I can't even find ZoomText conflicts
to explain that. There are certain areas of the Outlook main window, for
example, that just can't be had without a mouse.

Internet Explorer is a nightmare without a mouse. I can't even TAB or F6 out
of the menu bar/toolbar area, into the actual web page. This is where MS
MouseKeys comes in handy. I can use keys to move and click the mouse; once
you get used to it, control is much better than handling the mouse, although
it's slower. (See Accessibility Wizard in Control Panel. You can also adjust
the thickness and blink rate of the cusor.) ZoomText does have the Web Finder
feature that works with IE. That is useful, if I am determined to use the
keyboard just on principle, but it is more cumbersome and time-consuming than
using the mouse.

As for Access and keyboard shortcuts--well, I haven't even gotten that far
yet. Just getting comfortable with Access itself is all I can handle right
now. The shortcut learning curve will have to wait. And I'm leary of it,
because I supsect all the navigating between multiple objects and views will
be really hairy. Again, the ALT+TAB does help for switching windows. But
receiving focus...? I don't know.

Do you work with ZoomText much? Got any tips for me?

write on
P.S. Should we change the subject, and/or move this discussion to a
different forum? Can we? Will "Notify me of replies" work if we do?

Dear "write on":

It caught my eye that you say you are visually impaired. I teach at a school
for blind and visually impaired students. I have taught computer courses
involving Access to totally blind and low vision students. If you have
problems with Access related to your vision, perhaps I might be of
assistance; or, perhaps I can learn from you!

Fred Boer

Thanks for the response.

You wrote,

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

First, viewed how? I find subdatasheets to be too cumbersome. (I am visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I guess my
reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can set up
a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if I can
just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. 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.

You say:

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

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 newbies (at
least this newbie), who presumably use the sample to help us understand the
normal way to use a database.

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? 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? 3) I don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).

Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list box (on
the Products Form) to choose the Supplier Name (so I don't have to memorize
all Supplier ID#). I understand the norm is to then store the Supplier ID in
the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My thinking
was, "If I view the data in the Products Table, what good is the Supplier ID
to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a form, or
in a query, or in a report. 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?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).


When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Yes...you build query to do this. You then build combo box on the form that
displays that returns the id..and this comb box can search/display by any
column you want..but you always still store the id.


Is there a REASONABLE way for a non-programmer to accomplish this?

You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Fred Boer]

Dear write on:

I'm at home today, and can't really respond appropriately to your message,
but I will post back tomorrow from work (where I have Zoomtext and can try
to reproduce the example you mention...). Chat more tomorrow!

Fred

Dear Fred,

Wow! That's pretty cool!

I use ZoomText for screen magnification, and I love it. The downside is, it
doesn't play well with my graphics card (or visa versa), so mouse control can
be a hassle. The mouse can be difficult to position exactly where I need it;
it's hard to control highlighting of text, and may not stop highlighting when
I let go of the mouse button; mouse pointer may jump around the screen
out-of-control.

This has led me to learn and use keyboard shortcuts as much as I can. But
that presents its own set of challenges. First of all, There are cases where
the ZoomText hotkeys conflict with Microsoft shortcuts. ZoomText always wins.
Then I have to do the MS function another way--usually ALT+menu keys. But
even that doesn't always work. (Case in point: If I open a new message window
in Outlook Express, there is an address book icon next to the "To:" box
label. The idea is, you click that icon to open your address book and select
an address to put in the "To:" field. I cannot find a way to open that icon
without a mouse. I cannot even get it to receive focus so I can try to open
it. And this function can't be done with ALT+menus either. Do screen readers
have a way around this?)

Receiving focus is often a problem when trying to navigate. ZoomText has a
function that lets me ALT+TAB through all open windows, until I get to the
one I want. But the window I stop at doesn't always receive focus the way it
should, so that my next keyboard command will work there. I particularly have
trouble with Outlook in this regard.

Navigating within a window is usually a pain. The F6 key doesn't work most
of the time (to move between panes) the way MS shortcut key lists say it is
supposed to, and I don't even know why. I can't even find ZoomText conflicts
to explain that. There are certain areas of the Outlook main window, for
example, that just can't be had without a mouse.

Internet Explorer is a nightmare without a mouse. I can't even TAB or F6 out
of the menu bar/toolbar area, into the actual web page. This is where MS
MouseKeys comes in handy. I can use keys to move and click the mouse; once
you get used to it, control is much better than handling the mouse, although
it's slower. (See Accessibility Wizard in Control Panel. You can also adjust
the thickness and blink rate of the cusor.) ZoomText does have the Web Finder
feature that works with IE. That is useful, if I am determined to use the
keyboard just on principle, but it is more cumbersome and time-consuming than
using the mouse.

As for Access and keyboard shortcuts--well, I haven't even gotten that far
yet. Just getting comfortable with Access itself is all I can handle right
now. The shortcut learning curve will have to wait. And I'm leary of it,
because I supsect all the navigating between multiple objects and views will
be really hairy. Again, the ALT+TAB does help for switching windows. But
receiving focus...? I don't know.

Do you work with ZoomText much? Got any tips for me?

write on
P.S. Should we change the subject, and/or move this discussion to a
different forum? Can we? Will "Notify me of replies" work if we do?

Dear "write on":

It caught my eye that you say you are visually impaired. I teach at a school
for blind and visually impaired students. I have taught computer courses
involving Access to totally blind and low vision students. If you have
problems with Access related to your vision, perhaps I might be of
assistance; or, perhaps I can learn from you!

Fred Boer

Thanks for the response.

You wrote,
As others mentioned, the WHOLE idea of relational database system is

hat
you

only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

First, viewed how? I find subdatasheets to be too cumbersome. (I am visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I

guess
my

reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can

set
up

a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if

I
can

just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. 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.

You say:
The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

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

newbies
(at

least this newbie), who presumably use the sample to help us

understand
the

normal way to use a database.

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? 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? 3) I don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:
Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store

Supplier
NAMES

(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not

clear
on

name vs product name...or do you mean one and the same?).

Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list

box
(on

the Products Form) to choose the Supplier Name (so I don't have to memorize
all Supplier ID#). I understand the norm is to then store the Supplier

ID
in

the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My thinking
was, "If I view the data in the Products Table, what good is the

Supplier
ID

to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a

form,
or

in a query, or in a report. 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?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.



:

As others mentioned, the WHOLE idea of relational database system is

hat
you

only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store

Supplier
NAMES

(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not

clear
on

name vs product name...or do you mean one and the same?).


When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Yes...you build query to do this. You then build combo box on the

form
that

displays that returns the id..and this comb box can search/display

by
any

column you want..but you always still store the id.


Is there a REASONABLE way for a non-programmer to accomplish this?

You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D. Kallal]

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.

 

[Guest]

I am just like you a novice on this land of masters. I did it by using a
simple querry selecting the names out of the table.

In the combo box:
Row Source Type: Table/Querry
Row Source: SELECT [Query].[NAME] FROM [Query];

This was simple and it worked for me.

 

[Fred Boer]

Hi:

Ok, well, I am at work now (my lunchtime...). What version of Zoomtext are
you using? Have you contacted AiSquared about your graphics card
incompatability issue?

I *still* can't try to reproduce your issue with Outlook Express, since the
school workstations (which have Zoomtext) don't have Outlook Express, and my
computer (which has Outlook Express) doesn't have Zoomtext. I hope to grab
the systems officer and install Zoomtext this afternoon. Then I can try to
reproduce the problems you describe.

As for Access and keyboard shortcuts--well, I haven't even gotten that far
yet. Just getting comfortable with Access itself is all I can handle right

Yes, well, Access has a steeper learning curve than Excel or Word. You have
to learn a bit about "normalization" and other things before you can start
setting up an effective Access application. The "10 Commandments" were meant
to help beginners avoid certain pitfalls, but I suppose they can be
confusing.

One thing I will add to other responses, is that you really must avoid
working directly with tables. The general concept is that you set up the
tables, but you do all your data entry/editing only through forms. This has
an advantage for you, btw, in that you can design the forms with whatever
font size you want - so, if you prefer a 24 point Arial font for visual
comfort, this is no problem.

I have many students that use Zoomtext. I have explored the program, but, I
am not blind or low vision, so I don't use it regularly. However, my
students can often answer questions I might need answering, and I can
experiment myself if necessary. As I say, I'll grab an installation this
afternoon, so that I can play with it on my own machine.

P.S. Should we change the subject, and/or move this discussion to a
different forum? Can we? Will "Notify me of replies" work if we do?

If you wish, feel free to email me privately. The "reply to" email address
for this post will give you a "munged" copy of my email address. Just remove
the "NO" and "SPAM" to derive my actual email address. Revealing an
un-munged email address in newsgroups is a surefire way to be inundated with
spam....

I think as long as we are even peripherally dealing with Access this forum
will be ok. If we want to move more exclusively into Zoomtext, we might want
to move. I'll have a look around for a Zoomtext oriented newsgroup...

Looking forward to chatting more with you...

Fred

 

[Fred Boer]

Well, looked at three things you mention:

Outlook Express, "To" button issue: It is in the menus: Alt-T,R

You say "ZoomText has a function that lets me Alt-Tab...". Alt-Tab is a
Windows shortcut keystroke. If you simply hit Alt-Tab it cycles to the next
open window. If you hold the Alt key, you can tab through the list of open
windows to pick the one you want.

When I open Internet Explorer, I am placed on the web page and I can simply
tab through all the links. Seems to work the same with or without Zoomtext
running...


Cheers!
Fred

 

[Guest]

Dear Fred,

I was running ZoomText 8.12.2.2. Your question reminded me to check for
updates, so I just downloaded 8.13.0.6.

I think I discussed graphics cards with AiSquared tech when I was having a
problem with a driver update. The tech was using the same card I have, I
think, but had no problems. I think my card might be just plain quirrely, but
I can't get any tech support for that. I never know when I start up whether
my display is going to be nice and sharp and clean, or too blurry to look at.
There's no rhyme or reason to it. But I have tested my graphics with and
without ZoomText, and there is a definite problem with ZT running. I've even
had my mouse pointer up and disappear a couple of times. The first time, I
was so glad I knew how to close programs and shut down from the keyboard. The
second time, I discovered that if I disable ZT, the pointer comes right back.

Access and shortcuts: Try to create a new query in Design View. How do you
drag and drop the fields from the Table list to the grid without using the
mouse? Then navigate to the sort field; now open the drop down list of
choices. Finish the grid and save your file. Now you want to close that
desing window. The first--andlast--time I tried to do something like that, I
opened the window shortcut menu (ALT+SPACEBAR

 

[Guest]

Ok...So I'm typing away, and all of a sudden look up and see that my post has
been sent, and I'm "Done!" What???

I need to deal with this later. GRRRR.

 

[Fred Boer]

Hi!

Well, if AiSquared tech support couldn't solve the graphics card issue, I
don't think there is anything I can suggest. However as far as the Access
shortcut keystrokes goes:

Dragging and dropping to the design grid issue:

You don't need to drag and drop here. Just navigate to the top of the column
in the design grid. As you enter, a dropdown arrow is displayed. Press F4
and you will get a dropdown list of all available fields. Simply select the
field you need.

Closing design view window:

To close the design window, try Ctrl-F4.

HTH
Fred

 

[Guest]

Dear Fred,

I don't even know what else I was going to say when my post went POOF,
except that one time I tried to close a design window with shortcuts, and
ended up somehow closing the whole Access program-along with a whole bunch of
object windows I had open.

Your Access keyboard shortcuts sound workable. It may be a while before I
mess much with that, but your help will give me a good head start, and more
courage to try. Thanks.

A ZoomText newgroup would be nice, except that you'll then have to teach me
how to use a newsgroup! I'm clueless. I know they exist, and that I can
theoretically use them in Outlook Express (which I currently use for email)
or in Outlook (which is where I plan to shift my email soon), but that's all
I know.

Thanks,

write on

 

[Jeff Conrad]

in message:

A ZoomText newgroup would be nice, except that you'll then have to teach me
how to use a newsgroup! I'm clueless. I know they exist, and that I can
theoretically use them in Outlook Express (which I currently use for email)
or in Outlook (which is where I plan to shift my email soon), but that's all
I know.

A Google search revealed several newsgroups you may find of interest:

http://groups-beta.google.com/groups?q=zoomtext