ability to turn off all the BS

A

aaron.kempf

hey

i want to be able to toggle on things like combo boxes

a lot of times--- i mean-- a lot of times.. i go to open a table and im
frustrated since it has a combo box on it; and it's difficult for me to
see what is in that field

you know what i mean?

i want a global 'turn off all the extra frilly stuff and open my table
in pure datasheet mode'

that's why i love ADP since i can get to this via QA or EM and it
doesn't have all the extra frilly stuff.

I just want to be able to do this in MDB..

So i open a table; i can't find what projectid something is assigned
to; since that is a combo box

i just one of these options

a) 'strict datasheet view' instead of just design and datasheet, have
strict datasheet
b) 'toggle -- on the application level all the extra frilly stuff'
c) most importantly-- be able to REMOVE all this extra frilly stuff.
like i was talking to the access team about making a button on the ui
that would go through and delete all of the extensded properties for an
ADP so that i could reset it to scratch

so that when something is goofy; or when i want to cut the crap and
view the data-- i can just flush all the column widths type information
out.

like.. yeah-- column widths.. i have one table with 37 different
columns widths and i want to reset their widths instead of doign it by
hand.

i want an option to be able to reset all of this frilly stuff

because right now.. i have this table; and i can't physically filter
100 different tables for projectid = 2835

so i have to write 100 queries to do this.

im trying to check out 'which records are related to 2835' in my
database
 
A

Allen Browne

Aaron, you are probably letting off steam here, rather than really looking
for a solution. And you probably know that many of us refuse to use combos
at the table level, partly for the reasons you cite:
http://www.mvps.org/access/lookupfields.htm

If you do want to clean up your database, it's not too difficult to write a
bit of quick'n'dirty code to permanently remove this "frilly stuff". You
would:
- loop through the TableDefs in the Database;
- loop through the Fields in each TableDef;
- if the DisplayControl property of the Field if it is acComboBox, change it
to acTextBox.

Untested example below.

------------------code starts------------------
Public Function DumpLookups(Optional bNoConfirm As Boolean)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strMsg As String
Dim lngResponse As Long
Dim strField As String

Set db = CurrentDb
For Each tdf In db.TableDefs
If Not ((tdf.Attributes And (dbSystemObject Or dbHiddenObject)) Or
(tdf.Name Like "~*")) Then
For Each fld In tdf.Fields
If HasProperty(fld, "DisplayControl") Then
strField = tdf.Name & "." & fld.Name

Select Case CLng(fld.Properties("DisplayControl"))
Case acComboBox
If bNoConfirm Then
lngResponse = vbYes
Else
strMsg = "Reset " & strField & " to text box?"
lngResponse = MsgBox(strMsg, vbYesNoCancel,
"Confirm dumping this combo")
End If

Select Case lngResponse
Case vbYes
fld.Properties("DisplayControl") =
CInt(acTextBox)
Debug.Print strField & " changed to text box."
Case vbNo
Debug.Print " No change to " & strField
Case vbCancel
Exit For
Case Else
Debug.Print "Qutting: response " & lngResponse &
"not handled."
lngResponse = vbCancel
Exit For
End Select

Case acCheckBox, acTextBox
'do nothing
Case Else
Debug.Print "Ignored " & strField & " - " &
FieldTypeName(fld.Properties("DisplayControl"))
End Select
End If
Next
'Jump right out if the user canceled.
If lngResponse = vbCancel Then
Exit For
End If
End If
Next

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

Function FieldTypeName(n As Long) As String
'Purpose: Converts the numeric results of DAO fieldtype to text.
'Note: fld.Type is Integer, but the constants are Long.
Dim strReturn As String 'Name to return

Select Case n
Case dbBoolean: strReturn = "Yes/No" '1
Case dbByte: strReturn = "Byte" '2
Case dbInteger: strReturn = "Integer" '3
Case dbLong: strReturn = "Long Integer" '4
Case dbCurrency: strReturn = "Currency" '5
Case dbSingle: strReturn = "Single" '6
Case dbDouble: strReturn = "Double" '7
Case dbDate: strReturn = "Date/Time" '8
Case dbBinary: strReturn = "Binary" '9
Case dbText: strReturn = "Text" '10
Case dbLongBinary: strReturn = "OLE Object" '11
Case dbMemo: strReturn = "Memo" '12
Case dbGUID: strReturn = "GUID" '15
Case dbBigInt: strReturn = "Big Integer" '16
Case dbVarBinary: strReturn = "VarBinary" '17
Case dbChar: strReturn = "Char" '18
Case dbNumeric: strReturn = "Numeric" '19
Case dbDecimal: strReturn = "Decimal" '20
Case dbFloat: strReturn = dbFloat '21
Case dbTime: strReturn = "Time" '22
Case dbTimeStamp: strReturn = "Time Stamp" '23
Case Else: strReturn = "Field type " & n & "unknown"
End Select

FieldTypeName = strReturn
End Function
-------------------code ends-------------------
 
K

KARL DEWEY

Build a form and set it for datasheet view -- you do know how to build a form
don't you?
 
A

aaron.kempf

yes.. i know how to build a form lol

i just think that you should be able to

a) toggle this stuff on or off

b) remove all customizations like that
-- especially in an adp

is it just records in mysqsobjects or what is it on the mdb side?

i wish i knew more about where it was stored on the adp side; i screwed
up a dts copy database wizard by including 'extended properties' when i
shouldnt have and i had all sorts of treachery

i just think that it would be swell to have that built into the next
version.. or even get some feedback on an easy way to do those thigns

i woudln't really delete the records; i'd push the values into a table
and then push them back right??

i just do a lot of screwed up stuff.. like copying objects and that
sort of thing

and i had this view today that adp kept on choking on.. it just keeps
on saying that it can't find the object

and i just want to know where to delete that kinda stuff on the mdb
side and on the adp side

so i can get by when these column widths and all that stuff gets hosed

because as you all know; stuff gets hosed from time to time
 
A

Albert D.Kallal

hey

i want to be able to toggle on things like combo boxes

a lot of times--- i mean-- a lot of times.. i go to open a table and im
frustrated since it has a combo box on it; and it's difficult for me to
see what is in that field

you know what i mean?
Click to expand...

Yes, if you read the 10 commandments of access here:

http://www.mvps.org/access/tencommandments.htm

Pay attention to #2, and most certainly says that people should avoid the
lookup feature that allows people turn a field in a table to a combo box.

So, in place of turning off this feature, would it not be better to exercise
some discipline, and not use the feature in the first place?

Likely, this table may have been designed by someone else. The problem with
"turning off" this feature, is that you might have a application with 150
forms, 00+ reports, and a 100+ queries, and THEY ALL ARE BASED ON THE FACT
that a user setup this lookup field as a combo box. So, turning off the
feature means that virtually ANY report, or form that used this lookup field
will now break.

So, while that feature is considered a set of "training wheels", many new
users to ms-access find that feature makes life very easy for them (they
don't have to write a sql join to see the "other" values from the table.

While I never use these lookup fields in table design, many people do. It is
much like a racing car driver getting mad at the fact hat so many people can
drive cars now that we got automatic transmissions in cars.

So, lookup fields in table design mode should be avoided like the plague,
but the problem is you can't just have a option to "turn off" this display
feature, since all of the code, forms, reports, queries will, and can break
as a result of doing so.
So i open a table; i can't find what projectid something is assigned
to; since that is a combo box
Click to expand...

Well, you will have to talk to the person who designed the database, and
asked them why they used that feature (answer = it was easy, and now to
display the lookup values I don't have to write a query,a nd when I design a
form, I just place the field on the form,a nd I get a nice cool combo box).

So, the feature is there for ease of use, and those that use it find it
great. However, since the design of the application is based on that lookup
field, toggling it off would in fact break the application.

About the only easy work around is to build a query (a view for you sql
folks), and drop in the other related lookup table, and grab the "pk" id
from the child table, and put that in the column view...
i just one of these options

a) 'strict datasheet view' instead of just design and datasheet, have
strict datasheet
Click to expand...

The above begs the issue what the heck are all those users, including you
doing in datasheet view? In fact, the WHOLE problem is that we got crazy
people and armatures entering data directly into table (and, also, you
seem to want to do this also...and that is exactly the same trap that those
stupid users that went with lookup fields in the first place).

And, I suspect if we had a toggle feature to turn off the lookup fields,
then users would wonder what happened to the lookup values everywhere, and
all of a sudden wonder why the combo boxes are gone? They would then try to
type in the lookup values, and wonder why the table view is broken? Of
course then, we need another "special" table view that ignores all of
this...and this just goes on and on forever. There is no solution to
stupidity here, and the solution is to have good designs, and avoid the
lookup feature.
so that when something is goofy; or when i want to cut the crap and
view the data-- i can just flush all the column widths type information
out.
Click to expand...

The problem is that when the application is designed to se that lookup
field,
then what do you do? If you turn off the lookup feature, then how will users
enter data into those tables? (or, why are they using the tables directly in
the first place?).

like.. yeah-- column widths.. i have one table with 37 different
columns widths and i want to reset their widths instead of doign it by
hand.
Click to expand...

Hum, the above makes a lot of sense. I not needed the above, but it should
be a simple matter to delete the appropriate reocrd(s) in the system
table(s) that stores this. You might want to make/ask a separate post on the
above (I don't have a handy answer, but it should be possible re-set this
without much effort).
because right now.. i have this table; and i can't physically filter
100 different tables for projectid = 2835

so i have to write 100 queries to do this.
Click to expand...

Hum, is the problem here that someone was again dumb, and got 100 tables
where they should only have had one table? (you see this all the time, and
new table is created for each month of data, where in fact just adding ONE
column for the month would eliminate all those tables!! So, likely this is a
probably of badly normalized data).

So, to search a 100 tables, why not write a small VBA script? The VBA we
have is
a ZILLION times better that HORRIBLE t-sql;.

Just write a small VBA script.......
 
A

aaron.kempf

just because someone 12 years ago reccomend to not do smoething-- that
doesn't mean that 'Access is good enough as it is'

give us more options

hahahahaha
now wait a second.. you really think that VBA is more powerful than
VBA?

I mean-- get real kid you're friggin whack.

it's EASIER for somethings. but TSQL is always faster than VBA any way
you look at it.

but the thing that you dont grasp is that im able to use TSQL and VBA.

you kids choose vba over TSQL... by relying on a crappy MDB and linked
tables-- gag me with a spoon

im an ADP developer

so im allowed to mix vba and tsql if and when i please.

and i can take my vba code and copy it into a job in SQL Server and it
works on a scheduled basis. can you do that with MDB?

and it actually works for complex things lol

and just for the record; no im not talking about 100 tables. I'm not
scared of 100 tables. I partition shit if and where i please.

I just want the ability to reset columns and remove lookups and all
that stuff

is what you're telling me-- oh precious MDB Queen-- is that you dont
know where i can delete this information out of a MDB?

if you just answer me that; then i'll be on my way. I can find it on
the ADP side myself.


re:
Likely, this table may have been designed by someone else. The problem
with
"turning off" this feature, is that you might have a application with
150
forms, 00+ reports, and a 100+ queries, and THEY ALL ARE BASED ON THE
FACT
that a user setup this lookup field as a combo box. So, turning off
the
feature means that virtually ANY report, or form that used this lookup
field
will now break.

you're friggin whacky man

a) you setup a lookup on a table
b) you make a form based off of that table
c) you remove the lookup and you think that the form magically changes
everything from a combobox back to a textbox lol? you think that the
access chipmunks run through all your forms and reports and say 'oh my
god we got this job order that says homedude changed the lookup field
we have to change every form field that is on every form and report
from a cbo to a textbox

lol

i mean no-- what you dont understand

i put it on the tables during certain phases of development

i build all my forms and reports-- i dont let people edit tables
directly, but there are certain times when i want 'all of the lookups
on' and some times when i want 'all of the lookups off'

i just think that this type of functionality should be available in
Access. Access isn't for kids-- we've all been doing Access fulltime
for years.

give us more options; more customizability-- instead of treating us
like AOL-kiddies
 
Top