This is basically a macro I guess but I'm so rusty I can't quite think how
to start.

When my form opens focus is set to a textbox in the first row within a
subform.

I just want to calculate the difference between two other columns (also
displayed on the subform) and then have that figure be automatically
entered. This will be the same as if a person was entering the data (the
afterupdate code would then run which is important).

Then I want the return key to be pressed so that the focus shifts to the
textbox in the next row. Then repeat til we hit the bottom and trap that
error.

Ultimately I want to set this in motion from a command button and the form
will be opened and then shut invisibly.

The reason for the automation is to quickly fill out these figures when
we're in a hurry. Normally the user will do it line by line but this
scenario needs to be supported.

Any quick tips on how to proceed?


thanks

What are you trying to accomplish by calculating and storing into a Text Box
Control on the Form embedded in the Subform Control? The simple way is to
use a Query as the Record Source of the Form embedded in the Subform
Control... calculate that value in the query.

BTW, if the Form in the Subform Control has a Table as a Record Source, and
you are storing the value calculated from two other Fields in that Field of
the Table, that's a violation of Relational Database design principles and
will, sooner or later, cause you a lot of problems.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"buckskin" <buckskin (AT) mailinator (DOT) com> wrote

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

No it's not that bad. The subform has a table as the source and the field
I'm talking about is simply for user input. They can type whatever they
like into it as long as it's a value. This is the basis of the program.

The other two values are simply Budget and Actual. The user evaluates these
and then they enter a Projection into the text box which produces an overall
total for the line.

All of this works just fine.

However I have a scenario for the advanced user who simply needs to produce
a report very quickly without performing any input.
So for each line we'll look at Budget and Actual. For instance if the
budget was $1000 and we've spent $200 so far I want $800 as a projection
from now til the end of the year.
Thus I want to automatically add $800 into the textbox, hit enter and repeat
with the next row.

I know I could manipulate the table source directly but I need to have the
textbox afterupdate code to fire - I just need to automate entry into the
form and all will be well.

Help me understand... is this simply a field for user entry and the
calculation you describe just a 'default value', or is the user entry into
the other two fields you mention, is the value stored in the table, are all
three values stored in the table?

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"buckskin" <buckskin (AT) mailinator (DOT) com> wrote

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

Well I'm not sure how to make it clearer but i'll try!

The table has all the data and this is bound to the subform. It's all
displayed in a continuous form. Imagine an Excel spreadsheet.

The user has this single textbox that they can tap into. That is bound to
the table as well. So they type in a value and it's written to the table.

That's the app. That's all it does (well that's a lie but let's keep this
simple).

Let's imagine there are 100 rows. The user opens the app, looks at the
various columns of which principally there are Budget and Actual. She goes
"Oooh. I have spend $450 on pencils to date and my budget says I have $700
in total. I'll assume I might overspend and I shall enter the figure of
$400. The final column now shows a forecast of $850.

She does this for all 100 rows and can now run reports. It's totally up to
her what she types in.

Now SuperUser comes along and wants to whip through this process very
quickly. She doesn't want to look at all these pesky rows. She's happy if
the projections are automatically calculated for her.

Now an auto-calculation can't make intelligent decisions about what the
figures might or should be. However we can simply subtract Actual from the
Budget for each line.
In the above example that would be $250. That figure is entered into the
projection textbox as though the user were there tapping away.

(I don't want to consider whether this is "accurate or not". The logic of
the database is something else altogether.)

We then move onto the next row and repeat til we hit the end. To do all
this the user will click a button on another form and then we open the
target form invisibly and display a Please Wait notice while all this is
going on. When done the form is closed and we're back at the starting form.

"Ed J." <ed (AT) smbpartners (DOT) com> wrote



Er no, not quite. If it was that simple I'd have it covered, thanks though.

I guess I'm not making myself understood and I am trying quite hard!

This is mostly about form automation. Had a mental breakthrough last night
and assuming I get this done today I shall reply to my own post with the
solution for sake of anyone else.

"I know I could manipulate the table source directly but I need to have the
textbox afterupdate code to fire - I just need to automate entry into the
form and all will be well"

You say that the target form wouldn't be visible to the super user and I'm
deducing that you don't want to manipulate the table data directly as you
want to fire the text box AfterUpdate events one the invisible target form?

Firstly I think I'm right in saying if you update a control in code it's
AfterUpdate doesn't fire anyway so I don't think this approach is going to
work for you.

"Ultimately I want to set this in motion from a command button..."

So why can't you just update the table directly and run whatever code you
had in the target form's TextBox AfterUpdate from this command button
(obviously making the code available outside of the form's class module)?

Opening a form invisibly just to manipulate it's underlying data or to run
code that happens to be in the form's class module is just wrong. I can't
think of any scenario where this would be necessary unless you can enlighten
us!


HTH







"buckskin" <buckskin (AT) mailinator (DOT) com> wrote

I agree with Jon. You are cruisin' toward a bruisin' here, because
calculating by automating form operations whether by macros or VBA code is
just not the way to try to use Access, and it will cause you grief later.
And, it's not clear just what nor why you are storing data, but then
calculating it for 'Superuser'. I perceive what you write as a detailed
description of how you think something should be done, not the 'what are you
trying to accomplish' that I asked for. It also seems that you are
overriding what the user has entered, at least sometimes, and are using a
field in a table for temporary storage... and that you are determined that
you are going to persist in asking how to make 'your way' correct.

If you'd back off, describe what data you have, and what you want to
accomplish, it's possible that someone might have useful suggestions;
otherwise, further discussion is likely fruitless.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

Jon's suggestion sounded interesting and I will explore it but I disagree
with the rest of what you're saying.

Basically all I'm fundamentally asking for is how to move down a row and
enter a figure. That's it. This is nothing to do with calculated fields
or, and I have no idea where this one came from, "usings a field in a table
for temporary storage". At no point did I say that.
BTW is there something wrong with sharing this knowledge? Am I only allowed
to know something as long as I'm not going to offend the Access Gods?

And you say "it's not clear, just what nor why you are storing data". Come
on Larry, after all my explanations I can only believe you're starting to
troll me.

As I've repeatedly said this is an alternate user scenario. If you don't
want to help that's fine but please put aside the judgemental tone. I've
seen threads here (entertaining ones mind) where people are ranting and
raving at each other about how something "should" be done in Access. I'm
not interested in that level of discussion.

Thanks again

"Ed J." <ed (AT) smbpartners (DOT) com> wrote


Because initially I thought it *had* to be done through manipulating to form
but it might be possible to do it as you suggest if I can control the other
events that normally happen when a user enters a figure into that textbox.
Jon's suggestion re that made me rethink the possibility.

Thanks for your contribution, others might do well to learn from that.