using ALTER Table to Yes/No display and format

[LisaB]

I need to alter a table that has over 100 Yes/No Data type fields.

Currently all the Yes/No Data type fields do not have the Format property
set and the Display Control property is "Text Box"

1. I would like the code to loop through each field in the table and
determine if it is a Yes/No Data type

2. if it is, I want to change the Format property to Yes/No and the Display
Control to Check Box

Can anyone help me out?

 

[Douglas J. Steele]

I can't imagine a properly normalized database that would have over 100
fields of any type in a single table, much less 100 boolean fields! I
suspect your field names are conveying information such as
"WasStep1Completed?" or the like, and would more properly be represented as
separate rows, rather than multiple fields.

If you're stuck with that design, though, something like the following
untested air code should work:

Sub ChangeCheckboxes()
On Error GoTo Err_ChangeCheckboxes

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("CheckBoxTable")
For Each fld In tdf.Fields
If fld.Type = dbBoolean Then
' 106 is CheckBox, 109 is TextBox, 111 is ComboBox
Set prp = fld.CreateProperty("DisplayControl", dbLong, 106)
fld.Properties.Append prp
End If
Next fld

End_ChangeCheckboxes:
Set dbs = Nothing
Exit Sub

Err_ChangeCheckboxes:
' Error 3367 means that the property already exists.
If Err.Number = 3367 Then
fld.Properties("DisplayControl") = 106
Resume Next
Else
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_ChangeCheckboxes
End If

End Sub


Note that the code above uses DAO. If you're using Access 2000 or 2002, make
sure you've got a reference set to the Microsoft DAO 3.6 Object Library.

I don't believe there's any point in change the Format property to Yes/No,
since the Display Control property will override that.

 

[CSmith]

I can't imagine a properly normalized

database that would have over 100
fields of any type in a single table,
much less 100 boolean fields!

Then you obviously haven't worked with surveys, questionnaires, etc, which
can easily be flat tables. So, it's a bit premature to imply that a database
is not "properly normalized", without knowing anything about it.

 

[LisaB]

This is a database that will hold the results of questionnaires and it is
properly normalized. The database is already created in SQL however, do to
the number of fields, we need to split it up into access tables (to much to
explain here).....anyway the Yes/No Data Type fields in the Access table are
not formatted and have the Display Control set to Text. We need to change
the display control to CheckBox. As you can imagine, it is too many fields
and tables to go through by hand field by field to change this.

I used the code you supplied however, I get a runtime error 3367 at the line
=> fld.Properties.Append prp

"Can't append. An object with the name already exists in the
collection."

also, Should the line => Set prp = fld.CreateProperty("DisplayControl",
dbLong, 106)
be changed to => Set prp = fld.CreateProperty("DisplayControl",
dbBoolean,106)

Thank You very much for your assistance.

 

[Douglas J. Steele]

Did you copy all of my code? It included specific handling for error 3367.

And no, the line Set prp = fld.CreateProperty("DisplayControl", dbLong, 106)
should not be changed to Set prp = fld.CreateProperty("DisplayControl",
dbBoolean, 106). The dbLong refers to the type of the property
DIsplayControl, not to the type of the field for which it's a property.

At the risk of antagonizing you, a properly normalized database to hold
questionnaires needn't contain 100 or more fields. That implies that you've
got a field for each question and that your field names are probably the
question numbers. That means that in essence you're hiding information in
the field name.

What do you do if you need to have another question on the survey? Add
another field? Having to make database changes can be difficult.

What if you want to know how many Trues were answered by a particular
respondent?

Go to http://www.rogersaccesslibrary.com/OtherLibraries.asp and take a look
at Duane Hookom's "At Your Survey"

 

[Douglas J. Steele]

Then you obviously haven't worked with surveys, questionnaires, etc, which
can easily be flat tables. So, it's a bit premature to imply that a database
is not "properly normalized", without knowing anything about it.

Just because many people chose to model surveys and questionnaires as flat
tables doesn't mean it's correct!

Having it modelled as a flat table implies that there's a field for each
question and that the field names are probably the question numbers. That
means that in essence you're hiding information in the field name.

Rather than:
RespondentID
ReponseToQuestion1
ReponseToQuestion2
ReponseToQuestion3
ReponseToQuestion4
etc.

you should have

RespondentID
QuestionNb
ReponseToQuestion

so that each response is represented as a separate row in a table.

What do you do if you need to have another question on the survey? Add
another field? Having to make database changes can be difficult, whereas
adding another row is a no-brainer.

What if you've got a multiple choice questionnaire, and you need to know the
count of As, Bs and Cs so that you can categorize the respondent? That's a
particularly ugly query to have to make if your data is a flat table, but
trivial when you've got each response as a separate row.

Go to http://www.rogersaccesslibrary.com/OtherLibraries.asp and take a look
at Duane Hookom's "At Your Survey", or go to
http://www.databaseanswers.com/data_models/index.htm and look at any of the
5 Questionnaire data models.

 

[LisaB]

Thank You, I did overlook the error handling. It works. And, I will take a
look at those sites you suggested.

 

[CSmith]

Just because many people

chose to model surveys and
questionnaires as flat tables
doesn't mean it's correct!

Again, because you know absolutely nothing about her database objective,
doesn't automatically mean that "not properly normalized", as you implied,
was the case either. My point was that without knowing anything about her
database, you SHOULD NOT imply that it's "not properly normalized" because
it might be a survey, questionnaire, etc, which ended up being the case.

FYI: Simple questionnaires are usually sent out to get answers from a SINGLE
ENTITY and that's it! Then those answers come back and get sucked in by
another larger process, which is where the relational model usually comes
into play. Note that I used "simple questionnaires" [which is typical
practice]. Meaning, if I'm getting information from Fidelity about the
number of years they have been in business, the number of customers they
have, their customer attrition percentage since inception, their customer
retention percentage since inception, etc, then there is no need to have
anything other than a flat table. And surely a questionnaire would not ask
for their customer demographic information. I won't contribute to this
thread turning into a debate on whether a questionnaire should be relational
or not. Again, my point was that without knowing anything about her
database, you SHOULD NOT imply that it's "not properly normalized" because
it might be a survey, questionnaire, etc, which ended up being the case.
It's safer to simply answer the question without trying to blindly dissect
someone's database design just because YOU can't imagine a design like hers.

 

[Tim Ferguson]

Again, my point was that without knowing anything about her
database, you SHOULD NOT imply that it's "not properly normalized"
because it might be a survey, questionnaire, etc, which ended up being
the case.

Yes he should, because he's been doing it a long time and we have all read
these groups and seen the holes that new users dig for themselves by using
a wide, flat design. Hell, most of us learned that way too, and I wish I
had had a Doug eight years ago to stop me then from screwing up someone
else's perfectly reasonable bit of medical research because I thought I
knew better.

Every time anyone posts a reply to a message on these groups, we take on a
huge raft of assumptions and plenty of times they are wrong. But not to
take any of those would make everything either painfully slow or simply not
feasible. You have been around here for long enough to see the same half-
dozen or so questions come around and around: and you know that this OP
will end up asking how to search for arbitrary combinations of responses,
or count the numbers of people with particular patterns, or whatever. You
know it makes sense: why the grump?

B Wishes


Tim F

 

[CSmith]

You know it makes sense:

why the grump?

No grump here; quite contrar actually. However, I strongly disagree! You may
have missed my point too. Once again, it just makes a lot more sense to ask
questions first prior to assuming that you know what's happening on someone
else's desktop regardless of who you are. Is that really that hard to
understand? I apologize if that only makes sense to me. Remember, this is
the "modulesdaovba" section, not the "tablesdbdesign" section. If someone is
asking questions in this section, then they've more than likely already
crossed / reviewed their database design phase, and simply want their
question answered to help meet their deadline. By the way, she never wrote
that she was a new user in her original thread.

Ha! Ha! This is really funny how this thread has blossomed into a pretty
useless circle now all behind a very valid suggestion that I gave to your
friend.

Anyway, her database design is fine as she wrote and as I originally
indicated that it probably was if she was using a questionnaire.