By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Last question for awhile...

P: n/a
Okay this will hopefully be my last question for awhile since I've had
a bunch over the last few weeks. Thank you all for being patient with
me:)

Last major obstacle is trying to set up combo boxes in a form that look
up values in another table.

The idea is that while the user will select a text answer, it will be
entered into the table itself as a number.
Example would be:
I have xtbl.MarrCoding with 2 fields: MarrCode and MarrText
0 Single
1 Married
2 Divorced
etc.

A form exists where there is a field Marital Status. I want them to be
able to select from Single, married or divorced in a drop down box and
have these text versions show up when looking at the form. However I
want the table itself to contain the coded values (i.e. 0, 1 or 2).

I've seen it done before and even have an example, but cannot for the
life of me figure out how its working. I assume it has something to do
with Row Source type and Row Source properties but I cannot seem to get
it working. I've replicated exactly what the working version has for
these properties to no avail.

If anyone has some instructions on how to do this that would be
fantastic. Thank you!

Jason

Jun 7 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
If you want to try to show yourself how to do it, make sure you have wizards
enabled, select wizard from the toolbox, then select a combobox, and drop it
on a form, any form, even a new blank form.

Tell the wizard that you want to type in the values yourself. You'll need 2
columns.

Want to try that first?
Jun 7 '06 #2

P: n/a
Well its not an elegant solution but I tried what you suggested and I
think I found one.

If I make a 2 column combo box and set it up like normal, I can then
change the column width of the first column to 0, so it becomes
invisible to the end users and they only see the text, not the coded
value.

Was that what you were getting at or is there a better way?

Thanks!
Jason
Rick Wannall wrote:
If you want to try to show yourself how to do it, make sure you have wizards
enabled, select wizard from the toolbox, then select a combobox, and drop it
on a form, any form, even a new blank form.

Tell the wizard that you want to type in the values yourself. You'll need 2
columns.

Want to try that first?


Jun 7 '06 #3

P: n/a
You got it. The only really viable alternative is to have a little table
that stores those values and then use a different row source type of
Table/Query. Same thing, more or less.

Curious to know why in your estimate it is "not an elegant solution".

You want to show one value, store another, and you don't want to show the
one you're storing. By definition you're describing two values presented in
a list, one column invisible. How much closer could you get to that
description than a combobox or listbox with one column width set to 0?
Jun 7 '06 #4

P: n/a
Not elegant doesn't necessarily mean bad:)

It just seems like a sort of loophole way of solving the problem rather
than some sort of option for a field that says "In form view, use
values from column a, in table view, use the equivalent values in
column b". So the two values would not actually be present in the list
at any given time.

Just semantics I know, but I'm more than a little OCD about anything
that touches data so I always strive for the cleanest way to do
something, even if there's an equally effective shortcut.

Rick Wannall wrote:
You got it. The only really viable alternative is to have a little table
that stores those values and then use a different row source type of
Table/Query. Same thing, more or less.

Curious to know why in your estimate it is "not an elegant solution".

You want to show one value, store another, and you don't want to show the
one you're storing. By definition you're describing two values presented in
a list, one column invisible. How much closer could you get to that
description than a combobox or listbox with one column width set to 0?


Jun 8 '06 #5

P: n/a
To be honest, it sounds to me like you're straining to see an issue where
there isn't one.

Somewhere, sometime, somehow, something has to pair up the two values, one
for storing, one for display. Whether you do it in simple text as a value
list, or let a table do it with a pair of fields, if you can't pair them up
somewhere (visible or otherwise) then you can't cause the right one to
display based on the other one.

If you truly believe there's something cleaner, describe it.
Jun 8 '06 #6

P: n/a
As a matter of fact, recent versions of Access have included the ability to
define a "lookup field" in a table. It violates relational design
principles, and sooner or later, is very likely to cause you grief, so I
consider it A Bad Thing and recommend against its use (as I also recommend
against "subdatasheets", and the already announced "multivalue field"
feature of the next version of Access).

On first glance, it might appear to be "more elegant," but you are very
likely to rue the day you decided to use it, instead of what may seem "less
elegant," if you go on to do more Access work.

If, on the other hand, you are a novice or casual user who only deals with
data in datasheet view, and never expect to progress beyond that level, it
could be A Good Thing for you.

Larry Linson
Microsoft Access MVP

<Ja************@gmail.com> wrote in message
news:11**********************@f6g2000cwb.googlegro ups.com...
Not elegant doesn't necessarily mean bad:)

It just seems like a sort of loophole way of solving the problem rather
than some sort of option for a field that says "In form view, use
values from column a, in table view, use the equivalent values in
column b". So the two values would not actually be present in the list
at any given time.

Just semantics I know, but I'm more than a little OCD about anything
that touches data so I always strive for the cleanest way to do
something, even if there's an equally effective shortcut.

Rick Wannall wrote:
You got it. The only really viable alternative is to have a little table
that stores those values and then use a different row source type of
Table/Query. Same thing, more or less.

Curious to know why in your estimate it is "not an elegant solution".

You want to show one value, store another, and you don't want to show the
one you're storing. By definition you're describing two values presented
in
a list, one column invisible. How much closer could you get to that
description than a combobox or listbox with one column width set to 0?

Jun 10 '06 #7

P: n/a

On 10-Jun-2006, "Larry Linson" <bo*****@localhost.not> wrote:
As a matter of fact, recent versions of Access have included the ability
to
define a "lookup field" in a table. It violates relational design
principles,
Not necessarily.
and sooner or later, is very likely to cause you grief,


However, this is true, even if you don't violate relational principles.
So , I say Yes and No to your assertion. Defining a lookup field in a table
(if you're talking about that Lookup spec in table design) really only
violates realtional design if you define a bad lookup. If you point to a
table that contains a correctly designed list of keys and values, then all
you're really doing in the Lookup design is using a shortcut for getting a
combobox or listbox on a form made from the table that uses that lookup.
The "looked up" data is (or can be)key+value pairs in a properly designed
table in a normalized database.

I'll even go so far as to assert that even using the Value List option you
can still do things properly, if less correctly. If the list you type in as
the rowsource is still a properly designed list of key+value pairs, you're
still in pretty good shape, I think. Not my way to do it, since I think
shortcuts like this wind up costing you more in the long run than you save
by taking them. That said, it is possible to recover (as long as the list
was designed properly) by coming back later, moving the list of values to
the table (where you really should have put them in the beginning) and then
feeding the reference to the new ("lookup") table through your system. That
can be costly in time. Better to have put them in a table to begin with.

On the subject of the new attachments field (which is what I'm thinking you
refer to as multivalue field) I'm somewhat relieved to find that under the
hood, although they hide this from the user and even from the developer,
they do create a table silently and place the attachments in that table.
Whew!

If there's another multivalue field, then this remark is off the mark. I
just adn't seen another reference like that yet.

As to the OP, I still haven't figured out exactly what the OP was asserting
was more elegant than the available solutions or in what way. I even asked
him to describe whatever it was and, unless I missed the posting, I haven't
seen an answser to that.
Jun 11 '06 #8

P: n/a
We'll agree to disagree, then.

I don't like _tables_ that display something other than what is actually
there. I don't doubt that "under the covers" it may be implemented
relationally. Maybe I should just say, it violates "rational" file design
principles... that is, showing something that isn't there, in a table. It's
fine to create a Query that uses a Lookup, but if you go back to Query
Design or to SQL, it's obvious you are doing a Lookup. It's not obvious in
Table Design unless you specifically click the Lookup tab.

No, I am not talking about attachments. I am talking about multi-value
fields -- it's in the blogs, somewhere. Under the covers, they say, it is
implemented relationally, but the user/developer does not have access to the
many side of the relational implementation. This clearly violates the
relational principle of "atomicity."

Larry Linson
Microsoft Access MVP
<w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t> wrote in message
news:Lt*********************@newssvr29.news.prodig y.net...

On 10-Jun-2006, "Larry Linson" <bo*****@localhost.not> wrote:
As a matter of fact, recent versions of Access have included the ability
to
define a "lookup field" in a table. It violates relational design
principles,


Not necessarily.
and sooner or later, is very likely to cause you grief,


However, this is true, even if you don't violate relational principles.
So , I say Yes and No to your assertion. Defining a lookup field in a
table
(if you're talking about that Lookup spec in table design) really only
violates realtional design if you define a bad lookup. If you point to a
table that contains a correctly designed list of keys and values, then all
you're really doing in the Lookup design is using a shortcut for getting a
combobox or listbox on a form made from the table that uses that lookup.
The "looked up" data is (or can be)key+value pairs in a properly designed
table in a normalized database.

I'll even go so far as to assert that even using the Value List option you
can still do things properly, if less correctly. If the list you type in
as
the rowsource is still a properly designed list of key+value pairs, you're
still in pretty good shape, I think. Not my way to do it, since I think
shortcuts like this wind up costing you more in the long run than you save
by taking them. That said, it is possible to recover (as long as the list
was designed properly) by coming back later, moving the list of values to
the table (where you really should have put them in the beginning) and
then
feeding the reference to the new ("lookup") table through your system.
That
can be costly in time. Better to have put them in a table to begin with.

On the subject of the new attachments field (which is what I'm thinking
you
refer to as multivalue field) I'm somewhat relieved to find that under the
hood, although they hide this from the user and even from the developer,
they do create a table silently and place the attachments in that table.
Whew!

If there's another multivalue field, then this remark is off the mark. I
just adn't seen another reference like that yet.

As to the OP, I still haven't figured out exactly what the OP was
asserting
was more elegant than the available solutions or in what way. I even
asked
him to describe whatever it was and, unless I missed the posting, I
haven't
seen an answser to that.

Jun 12 '06 #9

P: n/a

On 11-Jun-2006, "Larry Linson" <bo*****@localhost.not> wrote:
We'll agree to disagree, then.

I don't like _tables_ that display something other than what is actually
there. I don't doubt that "under the covers" it may be implemented
relationally. Maybe I should just say, it violates "rational" file design
principles... that is, showing something that isn't there, in a table.
Actually, I like the restatement quite a lot. I agree.
No, I am not talking about attachments. I am talking about multi-value
fields -- it's in the blogs, somewhere. Under the covers, they say, it is
implemented relationally, but the user/developer does not have access to
the
many side of the relational implementation. This clearly violates the
relational principle of "atomicity."


Ouch!
Jun 12 '06 #10

P: n/a
<w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t> wrote
No, I am not talking about attachments. I am
talking about multi-value fields -- it's in the
blogs, somewhere. Under the covers, they say,
it is implemented relationally, but the user /
developer does not have access to the
many side of the relational implementation.
This clearly violates the relational principle
of "atomicity."


Ouch!


But it allows Access to integrate very nicely with SharePoint, which seems
to be a major objective of this release. SharePoint data, as far as I know,
has never been represented as being really relational.

Larry Linson
Microsoft Access MVP
Jun 12 '06 #11

P: n/a

On 12-Jun-2006, "Larry Linson" <bo*****@localhost.not> wrote:
But it allows Access to integrate very nicely with SharePoint, which seems

to be a major objective of this release. SharePoint data, as far as I
know,
has never been represented as being really relational.


Please tell me that there is an overarching plan unfolding in the background
that will make all of this come out right somehow.

I won't hold my breath waiting for that confirmation.
Jun 12 '06 #12

P: n/a
"Rick Wannall" wrote
Please tell me that there is an overarching
plan unfolding in the background that will
make all of this come out right somehow.
Strangely enough, at the last MVP Summit Meeting, there was no "Overarching
Plan" session, not even under stricter, separate Non-Disclosure Agreement
guaranteeing silence on pain of death. I guess the "Overarching Plan"
department didn't have anything they wanted to share <SIGH>, so we'll just
each have to draw our own conclusions from what we see and hear about the
next releases of Windows and Office and other Microsoft software.

I'm reluctant even to indulge in speculation as to what _might_ be
motivations, reasons, etc. Even if I made something up and posted it, if it
turned out to be close to the truth, it could _look_ as if I'd had something
revealed to me and then passed it on, in violation of my Non-Disclosure
Agreement.
I won't hold my breath waiting for that confirmation.


In the words of Maynard G. Krebs, "Good thinkin', Dobe!"

Larry Linson
Microsoft Access MVP
Jun 13 '06 #13

P: n/a
Larry Linson wrote:
We'll agree to disagree, then.

I don't like _tables_ that display something other than what is actually
there. I don't doubt that "under the covers" it may be implemented
relationally. Maybe I should just say, it violates "rational" file design
principles... that is, showing something that isn't there, in a table. It's
fine to create a Query that uses a Lookup, but if you go back to Query
Design or to SQL, it's obvious you are doing a Lookup. It's not obvious in
Table Design unless you specifically click the Lookup tab.

No, I am not talking about attachments. I am talking about multi-value
fields -- it's in the blogs, somewhere. Under the covers, they say, it is
implemented relationally, but the user/developer does not have access to the
many side of the relational implementation. This clearly violates the
relational principle of "atomicity."

Larry Linson
Microsoft Access MVP


I'll agree to agree with Larry.

I saw some information on Erik Rucker's blog dated 3/14/2006

Title: Creating Schema

Section: Lookup Fields and Complex Data

http://blogs.msdn.com/access/archive...14/551556.aspx

Expanded Lookup Fields, reliance on Macros -- is MS purposely trying to
irritate developers by turning best practices on end? What better way
to surprise us? I imagine that not a single MVP or developer was in
favor of expanded lookup fields. Does this mean Sharepoint is destined
to become the next database for those who know nothing about databases?

If you think a lot of Access databases are poorly designed now, just
wait until people try to save on development by doing it themselves
using Sharepoint to handle the networking aspects. Imagine the mess
developers will face trying to fix one of those low cost beauties.
Imagine the Schema they'll come up with using "Schema by Example." If
databases are art, we're about to see some truly modern art.

James A. Fortune
CD********@FortuneJames.com

Jun 14 '06 #14

P: n/a
ROFLMAO!!!!

Two kinds of tears.
Jun 14 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.