A2010 macro builder and a message box
in [Help]
|
Prev: Help in A2010
Next: New features of Access 2010
From: Albert D. Kallal on 29 Nov 2009 10:13 "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9CD1ED016DABDf99a49ed1d0c49c5bbb2(a)74.209.136.90... > "Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in > news:FZYPm.36883$ZF3.470(a)newsfe13.iad: > >> As an "general" rule, we don't have >> to state what kind of module, but if I asking an question about a >> report and why I can't use me.FieldName in a reports code module, >> then it is clear. As you know, in a forms code module, you do NOT >> have to place text boxes on the form to reference/use fields in >> vba code, but in a reports code module, you DO HAVE to place text >> boxes on the report to reference any field in the reports data >> source. > > I do *not* know this to be the case. > Sure, just build a report, and try something like: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Debug.Print Me.City End Sub In the above report example, it is based on a table where the city field exists. In fact, during coding your get intel-sense. But, if your try to run the above report, you get an error message. Just try it... Add the text box city..run the report...it works ok. Now, remove the city text box, the above code fails... You can't use me.FieldName in the report DESPITE the fact that inteli-sense gives you that while you code... It rather a simple example.... So, as I pointed out, the context of a reports class module code works quite different then that of a forms code module... Give the above try..it takes less time then to type this response. (base the report on the base table). -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: Marshall Barton on 29 Nov 2009 11:36 Albert D. Kallal wrote: >"David W. Fenton" wrote >> "Albert D. Kallal" wrote >> >> As an "general" rule, we don't have >>> to state what kind of module, but if I asking an question about a >>> report and why I can't use me.FieldName in a reports code module, >>> then it is clear. As you know, in a forms code module, you do NOT >>> have to place text boxes on the form to reference/use fields in >>> vba code, but in a reports code module, you DO HAVE to place text >>> boxes on the report to reference any field in the reports data >>> source. >> >> I do *not* know this to be the case. >> >Sure, just build a report, and try something like: > >Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) > Debug.Print Me.City >End Sub > >In the above report example, it is based on a table where the city field >exists. In fact, during coding your get intel-sense. But, if your try to run >the above report, you get an error message. Just try it... > >Add the text box city..run the report...it works ok. Now, remove the city >text box, the above code fails... > >You can't use me.FieldName in the report DESPITE the fact that inteli-sense >gives you that while you code... > >It rather a simple example.... > >So, as I pointed out, the context of a reports class module code works quite >different then that of a forms code module... > >Give the above try..it takes less time then to type this response. (base the >report on the base table). Albert, the way I understand things, that's not a valid example of a distinction between different "types" of modules and I believe that report modules and form modules are the same thing. I'm pretty sure that he difference you cite is an artifact of MS's implementation of the *form* object where they create the incomplete, buggy and artificial AccessField thingy as a substitute for fields not bound to a control. Allen Browne has discussed this in several posts and strongly recommnds that every field referenced in a VBA procedure be bound to a control. -- Marsh
From: David W. Fenton on 29 Nov 2009 17:43 "Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in news:r2wQm.47504$X01.1485(a)newsfe07.iad: > "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message > news:Xns9CD1EC8999FC6f99a49ed1d0c49c5bbb2(a)74.209.136.90... >> "Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in >> news:FZYPm.36883$ZF3.470(a)newsfe13.iad: >> >>> You can use "me" in a standard forms VBA >>> module, but not in a code module, or an class code module that >>> you create. So, that's 3 different kind of code modules right >>> there >> >> Wrong, Albert. Standalone class modules can use Me just like >> form/report class modules. > > Yes, sorry, my bad... > > I meant to say me.FieldName, not me. Well, of *course* you can't use a field name because a standalone class module has not fields. A regular module has no field names, either. > I was not trying to state > that you can't use me in a class module. (I do that all the time). > > The "basic" point here was that even a forms > code module behaves different then a reports code module for > example. And, so does a standard class module that is not > attached to any form. To me, there are two types of modules: 1. regular modules 2. class modules So far as I can tell, they all behave the same, and the differences is in what they are attached to (or not). > So, a question as to why me.FieldName don't work in a > reports module is a DIFFERENT question then that of > using me. So far as I know, it *does* work in a report's module. Ah, just testing, I see this is something that changed with Access 2000. In A97, you can refer to fields in the events that have data already loading. Basically, that means the OnPage event. That requires an understanding of how a report is different from a form, in that at the time most of the report's events happen, there is no data available to display. Also, if you use events like OnActivate, you have to understand that by the time a page is formatted and displayed, the current record is the *next* one, so if you try to do something with a field from the underlying recordsource, you're going to get unexpected results. For that reason, you usually end up getting information out of controls on the report, since the binding of the visual display to the underlying data source is quite different from forms. But I do see that it doesn't work at all in A2000 and A2003 in any of the situations where it worked just fine in A97. This was a big surprise to me. It's not that I don't do lots with report events, but mostly it's these things: 1. in OnOpen, open a dialog to collect values to use in filtering the report, or assigning its recordsource on the fly. 2. in Format events, draw lines. 3. in Detail events, move and resize controls and change their ControlSources. That's all I ever do (except for the NoData event, of course). None of those require looking at data in the report's Fields collection, so I've never noticed that something that worked in A97 no longer works. It also means I never did it in A97, as any number of my apps got converted to A2000 and the reports did not break. In poking around with this, I noticed something odd. I thought maybe that I could use the report's .Recordset property to get a field value, but that doesn't work -- it gives an error message saying "this is not available in an MDB." I can't figure out what it *is* available in -- an ADP? The help file for the .Recordset property says nothing about limitations of using it in a report, so I'm pretty mystified. It's also odd that the report module's Intellisense list includes the fields in the recordset. I wonder if this is really an intentional change or not. As I've noted elsewhere, referring to columns in the Fields collection of a form is also not 100% reliable and sometimes requires a hidden control to get around the issue. The fact that it's unreliable suggests to me that it's not by design. And I've always seen it as a result of the alteration of the VBA project to be a single BLOB instead of individual objects, and of the decoupling of the forms from Jet in order to support recordsets of different types. > FieldName in a forms module. So, me.fieldName > usually works in a form (regardless if their is a text box > on the form, but in the format events in a reports code module, > me.Fieldname don't work unless a text box is placed on the > report that is bound to the column. It used to work (i.e., in A97). I never noticed that it no longer does. But I'm not sure that this means that report modules are different from form modules so much as it is a result of the fact that reports are very different animals than forms. > We often see questions in this newsgroup where someone > moved their code from a forms code (class) module into an > standard code module (or even a class code module), and then > wonder why they can't use me.fieldName anymore. Well, that's just novice ignorance, a failure to understand what they are doing. > In a nutshell, my "point" here is that while do seem to have > "quite a few" names for macros, it not a whole lot different then > that of the several types of class modules we have (forms, > reports, class modules). I disagree. > I simply just spent some extra time to point out the terminology > we have for macros, and one will often need to distinguish between > the different types when discussing them.. Yes, and I think that the problems that come with this are evident in your exchange with Bob Alston, where it looked to me like you were giving pretty clear instructions, but he had missed the web/client distinction on the front end (as well as the Office Live vs. Office Small Business Live). If an experienced programmer like him has trouble seeing the distinction, I expect it to be the source of a lot of frequently-asked questions in the newsgroups. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 29 Nov 2009 17:47 Marshall Barton <marshbarton(a)wowway.com> wrote in news:ib65h51fpln317cckhepoipb5qaoh5suva(a)4ax.com: > Albert, the way I understand things, that's not a valid > example of a distinction between different "types" of > modules and I believe that report modules and form modules > are the same thing. I'm pretty sure that he difference you > cite is an artifact of MS's implementation of the *form* > object where they create the incomplete, buggy and > artificial AccessField thingy as a substitute for fields not > bound to a control. > > Allen Browne has discussed this in several posts and > strongly recommnds that every field referenced in a VBA > procedure be bound to a control. Well-said. This is exactly the point I tried to make in a reply to Albert that I posted a moment ago, that the difference comes not from a distinct type of class module, but from the fact that the object the class module is bound to is of a different nature. Reports are different from Forms and thus, they work differently. While the whole Fields/Controls mega-collection as default collection for the Me object in forms is awfully convenient, it has always bothered me. The fact is, reports in A97 worked the same as forms do now, with the exception of events where the recordset didn't yet exist (a report's OnOpen is not happening in the same data context as a form's), or where the current record in the recordset is not what you'd expect (in OnActivate, the record pointer is on the *next* record, not the one that's being displayed onscreen). Thus, forms and reports are *different*, not because of distinct types of attached class modules, but because the relationship between a report and it's data buffer is not identical. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 29 Nov 2009 17:50
"Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in news:vcwQm.47505$X01.13242(a)newsfe07.iad: > In the above report example, it is based on a table where the city > field exists. In fact, during coding your get intel-sense. But, if > your try to run the above report, you get an error message. Just > try it... I did, and it didn't work. But in A97 it *did* work, so this seems to be something introduced in A2000 (and I tested in A2000 and it behaves as you say, just as it does in A2003). This is not a difference between the form and report class modules, but in the type of object the class module is bound to. The relationship between the report or form's display and the data buffer is different, and because of that, looking at the data from the report module has different results. I don't quite understand why the fields collection shows up in a report's Intellisense list if it's unavailable, though, and this makes me think that this is not something that was implemented by design, but some kind of unintended side effect of other changes introduced in A2000. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |