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
14  1326 
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?
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?
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?
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?
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.
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?
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.
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.
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!
<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
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.
"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
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
ROFLMAO!!!!
Two kinds of tears.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Vani Murarka |
last post by:
Hi Everyone,
Does .NET offer any collection class which will give me objects last
*accessed* such that I may build a least-recently-used cache that
kills off objects that haven't been used for...
|
by: AndyBell |
last post by:
Hi all!
I have an Access 2000 database for the Habiat for Humanity where I work.
This is the second database I have written and it gets a bit more complex
each time... I have learned much and...
|
by: James Curran |
last post by:
I'd like to make the following proposal for a new feature for the C#
language. I have no connection with the C# team at Microsoft. I'm posting
it here to gather input to refine it, in an "open...
|
by: mark |
last post by:
Can't figure this one out. I appears to not be code related. After
surfing my asp.net web app for a while 5 - 10 min. it fails to open
any further connections to my access 2000 database. There...
|
by: rf |
last post by:
Hey I need to be able to get the dates from last wednesday to the past
tuesday on thursday or friday every week. How would I do that?
For example:
m t W TH F
M T w th f
I would need the...
|
by: michel.ank |
last post by:
Hi,
I'm using the class PrintLines and my last record of page aren't with
the borders.
Somebody can help me?
Thanks,
|
by: magix |
last post by:
Hi,
when I read entries in file i.e text file, how can I determine the first
line and the last line ?
I know the first line of entry can be filtered using counter, but how about
the last line...
|
by: Kevin Burton |
last post by:
I don't think I understand the last() function.
I have a document that looks like:
<Root>
<Header>Some text</Header>
<Message>
<MessageID>1</MessageID>
. . . .
</Message>
|
by: Greg |
last post by:
Most suggestions on this topic recommend to use a page footer and make
it visible only on the last page. My problem is that the footer is half
of the height of a page which means the detail would...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
