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 said:
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 said:
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 said:
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
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
.