Sendkeys does not work after switching to Windows Vista

[Tony L.]

My invoicing Forms run well in Access 2003 in Windows XP. On the operator's
input of the item code (one by one), DLookup gets the item name and price;
the total for that item is displayed after multiplying the price and quantity
and discount; finally the Grand Total is displayed. The same steps are
repeated as the operator inputs the other items.

But on switching to Windows Vista, the Grand Total does not add up.
Obviously the 'Sendkeys' function is not working properly. Extracted below
are my codings. Grateful for help please.

---------------------------------------------------------------
Private Sub MCode_AfterUpdate()
Dim mydb As Database, myrs As Recordset
Set mydb = CurrentDb()
Set myrs = mydb.OpenRecordset("Select [Inventory].* From [Inventory]
Order by [Inventory].

", dbOpenDynaset)
myrs.FindFirst "[Code] = '" & Me.mCode & "'"
If Not myrs.NoMatch Then
mDescription = myrs("[Name1]")
mUnitPrice = myrs("[Selling Price]")
DisplayPrice
Else
MsgBox " Code Not Registered ... Re-enter Code Please "
End If
myrs.Close
End Sub

Private Function DisplayPrice()
mTotalUnitPrice = mUnitPrice * mUnitDiscount * mQuantity
SendKeys "+{ENTER}", True
DisplayTotal
End Function

Private Function DisplayTotal()
Dim myset As Recordset, mtot As Double
Set myset = Me.RecordsetClone
If myset.RecordCount <= 1 Then
Me.Parent.mTotal = Me.mTotalUnitPrice
Else
myset.MoveFirst
mtot = 0
Do While Not myset.EOF
mtot = myset("[Total Unit Price]") + mtot
myset.MoveNext
Loop
Me.Parent.mTotal = mtot
End If
End Function
---------------------------------------------------------------

 

[Stefan Hoffmann]

hi Tony,

But on switching to Windows Vista, the Grand Total does not add up.
Obviously the 'Sendkeys' function is not working properly. Extracted below
are my codings.

It is blocked by Vista. You should rewrite your code. btw, why don't you
use a query to calculate your sums?


mfG
--> stefan <--

 

[Tony L.]

Dear Stefan

Thanks for your prompt reply.

Well, since it is blocked by Vista, is there a way to de-block it? Hope
this is not too stupid a question, but I guess there is not unless the
codings are rewritten. And I cannot think of any other function besides
Sendkeys. Would you kindly suggest a way please?

On using a query as an alternative, the situation is this. My item details
(Code, Name, Unit Price, Quantity, Discount and Unit Total) are tabulated on
a sub-form embedded in a main form; and the Grand Total for all items
invoiced is to be displayed in an unbound field (mTotal) in the main form.
I have tried using queries also, but difficulty remains that the calculated
Grand Total cannot be sent to show up on the main form.

Help very much appreciated.

Tony

 

[John W. Vinson]

Dear Stefan

Thanks for your prompt reply.

Well, since it is blocked by Vista, is there a way to de-block it? Hope
this is not too stupid a question, but I guess there is not unless the
codings are rewritten. And I cannot think of any other function besides
Sendkeys. Would you kindly suggest a way please?

On using a query as an alternative, the situation is this. My item details
(Code, Name, Unit Price, Quantity, Discount and Unit Total) are tabulated on
a sub-form embedded in a main form; and the Grand Total for all items
invoiced is to be displayed in an unbound field (mTotal) in the main form.
I have tried using queries also, but difficulty remains that the calculated
Grand Total cannot be sent to show up on the main form.

Sendkeys is not necessary to do so, and has not been reliable or safe for
years: you cannot guarantee *which application* might receive the keystroke,
if the user has multiple apps open. There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

In your example, you can display the value of the grand total on the mainform
by simply setting the Control Source of a mainform textbox to

=subformname.Form!grandtotaltextboxname

If you're trying to store the grand total in a field in the main form's
recordsource... don't. Storing derived data such as this in your table
accomplishes three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and most importantly,
it risks data corruption. If one of the underlying fields is subsequently
edited, you will have data in your table WHICH IS WRONG, and no automatic way
to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it - in the control source of a Form or
a Report textbox.

 

[Tony L.]

Dear John

I have no idea that Sendkeys can be so unsafe. Many thanks for the warning.
I cannot agree with you more regarding the useful advice on not to store the
grand calculated total into recordsource.

I removed the lines on 'Sendkeys' and 'DisplayTotal' from my DisplayPrice
function as follows :
Private Function DisplayPrice() ............
mTotalUnitPrice = mUnitPrice * mUnitDiscount * mQuantity
' SendKeys "+{ENTER}", True
' DisplayTotal
End Function .......................................
and added a textbox named mTotal in the mainform which Control Source
=[Sales Book Details].[Form]![Text25]. But then error (#Name?) appears. Any
ideas please.

Tony

 

[John W. Vinson]

Dear John

I have no idea that Sendkeys can be so unsafe. Many thanks for the warning.
I cannot agree with you more regarding the useful advice on not to store the
grand calculated total into recordsource.

What's your rationale? Do you have a DEMONSTRATED (not assumed) intolerable
perforamance penalty for calculating the sum, or will you in fact want to
store the calculated sum in a way that can be edited independent of the values
which go into the sum?

I removed the lines on 'Sendkeys' and 'DisplayTotal' from my DisplayPrice
function as follows :
Private Function DisplayPrice() ............
mTotalUnitPrice = mUnitPrice * mUnitDiscount * mQuantity
' SendKeys "+{ENTER}", True

If the purpose is just to save the record use

Me.Dirty = False

or

DoCmd.RunCommand acCmdSaveRecord

in place of the sendkeys line.

' DisplayTotal
End Function .......................................
and added a textbox named mTotal in the mainform which Control Source
=[Sales Book Details].[Form]![Text25]. But then error (#Name?) appears. Any
ideas please.

Is [Sales Book Details] the name *of the Subform control* (correct) or the
name of the Form being used as the SourceObject of the subform (incorrect)?

 

[Peter Doering]

[Sendkeys]

[...] There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

John, there is one thing: Show/hide the ribbon by VBA in Access 2007. ;-)

Otherwise full ack.

 

[John W. Vinson]

[...] There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

John, there is one thing: Show/hide the ribbon by VBA in Access 2007. ;-)

Otherwise full ack.

Eep. Haven't run into that in my limited use of 2007... and I'd consider that
a flat-out bug, or at the very best a bad misfeature.

 

[Larry Linson]

[Sendkeys]

[...] There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

John, there is one thing: Show/hide the ribbon by VBA in Access 2007. ;-)

Otherwise full ack.

Peter -- Every one of my paying customers/clients has a better way than
SendKeys to hide the Ribbon: they run Access 2003 and spit in the general
direction of Office 2007 and Ribbons.

I have recently been working remotely on a project where some of the
participants are using Office 2007 and some are using Office 2003. My
observation is that the participants who have experienced things "that don't
seem to work, or don't seem to work as well," on that project are the ones
using Access 2007. And, while we know that the Ribbon isn't likely to go
away in the next version of Office, some are hoping that next version will
have a significant bug-fix component as Access 97 did to fix Access 95's
shortcomings.

Larry

 

[Tony L.]

Dear John

My prime purpose is to list out on the display monitor the x pieces of items
being sold, namely: (a) item code , (b) name, (c) unit price, (d) quantity,
(e) discount, (f) total unit price; and secondly show the grand total price
for the x pieces of items --- the salesman can then tell the customer the
amount due. All totals need not be stored in record or database because
re-calculation will be done separately in subsequent invoice or report
printing.

[Sales Book Menu] is the main form.

[Sales Book Details] is the subform embedded in the main form.

"mTotalUnitPrice" is the textbox (on the subform) for showing the Total Unit
Price for each of the items --- (c) x (d) x (e) = (f).

"mTotal" is the textbox (on the main form) for showing the Grand Total Price
(i.e. sum of the (f)’s of all the items).

I also tried an alternative of showing the Grand Total Price in a textbox
(control source =Sum([Total Unit Price]) at the subform footer. But that
neither works.

Advice appreciated please.

Tony

Dear John

I have no idea that Sendkeys can be so unsafe. Many thanks for the warning.
I cannot agree with you more regarding the useful advice on not to store the
grand calculated total into recordsource.

What's your rationale? Do you have a DEMONSTRATED (not assumed) intolerable
perforamance penalty for calculating the sum, or will you in fact want to
store the calculated sum in a way that can be edited independent of the values
which go into the sum?

I removed the lines on 'Sendkeys' and 'DisplayTotal' from my DisplayPrice
function as follows :
Private Function DisplayPrice() ............
mTotalUnitPrice = mUnitPrice * mUnitDiscount * mQuantity
' SendKeys "+{ENTER}", True

If the purpose is just to save the record use

Me.Dirty = False

or

DoCmd.RunCommand acCmdSaveRecord

in place of the sendkeys line.

' DisplayTotal
End Function .......................................
and added a textbox named mTotal in the mainform which Control Source
=[Sales Book Details].[Form]![Text25]. But then error (#Name?) appears. Any
ideas please.

Is [Sales Book Details] the name *of the Subform control* (correct) or the
name of the Form being used as the SourceObject of the subform (incorrect)?

 

[Peter Doering]

[...] There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

John, there is one thing: Show/hide the ribbon by VBA in Access 2007. ;-)

Otherwise full ack.

Eep. Haven't run into that in my limited use of 2007... and I'd consider that
a flat-out bug, or at the very best a bad misfeature.

Let's say, a property (hidden=false/true) missing in VBA. BTW, it seems
from your and Larry's answers, I've hit a sensitive area, have I? <g>

 

[Tony L.]

Dear John

Further to my post of Oct 7 please.

My prime purpose is to list out on the display monitor the x pieces of items
being sold, namely: (a) item code , (b) name, (c) unit price, (d) quantity,
(e) discount, (f) total unit price; and secondly show the grand total price
for the x pieces of items --- the salesman can then tell the customer the
amount due. All totals need not be stored in record or database because
re-calculation will be done separately in subsequent invoice or report
printing.

[Sales Book Menu] is the main form.
[Sales Book Details] is the subform embedded in the main form.
"mTotalUnitPrice" is the textbox (on the subform) for showing the Total Unit
Price for each of the items --- (c) x (d) x (e) = (f).
"mTotal" is the textbox (on the main form) for showing the Grand Total Price
(i.e. sum of the (f)’s of all the items).

I also tried an alternative of showing the Grand Total Price in a textbox
Text123
(control source =Sum([Total Unit Price]) at the subform footer. But that
neither works. But putting back the Sendkeys line, Text123 can show the
grand total price.

Would you gentlemen kindly advise on a way to help me get rid of the risky
Sendkeys but show the grand total please.

Tony

Tony L. wrote:

[Sendkeys]

[...] There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

John, there is one thing: Show/hide the ribbon by VBA in Access 2007. ;-)

Otherwise full ack.

Peter -- Every one of my paying customers/clients has a better way than
SendKeys to hide the Ribbon: they run Access 2003 and spit in the general
direction of Office 2007 and Ribbons.

I have recently been working remotely on a project where some of the
participants are using Office 2007 and some are using Office 2003. My
observation is that the participants who have experienced things "that don't
seem to work, or don't seem to work as well," on that project are the ones
using Access 2007. And, while we know that the Ribbon isn't likely to go
away in the next version of Office, some are hoping that next version will
have a significant bug-fix component as Access 97 did to fix Access 95's
shortcomings.

Larry

 

[Larry Linson]

To my customers/clients, Access 2007 with Ribbon is indeed a "sensitive
issue". For all the hoopla and hoorah about how easy it is for _beginners_,
they understand the cost of re-training _experienced users_, and even more,
the cost of the interface becoming "second nature" to those experienced
users (some of whom have, now, 15 years of accumulated "muscle memory" of
the location of menu items and commands which haven't changed all that much
in Office and Access over the years).

And, if they aren't using it, nor intending to use it, then I don't have a
great deal of incentive to spend time cuddling up to it, either.

Larry

[...] There is NOTHING that can be done with
sendkeys that cannot be done better in some other way.

John, there is one thing: Show/hide the ribbon by VBA in Access 2007.
;-)

Otherwise full ack.

Eep. Haven't run into that in my limited use of 2007... and I'd consider
that
a flat-out bug, or at the very best a bad misfeature.

Let's say, a property (hidden=false/true) missing in VBA. BTW, it seems
from your and Larry's answers, I've hit a sensitive area, have I? <g>