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 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" <(E-Mail Removed)> wrote in message
news:jnpdiu$5ov$(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:jnqhda$fbf$(E-Mail Removed)...
> 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 wrote, on 02nd May 2012 16:16 UTC + 1 :
> "Gloops"<(E-Mail Removed)> wrote in message
> news:jnqhda$fbf$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:jnri1k$nsa$(E-Mail Removed)...
> Neil wrote, on 02nd May 2012 16:16 UTC + 1 :
>> "Gloops"<(E-Mail Removed)> wrote in message
>> news:jnqhda$fbf$(E-Mail Removed)...
>>> 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" wrote in message news:jnp3f3$ebd$(E-Mail Removed)...

>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.com...ce1.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.com...ata.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
(E-Mail Removed)

Albert D. Kallal wrote:
> "Neil" wrote in message news:jnp3f3$ebd$(E-Mail Removed)...
>
>> 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.

>>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