A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #21  
Old April 26th, 2005, 05:51 PM
Fred Boer
external usenet poster
 
Posts: n/a
Default

The Access shortcuts should work, try them when you get to that point...

Well, I don't know if I have to "teach you to use a newsgroup"! That is
exactly what you are doing now... This is a newsgroup, and you and I are
posting messages in the newsgroup. So... you already know how to use a
newsgroup.. you just didn't know that you did!

I see Jeff has found a suitable newsgroup for Zoomtext issues. Certainly if
you have Access related questions, you can post here. I expect you will have
better luck in, perhaps, comp.speech.users for adaptive technology
questions. Even better, perhaps would be AiSquared's own forums which you
can get to via the following link:

http://www.aisquared.com/forums/index.php

Good Luck!
Fred

"write on" wrote in message
news
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



  #22  
Old April 26th, 2005, 05:56 PM
Fred Boer
external usenet poster
 
Posts: n/a
Default

Other possibilities:

http://groups.yahoo.com/group/zoomtext/

http://www.magnifiers.org./

Cya!
Fred



"write on" wrote in message
news
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


"Fred Boer" wrote:

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



"write on" wrote in message
...
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
"Fred Boer" wrote:

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











  #23  
Old April 26th, 2005, 05:58 PM
write on
external usenet poster
 
Posts: n/a
Default

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

http://www.members.shaw.ca/AlbertKallal




  #24  
Old April 26th, 2005, 07:37 PM
BruceM
external usenet poster
 
Posts: n/a
Default

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

http://www.members.shaw.ca/AlbertKallal




  #25  
Old April 28th, 2005, 07:26 PM
write on
external usenet poster
 
Posts: n/a
Default

OK, here is what I did that finally worked...almost.

I created the table (actually, two tables) in design view first. I created a
combo box in the table, where I wanted the (in this case) CategoryName, from
the previously created Category Table. I used the expression builder to add
the CategoryID and CategoryName fields to the grid. (An aside: In the field
list, the first choice is an "*". I have found instructions for how to use
it, but I can't figure out what it does, or when/why I should or shouldn't
use it. Will someone please educate me?) I set the Bound Column to 1, and the
Column Count to 2. I set the Column Width for the first column (CategoryID)
to 0". I switched to table view, and verified that this worked as desired: I
could use the drop down combo box list to select the CategoryName I wanted
for each record. When the table was complete, I used AutoForm to create a
form based on that table, and it put the combo box in for me. I don't
remember if I had to tweak the combo box properties in form design--maybe. I
did this for two sets of table-and-form.

With those in place, I started using the form to enter data in the first
table. I was happy as a clam, until... I came to a category field where I
wanted to enter something that wasn't in my list (from Category Table). Since
this was a combo box, that shouldn't be a problem, right? Wrong. Access
wouldn't let me enter a value that wasn't on the list. I went to design view
to figure out why. I looked at properties for the combo box. I noticed that
"Limit to List" was marked "Yes." I thought setting that to "No" would solve
the problem. But Access said it couldn't change that setting right now,
because the first visible row was not equal to the bound column. The message
instructed me to adjust the column width appropriately, then change the
setting. But if I put a column width, other than zero, in for the Bound
Column, I was back to getting ID#s, instead of Names, to choose from in my
combo box. So what's the point of having a combo box, if you can't enter a
value that isn't in the list? Does this only work when you have manually
typed in the list, and not when you are picking it from a table?

The other issue I have come across is wanting to merge these two tables
together. I found an example of roughly want I want in Northwinds (sample
database--the Union Query "Customers and Suppliers by City," with a
"Relationship" field to show whether the Name is a customer or a supplier),
but it looks as if it has to be done in SQL, and I don't think I'm up to all
that.

I have these two tables with closely related, but not quite the same,
groups of data. About half of the fields are the same, or close enough. One
field could be accomplished with a set of three checkboxes to choose from:
the first two (choose one) would apply to Table 1 item; the third box would
apply only to Table 2 items. There is one field that is not quite the same
thing, but the format (hyperlink) is the same, and I could use one field for
both groups (may or may not be an email address for Table 1 items, will
always be a website URL for Table 2 items). Then there are 3 or 4 fields that
pertain only to Table 1 items.

It occurred to me that I might try merging the two groups of data into one
table and form that used all the common fields. Then, use a subform for the
fields that apply only to one group, and save that in a separte table, much
like Northwinds' Orders and Order Detail tables, with a Order Detail subform
in the Orders form. So which is easier--a union query in SQL, or deciding
which fields to put in which table and setting up a subform? The latter still
uses two tables, not to mention a complicated subform. Then I still have to
write the query or queries I need. Maybe I'm better off with what I have for
now. Thoughts, anyone?

"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

http://www.members.shaw.ca/AlbertKallal




  #26  
Old April 28th, 2005, 07:34 PM
write on
external usenet poster
 
Posts: n/a
Default

Thanks, Jeff. I linked over there and bookmarked it. I will check it out when
I have a chance, but I'm in the middle of too many things right now.

"Jeff Conrad" wrote:

"write on" wrote in message:
news
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

--
Jeff Conrad
Access Junkie
Bend, Oregon



  #27  
Old April 28th, 2005, 08:06 PM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default

"write on" wrote in message:
...

Thanks, Jeff. I linked over there and bookmarked it. I will check it out when
I have a chance, but I'm in the middle of too many things right now.


You're welcome, glad to help.
Don't hesitate about taking Fred up on his offer for off-line assistance.
He really knows this stuff.
--
Jeff Conrad
Access Junkie
Bend, Oregon


  #28  
Old April 28th, 2005, 08:16 PM
write on
external usenet poster
 
Posts: n/a
Default

Fred,

The Access shortcuts should work, try them when you get to that point...


I did start using the shortcuts, like typing in a box the first letter or
two and letting it fill in. Neat.

Well, I don't know if I have to "teach you to use a newsgroup"! That is
exactly what you are doing now... This is a newsgroup, and you and I are
posting messages in the newsgroup. So... you already know how to use a
newsgroup.. you just didn't know that you did!


Fascinating. But how do I set this up to use it in Outlook Express or
Outlook, especially with that
dreadful-NET.Passport-sign-in-business-which-I-hate-so-passionately
(Microsoft, are you listening???)? And is there a difference between a
newsgroup and a chatroom?

I see Jeff has found a suitable newsgroup for Zoomtext issues. Certainly if
you have Access related questions, you can post here. I expect you will have
better luck in, perhaps, comp.speech.users for adaptive technology
questions. Even better, perhaps would be AiSquared's own forums which you
can get to via the following link:


I will check out both your and Jeff's ZoomText newsgroup suggestions. They
look interesting.

"Fred Boer" wrote:

The Access shortcuts should work, try them when you get to that point...

Well, I don't know if I have to "teach you to use a newsgroup"! That is
exactly what you are doing now... This is a newsgroup, and you and I are
posting messages in the newsgroup. So... you already know how to use a
newsgroup.. you just didn't know that you did!

I see Jeff has found a suitable newsgroup for Zoomtext issues. Certainly if
you have Access related questions, you can post here. I expect you will have
better luck in, perhaps, comp.speech.users for adaptive technology
questions. Even better, perhaps would be AiSquared's own forums which you
can get to via the following link:

http://www.aisquared.com/forums/index.php

Good Luck!
Fred

"write on" wrote in message
news
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




  #29  
Old April 29th, 2005, 03:10 PM
BruceM
external usenet poster
 
Posts: n/a
Default

For the combo box issue, on the combo box property sheet click the Event tab.
One of the events is On Not in List. You could use that to open a data
entry form or something like that.
I am a bit puzzled about something. When you say you created a combo box in
the table, are you saying that you created one on the form that is bound to
the table? That seems to be the drift of the rest of that section of your
question.
On the other issue, when you speak of combining the tables. I expect you
mean with a query and not by literally making one table out of two. The
details would be much easier to address if you could describe the purpose of
each table, and what you hope to gain by combining the information. Do want
to list by city, or what exactly? In describing a table, do so in one
sentence without using the word "and". Don't get carried away with this;
address and phone number are part of personal information, and can be
together in a table, but customers and suppliers do not belong in the same
table, even if both have addresses and phone numbers. Instead, the tables
are related to each other. A query can bring tables together to organize
information, but that is very different from combining two tables into one.
There are legitimate reasons for doing that, but I don't think your situation
is among them.

"write on" wrote:

OK, here is what I did that finally worked...almost.

I created the table (actually, two tables) in design view first. I created a
combo box in the table, where I wanted the (in this case) CategoryName, from
the previously created Category Table. I used the expression builder to add
the CategoryID and CategoryName fields to the grid. (An aside: In the field
list, the first choice is an "*". I have found instructions for how to use
it, but I can't figure out what it does, or when/why I should or shouldn't
use it. Will someone please educate me?) I set the Bound Column to 1, and the
Column Count to 2. I set the Column Width for the first column (CategoryID)
to 0". I switched to table view, and verified that this worked as desired: I
could use the drop down combo box list to select the CategoryName I wanted
for each record. When the table was complete, I used AutoForm to create a
form based on that table, and it put the combo box in for me. I don't
remember if I had to tweak the combo box properties in form design--maybe. I
did this for two sets of table-and-form.

With those in place, I started using the form to enter data in the first
table. I was happy as a clam, until... I came to a category field where I
wanted to enter something that wasn't in my list (from Category Table). Since
this was a combo box, that shouldn't be a problem, right? Wrong. Access
wouldn't let me enter a value that wasn't on the list. I went to design view
to figure out why. I looked at properties for the combo box. I noticed that
"Limit to List" was marked "Yes." I thought setting that to "No" would solve
the problem. But Access said it couldn't change that setting right now,
because the first visible row was not equal to the bound column. The message
instructed me to adjust the column width appropriately, then change the
setting. But if I put a column width, other than zero, in for the Bound
Column, I was back to getting ID#s, instead of Names, to choose from in my
combo box. So what's the point of having a combo box, if you can't enter a
value that isn't in the list? Does this only work when you have manually
typed in the list, and not when you are picking it from a table?

The other issue I have come across is wanting to merge these two tables
together. I found an example of roughly want I want in Northwinds (sample
database--the Union Query "Customers and Suppliers by City," with a
"Relationship" field to show whether the Name is a customer or a supplier),
but it looks as if it has to be done in SQL, and I don't think I'm up to all
that.

I have these two tables with closely related, but not quite the same,
groups of data. About half of the fields are the same, or close enough. One
field could be accomplished with a set of three checkboxes to choose from:
the first two (choose one) would apply to Table 1 item; the third box would
apply only to Table 2 items. There is one field that is not quite the same
thing, but the format (hyperlink) is the same, and I could use one field for
both groups (may or may not be an email address for Table 1 items, will
always be a website URL for Table 2 items). Then there are 3 or 4 fields that
pertain only to Table 1 items.

It occurred to me that I might try merging the two groups of data into one
table and form that used all the common fields. Then, use a subform for the
fields that apply only to one group, and save that in a separte table, much
like Northwinds' Orders and Order Detail tables, with a Order Detail subform
in the Orders form. So which is easier--a union query in SQL, or deciding
which fields to put in which table and setting up a subform? The latter still
uses two tables, not to mention a complicated subform. Then I still have to
write the query or queries I need. Maybe I'm better off with what I have for
now. Thoughts, anyone?

"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

http://www.members.shaw.ca/AlbertKallal




  #30  
Old April 29th, 2005, 08:41 PM
write on
external usenet poster
 
Posts: n/a
Default

on the combo box property sheet click the Event tab.
One of the events is On Not in List. You could use that to open a data
entry form or something like that.


Huh? I was already in a data entry form when the problem occurred. Are you
saying I need to nest another data entry form into the one I already have, in
this Event List thing? And wouldn't I just have the same problems with
properties? How would this help?

I am a bit puzzled about something. When you say you created a combo box in
the table, are you saying that you created one on the form that is bound to
the table? That seems to be the drift of the rest of that section of your
question.


No, I created the table first. In the Data Type field, I used the Lookup tab
(properties, at the bottom) to create a combo box in the table. When I was
done creating the table, I used Autoform to create the form. That combo box I
created in the table automatically translated into a combo box on the form.
Then I switched to the form to do data entry, and that's where the problem
happened.

On the other issue, when you speak of combining the tables. I expect you
mean with a query and not by literally making one table out of two. The
details would be much easier to address if you could describe the purpose of
each table, and what you hope to gain by combining the information.


OK, I'll tell you what I'm up to, but you have to promise not to laugh or be
disgusted. Remember that I did this mostly for learning curve and practice
before I tackle my real database--not because this had to be done in a
database, or done at all.

In describing a table, do so in one
sentence without using the word "and".


The other reason I haven't been specific is that it's really not that easy
to describe. It's a non-standard thing that only I would even think of doing.
(OK, I love to play with data. There; I admitted it. And I think in lists,
categories, charts, and graphs.)

Part of the problem, I suppose is that I'm trying to serve two purposes with
one database. Why? Because of the overlap in data records. Same basic data to
start with, adding different sets of particulars and doing different kinds of
sorts.
Purpose 1: Keep track of all the Email newsletters (full info or links) I
receive and all the websites I want to check regularly to read. I want to
sort these both by Category and by DoDay (the day(s) of the week on which I
will process these messages and check these websites).
Purpose 2: Have a list of all the Email senders, and online accounts
(telephone autobill, bank, etc.), and their addresses, websites, and
instructions I need to notify them should my Email address change (could
happen).

As it stands now:
Table 1: Email Subscriptions
Contains only Email I receive with details about how I receive it, sorting
fields (Category, DoDay), and notification information
Table 2: Web Reading
Contains only websites I need to manually go check, and same sorting fields.
No receipt info, and no need for notification.
Table 3: Online Accounts - not yet created
Will contain websites where I am registered, a few particulars, and their
notification info. I'm trying to resist the compulsion to put the sorting
fields in this one, but I don't know... (I should just do them as needed, or
the day a notice comes.)
Table 4: Email Correspondents - tentative, probably will not even do
Would contain list of family, friends, business contacts (individuals, not
listserves) to notify, with their Email address. I will probably just use my
Email client address book or contacts for this purpose

I would like to have at least Table 1 and Table 2 records on one list (one
table or query) that could be sorted. If I keep these as separate tables, my
query would have to have two Category columns, and two DoDay columns, one set
for each set of records (because the values are stored in two different
tables), and the two sets of data would be separated in the query. I'd like
to have the two lists (Email Subscriptions and Web Reading sources) combined
into one list, with all the appropriate fields (maybe one query for DoDay,
another for notifications). I would want one Category column and one DoDay
column, which would require that the data come from one table. The remaining
fields could be filled in or blank, according to which kind of source each
record is.

If I figured out how to combine Tables 1 & 2, I could probably add Table 3
the same way, if I really wanted to. Then I could see ALL my "Web Work" and
ALL the lists and registrations I would need to change in one place. How cool
is that? (Well, I think it is.)

See Northwinds' Query:Customers and Suppliers by City. Customers and
Suppliers are not normally related, but "Northwinds" had a special reason to
want to see both groups combined, and sorted by city. (I know-the "reason" is
to show us that it can be done. And now that I know that...)

So, do you understand what I want now, or are you just more confused. You
asked, I tried.


"BruceM" wrote:

For the combo box issue, on the combo box property sheet click the Event tab.
One of the events is On Not in List. You could use that to open a data
entry form or something like that.
I am a bit puzzled about something. When you say you created a combo box in
the table, are you saying that you created one on the form that is bound to
the table? That seems to be the drift of the rest of that section of your
question.
On the other issue, when you speak of combining the tables. I expect you
mean with a query and not by literally making one table out of two. The
details would be much easier to address if you could describe the purpose of
each table, and what you hope to gain by combining the information. Do want
to list by city, or what exactly? In describing a table, do so in one
sentence without using the word "and". Don't get carried away with this;
address and phone number are part of personal information, and can be
together in a table, but customers and suppliers do not belong in the same
table, even if both have addresses and phone numbers. Instead, the tables
are related to each other. A query can bring tables together to organize
information, but that is very different from combining two tables into one.
There are legitimate reasons for doing that, but I don't think your situation
is among them.

"write on" wrote:

OK, here is what I did that finally worked...almost.

I created the table (actually, two tables) in design view first. I created a
combo box in the table, where I wanted the (in this case) CategoryName, from
the previously created Category Table. I used the expression builder to add
the CategoryID and CategoryName fields to the grid. (An aside: In the field
list, the first choice is an "*". I have found instructions for how to use
it, but I can't figure out what it does, or when/why I should or shouldn't
use it. Will someone please educate me?) I set the Bound Column to 1, and the
Column Count to 2. I set the Column Width for the first column (CategoryID)
to 0". I switched to table view, and verified that this worked as desired: I
could use the drop down combo box list to select the CategoryName I wanted
for each record. When the table was complete, I used AutoForm to create a
form based on that table, and it put the combo box in for me. I don't
remember if I had to tweak the combo box properties in form design--maybe. I
did this for two sets of table-and-form.

With those in place, I started using the form to enter data in the first
table. I was happy as a clam, until... I came to a category field where I
wanted to enter something that wasn't in my list (from Category Table). Since
this was a combo box, that shouldn't be a problem, right? Wrong. Access
wouldn't let me enter a value that wasn't on the list. I went to design view
to figure out why. I looked at properties for the combo box. I noticed that
"Limit to List" was marked "Yes." I thought setting that to "No" would solve
the problem. But Access said it couldn't change that setting right now,
because the first visible row was not equal to the bound column. The message
instructed me to adjust the column width appropriately, then change the
setting. But if I put a column width, other than zero, in for the Bound
Column, I was back to getting ID#s, instead of Names, to choose from in my
combo box. So what's the point of having a combo box, if you can't enter a
value that isn't in the list? Does this only work when you have manually
typed in the list, and not when you are picking it from a table?

The other issue I have come across is wanting to merge these two tables
together. I found an example of roughly want I want in Northwinds (sample
database--the Union Query "Customers and Suppliers by City," with a
"Relationship" field to show whether the Name is a customer or a supplier),
but it looks as if it has to be done in SQL, and I don't think I'm up to all
that.

I have these two tables with closely related, but not quite the same,
groups of data. About half of the fields are the same, or close enough. One
field could be accomplished with a set of three checkboxes to choose from:
the first two (choose one) would apply to Table 1 item; the third box would
apply only to Table 2 items. There is one field that is not quite the same
thing, but the format (hyperlink) is the same, and I could use one field for
both groups (may or may not be an email address for Table 1 items, will
always be a website URL for Table 2 items). Then there are 3 or 4 fields that
pertain only to Table 1 items.

It occurred to me that I might try merging the two groups of data into one
table and form that used all the common fields. Then, use a subform for the
fields that apply only to one group, and save that in a separte table, much
like Northwinds' Orders and Order Detail tables, with a Order Detail subform
in the Orders form. So which is easier--a union query in SQL, or deciding
which fields to put in which table and setting up a subform? The latter still
uses two tables, not to mention a complicated subform. Then I still have to
write the query or queries I need. Maybe I'm better off with what I have for
now. Thoughts, anyone?

"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

http://www.members.shaw.ca/AlbertKallal




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
show data from a table based upon combo box Daiuy New Users 3 January 8th, 2005 04:54 PM
Access 2000 -vs- Access 2003? Mark General Discussion 5 November 30th, 2004 07:36 AM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM
Access XP Compared to Access 2003 Mardene Leahu New Users 1 October 1st, 2004 05:11 AM
COMPARE THE TWO TABLES Stefanie General Discussion 0 June 4th, 2004 04:36 PM


All times are GMT +1. The time now is 08:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.