how to NOT show a listbox in a query / report that is defined at the table level?

[xz]

I have a listbox defined in the lookup tab of a table.
I like it that way because I don't have to mess around with forms -
I'm the only one using this database, and I find it easier just to
enter data into the table.

But when I go to do a report, that listbox carries over. I tried
creating a query, hoping the listbox would disappear - nope its
there. I want to tell Access to just query the data - snapshot (which
I selected) - not show me a listbox for data entry. And the same goes
for the report. I only want that listbox to appear when I'm in the
table.

Any ideas? I'm using Access 2007.

Thanks
JJ

 

[Tom Wickerath]

Ah, the evils of lookup fields! You should read The Ten Commandments of
Access, and, in particular, pay attention to the second commandment:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

If you absolutely cannot live without a lookup field, try creating a query
that pulls the data directly from the looked up table, without including the
lookup field. Personally, I would perform an exocism of your lookup field(s).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[xz]

thanks Tom - i liked the 10, although I read only the first few so
far
Jeeze you would think that Access 2007 is smart enough to say, "OK,
the query is a snapshot only, so I'm not going to display that listbox
defined at the table level".

I mean comeon - Its smart enough to give me an error if I select
something from that listbox in the query, but why even have that
listbox appear - it should be the value only in the field Drives
me nuts - stupid things like this. In my case, my listbox is pulling
from a separate table - a lookup table. So I'm relational right now,
ref integrity established etc. Its just the listbox is a cute way of
allowing me to edit the data directly in the table... I don't have to
create a form for data input. But this is awful. I can't even tell
the report to show the contents for that field in a regular text field
- it makes it appear white on black (black for the listbox) --- it
actually carries over to the report (!)

 

[Tom Wickerath]

Jeeze you would think that Access 2007 is smart enough to say, "OK,

the query is a snapshot only, so I'm not going to display that listbox
defined at the table level".

That's potentially worse, because unless the actual value is being stored in
the field (ie. natural keys), all the user might see is an surrogate key that
corresponds with the autonumber PK.

But this is awful. I can't even tell
the report to show the contents for that field in a regular text field
- it makes it appear white on black (black for the listbox) --- it
actually carries over to the report (!)

I can't say that I have experienced this, but, on the other hand, I avoid
the use of Lookup fields defined at the table level.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[dbahooker]

use Query Analyzer if you're serious about not seeing the lookups


Lookup fields are GREAT!
It's the MDB file format that is a piece of crap

 

[dbahooker]

surrogate keys don't need to be confusing to end users

that's an old myth and you're a dork

there's nothing 'too confusing about surrogate keys' that they must be
hidden from end users


this is just one symptom that your development methodology is out of
date.
End Users UNDERSTAND surrogate keys


it's better that we make them know the numbers than little text values
like 'CPS' and 'DDK' and other little crap TLA like that

 

[dbahooker]

right click on the control (that is a combo box) and convert to a text
field

 

[Aaron Kempf]

I disagree

you can always turn off lookup fields.
what is not reccomended is the lookup field DATATYPE isn't it?

you can always right-click CONVERT TO TEXT FIELD in the design view of a
form or report

 

[Tom Wickerath]

Aaron,

what is not reccomended is the lookup field DATATYPE isn't it?

There is no data type named Lookup Field. There *is* a selection in the data
type list that reads Lookup Field. When one selects this, they activate the
Lookup Wizard. This wizard does it's thing, and the end result is what is
commonly known as a table lookup. But, technically speaking, there is no
"lookup field DATATYPE".

you can always right-click CONVERT TO TEXT FIELD in the design view of a
form or report

Yes. That is the exorcism that I recommend performing.

So, I'm not sure that I understand exactly what it is that you disagree with
here? Certainly you're not a fan of lookups defined at the table or query
level, are you?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

I misread one statement you made:

you can always right-click CONVERT TO TEXT FIELD in the design view of a
form or report

Since you mentioned "form or report" instead of "table or query", here is my
revised answer:

There's nothing wrong with a lookup defined in a form. That's what we
typically call a bound combo box. It's when that bound combo box is present
at the table or query level, and it is based on a lookup to another table
(ie. not a value list) that it is considered the creation of thy evil one.

As for combo boxes in reports, the three of us (me, myself and I [a old
quote I remember from an episode of Gilligan's Island many years ago] )
prefer to use a text box instead of a combo box in these situations. That
usually means adding the lookup table to the underlying recordset for the
report, assuming surrogate keys were used in place of natural keys.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[dbahooker]

I just disagree; it sounds like you guys are saying 'dont use the
lookup tab'
when in fact; I think that it's the most helpful thing in the world

if I ever want to look at a tabel WITHOUT using the lookup?
I just look in Query Analyzer

you know what platform I use though

 

[dbahooker]

yeah; I just think that surrogates should be used everywhere.. but
i've never been of the belief that surrogates are soemthing that we
_NEED_ to hide from end users

I've supported a lot of Access users in my day; and in general--
they're pretty good at what they do-- knownig the data -- that is.

I just dont' think that certain long-standing traditions like 'you
must keep your surrogates secret' are that big of a deal

sometimes, people need to write a query and if sharon; in accounting--
understands that the surrogate number '14' corresponds to 'Human
Resources Department' then I think that she is extremely powerful

I'm all about teaching end users to write their own queries.
I never ever ever ever would have gotten into databases a decade ago-
if I wasn't on this team where 30 of us were taught how to use the QBE
window in 30 minute session.

I think that 'most employees should know how to write queries' and
more logic should be done on the database side; instead of having
everyone use Excel for everything.

I just don't think that Access MDB is a great choice for most
beginners.. and I think that ADP is a better choice-- and I wish that
MS and the rest of the people around here-- would start treating it
seriously.

I've heard nothing but FUD in this newsgroup; it's like half the
people around here are on the Oracle payrolll or something-- they just
can't fathom using SQL Server.

I just wish that WinFS was here and Now.

Because ADP on everyone's desktop would have been a lot of fun.
Replication from a server to a desktop database SERVER would have been
a lot of fun.

But now we're all stuck; heading back to the stone ages


At this point-- since ADP is dead--- I wish that IBM won the OS/2 war.

-Aaron

I misread one statement you made:

you can always right-click CONVERT TO TEXT FIELD in the design view of a
form or report

Since you mentioned "form or report" instead of "table or query", here is my
revised answer:

There's nothing wrong with a lookup defined in a form. That's what we
typically call a bound combo box. It's when that bound combo box is present
at the table or query level, and it is based on a lookup to another table
(ie. not a value list) that it is considered the creation of thy evil one.

As for combo boxes in reports, the three of us (me, myself and I [a old
quote I remember from an episode of Gilligan's Island many years ago] )
prefer to use a text box instead of a combo box in these situations. That
usually means adding the lookup table to the underlying recordset for the
report, assuming surrogate keys were used in place of natural keys.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Aaron,

There is no data type named Lookup Field. There *is* a selection in the data
type list that reads Lookup Field. When one selects this, they activate the
Lookup Wizard. This wizard does it's thing, and the end result is what is
commonly known as a table lookup. But, technically speaking, there is no
"lookup field DATATYPE".

Yes. That is the exorcism that I recommend performing.

So, I'm not sure that I understand exactly what it is that you disagree with
here? Certainly you're not a fan of lookups defined at the table or query
level, are you?

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Aaron Kempf" wrote:

- Show quoted text -