Access 2010 Data Macros: The Three-Legged Hunting Dog With One Bad Eye

Discussion in 'Microsoft Access' started by Neil, May 1, 2012.

  1. Neil

    Neil Guest

    Wow, it's just amazing how difficult it is to do even the smallest of tasks
    in data macros. Like, right now, I'm creating a record in a table, and need
    to insert the date value from a text box on a form into a field of that
    table. That seems to be impossible to do.

    I realize the response is going to be: your form won't be available when the
    tables are on the web, so they can't reference it. I understand that. Still,
    since Access is converting the form to a web form, and the macro to XML (I
    assume) it seems there must be some way to pass a value along to the macro.
    But there isn't, apparently.

    All I want to do is insert a value from a text box into record field when I
    create a record using a data macro. Is that possible?

    Thanks!
     
    Neil, May 1, 2012
    #1
    1. Advertisements

  2. Neil

    Gloops Guest

    Re: Access 2010 Data Macros: The Three-Legged Hunting Dog With OneBad Eye

    Neil wrote, on 01st May 2012 18:35 UTC + 1 :
    > Wow, it's just amazing how difficult it is to do even the smallest of tasks
    > in data macros. Like, right now, I'm creating a record in a table, and need
    > to insert the date value from a text box on a form into a field of that
    > table. That seems to be impossible to do.
    >
    > I realize the response is going to be: your form won't be available when the
    > tables are on the web, so they can't reference it. I understand that. Still,
    > since Access is converting the form to a web form, and the macro to XML (I
    > assume) it seems there must be some way to pass a value along to the macro.
    > But there isn't, apparently.
    >
    > All I want to do is insert a value from a text box into record field when I
    > create a record using a data macro. Is that possible?
    >
    > Thanks!
    >
    >


    Hello,

    According to what you say above, your aim is not just to insert a value
    from a text box into a field in a record of a table. I imagine you
    already did that many times through VBA.

    So, the first thing to do is probably to precise exactly the technical
    context and constraints, as precisely as possible. After that, maybe
    somebody is able to tell you that you have to introduce a change in the
    technical context, or that it is not necessary.



    (Well, you said you wanted the answer duplicate on both
    comp.databases.ms-access, and microsoft.public.access, did not you ?
    This is the meaning of the absence of the field followup-to in your post.)
     
    Gloops, May 1, 2012
    #2
    1. Advertisements

  3. Neil

    Neil Guest

    "Gloops" <> wrote in message
    news:jnpdiu$5ov$...
    > Neil wrote, on 01st May 2012 18:35 UTC + 1 :
    >> Wow, it's just amazing how difficult it is to do even the smallest of
    >> tasks
    >> in data macros. Like, right now, I'm creating a record in a table, and
    >> need
    >> to insert the date value from a text box on a form into a field of that
    >> table. That seems to be impossible to do.
    >>
    >> I realize the response is going to be: your form won't be available when
    >> the
    >> tables are on the web, so they can't reference it. I understand that.
    >> Still,
    >> since Access is converting the form to a web form, and the macro to XML
    >> (I
    >> assume) it seems there must be some way to pass a value along to the
    >> macro.
    >> But there isn't, apparently.
    >>
    >> All I want to do is insert a value from a text box into record field
    >> when I
    >> create a record using a data macro. Is that possible?
    >>
    >> Thanks!
    >>
    >>

    >
    > Hello,
    >
    > According to what you say above, your aim is not just to insert a value
    > from a text box into a field in a record of a table. I imagine you already
    > did that many times through VBA.
    >
    > So, the first thing to do is probably to precise exactly the technical
    > context and constraints, as precisely as possible. After that, maybe
    > somebody is able to tell you that you have to introduce a change in the
    > technical context, or that it is not necessary.


    Well, I actually already found a workaround for my problem without doing the
    insert value into table record thing I was looking to do. Still, I find that
    annoyingly limited that I couldn't do that.

    I find, as I begin to work with web data macros, that at every step there's
    some severe limitation. Hence, the title of this thread. A three-legged
    hunting dog with one blind eye COULD be used for hunting; but is it really
    worth it?...

    So, no sooner had I found a workaround for this limitation (which was
    probably the 5th in a series of serious limitations) than I ran into another
    web query limitation that caused me to abandon what I was doing completely,
    and take an entirely different tack.

    See, I have a table of data, and I need to grab data for a particular day
    and give totals. Elementary, right? Only problem is: web queries don't have
    totals!!!! Ugh!!!! What????? A simple thing like that??

    So, Albert Kallal has been helpful, both here, as well as stuff he
    previously posted elsewhere on the web that I come across through Google.
    (I'd probably be lost without Albert. LOL) Anyway, in a thread I found,
    Albert says to just use a report, and do the totals in the report. Sounds
    great, especially since you can hide the detail section. Only thing is:
    Office 365 doesn't have Access Reporting enabled yet! So if I want to use a
    report, I have to use a different service.

    I swear, it's like I'm jumping through an obstacle course here, and just as
    I avoid landing on one obstacle, another gets you.

    Anyway, getting back to this, so I can't use reports unless I switch
    services. And, sure I can do totals in forms, putting Sum() in the form
    footer. But there's no grouping. And I have to have group totals.

    So, what am I left with? Compiling all my totals in client Access and
    uploading them to SharePoint, and just pulling the pre-calculated totals.

    Works fine as long as I only need to pull one day's worth of totals. But
    what happens when I need to pull a week's worth? Then I'll need to add up
    the daily totals, and I'm back to the same situation. Unless I can
    calculate the totals live from the web site, I'm basically stuck. And
    without the ability to do group totals in the form (or totals in the
    underlying query), I don't see any way I can do that.


    > (Well, you said you wanted the answer duplicate on both
    > comp.databases.ms-access, and microsoft.public.access, did not you ?
    > This is the meaning of the absence of the field followup-to in your post.)


    Yes, the point is to have a single thread that's updated in both forums,
    rather than having duplicate posts, one in each forum.

    Neil
     
    Neil, May 1, 2012
    #3
  4. Neil

    Gloops Guest

    Re: Access 2010 Data Macros: The Three-Legged Hunting Dog With OneBad Eye

    Well, you know, I am not trying to guess what you want to do with the
    sole clue of knowing how Albert did it.

    It works ? That is very fine.
     
    Gloops, May 2, 2012
    #4
  5. Neil

    Neil Guest

    "Gloops" <> wrote in message
    news:jnqhda$fbf$...
    > Well, you know, I am not trying to guess what you want to do with the sole
    > clue of knowing how Albert did it.
    >
    > It works ? That is very fine.


    I'm sorry. I was kind of rambling in that last post because I was kinda
    exhausted from hours of running into one obstacle after another and was very
    frustrated. After having slept, let me give a more clear explanation.

    There is a bunch of data in an Access app. The goal here is to give people
    the ability to open a browser and report on that data, either for a single
    day, or for a date range. Right now the reports we have in Access only
    report on a single day. But eventually we want to expand that into date
    range reports.

    OK, so going to SharePoint, the original idea was to upload the raw data to
    the web, and then construct the reports with summation values on the fly
    when the user requests it for a particular date or date range.

    My first attempt was to build temporary tables for the report, which is how
    I have it in client Access. Doing this I ran into numerous limitations when
    working with web data macros and queries. Most of them I overcame after a
    substantial amount of research and trial and error. The one that I posted
    about - being able to put a value from an unbound text box on a form into a
    newly-created record, I could not overcome.

    So then I realized I could probably rework my queries to not use temporary
    tables at all, but just query the data. Even though I find building a report
    using temporary tables to be a cleaner and more efficient method than using
    complex multi-layered queries, which sometimes bog down, this was doable.

    So I switched to doing that, so I wouldn't have to insert any values at all.

    However, no sooner had I switched to that approach than I realized that web
    queries don't allow totaling! This extremely basic and essential
    functionality was something I just assumed would be possible. But, no, not
    possible.

    So I was left with doing the totals in the form or report footer - which is
    fine except that, since I'm using Office 365, and Office 365 does not yet
    have Access Reporting, I could not use a report footer for the totals (which
    was Albert's solution, that I had previously mentioned); and, when trying to
    do totals in the form footer, I was unable to because I need group totals,
    not just report totals, and I don't know of a way to do group totals in a
    form. (If anyone has any techniques for doing that, I'd be interested in
    hearing.)

    So what I was left with was not uploading the raw data to SharePoint and
    doing calculations on the fly, but, rather, doing each day's calculations in
    the Access app, and then just uploading the calculated values for each day
    to SharePoint.

    That works fine, and allows the user to select any day and get the values.
    However, when the report is modified to be for a date range instead for a
    single day, I'll be back in the same situation. I'd have to sum up all
    totals for the date range, and there's apparently no way to do that in
    SharePoint when using forms and needing group totals.

    So what I ended up with was not a solution, but, rather, a temporary fix.
    Either I find a way to do group totals in SharePoint using forms, or I'll
    need to switch from Office 365 to a different SharePoint provider that does
    provide reporting, but which is much more expensive.

    So that's my still-unresolved situation.

    BTW, note to community (or whoever here uses Office 365, anyway): I spoke
    with a Microsoft technician at Office 365 yesterday, and asked if they were
    going to be getting Access Reporting anytime soon. She said, yes, they would
    be getting Access Reporting sometime in the future, but there is no ETA. She
    said that if enough people request it, it would probably push them along to
    getting it. So I suggest that anyone who is looking for that feature contact
    the Office 365 team and request (and keep requesting) Access Reporting
    (without being obnoxious about it, of course :) ).

    Neil
     
    Neil, May 2, 2012
    #5
  6. Neil

    Gloops Guest

    Re: Access 2010 Data Macros: The Three-Legged Hunting Dog With OneBad Eye

    Neil wrote, on 02nd May 2012 16:16 UTC + 1 :
    > "Gloops"<> wrote in message
    > news:jnqhda$fbf$...
    >> Well, you know, I am not trying to guess what you want to do with the sole
    >> clue of knowing how Albert did it.
    >>
    >> It works ? That is very fine.

    >
    > I'm sorry. I was kind of rambling in that last post because I was kinda
    > exhausted from hours of running into one obstacle after another and was very
    > frustrated. After having slept, let me give a more clear explanation.
    >
    > There is a bunch of data in an Access app. The goal here is to give people
    > the ability to open a browser and report on that data, either for a single
    > day, or for a date range. Right now the reports we have in Access only
    > report on a single day. But eventually we want to expand that into date
    > range reports.


    Hello,

    You know, to do just that (in fact I have to admit I read specially the
    beginning ;) ), I learned the asp.net platform, with SQL Server.

    Other solutions existed before, as compiling a program in VB6 to run the
    queries, and that program is supposed to receive the good parameters to
    run the queries. I read this solution in details a certain time ago, but
    had no occasion to apply it. A special attention must be brought to the
    security, for instance only use recorded queries.

    Perhaps the second solution will take you less education effort, but
    supposing you are going to have other applications to develop, being
    up-to-date has its own certain advantages.

    By the way, when you show data on an ASP page, with a dataset on an
    Access database, you have to know that the parameters are only known by
    their order, opposite to SQL Server or Oracle, that are able to
    recognize parameters by their names.

    I am afraid I cannot answer about Sharepoint.
     
    Gloops, May 2, 2012
    #6
  7. Neil

    Neil Guest

    "Gloops" <> wrote in message
    news:jnri1k$nsa$...
    > Neil wrote, on 02nd May 2012 16:16 UTC + 1 :
    >> "Gloops"<> wrote in message
    >> news:jnqhda$fbf$...
    >>> Well, you know, I am not trying to guess what you want to do with the
    >>> sole
    >>> clue of knowing how Albert did it.
    >>>
    >>> It works ? That is very fine.

    >>
    >> I'm sorry. I was kind of rambling in that last post because I was kinda
    >> exhausted from hours of running into one obstacle after another and was
    >> very
    >> frustrated. After having slept, let me give a more clear explanation.
    >>
    >> There is a bunch of data in an Access app. The goal here is to give
    >> people
    >> the ability to open a browser and report on that data, either for a
    >> single
    >> day, or for a date range. Right now the reports we have in Access only
    >> report on a single day. But eventually we want to expand that into date
    >> range reports.

    >
    > Hello,
    >
    > You know, to do just that (in fact I have to admit I read specially the
    > beginning ;) ), I learned the asp.net platform, with SQL Server.
    >
    > Other solutions existed before, as compiling a program in VB6 to run the
    > queries, and that program is supposed to receive the good parameters to
    > run the queries. I read this solution in details a certain time ago, but
    > had no occasion to apply it. A special attention must be brought to the
    > security, for instance only use recorded queries.
    >
    > Perhaps the second solution will take you less education effort, but
    > supposing you are going to have other applications to develop, being
    > up-to-date has its own certain advantages.
    >
    > By the way, when you show data on an ASP page, with a dataset on an Access
    > database, you have to know that the parameters are only known by their
    > order, opposite to SQL Server or Oracle, that are able to recognize
    > parameters by their names.
    >
    > I am afraid I cannot answer about Sharepoint.


    Thanks. I appreciate your input!

    Neil
     
    Neil, May 3, 2012
    #7
  8. "Neil" wrote in message news:jnp3f3$ebd$...

    >Like, right now, I'm creating a record in a table, and need to insert the
    >date value from a text box on a form into a field of that table. That seems
    >to be impossible to do.
    >



    it is quite easy.

    And as a side note anyone here ever use SQL server and write code for those
    store procedures?

    No debugger, and it is a rather lousy language.

    However, I been busy and was sick today. So I shall make a short post.

    I would love to tell one of my favorite "war" stories about how when came
    over to Access from having used FoxPro for many years. Us FoxPro people
    could not believe how stupid it was that Access did not have record numbers!
    I mean, data written out to a table ALWAYS maintained the order. Having
    written systems in Pascal, in FORTRAN, FoxPro, dBase and many others, they
    all had record numbers and order of data written out was always maintained.
    Heck writing data out to a comma delimited text file ALSO maintained order.

    All of sudden years of that type of thinking had to be tossed out when I
    adopted Access.

    So why would such a simple thing now become so difficult with Access? Of
    course the issue is as our industry moved towards a more abstracted out
    database system, and farther and farther way from punch cards and the actual
    file system on the disk drive, then these abstractions meant that the idea
    of order of data inside a database table and that of using record numbers
    simply were not appropriate designs for the needs of the industry and they
    actually became not useful concepts in the Data Processing industry anymore.

    In other words simple code designs that wrote data out to a table in a
    particular order had to be tossed out. All of the designs and methodology
    and things had been using for years all the sudden also had to be tossed
    out.

    At the end of the day it's an architecture change.

    Anyway, I only have a bit of time here, so while I'd love to wax eloquent
    about the FoxPro methodology change in which I had to go through when I
    started uses Access, but let me just post an answer here.


    >All I want to do is insert a value from a text box into record field when
    >I create a record using a data macro. Is that possible?


    Sure. No problem. And this does not matter if it is a bound form, or not.

    Here is a data macro in which I want to create a child record A invoice
    record, and I want to PASS the customer ID (the parent record PK) to this
    routine that will create a new child record for me.

    The code that does this is this:

    https://public.sn2.livefilestore.co...7kz-R_cSfhEQDHmdNzQ/createinvoice1.png?psid=1

    Note the use of a parameter to pass the value form the form. Also note how
    the above RETURNS BACK the new ID of the record just created (I need that in
    the form's code that calls the data macro).

    The form code while not important, is this :

    https://public.sn2.livefilestore.co...8-DcUfMCQmJjO1_kT0aTNkSQA/calldata.png?psid=1

    Note how the above references a sub form, checks for a invoice or checks if
    the current invoice date is NOT this month, then we call (and pass) the
    value of the customer ID to the data macro. The data macro creates a new
    invoice record, and RETURNS that PK value back to the calling form code of
    what the new record ID was created.

    So in above example:

    I pass a value to a data macro from a form. The data macro creates a new
    record in a table, and then as noted returns the PK value. You might not
    need to return values, but the above code snips shows how this works anyway.

    Just remember, if the value you grab is a un-bound text box and it suppose
    to be a number value, then set the format of the text box to general number.

    --
    Albert D. Kallal
    Edmonton, Alberta Canada
     
    Albert D. Kallal, May 3, 2012
    #8
  9. Neil

    Bob Barrows Guest

    Albert D. Kallal wrote:
    > "Neil" wrote in message news:jnp3f3$ebd$...
    >
    >> Like, right now, I'm creating a record in a table, and need to
    >> insert the date value from a text box on a form into a field of that
    >> table. That seems to be impossible to do.
    >>

    >
    >
    > it is quite easy.
    >
    > And as a side note anyone here ever use SQL server and write code for
    > those store procedures?
    >
    > No debugger, and it is a rather lousy language.


    Obsolete information. There absolutely is a debugger now, starting in SQL
    2005, when using VS. In SQL 2008, the debugger became available in SSMS when
    it was made clear to MS that developers weren't going to switch to a
    pardigm of using VS to design and SSMS to manage.And in prior versions of
    SS, third-party applications such as RapidSQL offered debuggers.

    For a "lousy" language, it's incredible how much can be done with it. Yeah,
    some things are easier in VBA (error-handling for one, but even VBA sucks at
    that - google for an article called "On Error GOTO Hell", but cursors are
    much easier to employ in VBA - some would call that a bad thing ... <g>),
    but there are things like string-handling that VBA does in a very
    inefficient way.
     
    Bob Barrows, May 3, 2012
    #9
  10. Neil

    Neil Guest


    >>All I want to do is insert a value from a text box into record field when
    >>I create a record using a data macro. Is that possible?

    >
    > Sure. No problem. And this does not matter if it is a bound form, or not.


    Well, if it were a bound form, then I'd be copying from the table, right?
    :) So, yes, an unbound control on a form.

    > Just remember, if the value you grab is a un-bound text box and it suppose
    > to be a number value, then set the format of the text box to general
    > number.


    It's an unbound text box that contains a date value. And, yes, the format of
    the text box was set to Date. But I could not get it to work. I'll look at
    it again with your code in mind. Thanks.

    --> Which actually brings up another question. Is there a way to get an
    error message when running data macros? I noticed that when it didn't work,
    it just didn't work. But I didn't get any error message. Is there a way to
    get a message that tells you exactly what the problem is (or at least that
    there is a problem?). Just having the macro run but not perform its task but
    without an error isn't a good idea.

    Anyway, getting back to this. I'll take another look at what I did wrong.
    But, as I noted in a subsequent post, I actually found a workaround to avoid
    having to do the insert. But then I ran into an even bigger problem: I could
    not do summations in web data queries! I suppose this is an advance in
    technology, like moving away from punch cards; but I don't see it as an
    advantage. ;-)

    Seriously, though, that was what triggered the title of this thread. Could
    data macros seriously not have summations? Is there any logical reason for
    that? Or has Microsoft just not gotten around to implementing them yet? That
    seems like a SERIOUS limitation!

    So I went searching around the inet, and found a post by you that addressed
    the issue. In it, you advised someone to just use a report and do the totals
    in the footer. And that's fine. Except that I'm using Office 365, and they
    haven't implemented Access reporting yet. (In another place, I believe here,
    you mentioned that you should be able to do with forms what you do with
    reports - which is true: AS LONG AS TOTALS ARE IN THE QUERY!! LOL :) ).

    In any case, so I found myself stuck once again. Sure, I could do the totals
    in the form footer. No problem. But I need group totals. And for that you
    need a report.

    So I'm kind of stuck.

    I ended up, as a temporary fix, to just do all the calculations in my Access
    app and upload the data already calculated to SharePoint. And that's fine.
    As long as the user only needs one day's worth of data (totals are per day).
    But when we modify the site to allow them a date range, then I'm back in the
    same place: no ability to do totaling because I need group totals, and
    there's no reporting in Office 365.

    So, without totals in queries (which I never DREAMED would be the case!!),
    I'm left with probably having to switch to a different service instead of
    Office 365. Unless you know of a way to do group totals in web forms. The
    data (in simplified format) looks something like this:

    Device Reading
    --------- --------
    Device 1 10
    Device 2 13
    Device 1 15
    Device 3 22
    Device 2 8
    Device 2 20
    Device 1 10
    Device 3 18

    etc.

    Just various listings with values for each. I need a total and average for
    each device.

    Now, sure, I could create 3 text boxes, and use something like
    Sum(IIF([Device]=Device1, [Reading], 0)) to get the sum for a particular
    device, etc. But that assumes that I know how many devices there will be. I
    don't.

    So, with reporting totals, I could just group by device and get a total in
    the group footer and have what I need. But with forms; and with no ability
    to total in the data query; I'm stuck.

    Thanks,

    Neil
     
    Neil, May 3, 2012
    #10
  11. Neil

    Neil Guest

    "Bob Barrows" <> wrote in message
    news:jntmg4$5bh$...
    > Albert D. Kallal wrote:
    >> "Neil" wrote in message news:jnp3f3$ebd$...
    >>
    >>> Like, right now, I'm creating a record in a table, and need to
    >>> insert the date value from a text box on a form into a field of that
    >>> table. That seems to be impossible to do.
    >>>

    >>
    >>
    >> it is quite easy.
    >>
    >> And as a side note anyone here ever use SQL server and write code for
    >> those store procedures?
    >>
    >> No debugger, and it is a rather lousy language.

    >
    > Obsolete information. There absolutely is a debugger now, starting in SQL
    > 2005, when using VS. In SQL 2008, the debugger became available in SSMS
    > when it was made clear to MS that developers weren't going to switch to a
    > pardigm of using VS to design and SSMS to manage.And in prior versions of
    > SS, third-party applications such as RapidSQL offered debuggers.
    >
    > For a "lousy" language, it's incredible how much can be done with it.
    > Yeah, some things are easier in VBA (error-handling for one, but even VBA
    > sucks at that - google for an article called "On Error GOTO Hell", but
    > cursors are much easier to employ in VBA - some would call that a bad
    > thing ... <g>), but there are things like string-handling that VBA does in
    > a very inefficient way.
    >


    Bob, PMFJI, but I looked up what you mentioned, and all I could find was
    this: http://www.vbforums.com/showthread.php?t=110745 where the person talks
    about having to put an On Error Goto into every routine he writes. Yeah,
    that's kind of a pain. I, personally, have my error handler block stored in
    a text file with the name eh.txt. Then, in Access, whenever I start a new
    routine, I just do alt, i, l, e, h, <Enter> (which accesses the insert menu,
    then insert file, then types "eh" as the name, and then enter selects it).
    So it's very quick.

    And I've found Access error handling to be very flexible and efficient.

    So I'm not sure what you mean by that VBA sucks at error handling. Perhaps
    you could elaborate....

    Thanks,

    Neil
     
    Neil, May 3, 2012
    #11
  12. "Bob Barrows" wrote in message news:jntmg4$5bh$...

    >> And as a side note anyone here ever use SQL server and write code for
    >> those store procedures?
    >>
    >> No debugger, and it is a rather lousy language.


    >Obsolete information. There absolutely is a debugger now, starting in SQL
    >2005, when using VS. In SQL 2008, the debugger became available in SSMS
    >when it was made clear to MS that developers weren't going to switch to a
    >pardigm of using VS to design and SSMS to manage.And in prior versions of
    >SS, third-party applications such as RapidSQL offered debuggers.


    Thanks for the heads up. I always encourage people to correct such
    statements.

    I should have really said I was talking about SSMS (SQL Server Management
    Studio).

    However, at the end of the day my point stands that for years and years with
    SSMS we did not have a "easy" debugging option, and having to adopt Visual
    Studio not such a simple nor light solution either.

    However, I was not aware that SSMS now can debug t-sql code. Regardless, as
    you point out there are options in this area now.

    >For a "lousy" language, it's incredible how much can be done with it. Yeah,
    >some things are easier in VBA (error-handling for one, but even VBA sucks
    >at that - google for an article called "On Error GOTO Hell"


    Yes, I do agree. t-sql is quite incredible.

    So perhaps lousy not the best term. What I mean to say is that T-sql is
    still what I would call a weaker "programming" language.

    However, t-sql is very much a procedural version of SQL and t-sql is rather
    at home using SQL.

    So this mix of procedural ability with the power of SQL is not something to
    scoff at or laugh at. And you can write scalar functions (that simply a
    user defined function that can be used in any sql expression just like we
    can with VBA). I often had to replace some custom VBA functions I was using
    in my Access SQL to now run server side. So far I always been able to
    reproduce those custom VBA functions used in my Access SQL with custom "UDF"
    (called scalar) functions in t-sql and this ability has helped huge in
    migration projects.

    In fact combining the non procedural ability of SQL with all its amazing
    power and THEN adding even a limited procedural ability results in a VERY
    powerful setup. And on top of this you can write UDF functions in t-sql
    also.


    To be fair, most store procedure code should not be that large of a chunk of
    code anyway.

    And to be fair such chunks of code hardly ever need to be that long and
    winding due to having SQL at one's disposal.


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, May 4, 2012
    #12
  13. "Neil" wrote in message news:jnu9g6$m4r$...

    >So, with reporting totals, I could just group by device and get a total in
    >the group footer and have what I need. But with forms; and with no ability
    >to total in the data query; I'm stuck.


    You could write a table trigger to always maintain and update the totals -
    they then be available at all times without the need for a group by query...

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, May 4, 2012
    #13
  14. Neil

    Neil Guest

    "Albert D. Kallal" <> wrote in message
    news:71Yor.176317$...
    > "Neil" wrote in message news:jnu9g6$m4r$...
    >
    >>So, with reporting totals, I could just group by device and get a total in
    >>the group footer and have what I need. But with forms; and with no ability
    >>to total in the data query; I'm stuck.

    >
    > You could write a table trigger to always maintain and update the totals -
    > they then be available at all times without the need for a group by
    > query...
    >


    No, that wouldn't work. If that were the case, then my workaround of just
    uploading the totals from the Access app would also work. But it doesn't.

    The problem is that the totals will be for a user-defined date range. So
    it's not possible to calculate them with a trigger or upload them from
    Access, because I don't know what totals will be needed. They may specify a
    7-day range, a month range, a two-week range, etc.

    With the very basic functionality of having totals in queries, this would be
    a no-brainer - a simple where clause in a group by query. But now it's a
    problem - one solved only (apparently) by moving to a much more expensive
    platform and using report footers for totaling.

    I am curious if you know the reason they don't have totals in web queries.
    Is this a technological limitation, or has Microsoft just not gotten around
    to implementing it yet?

    Also, did you know about the error thing I asked about? Is there a way to
    get an error message when running data macros? That is, a way to get a
    message that tells you exactly what the problem is (or at least that there
    is a problem)?

    Thanks, Albert!

    Neil
     
    Neil, May 5, 2012
    #14
  15. Neil

    Rob Parker Guest

    Hi Neil,

    A little off-topic, but since you're handling your error handler code in
    such a clunky fashion, I figure it's worth mentioning.

    There's a freeware add-in called MZ Tools that's a must for any serious
    coder. It does heaps of useful thing; one of which is to add in a
    standard - customisable - error handler in any module by a sinlge
    button-click in its toolbar. You can find it at www.mztools.com, and it
    will save you hours of effort in no time at all, for all sorts of common
    tasks during code development.

    HTH,

    Rob


    "Neil" <> wrote in message
    news:jnua42$qc0$...
    >

    <snip>
    > Bob, PMFJI, but I looked up what you mentioned, and all I could find was
    > this: http://www.vbforums.com/showthread.php?t=110745 where the person
    > talks about having to put an On Error Goto into every routine he writes.
    > Yeah, that's kind of a pain. I, personally, have my error handler block
    > stored in a text file with the name eh.txt. Then, in Access, whenever I
    > start a new routine, I just do alt, i, l, e, h, <Enter> (which accesses
    > the insert menu, then insert file, then types "eh" as the name, and then
    > enter selects it). So it's very quick.

    <snip>
     
    Rob Parker, May 5, 2012
    #15
  16. Neil

    Neil Guest

    "Rob Parker" <NO robpparker SPAM @ FOR optusnet.com.au ME> wrote in message
    news:jo21vj$q9u$...
    > Hi Neil,
    >
    > A little off-topic, but since you're handling your error handler code in
    > such a clunky fashion, I figure it's worth mentioning.
    >
    > There's a freeware add-in called MZ Tools that's a must for any serious
    > coder. It does heaps of useful thing; one of which is to add in a
    > standard - customisable - error handler in any module by a sinlge
    > button-click in its toolbar. You can find it at www.mztools.com, and it
    > will save you hours of effort in no time at all, for all sorts of common
    > tasks during code development.
    >
    > HTH,
    >
    > Rob
    >
    >
    > "Neil" <> wrote in message
    > news:jnua42$qc0$...
    >>

    > <snip>
    >> Bob, PMFJI, but I looked up what you mentioned, and all I could find was
    >> this: http://www.vbforums.com/showthread.php?t=110745 where the person
    >> talks about having to put an On Error Goto into every routine he writes.
    >> Yeah, that's kind of a pain. I, personally, have my error handler block
    >> stored in a text file with the name eh.txt. Then, in Access, whenever I
    >> start a new routine, I just do alt, i, l, e, h, <Enter> (which accesses
    >> the insert menu, then insert file, then types "eh" as the name, and then
    >> enter selects it). So it's very quick.

    > <snip>
    >


    OK, thanks.
     
    Neil, May 5, 2012
    #16
  17. Neil

    Neil Guest

    "Albert D. Kallal" <> wrote in message
    news:71Yor.176317$...
    > "Neil" wrote in message news:jnu9g6$m4r$...
    >
    >>So, with reporting totals, I could just group by device and get a total in
    >>the group footer and have what I need. But with forms; and with no ability
    >>to total in the data query; I'm stuck.

    >
    > You could write a table trigger to always maintain and update the totals -
    > they then be available at all times without the need for a group by
    > query...
    >


    BTW, previously I said that I could do something like
    Sum(IIF([Device]=Device1, [Reading], 0)) to simulate group totals in the
    form footer, if I knew how many devices there would be. Well, I decided
    that, given the parameters of the data, I could make certain assumptions
    about the number of devices, and then just use different forms for different
    situations where the number of devices was different.

    So, encouraged, I decided to plod along, creating my totals on the fly in
    the form footer, using IIF statements to give me group totals. And, despite
    other limitations that I had to spend an hour doing something that normally
    would have taken me less than a minute to do, I got it to work! Woot!!

    But, as has been the case so often in this journey, once I overcome one
    hurdle, a fresh new one is staring me in the face, ruthlessly mocking me,
    taunting me, _daring me_ to try and overcome it. But this one seems
    unovercomable.

    And this one is, again, something that I assumed would be there (something I
    realized one should NEVER do with Access web databases!!!). That is, I
    assumed that it would be possible to make the detail section of the form
    invisible. But, again, I was proven wrong. You can't do that with an Access
    web form. You can do it with an Access web report; but not a form.

    So this is the end of the road, I'm afraid. I can't present the totals only
    without presenting all the underlying data, since I can't hide the Detail
    section of the form. So I'm stuck with either:

    1) Calculate the totals in Access and upload them to the web, and base the
    form on the totals table (problem here is that I have to know ahead of time
    the date range for the totals, which I don't, or else limit it to one day);
    or

    2) Move from Office 365 to a more expensive service that provides Access
    reporting in SharePoint (the simple solution, but a disappointing one, since
    it wouldn't be necessary if Office 365 provided reporting, or if Access web
    queries provided totals.

    I suppose one last option remains, one which I hadn't considered and haven't
    yet explored: use a loop in a data macro to calculate the totals based on
    the specified date range. I don't know if that would work, or what other
    issues I'd run into.
     
    Neil, May 5, 2012
    #17
  18. Neil

    Bob Barrows Guest

    Albert D. Kallal wrote:
    >
    > So this mix of procedural ability with the power of SQL is not
    > something to scoff at or laugh at. And you can write scalar
    > functions (that simply a user defined function that can be used in
    > any sql expression just like we can with VBA).


    Scalar functions tend to be avoided, especially against large data sets, due
    to their impact on performance. The more powerful table-valued functions are
    preferred

    > I often had to
    > replace some custom VBA functions I was using in my Access SQL to now
    > run server side. So far I always been able to reproduce those custom
    > VBA functions used in my Access SQL with custom "UDF" (called scalar)
    > functions in t-sql and this ability has helped huge in migration
    > projects.
    > In fact combining the non procedural ability of SQL with all its
    > amazing power and THEN adding even a limited procedural ability
    > results in a VERY powerful setup. And on top of this you can write
    > UDF functions in t-sql also.
    >
    >

    There is also now the possiblility of writing CLR functions and procedures
    (introduced in SQL 2005), which, combined with the addition of new
    TRY...CATCH error-handling, table variables, DATE and TIME datatypes, and
    other keywords, allow t-sql to be compared very comfortably with VBA. There
    is no longer any pain involved with switching to SQL Server beyond the pain
    of replacing VBA keywords and functions with the t-sql equivalents.
     
    Bob Barrows, May 5, 2012
    #18
  19. Re: Access 2010 Data Macros: The Three-Legged Hunting Dog With OneBad Eye

    On May 5, 12:04 am, "Neil" <> wrote:
    > "Albert D. Kallal" <> wrote in messagenews:71Yor.176317$...
    >
    > > "Neil"  wrote in messagenews:jnu9g6$m4r$...

    >
    > >>So, with reporting totals, I could just group by device and get a totalin
    > >>the group footer and have what I need. But with forms; and with no ability
    > >>to total in the data query; I'm stuck.

    >
    > > You could write a table trigger to always maintain and update the totals -
    > > they then be available at all times without the need for a group by
    > > query...

    >
    > BTW, previously I said that I could do something like
    > Sum(IIF([Device]=Device1, [Reading], 0)) to simulate group totals in the
    > form footer, if I knew how many devices there would be. Well, I decided
    > that, given the parameters of the data, I could make certain assumptions
    > about the number of devices, and then just use different forms for different
    > situations where the number of devices was different.
    >
    > So, encouraged, I decided to plod along, creating my totals on the fly in
    > the form footer, using IIF statements to give me group totals. And, despite
    > other limitations that I had to spend an hour doing something that normally
    > would have taken me less than a minute to do, I got it to work! Woot!!
    >
    > But, as has been the case so often in this journey, once I overcome one
    > hurdle, a fresh new one is staring me in the face, ruthlessly mocking me,
    > taunting me, _daring me_ to try and overcome it. But this one seems
    > unovercomable.
    >
    > And this one is, again, something that I assumed would be there (something I
    > realized one should NEVER do with Access web databases!!!). That is, I
    > assumed that it would be possible to make the detail section of the form
    > invisible. But, again, I was proven wrong. You can't do that with an Access
    > web form. You can do it with an Access web report; but not a form.
    >
    > So this is the end of the road, I'm afraid. I can't present the totals only
    > without presenting all the underlying data, since I can't hide the Detail
    > section of the form. So I'm stuck with either:
    >
    > 1) Calculate the totals in Access and upload them to the web, and base the
    > form on the totals table (problem here is that I have to know ahead of time
    > the date range for the totals, which I don't, or else limit it to one day);
    > or
    >
    > 2) Move from Office 365 to a more expensive service that provides Access
    > reporting in SharePoint (the simple solution, but a disappointing one, since
    > it wouldn't be necessary if Office 365 provided reporting, or if Access web
    > queries provided totals.
    >
    > I suppose one last option remains, one which I hadn't considered and haven't
    > yet explored: use a loop in a data macro to calculate the totals based on
    > the specified date range. I don't know if that would work, or what other
    > issues I'd run into.


    The tale of your mental anguish both entertains me and I laugh while I
    also recall times of code frustration and share your pain and
    sympathize with your plight.
     
    Patrick Finucane, May 5, 2012
    #19
  20. "Bob Barrows" wrote in message news:jo34km$l8r$...


    >There is also now the possiblility of writing CLR functions and procedures
    >(introduced in SQL 2005), which, combined with the addition of new
    >TRY...CATCH error-handling, table variables, DATE and TIME datatypes, and
    >other keywords, allow t-sql to be compared very comfortably with VBA. There
    >is no longer any pain involved with switching to SQL Server beyond the pain
    >of replacing VBA keywords and functions with the t-sql equivalents.


    I am a VERY big believer in the concept of CLR and assemblies for SQL
    server.

    In fact I waited nearly 10 years for this ability. And I dare say that most
    in the SQL server world are not too keen on this idea, but I am. This is a
    two tier (as opposed to 3 tier setup), but I love this concept.

    The reasons for having the database server consume and utilize a "decent"
    coding system are many, but one simple reason is the ability to leverage
    one's vb.net or c# skills to write server side procedure code for SQL server
    and not have to adopt t-sql.

    And there also a few other REALLY amazing things that bubble up as a result
    of the CLR support with SQL server and I will in some future point post and
    explain why this setup is beyond outer space cool and better then taking a
    trip to a planet that is only populated by beautiful alien women models.

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, May 6, 2012
    #20
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    2
    Views:
    368
    Brendan Reynolds
    Aug 30, 2005
  2. Guest

    Forms - Eye Candy

    Guest, Dec 9, 2005, in forum: Microsoft Access
    Replies:
    4
    Views:
    296
    RobFMS
    Dec 10, 2005
  3. Guest

    how do i get a picture of fox hunting

    Guest, Apr 25, 2006, in forum: Microsoft Access
    Replies:
    1
    Views:
    217
    Cindy Winegarden
    Apr 25, 2006
  4. Tim
    Replies:
    1
    Views:
    197
    John Spencer
    Jan 8, 2010
  5. amach

    Access 2010 very slow with Sharepoint 2010

    amach, May 27, 2010, in forum: Microsoft Access
    Replies:
    1
    Views:
    853
    David W. Fenton
    May 29, 2010
Loading...

Share This Page