SendKeys

[Bald Eagle]

I have developed a new Database on my PC using Access 2002 with Windows XP.
When I try to run it on a laptop using the same version of Access but with
Windows Vista I get problems. In a couple of Macros my SendKeys instructions
fail - I am changing a field type from Number to Currency.

Is this something to do with the operating system? Or is there some other
reason?

 

[Robert Morley]

Yes, it's very much something to do with Vista. Karl Petersen, a popular
poster in some of the VB6 newsgroups, has developed a workaround...you can
find it here: http://vb.mvps.org/samples/project.asp?id=sendinput


Rob

 

[Marshall Barton]

I have developed a new Database on my PC using Access 2002 with Windows XP.
When I try to run it on a laptop using the same version of Access but with
Windows Vista I get problems. In a couple of Macros my SendKeys instructions
fail - I am changing a field type from Number to Currency.

Is this something to do with the operating system? Or is there some other
reason?

That is just one of the problems with SendKeys. Actually, I
think there are so many problems with SendKeys that Vista
all but disables it.

Bottom line: find another way to do whatever it is you are
trying to do.

 

[Bald Eagle]

This looks promising. Thanks for the lead. I assume from this that I can't
do something within the Macro to get round the problem? Do I have to resort
to Code? I am not an expert! Is it possible in some way to get the Macro to
pause for a couple of seconds in order to get the SendKeys instruction to
work before continuing with the rest of the Macro?

 

[Robert Morley]

I'm not sure...it's been so long since I've used Macros, I couldn't tell
you. It'd be a truly dumb way to do it, but if nothing else presents
itself, you could try something like opening and closing a dummy form or
similar delaying tactic.


Rob

 

[John W. Vinson]

This looks promising. Thanks for the lead. I assume from this that I can't
do something within the Macro to get round the problem? Do I have to resort
to Code? I am not an expert!

Well... you have a whole bunch of experts here who can help.

Sendkeys is VERY buggy and VERY unreliable and totally uncontrollable. You
could have some other application open - your Microsoft Money connected to
your bank account say - and Sendkeys would blithely send keystrokes to that
application, closing your checking account let's say!

Sendkeys is *never* necessary and is *never* the preferable way to do
anything.

If you'll post the details of what you're trying to do with the macro, someone
here will suggest how to convert the operation into VBA code not using
Sendkeys.

 

[Robert Morley]

"Never" isn't quite true. In cases where automation isn't available, or
doesn't quite fit the bill, it may be necessary. It should still be an
absolute last resort, though, definitely.


Rob

 

[Bald Eagle]

Thanks for the further guidance.

The Macro which I have developed is used to import an MS Excel spreadsheet
which contains the monthly Bank details of the Charity which have been
downloaded from the internet. Unfortunately the data needs to be converted
to make it usable in Access.

The Date field simply contains a string of numbers and dots and my Query
converts this into Date format although the field is showing up as a ‘Text’
field. This is where I used SendKeys to change it to Date format (d-mmm-yy).

There are also 2 money fields which are simply in ‘Number’ format. This was
my second use of SendKeys. However someone here has suggested that I could
use ‘CCur’ in the Query and I have done this and it works on my PC – I
haven’t had an opportunity to try it on the Vista laptop, do you think this
should work OK on it?

Lastly I create a new field from the ‘Details’ field in the spreadsheet to
hold the Cheque numbers for reconciliation purposes. However this is also
showing as a ‘Text’ field in the Access Table. This was my final use of
SendKeys.

Is there anything similar to CCur which I could use in the Query to make the
first ‘Text’ field into ‘Date’ with d-mmm-yy format and the second ‘Text’
field into ‘Number’ format?

It’s great that there is such a wealth of expertise out there which I can
tap into! I must use it more often when I hit a brick wall!

 

[JonWayn]

I have applications I wrote that are very similar in the sense that I am
importing data from a non-Access application (like Excel or text file), and
the integrity of the data types are not reliable. For instance, a date field
that has non-datable info (such as 'Not Known instead of a true date). What I
do is create 2 tables of identical structure except 1 has all text fields and
the other has the various numeric, date and other field types as necessary. I
import the data into the all-text table. I run queries to update the bad data
to null and convert to my preferred format otherwise. Example:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = Null " _
& "WHERE Not IsDate([Date Of Birth])"

and:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = " _
& "Format([Date Of Birth], 'mm/dd/yyyy')"

Then do similar representation of the above 2 queries to other sensitive
fields. After all that is complete, I transfer the data from the all-text
table to the other table and there you have your data in the format you like.

If I understood the issue correctly, that should do the trick

 

[JonWayn]

And I totally agree with one of the reply posters: SendKeys sucks ROYALLY. I
NEVER use it. It just cant be tamed

 

[Bald Eagle]

Thanks for the suggestion. I will try that and see how I get on.

Has anyone got a comment on whether there is something similar to CCur which
I could use in a standard Query which would ensure that the date field
created holds the 'd-mmm-yy' format in the new Table?
--
Bald Eagle

I have applications I wrote that are very similar in the sense that I am
importing data from a non-Access application (like Excel or text file), and
the integrity of the data types are not reliable. For instance, a date field
that has non-datable info (such as 'Not Known instead of a true date). What I
do is create 2 tables of identical structure except 1 has all text fields and
the other has the various numeric, date and other field types as necessary. I
import the data into the all-text table. I run queries to update the bad data
to null and convert to my preferred format otherwise. Example:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = Null " _
& "WHERE Not IsDate([Date Of Birth])"

and:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = " _
& "Format([Date Of Birth], 'mm/dd/yyyy')"

Then do similar representation of the above 2 queries to other sensitive
fields. After all that is complete, I transfer the data from the all-text
table to the other table and there you have your data in the format you like.

If I understood the issue correctly, that should do the trick

Thanks for the further guidance.

The Macro which I have developed is used to import an MS Excel spreadsheet
which contains the monthly Bank details of the Charity which have been
downloaded from the internet. Unfortunately the data needs to be converted
to make it usable in Access.

The Date field simply contains a string of numbers and dots and my Query
converts this into Date format although the field is showing up as a ‘Text’
field. This is where I used SendKeys to change it to Date format (d-mmm-yy).

There are also 2 money fields which are simply in ‘Number’ format. This was
my second use of SendKeys. However someone here has suggested that I could
use ‘CCur’ in the Query and I have done this and it works on my PC – I
haven’t had an opportunity to try it on the Vista laptop, do you think this
should work OK on it?

Lastly I create a new field from the ‘Details’ field in the spreadsheet to
hold the Cheque numbers for reconciliation purposes. However this is also
showing as a ‘Text’ field in the Access Table. This was my final use of
SendKeys.

Is there anything similar to CCur which I could use in the Query to make the
first ‘Text’ field into ‘Date’ with d-mmm-yy format and the second ‘Text’
field into ‘Number’ format?

It’s great that there is such a wealth of expertise out there which I can
tap into! I must use it more often when I hit a brick wall!

 

[John Spencer]

If you are storing the data in a datetime field then the data is being
stored as a number. The display of the data is controlled by the formats
you have set either at the system level or by formats set using the
properties of fields and controls.

To get the actual value of an item stored as a string that looks like a
date, try the following expression
IIF(IsDate(TheTextField), DateValue(TheTextField),Null)
That will strip off any time portion if there is a time portion and will
return a DateTime value.

You can also use the CDate function
IIF(IsDate(TheTextField),CDate(TheTextField),Null)


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the suggestion. I will try that and see how I get on.

Has anyone got a comment on whether there is something similar to CCur
which
I could use in a standard Query which would ensure that the date field
created holds the 'd-mmm-yy' format in the new Table?
--
Bald Eagle

I have applications I wrote that are very similar in the sense that I am
importing data from a non-Access application (like Excel or text file),
and
the integrity of the data types are not reliable. For instance, a date
field
that has non-datable info (such as 'Not Known instead of a true date).
What I
do is create 2 tables of identical structure except 1 has all text fields
and
the other has the various numeric, date and other field types as
necessary. I
import the data into the all-text table. I run queries to update the bad
data
to null and convert to my preferred format otherwise. Example:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = Null " _
& "WHERE Not IsDate([Date Of Birth])"

and:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = " _
& "Format([Date Of Birth], 'mm/dd/yyyy')"

Then do similar representation of the above 2 queries to other sensitive
fields. After all that is complete, I transfer the data from the all-text
table to the other table and there you have your data in the format you
like.

If I understood the issue correctly, that should do the trick

Thanks for the further guidance.

The Macro which I have developed is used to import an MS Excel
spreadsheet
which contains the monthly Bank details of the Charity which have been
downloaded from the internet. Unfortunately the data needs to be
converted
to make it usable in Access.

The Date field simply contains a string of numbers and dots and my
Query
converts this into Date format although the field is showing up as a
'Text'
field. This is where I used SendKeys to change it to Date format
(d-mmm-yy).

There are also 2 money fields which are simply in 'Number' format.
This was
my second use of SendKeys. However someone here has suggested that I
could
use 'CCur' in the Query and I have done this and it works on my PC - I
haven't had an opportunity to try it on the Vista laptop, do you think
this
should work OK on it?

Lastly I create a new field from the 'Details' field in the spreadsheet
to
hold the Cheque numbers for reconciliation purposes. However this is
also
showing as a 'Text' field in the Access Table. This was my final use
of
SendKeys.

Is there anything similar to CCur which I could use in the Query to
make the
first 'Text' field into 'Date' with d-mmm-yy format and the second
'Text'
field into 'Number' format?

It's great that there is such a wealth of expertise out there which I
can
tap into! I must use it more often when I hit a brick wall!

--
Bald Eagle


:

On Mon, 3 Mar 2008 10:40:02 -0800, Bald Eagle

This looks promising. Thanks for the lead. I assume from this that
I can't
do something within the Macro to get round the problem? Do I have
to resort
to Code? I am not an expert!

Well... you have a whole bunch of experts here who can help.

Sendkeys is VERY buggy and VERY unreliable and totally
uncontrollable. You
could have some other application open - your Microsoft Money
connected to
your bank account say - and Sendkeys would blithely send keystrokes
to that
application, closing your checking account let's say!

Sendkeys is *never* necessary and is *never* the preferable way to do
anything.

If you'll post the details of what you're trying to do with the
macro, someone
here will suggest how to convert the operation into VBA code not
using
Sendkeys.

 

[Bald Eagle]

Thanks to everyone who has responded to my original question. I have now got
my Macro to work on the Vista laptop!
Since I am not a 'Code' man it was Jon Wayn's suggestion of making an empty
table with the correct field formatting and then appending my new data that
was the trigger to the solution which I have adopted. This now works from a
Macro without having to resort to 'Code'.
--
Bald Eagle

If you are storing the data in a datetime field then the data is being
stored as a number. The display of the data is controlled by the formats
you have set either at the system level or by formats set using the
properties of fields and controls.

To get the actual value of an item stored as a string that looks like a
date, try the following expression
IIF(IsDate(TheTextField), DateValue(TheTextField),Null)
That will strip off any time portion if there is a time portion and will
return a DateTime value.

You can also use the CDate function
IIF(IsDate(TheTextField),CDate(TheTextField),Null)


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the suggestion. I will try that and see how I get on.

Has anyone got a comment on whether there is something similar to CCur
which
I could use in a standard Query which would ensure that the date field
created holds the 'd-mmm-yy' format in the new Table?
--
Bald Eagle

I have applications I wrote that are very similar in the sense that I am
importing data from a non-Access application (like Excel or text file),
and
the integrity of the data types are not reliable. For instance, a date
field
that has non-datable info (such as 'Not Known instead of a true date).
What I
do is create 2 tables of identical structure except 1 has all text fields
and
the other has the various numeric, date and other field types as
necessary. I
import the data into the all-text table. I run queries to update the bad
data
to null and convert to my preferred format otherwise. Example:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = Null " _
& "WHERE Not IsDate([Date Of Birth])"

and:
DoCmd.RunSQL "UPDATE CharityTable SET [Date Of Birth] = " _
& "Format([Date Of Birth], 'mm/dd/yyyy')"

Then do similar representation of the above 2 queries to other sensitive
fields. After all that is complete, I transfer the data from the all-text
table to the other table and there you have your data in the format you
like.

If I understood the issue correctly, that should do the trick

:

Thanks for the further guidance.

The Macro which I have developed is used to import an MS Excel
spreadsheet
which contains the monthly Bank details of the Charity which have been
downloaded from the internet. Unfortunately the data needs to be
converted
to make it usable in Access.

The Date field simply contains a string of numbers and dots and my
Query
converts this into Date format although the field is showing up as a
'Text'
field. This is where I used SendKeys to change it to Date format
(d-mmm-yy).

There are also 2 money fields which are simply in 'Number' format.
This was
my second use of SendKeys. However someone here has suggested that I
could
use 'CCur' in the Query and I have done this and it works on my PC - I
haven't had an opportunity to try it on the Vista laptop, do you think
this
should work OK on it?

Lastly I create a new field from the 'Details' field in the spreadsheet
to
hold the Cheque numbers for reconciliation purposes. However this is
also
showing as a 'Text' field in the Access Table. This was my final use
of
SendKeys.

Is there anything similar to CCur which I could use in the Query to
make the
first 'Text' field into 'Date' with d-mmm-yy format and the second
'Text'
field into 'Number' format?

It's great that there is such a wealth of expertise out there which I
can
tap into! I must use it more often when I hit a brick wall!

--
Bald Eagle


:

On Mon, 3 Mar 2008 10:40:02 -0800, Bald Eagle

This looks promising. Thanks for the lead. I assume from this that
I can't
do something within the Macro to get round the problem? Do I have
to resort
to Code? I am not an expert!

Well... you have a whole bunch of experts here who can help.

Sendkeys is VERY buggy and VERY unreliable and totally
uncontrollable. You
could have some other application open - your Microsoft Money
connected to
your bank account say - and Sendkeys would blithely send keystrokes
to that
application, closing your checking account let's say!

Sendkeys is *never* necessary and is *never* the preferable way to do
anything.

If you'll post the details of what you're trying to do with the
macro, someone
here will suggest how to convert the operation into VBA code not
using
Sendkeys.