Make the syntax in Access consistent

A

aoeusnth

CONSISTANCY – SYNTAX:

Learning to 'program' small lines of syntax into controls in the MSAccess is
really difficult. One of the reasons why is bc the syntax within VBA changes
from location to location. As an example, 'if-then' statements can be
written in the following ways within the VB world:
1) IF A=1 THEN 2 ELSE 1
2) IF(A=1, 2, 1)
3) IIF(A=1, 2, 1)

#2 above can be entered into excel, but when I tried to enter it into
access, it wouldn't work. I spent about an hour trying to figure out what
was wrong before I tried the "If and only IF" of #3, which did work. It's
all VBA, I should be able to enter any of those three anywhere and they
should work. There should be an overview explanation of the ‘feel’ of the
syntax in access, which combined with looking up functions would greatly
improve the ease of use.

I can’t use an SQL expression in a textbox control source, which is a hassle.

EXAMPLES:
Are priceless. How am I supposed to know that I should put ‘#’ around dates
in vba? The definition of a function won’t tell me that, but the examples
will. Suppose I’m writing a value into a field in the database. It’s
slightly different based off of what the data type is, and the only way I’ll
ever know is by seeing examples. Often tutorials give you two examples: 1)
The one that’s too basic to be much help 2) The one that’s too complex to
understand.

The more complex a piece of code / function / etc. is, the more examples
there should be. If there are 8 options for a function, then there should be
an example for each one.

REFERENTIAL INTEGRITY:
Fails silently when it should fail loudly. If you try to delete an item
that is being used by another field, access won’t let you, but it won’t tell
you what happened either.

NAMING CONVENTION:
I should be able to set the default naming convention that gets tacked onto
new tables/queries/forms/reports/macros & controls. I like to use the
three-letter Hungarian naming convention, and it would save time if Access
would automatically take “tbl†onto the front of report names, and cbo – for
control boxes, txt – for text boxes, etc. If the user could set that
themselves, that would be great. On a related note, it should be hard to
figure out how to disable the naming convention. If it had been strictly
enforced, the database I’ve been working on wouldn’t have been in such bad
shape by the time I got ahold of it. If would be helpful for people that
know what they’re doing, and it would be a guard rail for people who
shouldn’t be developing databases in the first place.

COPYING FORMS:
Is messed up. I got some serious faults doing that in MSA2000, but I guess
you guys are already aware of that one.

CHECKBOXES:
I can’t set the back color on the checkboxes, so when I have a form that’s
supposed to be all grayed-out, the checkbox backgrounds are still white, so
it looks like the user can change the value. That’s a really irritating one.

EXPRESSION BUILDER:
Another example is that the expression builder in Access provides buttons
for the basic math operations (+,-,*,/), but does not have a button for If
statements, which would be much more helpful, since the basic math operation
symbols are consistent with every other program, and are intuitive. At times
the expression builder has generated code that WILL NOT WORK. Example:
1) I make a text box on a form.
2) I go to the control source field, and open the expression builder.
3) I select a table, and then a field, so that the expression builder writes
"=table.field"
4) I open the form, and the text box displays "#Name?" in the textbox.
The reason why is bc the expression builder added the '=' in front. When I
delete it, the textbox works. So the expression builder is too dumb to know
not to put an equals sign in place. The important thing to note is that I
DIDN'T TYPE ANYTHING. It was purely a point-and-click operation, and it
failed. I didn't type garbage in. The expression builder did.

PROGRAMMING:
Programming is really easy, and it should be easy to learn, but it's not.
Finding the correct syntax seems to usually be the problem. Most people with
some programming experience have a good feel for what the computer SHOULD be
capable of.

There should be a more simple way to explain how to do things with code.
I'm doing fairly well on my own, and getting into some fairly complex
maneuvers, for instance I made some code that maps out an Access DB to show
relationships between the TABLES-QUERIES-FORMS-REPORTS and displays it in a
treeview control. It's not smart enough to figure out what you did with
code, but it was good enough to help untangle a real medusa of a DB that I
got thrown onto. I think this tool should be incorporated into access.

I'm not looking for work-arounds, bc I figure them out as I need them, I am
hoping that MS will improve their product. I really like VB/VBA/MSOffice,
which is why I want it to improve. I learned basic when I was a kid, and the
Excel macro recorder allowed me to pick up on syntax. Once I got good enough
at that, I moved on to developing in access. The macro recorder is probably
the easiest way to learn syntax, and should be built into each tool when
possible. The Object Browser is also an excellent tool. Keep up the good
work, and I look forward to seeing what's next.

Thanks,
Nate
 
D

Dirk Goldgar

Nice suggestions, for the most part, and I hope some of them are read
and considered by the Microsoft folks. I trust you posted this as a
"suggestion" message from the web newsreader. I do have a couple of
quibbles, though.
CONSISTANCY - SYNTAX:

Learning to 'program' small lines of syntax into controls in the
MSAccess is really difficult. One of the reasons why is bc the
syntax within VBA changes from location to location. As an example,
'if-then' statements can be written in the following ways within the
VB world:
1) IF A=1 THEN 2 ELSE 1
2) IF(A=1, 2, 1)
3) IIF(A=1, 2, 1)

#2 above can be entered into excel, but when I tried to enter it into
access, it wouldn't work.

#2 is not VBA at all, nor is it VB. In Excel, it's a worksheet
function. Excel supplies some functions that Access doesn't, but which
are useful in Excel; Access supplies some functions that Excel doesn't,
but which are useful in Access.

#1 is a VBA statement, though the example doesn't do what the others do.
It doesn't return a value of 2 or 1; instead it branches to statement
label 2: or statement label 1: depending on the value of A.

#3 is a VBA function, not a statement, so unlike #1 it can be used in
places where a value is required.
I spent about an hour trying to figure out
what was wrong before I tried the "If and only IF" of #3, which did
work.

Note: "IIf" doesn't stand for "If and only If". It seems to me that. in
formal logic, that condition is written "IFF", but I could be mistaken.
Anyway, I'm pretty sure that "IIf" stands for "Immediate If", which
makes more sense..
It's all VBA, I should be able to enter any of those three
anywhere and they should work.

It's *not* all VBA, and the two VBA snippets you posted have very
different meanings.
I can't use an SQL expression in a textbox control source, which is a
hassle.

Agreed. It would be rather nice.
EXAMPLES:
Are priceless. How am I supposed to know that I should put '#'
around dates in vba? The definition of a function won't tell me
that, but the examples will.

Or actually reading a the help file entry on the Date data type.
Suppose I'm writing a value into a
field in the database. It's slightly different based off of what the
data type is, and the only way I'll ever know is by seeing examples.
Often tutorials give you two examples: 1) The one that's too basic
to be much help 2) The one that's too complex to understand.

The more complex a piece of code / function / etc. is, the more
examples there should be. If there are 8 options for a function,
then there should be an example for each one.

But on the whole, I agree. Access 97 help had more and better examples,
I believe.
REFERENTIAL INTEGRITY:
Fails silently when it should fail loudly. If you try to delete an
item that is being used by another field, access won't let you, but
it won't tell you what happened either.

Only if you use the DAO execute method and don't specify the
dbFailOnError option. By not using the dbFailOnError option, you are
saying, "don't raise an error if this statement fails."
NAMING CONVENTION:
I should be able to set the default naming convention that gets
tacked onto new tables/queries/forms/reports/macros & controls. I
like to use the three-letter Hungarian naming convention, and it
would save time if Access would automatically take "tbl" onto the
front of report names, and cbo - for control boxes, txt - for text
boxes, etc. If the user could set that themselves, that would be
great. On a related note, it should be hard to figure out how to
disable the naming convention. If it had been strictly enforced, the
database I've been working on wouldn't have been in such bad shape by
the time I got ahold of it. If would be helpful for people that know
what they're doing, and it would be a guard rail for people who
shouldn't be developing databases in the first place.

I'd like to be able to choose from a set of supplied naming conventions,
define my own naming convention (and have the option to share naming
conventions throughout the company), or have no naming convention. I
should warn you, though, that this is a contentious issue. Many skilled
developers champion the use of naming conventions, while other --
equally high-powered -- think they're a waste of times.
COPYING FORMS:
Is messed up. I got some serious faults doing that in MSA2000, but I
guess you guys are already aware of that one.

I have no idea what you're talking about. Would you care to explain?
CHECKBOXES:
I can't set the back color on the checkboxes, so when I have a form
that's supposed to be all grayed-out, the checkbox backgrounds are
still white, so it looks like the user can change the value. That's
a really irritating one.

I've never faced that problem, but it would be a nice feature.
EXPRESSION BUILDER:
Another example is that the expression builder in Access provides
buttons for the basic math operations (+,-,*,/), but does not have a
button for If statements, which would be much more helpful, since the
basic math operation symbols are consistent with every other program,
and are intuitive.

The Expression Builder builds *expressions*, not program code. However,
it does allow you to select the IIf(), Choose(), and Switch() functions
from among the built-in functions, to support conditional expressions.
At times the expression builder has generated
code that WILL NOT WORK.

Yes, I'd like to see the Expression Builder be more sensitive to the
context in which it is called, so that it doesn't let you build
expressions that are invalid in that context. It wouldn't be an easy
task, though.
1) I make a text box on a form.
2) I go to the control source field, and open the expression builder.
3) I select a table, and then a field, so that the expression builder
writes "=table.field"
4) I open the form, and the text box displays "#Name?" in the textbox.
The reason why is bc the expression builder added the '=' in front.
When I delete it, the textbox works. So the expression builder is
too dumb to know not to put an equals sign in place. The important
thing to note is that I DIDN'T TYPE ANYTHING. It was purely a
point-and-click operation, and it failed. I didn't type garbage in.
The expression builder did.

But using the Expression Builder to enter a controlsource is tantamount
to saying "I want to make a calculated control," and the controlsource
of a calculated control *must* begin with an equals sign. Maybe the
Expression Builder should be smart enough to recognize if you build an
expression that resolves to a field in the form's recordsource, and just
replace the expression with that field name. But it would never occur
to me to use the Expression Builder for such a thing in the first place,
since all the fields in the recordsource are right there in the dropdown
list of the controlsource property.

A more common problem is that the EB lets you build a reference like

[TableFoo]![FieldBar]

when neither TableFoo nor FieldBar is in the form's recordsource. Yes,
the Expression Builder could certainly be smarter.
PROGRAMMING:
Programming is really easy, and it should be easy to learn, but it's
not. Finding the correct syntax seems to usually be the problem.
Most people with some programming experience have a good feel for
what the computer SHOULD be capable of.

There should be a more simple way to explain how to do things with
code.
I'm doing fairly well on my own, and getting into some fairly complex
maneuvers, for instance I made some code that maps out an Access DB
to show relationships between the TABLES-QUERIES-FORMS-REPORTS and
displays it in a treeview control. It's not smart enough to figure
out what you did with code, but it was good enough to help untangle a
real medusa of a DB that I got thrown onto. I think this tool should
be incorporated into access.

Sounds nice. What's it look like?
I'm not looking for work-arounds, bc I figure them out as I need
them, I am hoping that MS will improve their product. I really like
VB/VBA/MSOffice, which is why I want it to improve. I learned basic
when I was a kid, and the Excel macro recorder allowed me to pick up
on syntax. Once I got good enough at that, I moved on to developing
in access. The macro recorder is probably the easiest way to learn
syntax, and should be built into each tool when possible.

My experience with the macro recorder is that it's a start, but it's no
substitute for learning the object model of the application you want to
control. Often the manual steps that the records macro shows you are
far from the most efficient way of accomplishing the goal.
The Object Browser is also an excellent tool.

Oh, yeah!
Keep up the good work, and I look
forward to seeing what's next.

Thanks,
Nate

Nice sentiments, Nate, and I second them.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top