Fog Creek Software
Discussion Board




Why didn't you tell me!?

Something happened yesterday that reminded me of when I bought my first computer. It was in Barcelona in November 1996. A cloned Pentium 133 with 16 MB of RAM, no CD, a 14" monitor that always ran in 16 color mode because nobody told me how to get it to run any other way, an HP 690 DeskJet, and Windows 3.11 pre-installed (W95 was costing $100 more).

I took it home by taxi, went into tech whiz kid mode, put all the wires in the right place, and  fired it up. I got a black screen with some writing finishing in “C:\” . I found I could type what I wanted and it would show up on screen, but nothing exciting appeared to be happening. Now, I knew that Windows wasn't all it was hyped up to be, but this was ridiculous. I dove in a taxi and went back to the shop. "Ah, but you must type in 'Win'" they said. "Thanks, but you could have told me before!" I say.

Back I go home and sure enough it works. The joys of the Windows Welcome screen appear. But now we get to the second problem; the mouse doesn't work. There is a very helpful tutorial on screen, and I'm sure it would tell me how the mouse would work but unfortunately you need the mouse to work in the first place to access it. I look at my watch, and find it's too late to get to the shop but there's still enough time to hit the bookshop. I get to Plaça Universitat just before the shop closes and pick up a 700-page book on DOS and Windows 3.1

I spend the whole night reading the book but getting nowhere, and went bleary-eyed the next morning to the shop. The saleswoman gave me a patronizing smile, and the tech guy explained that I had to install something called the "mouse driver" which came on the floppies they'd given me. And once again, but stronger, out came the "cri de coeur", "Why oh why didn't you tell me!"

What bought this to mind was that yesterday I finally hit the bullet and started to redo an Access application I'd written a couple of years ago for work. As you probably know I am an English teacher not a programmer, and the particular program to keep track of job applicants, was written with the aid of a very large book on using Access, so it has lots of macros (none of which are grouped), no code or comments, and some truly marvelous kludges, such as making a whole new query to get a calculated field for the source of a combo box, because I hadn't realized I could just type the formula in the combo box properties.

One thing the program does is open up a mail merge document to print a report on a candidate. This has certain disadvantages too obvious to mention, so I decided I was going to design an Access report to look like the original Word document and print that.

What I needed was the code to get the report simply to print the current record from the form. I check the help file and it comes up with

>>>To open a report and restrict its records to those specified by the value of a control on a form, use the following expression:

[fieldname] = Forms![formname]![controlname on form]<<<

So I type in

Dim stDocName As String
   
    stDocName = "Evaluationquery"
    DoCmd.OpenReport stDocName, acPreview, , ID = Forms!Evaluationquery!ID

The report opens but at the first page, with all 400 odd reports there.

I go and check similar code in the application used to open a form at the correct record. The code is in a macro and works perfectly. So I change the SQL where clause to what works in the macro
[ID] = [Forms]![Evaluationquery]![ID]

Still opens the form at the beginning with all records. I play around a bit, manage to get the header to show on a separate page and not show on a separate page, spend over an hour looking through the help file and various books, but get nowhere.

In desperation I go to one of my books and adapt a sample of code that opens a report with a subset of records.

Dim strReportName As String
Dim strFilterName As String
   
    strReportName = "Evaluationquery"
    strFilterName = "ID = Forms!Evaluationquery!ID"
    DoCmd.OpenReport strReportName, acPreview, ,  strFilterName

Whoa and behold, it opens one record at the right place!

Puzzled, I quickly check out what happens when I substitute the last line by

DoCmd.OpenReport strReportName, acPreview, , "ID = Forms!Evaluationquery!ID"

And it still works perfectly.

Now the reason for this is obvious a posteriori. In VBA the embedded SQL WHERE clause is a string, and thus needs the quotes. But nowhere does the example tell you. Nowhere accessible does the help file clearly mention that actions such as OpenRecord only work with macros, and that for code you must use methods of the DoCmd action, or that your field names will always be enclosed in square brackets in macros, but that this is not necessary in code.

This is just one minor example of a most irritating trend which is not confined to Visual Basic, but appears more there. The lack of any book that actually gives you any basic theory. It's as if the language was called "Visual Botchit" instead of "Visual Basic".

I have read through various VB in 24 hours books (it normally takes 24 hours to read the book and another 24 to debug the faulty code samples but that's another story) and yet nowhere is it explicitly stated why this verbose code below

Dim strReportName As String
Dim strFilterName As String
   
    strReportName = "Evaluationquery"
    strFilterName = "ID = Forms!Evaluationquery!ID"
    DoCmd.OpenReport strReportName, acPreview, ,  strFilterName

is better than the more concise one line


DoCmd.OpenReport "EvaluationQuery", acPreview, ,  "ID = Forms!Evaluationquery!ID"

I would think this is obvious to nearly everybody on this forum (and is the answer to Wei's complaint about VB being verbose) but to the non-programmer trying to get his office app to work it's not.

On the VISBAS beginners forum a week ago there was somebody whose code didn't work because he had not used a Set statement. It was completely beyond him why he needed to, until somebody explained it. NOBODY HAD TOLD HIM.

Everybody here complains about having to clear up spaghetti code from the VB-in-24-hours-brigade. But has it never occurred to you that the reason this is so is that nobody has ever told us any better. How can you be expected to comment code when you haven't the least idea how it works in the first place?

The purpose of the "for dummies" books is supposed to be to make you smart. Can somebody, and Joel obviously springs to mind, write a Visual Basic book that is not aimed at ensuring you really are a dummy after you've read it. A book for Visual Basic along the lines of Greenspun's book on database web applications.

When you're cleaning up code in a few years you'll appreciate it!

Stephen Jones
Sunday, January 19, 2003

This is a big issue with technology in general. It's easy for those well versed in the technology to forget a detail that they've internalized so much that they don't think about it anymore.

This is what seperates good teachers from poor teachers: the good ones know what these details are, and the bad ones just ignore the sticky points like these, because they don't even think about them, and it's automatic.

Mike Swieton
Sunday, January 19, 2003

I know what you mean Stephen, likewise, I have the opposite problem, I've been using VB on and off ever since it was invented, so I know it pretty well. I can't find a book at the other end of the spectrum. Likewise when looking up reference books for learning vb.net, all the 1000+ tomes that I read were full of 900 pages of sheer drivel that I already knew ten years ago.

You and me both are in minority markets that book writers don't want to restrict themselves to.

Alberto
Sunday, January 19, 2003

"You and me both are in minority markets that book writers don't want to restrict themselves to. "

Yet if we were dealing with hardware we could buy Scott Mueller, and it would do for both of us. It covers the theory which I want, covers beginners hardware, and has all the details you want. And sells millions of copies.

The problem appears to be most typical of VB. I can find books on database design, networking, hardware, HTML and SQL that do what they are supposed to, and give you the theoretical grounding. Yet VB books are like manuals for an Airfix kit with bits missing and the wrong type of glue.

Stephen Jones
Sunday, January 19, 2003

This was great reading. Thanks. I like these little stories. It illustrates what I suffer. Finding the right help for my coding problems. Unless I have a genius whose been coding in my language for ten years sitting next to me, I have to *suffer* through my problems reading thousands of pages of stuff that I’m not sure if I need to understand or not to get to the one simple little thing that explains it.

I’m all for books on *any* subject that can give me what I really need to know.

Anyone who wants to invent a knowledge management software system which could actually help someone with a coding problem would be an instant gazillionaire.

WNC
Sunday, January 19, 2003

Hint for buying programming books:

- The thinnest book in the store on the subject is probably one of the best.
- The thickest book in the store is almost certainly one of the worst.

Andrew Reid
Sunday, January 19, 2003

Thin  = http://cm.bell-labs.com/cm/cs/cbook/

Bella
Sunday, January 19, 2003

The real key here is having a writer that can tell you what is important, and what is not important. I always hate paging through 30 pages that tell you about the file-open dialog! And to “save” something! It is like telling you that you have to open the door on a car before you drive it!

However, you can not learn brain surgery in a day by reading a book. Fortunately, for us developers, programming is skill that takes a good deal of time and you also can’t learn it in a day.

Further, you also have to understand that you are dealing with multiple technologies, and each single technology can be the subject of a great deal of learning.

Thus, the “where” clause is a expression. You now need to learn how to make expressions in VB (by the way, the programming language in ms-access is VB).

Lets take your docmd.OpenReport example.

First, the help says that the where clause is in fact a sql where clause!  This is critical piece of information. Anyone who has ever used sql knows that you must enclose text values in quotes, and you don’t  for number values. However, don’t confuse that problem with your problem of knowing that a expression needs to be surrounded in quotes! For example, your example of:

DoCmd.OpenReport strReportName, acPreview, , strFilterName

Will not work if you use quotes around the FilterName as:

DoCmd.OpenReport strReportName, acPreview, , “strFilterName”

Thus, there is no way out:

    You have to learn how to create expressions in VB to use the OpenReport command.

Further, you need to learn how to use sql. And you need to rules for condtions in sql for JET. Some of them are:

For strings you must surround values with “
For numbers don’t surround
For dates, you must surround the value with #

I suppose the above could/should be added to OpenReport help, but the OpenReport help page is already quite large in the help. While it is not the best to tell you to go and read up on sql where clause, it is probably what you should in fact you should do!

Hence, you now need to learn sql to really use that command correclity! Well, learning sql, and learning to build a “where” clause in sql is not rocket science, but just telling you to surround text in quotes does not really help. (and my above exaple with quotes around the the where clause in fact would not work!). quotes are not the problem, using expressions is!

Also, by just looking at examples and not actually understaing what is going on, you create a lot of confusion. For example you tried:

[ID] = [Forms]![Evaluationquery]![ID]

Looking at the aobve, the squrare brackets are legal, but not for keywords like the forms "keyword". Hence you CAN USE:

[ID] = Forms![Evaluationquery]![ID]

So, I can’t just say use square brackets, and don’t use square brackets. In fact, your complaint about using square brackets is not really fair. You are in fact completely free to use square brackets around your field and form names. However, you are also trying to place square brackets around the word “forms” and that does not make sense. No more then:

docmd.[OpenReport]

As the above shows, you would not want to place the square brackets around commands or keywords.  The real key to unlocking how those expressions work is to start understanding collections in VB. The forms object is a collection. If I were to say what the #1 problem area people have in ms-access is a lack of understanding of how to use collections. The second most problem is how to build expressions.

The following examples are all legal ways to reference a form:

Dim  strForm    as string
StrForm = “Evaluationquery”

Forms![Evaluationquery]![ID]
Forms!Evaluationquery![ID]
Forms!Evaluationquery!ID

Forms(“Evaluationquery”)!ID
Forms(strForm)!Id

All of the above are equivalents. In fact, you can even use the forms index value. If you knew that your form was the 3rd form that you just opened, then you could also use use:

F orms(2)!id

(the values start at 0, and work their way up).

So, to use that stupid OpenReport methoed also with your where clause you need:
* to understand how to create expressions in VB
* you need to understand how to create where clauses in SQL
*you need to understand how to reference collections in vb to correctly use the right form in code.

Gee, collections, expressions, and also SQL where clauses is tall order for a simple page!! It is not good enough to tell you to use square brackets in a macro, but not use them in VB. In fact, you can use square brackets all over the place in your VB code as long as it is a reference to a collection object that you created (ie: forms, reports, queries, tables). In all other places in code you should not, and can not use square brackets. Hence, the above is the REAL rule for square brackets. But now, you need to understand what a collection is to understand that above rule! (because in all those cases where you are using square brackets you are using a collection).

So, no..there is unfortunately no cut and dry and easy answer here! Each concept you learn is going to be based on another concept. When you stack up enough concepts in your brain you will become a software developer!

In fact, just so you know, the real reason why square brackets should be used in code is WHEN YOU  have spaces in form names, and spaces in field names (in fact, in all of the user defined collection objects). However, you should avoid spaces in field names like the plague (since most databases systems don’t allow this). Thus, exporting your data to other systems will cause all kinds of problems. Even MS’s own SQL server does not allow spaces in the field names!. Hence, just don’t do it! And you will never need square brackets!

You can see already in my simple and short response to you I am already writing all kinds of things that you need to know. (like no spaces etc). I could easily write  20 more pages on just that simple one line of code called

docmd.OpenReport

In fact, the
docmd.OpenForm is even more complex!

Further, I am not even starting to talk about the different modes that you can open the form in (acDialog, and model forms in ms-access are for two VERY VERY different purposes in ms-access. Yet most books don’t explain the difference between the two, and further they fail to explain when you use acDialog forms, and when you should use Model forms. (acDialog forms even disable your custom menus!). Again, I could write a dozen more pages on additional options available in the OpenFrom command.

Just how many pages do you want me to write here? I absolutely convinced I could continue typing here for 20 pages and I would start scratch the surface here!

Further you are in fact not even forming the where clauses correctly! You are very lucky that your example actually works (shame on those books for bad examples!). I don’t like those examples at all!!  In fact, your expression only works because ms-access has a expression evaluation system that kicks in and evaluates that forms reference for you!

How would you search a bug database for a form named:

“BugForm = Forms!Evaluationquery!ID"

Is the above going to search for a form named Forms!Evaluationquery!ID ???

What happens when you actually want to search for a form named

Froms!Evaluationquery!ID

How can the system tell the difference between the two? As mentioned, I am actually VERY surprised that  your example works.  (is does work because ms-access has a expression “service” that evaluates it for you. That is what Joel calls a leaky abstraction!!).

The correct way is in fact is:

StrWhere = “id = “ & Forms!Evaluationquery!ID

If you are searching for a string, then you need:

StrWhere = “id = “ & “”” & Forms!Evaluationquery!LastName & “””

Or, it is common to use:

StrWhere = “id = “ & chr(34) & Forms!Evaluationquery!LastName & chr(34)

Furtther, to search for a form name in your data base you would use:

StrWhere = “BadForm = “ & chr(34) & “Forms!Evaluationquery!LastName” & chr(34)

Notice the “ (quotes) surrounding the form name. So, your original problem is not that you need to be told to surround the values in code, but you need basic knowledge as to how to build string expressions in VB, and that again is another whole area.

Also, your use of FilterName is confusing, since the docmd.OpenReprot has both a FilterClause, and also has a Where clause! Now, you have to start reading up on the concept of filters in ms-access.! A where clause, and a filter clause are different.

The arguments for Open From are:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

Note both the filter option, and the "where" option (they also exist for Reports).

So, yes, some books are not the best, but I can’t see any shortcut on how to learn the tons of concepts you need to use the simple OpenFrom.

This stuff is not really hard, but you do in fact need several layers of concepts to actually use that command. You have to learn to walk before you learn to fly. That is what all developers have to learn!

So, yes..we need good books, but there is no secret shortcut to learning this stuff.  Sorry!!


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Monday, January 20, 2003

The reason nobody told you is because you buy crap books.  No offense; it's almost definitely not your fault; until you've bought a few good ones and a few bad ones, it can be hard to tell the difference.  Heck, even after you've bought quite a few, it can be difficult to tell with some of them.

The simplest test for a computer book (or any book) is the apparent pages / real pages ratio.  How wide are the margins?  How big is the type?  How many blank pages are left at the end of each chapter?  One example I noticed recently (partly because of its sheer ubiquity) is those Left Behind books that are being snapped up by millions.  Open one of those, and you find that the margins seem to be about two inches on each side, and the text is practically double-spaced and larger than it needs to be.  I'd put the apparent pages / real pages ratio for those at 2 / 1.  Obviously the higher this ratio is, the higher the price will be without the content to back it up; you're paying for dead trees, not brilliant content.

To go beyond that simple test you have to have some familiarity with your subject matter, and of course you won't till you've read a few books on the subject, so we get into the chicken-egg thing here.  One thing to do is find as many local, city, county, or university libraries around as you can and see if they carry computer books.  If they don't, see if they will, or if you can get them through interlibrary loan.  Generally, if a book is worth having, you can't absorb it in two weeks, but it's enough time to give you a feel for the book so you can figure out if it's worth spending your money on it.

I had a "nobody told me" moment myself this past week:  At work, I've been struggling with a better method for source code control in Access (without buying VSS).  Just last week I discovered that you can set references to a database you created and use its procedures.  It's precisely what I needed, and I felt like an idiot not knowing it, but then who has time to read all the books in the world?  I imagine that anyone who programs has a fair number of such moments, and a fair bit of old and embarrassing code created before they knew certain important facts they know now.  I wouldn't let it get you down.  As I was saying to someone the other day, the painful lessons are the ones you learn best.

Kyralessa
Monday, January 20, 2003

Albert,
          Thanks for all the advice. Can I just comment on a couple of points now.

            The main thing I was objecting to was that the help files do not clearly distinguish between the OpenReport action (which only works in macros) and the OpenReport method of the DoCmd action. If you type in OpenReport instead of DoCmd.OpenReport you will get an error in the code window. The OpenReport only works for macros, but the help doesn't make this clear.

          The syntax for the embedded where clause will work in Access. In fact if you try to run a macro using any other syntax it will fail. In particular you cannot put the controls parentage in the first part of the sequence you will not get the required result in either VBA or macros.

              "Queries!Evaluationquery!ID =Forms!Evaluationquery!ID

will not work.


          I have tried using your syntax
StrWhere = “id = “ & Forms!Evaluationquery!ID

but have not found any way to get it to work.

          The thing is that it appears you must use the exact syntax I have used in Access with OpenReport or DoCmd.OpenReport I am quite aware of the fact that standard SQL requires other syntax.

>>>Also, by just looking at examples and not actually understaing what is going on, you create a lot of confusion. For example you tried:

[ID] = [Forms]![Evaluationquery]![ID]

Looking at the aobve, the squrare brackets are legal, but not for keywords like the forms "keyword". Hence you CAN USE:

[ID] = Forms![Evaluationquery]![ID]<<<

It's not me that's placing the square brackets around the keyword Forms, its Access itself. If you leave Forms without the square brackets in the macro editor in design view, and save the macro, you will find that Access has put the square brackets back again! In VBA both yours and Access macro's code will work as long as you put quotes around the whole expression.

>>>Notice the “ (quotes) surrounding the form name. So, your original problem is not that you need to be told to surround the values in code, but you need basic knowledge as to how to build string expressions in VB, and that again is another whole area. <<<

The problem in this case appears more simple. When you are using a macro you don't have the quotes. When you are using VBA the where condition is a SQL string and to the best of my knowledge all string literals in VBA must be in quotes. It's just that it's not the kind of thing that's in the forefront of your mind. I still maintain that the "leaky abstraction" is that the Access help moves seamlessly between macros and VBA code, but the syntaxes, although similar, are not the same.

>>>Also, your use of FilterName is confusing, since the docmd.OpenReprot has both a FilterClause, and also has a Where clause! Now, you have to start reading up on the concept of filters in ms-access.! A where clause, and a filter clause are different.<<<

Yep, horrible isn't it! But it's not my use of the FilterName it comes from; the official Microsoft Press books on using VBA in Access 2000. I would change strFilterName to strWhereClause if I was writing the code myself. And just to show that the right hand doesn't know what the left hand is doing the only example the help gives for the DoCmd.OpenReport method is
DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

In this case the ReportFilter is a string literal the name of the particular saved filter; that is why you see the extra comma in my code, the comma is there to replace the non-existent filter.

In short I am in fact aware of many of the things you say, though to have them said again clearly is a great help. However my point is that both the help file and most VBA books are sloppy about these things, and that we should not have to be picking up the basic concepts almost by default. Above all it should be made clear where Access macros are different from Acess VBA and where Access VBA is different from VB and where Access SQL is different from standard SQL and where embedded Access SQL is different from either.

Incidentally Albert I had great difficulty a year and a half ago trying to persuade another VB expert that the syntax for the macro command in OpenForm was correct.

Dear Kyralessa,

>>>The reason nobody told you is because you buy crap books.  No offense; it's almost definitely not your fault; until you've bought a few good ones and a few bad ones, it can be hard to tell the difference.  Heck, even after you've bought quite a few, it can be difficult to tell with some of them<<<

No, I can tell the difference; I have a fair number of good books for varying things (for Access programming my favourite is probably the O'Reilly book by Steve Roman) but the real problem is that there do not appear to be any good books for VB. You have to work things out indirectly by yourself. Good for mental agility no doubt, but a highly ineffective way of learning a particular domain.

What books for example will bother to explain clearly why OK is nearly always boolean but Cancel is an integer? To put it in a nutshell, you can get books about programming theory, you can get books about VB, you can get books about programming theory using, C, C++, Python, Lisp, DosBasic, Perl, Java and Spaghetti Carbonari, but you can't get a book on progrmming theory using VB.

Stephen Jones
Monday, January 20, 2003

Stephen,

I had the same problem when I was learning VB.

The ironic thing is that I finally got the high level overview of VB I was looking for by reading the language specification for Lotuscript.

I too was completely confused by the square bracket notation (I believe Joel was responsible for that feature).  95% of the time its intuitive and nice, but then it would stop working and I wouldn't know why.

It was in the Lotuscript manual (A pdf which came with the Smartsuite installed on my machine) that I finally read about collections.  To access a control on a form you would write something like:

Forms("Form1").Control("txtName").value

Then it explained that the square bracket notation was a shortcut to the above notation.

[Form1]![txtName]

Finally it explained that the components had a default property, which in the case above was value.

The finally missing piece was that my context also had a default object - the Application object.  The default property was a collection of currently loaded forms.

This was all expressed in about three paragraphs, and suddenly it all made sense.  A simple object hierarchy with default properties and a shortcut notation for the collections.  Thats it.

Albert is right to explain the complexity of when and when not to use square brackets, but really there is a simple concept behind it all that just doesn't get explained.  The vast majority of the books for VB just encourages a cut and paste approach.

So too the need to encase an SQL statement in quotations it that complicated once you understand that the SQL is not part of the language, but is a string that you a passing to be processed by a separate object.

btw, have you tried Hardcore VB - http://www.mvps.org/vb/hardcore/

Ged Byrne
Monday, January 20, 2003

Joel was responsible for the square brackets.  He discusses it in this thread:

http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=1303&ixReplies=6

Just to be clear - I'm not criticising Joel and the square brackets.  It really is a nice syntatic feature.  My criticism is that it is never explained to newbies properly.

Ged Byrne
Monday, January 20, 2003

The trouble with hardcore VB is the name. The Saudi authoriites think it is a porn site (Visual + Basic + Hardcore) and the result is that it is banned. I will try again to get them to lift the ban, but I am dubious of success.

I'll be in Sri Lanka on holiday in two and a half weeks so maybe I can get it then. If you could email me anything you see on that site that is useful I would appreciate it. Click on the link to contact me, and I'll send you the address privately - if I put it here I'm likely to get another load of Viagra ads.

Stephen Jones
Monday, January 20, 2003

One of my "nobody told me" moments with VBA was bang (!) versus dot (.) -- I would get newsgroup suggestions to do stuff like Me.ControlName, even though all the help files use Me!ControlName (or maybe it was the other way around? Don't quite remember). Took a while to figure out the difference, i.e. when they're interchangeable and when they're not. I still haven't found a book that adequately explains the distinction.

Another problem I had when starting out with Access was the casual use of specific terminology. An Action is different than a Method, but there's nothing in the terms themselves that tells you which is which. (Makes the help files rather hard to understand. "The xyz method performs the xyz action in VBA." Gee, how helpful.) A Module sounds like a fancy mysterious something; nobody tells you it's just a place to put code.

Martha
Monday, January 20, 2003

I will just jump in with a few more comments:

>>>The OpenReport only works for macros, but the help doesn't make this clear.

All of the macro commands in VB are run by using the DoCmd. The very second you find the docmd, you should be home free!

You only have to use the DoCmd. I never used macros in ms-access anyway. Maco's are also a maintenance nightmare.. The question you are asking is how do you run or use macro commands in VB? The answer is to use the DoCmd object.

>>"Queries!Evaluationquery!ID =Forms!Evaluationquery!ID
>> will not work.

Of course it will not work!!. It will not work in SQL either. What part about the where clause being standard sql did you not understand? Sql where clauses are usually

City = “Edmonton”
Or
SomeFieldNameInTheQuery = some value

In the above, you are asking for sql to search a field named Queries!Evaluationquery!ID = to something. There is no field named that!!. So, that above is terrible example.

MyTableName!Id =

Again, the above will NOT work. You HAVE TO use a standard sql legal expresison.

Again, you have not yet grasped the concept of creating expressions.  You can use the standard SQL table qualifier such as:

MyQueryName.ID

The above is standard sql. If you know sql, then the above will make sense. You are trying to build expressions in sql without knowing sql!

So, the query name is Evaluationquery, then you CAN USE:

“Evaluationquery.ID  = “ & Forms!Evaluationquery!ID

The table qualifier in SQL is VERY standard.
Again, you are having problem here since you need to know SQL syntax.

>>>> I have tried using your syntax
StrWhere = “id = “ & Forms!Evaluationquery!ID

My example should work. The above assumes that you have a field called “ID” the query. The above also assumes that id is a number type field. Is Id a number type field and does it exist as a field in the query? As mentioned, I actually laid down the rules for SQL queries in my example? Did you bother to read my comments? Also,  I was very clear that the above was NOT a filter. Hence, try the following code:

Me.Refresh
StrWhere = “id = “ & Forms!Evaluationquery!ID
docmd.OpenReport "YouReport",acViewPreview,,strWhere

The above will work. Note the number of “,” for the parameters. If id is a text field, then the above will NOT work. Also, since you are in a form, the me.refresh forces a disk write. The reason you need that is when you are in a reocrd, the data has not been saved yet. You do realize that ms-access does not save the current record until you move to a new record, or exit the form right? Ask your self when does ms-access save the record? I am 100% certain you realize that ms-access saves the record when you close the form, or go to the next record. Thus, how can you possibility launch a report to the same record without saving the record first? It does not make sense to launch a report to report on that record without first saving?

While I accept that some manuals are not good, you have not taken the time to learn how to build expressions. You are still making basic mistakes. This is the source of your troubles.

Try the above example. It is correct. The only reason why it would not work is if ID is not the right field name in the query, or id is a text type field. Try it and post back here. If id is a text type field, then you need to surround the value with quotes IN the string.


>>> It's just that it's not the kind of thing that's in the forefront of your mind. I still maintain that the "leaky abstraction" is that the Access help moves seamlessly between macros and VBA code, but the syntaxes, although similar, are not the same.

Not really. The problem here is that you never made a distinction between methods of the docmd and macro actions. All actions in the help refer to macros, and methods refer to the docmd object.  There is not more here to know, but you at least have to make that distinction!

Also, my example of using [] brackets was not the best. In fact, in my examples I said to not surround keywords with [] brackets. This is correct advice for commands. However, in my examples the forms keyword is NOT a command. (so, I fact, I was wrong!). Hence, you CAN IN FACT fact surround the word “forms” with [] and it will work.

Remember, those macro commands send off that stuff to the VB docmd object anyway. So, if it works in a Macro, then it WILL WORK in VB. I don't even have to test this to know what will happen. Just like spock says I don't need to see a hammer hit the ground if it is dropped. So, if the square brackets work in the maco, then they will work in the VB. If they did not, then again you are doing somthing wrong. You *can* surround the word forms as [Forms].

Anyway, that above OpenReport example will  work.

Albert D. Kallal
Monday, January 20, 2003

In the amount of time it probably took to write that massive post, you could have rewritten everything from scratch in C, starting with your own from-scratch revolutionary database, all running on an Intel Paragon. As an added bonus, the Paragon could scroll your data in five foot tall letters while it processes. The program could also do some complex bioinformatics work on the side.

Super
Monday, January 20, 2003

O'Reilley books are generally very good.  Stick to those. 

Bella
Monday, January 20, 2003

Albert,
            Just a quickie before I go off to work. Your code with the Where does NOT work in Access. TRY IT OUT. I have read your comments, was aware of most of them anyway, and yes of course ID is a number field and of course it is present in the underlying query (and everywhere else in the database for that matter).

              Putting the name of the Query before the form as in
"EvaluationQuery.ID = Forms!Evaluationquery!ID"

works. both in the macro and in the code. It is not the format used in the Access help though, was touched on by another poster,  and the fact that you use the . instead of teh ! in the first part of the expression is something you are more likely to find out about by making a typo than getting it from the Access help.

              If am aware of the deficiency of macros, which is why I am changing everything to code. However you completely miss my point when you just tell me to use DoCmd What I, and Bertha, are saying, is that non-standard SQL works in Access, is  on occasion the recommended syntax, sometimes standard SQL does not work, and nowhere in the Access help is any of this made clear.

Stephen Jones
Monday, January 20, 2003

Jeeze, guys -- just gotta say 'ditto' to all that.

and this sort of shit is why my wife of now almost 19 years learned some time ago that when I'm back in my corner of the office at home surrounded by server's, laptops, and monitors, eyeballs bugged out staring holes into a monitor (often swearing assertions about the questionable parentage of the machine), and she calls back to remind me that tomorrow is trash pickup, my reply of ' Uh, yeah Hon, I'll get the trash in a second...I'm almost done...just let me try this one more thing...' really means that sometime in the next 8-12 hours, hopefully before the trash pickup the next morning, I'll actually get the cans to the curb.

damn these machines!

;-)

anonQAguy
Monday, January 20, 2003

I had a "why didn't you tell me" experience last week with PHP (the scripting language for web programming). I was upgrading from 4.1 to the latest 4.3, but it wouldn't work. After hours of monkeying around, mucking with the registry (on my Win98 machine) and trying some older versions I finally found the problem: they changed a security setting that required a flag to be set to "1" instead of the default "0" in the middle of the 1500 line php.ini file. And the installer failed to do that.

The only reason I found it is because the previous version, 4.2, issued an actual error message on failure, saying something about this security setting. Though 4.3 doesn't give any errors, it finally clicked that it might be the same error.

Once it was working, I just boggled over it, wondering why this wasn't mentioned somewhere obvious?

And then I discovered my efforts had bolluxed my computer and I spent the weekend re-installing Windows...

David Fischer
Monday, January 20, 2003

Final notes:

The example of was sent to me by the original poster:

stDocName = "Contacts"
StrWhere = “ContactID = “ & Forms!Contacts!ContactID

I stated the above should work. I usually write my posts for this BBS in word, and then paste it. My example was “air code”, but it still should have worked. Of course I use ms-word and the above “quotes” are actually the wrong quote characters!!

The above should be

stDocName = "Contacts"
StrWhere = "ContactID = " & Forms!Contacts!ContactID

Notice the non curly quotes used

Thus, when Stephan cut and pasted the code, the quotes were of the wrong type!

In fact, those quotes don’t even generate a compile error (they evaluate as an expresison to null!).

I was straining my brain as to why my examples did not work.  Thankfully Stephan took the time, and emailed be a tiny copy.

Sure enough, just changing the quotes to the *correct* quote character fixed all problems.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

Albert D. Kallal
Tuesday, January 21, 2003

Well Railtrack has the wrong kind of snow, and Microsoft has the wrong kind of quotes!

For those of you who want ot persist in using Word as your text editor, then you can turn off the curly quotes feature by going to Tools|Options|Autocorrect and uncheck the box "replace quotes with smart quotes".

Still not my favourite MS bug though. That is the one that explains why PhotoDraw doesn't start - "You have an odd number of fonts". Yep, you can only use Photo Draw if your font folder has an even number of fonts; doesn't matter how many, nor does it matter what (though there is one nasty font that makes Access 97 come up with a "You haven't got a license to use Access on this machine message) .

So don't come to me and say "Why didn't you tell me?" :-)

Stephen Jones
Wednesday, January 22, 2003

Stephen,

In comp.databases.ms-access I've observed that most of the professional Access developers (I wouldn't claim to be one myself) recommend _Access [year] Developer's Handbook_ by Litwin et al.  ISBN is 0782140114 for the 2002 version; 2000 and 97 versions are around as well.  Don't get a version ahead of what you're actually using, or the samples on CD-ROM won't work (so I discovered; I thought the 2002 databases might be in 2000 format, but they weren't).  A lot of the "Why didn't you tell me?" stuff is in this book.  (I think there may even be a dot vs. bang discussion in there.)

Kyralessa
Monday, January 27, 2003

Thanks; I'll give it a try.

At the moment I'm working through Novalis (having finished the MS Press book yesterday), and it doesn't seem to bad for explaining what goes on behind the scenes, but it does seem to be a question of finding things out bit by bit.

For database development in general there are some fine books, but the VB books I've seen appear to be written for the better kind of monkey (abstract thought definitely a no-no; maybe slip a little in while he's playing with the toys).

I've actually gone back to the Roman O'Reilly book. When I first read it I paid little attention to the VB chapters as I was concentrating on normalization and the general design of relational databases. On going back to them I do find them good (they give the difference between named and positional arguments as a matter of course).  The problem is the opposite of the other books in that it is short of examples.

Stephen Jones
Tuesday, January 28, 2003

*  Recent Topics

*  Fog Creek Home