How can I Position the Mouse Pointer? (A2003)

[Clif McIrvin]

So far I've had no luck searching for an answer (maybe I'm just using
the wrong search terms --- but then I've noticed that my brain doesn't
always name things the way the Microsoft Help writers do <g>)

I am using a form in datasheet view to enter production data; in this
instance the item has already been entered as 'pre-production' and after
it has been produced the date produced needs to be entered.

In the Production Date _Enter event procedure I am using a call to
MsgBox to confirm the date change which leaves the mouse pointer where
the dialog box popped up. I'd sure like to bring it back to the current
control but have had no success with anything I've tried.

I sure hope someone can point me in the right direction!

A bit more description:

My idea was to automatically change the 'not yet produced' date to the
'last date entered' when the user enters the date produced field,
whether by mouse-click or keyboard action. The difficulty I ran into is
that often there are many 'not yet produced' items between any two that
were produced 'today', and if using the keyboard to walk through the
records my code would obediently change the production date for every
record it passed through.

My work-around at this point is to introduce a call to MsgBox to confirm
the date change. This works well, except that the mouse pointer is left
where the dialog box popped up instead of in the control with the focus.

 

[Dirk Goldgar]

So far I've had no luck searching for an answer (maybe I'm just using the
wrong search terms --- but then I've noticed that my brain doesn't always
name things the way the Microsoft Help writers do <g>)

I am using a form in datasheet view to enter production data; in this
instance the item has already been entered as 'pre-production' and after
it has been produced the date produced needs to be entered.

In the Production Date _Enter event procedure I am using a call to MsgBox
to confirm the date change which leaves the mouse pointer where the dialog
box popped up. I'd sure like to bring it back to the current control but
have had no success with anything I've tried.

I sure hope someone can point me in the right direction!

A bit more description:

My idea was to automatically change the 'not yet produced' date to the
'last date entered' when the user enters the date produced field, whether
by mouse-click or keyboard action. The difficulty I ran into is that
often there are many 'not yet produced' items between any two that were
produced 'today', and if using the keyboard to walk through the records my
code would obediently change the production date for every record it
passed through.

My work-around at this point is to introduce a call to MsgBox to confirm
the date change. This works well, except that the mouse pointer is left
where the dialog box popped up instead of in the control with the focus.

Cliff -

It's perfectly possible to move the mouse pointer to a particular control --
so long as it's a control that can receive the focus -- but it involves a
bit of moderately complex code to call some Windows API functions. There's
no built-in method to move the mouse pointer, the way there is to set the
focus. (Bear in mind that a control may have the focus and be the active
control regardless of where the mouse pointer is.) Do you want to give the
API code a shot? If so, I'll post it.

It may be that just setting the focus to the control you want will serve
your needs. If so, that's easy enough: just execute the statement

YourControlName.SetFocus

And you may not need to do this at all. I noticed that you said this:

In the Production Date _Enter event procedure [...]

.... and this:

My idea was to automatically change the 'not yet produced' date to the
'last date entered' when the user enters the date produced field,

If you'll pardon me for saying so, this strikes me as a singularly bad idea.
Changing data just because a control receives the focus is a very dangerous
business. Normally a user expects to be able to tab through or click into
any field in any record without changing any data. If you don't let them do
that, I think you're going to have confused and annoyed users. Wouldn't it
make more sense to use AfterUpdate events of some controls to trigger
correlated data changes to other controls, where appropriate? Or else the
Click events of command buttons, where people know that they are asking for
some action to be taken in response to the click?

I don't really understand your description fully, so I may be off-base here,
but I think you may be creating problems for yourself. The fact that you're
asking to do something that Access has no built-in ability to do is a
suggestion that, at the very least, your approach is very unusual and maybe
should be re-examined.

 

[Clif McIrvin]

I don't really understand your description fully, so I may be off-base
here, but I think you may be creating problems for yourself. The fact
that you're asking to do something that Access has no built-in ability
to do is a suggestion that, at the very least, your approach is very
unusual and maybe should be re-examined.

That's one think I've grown to really like about these newsgroups ...
ideas and suggestions! (Well, I guess that's two things <g>)

Nah, I think I'll pass on the API ... I'm not going to be around
forever, and some poor soul is going to have the dubious joy of trying
to keep this thing going. [Have you ever seen an app that the specs
didn't evolve over time? I sure haven't!]

I'll ponder your various pointers ... this app is very much under
development ... it's just that circumstances forced me into developing
an app with live data .... sure glad folk like you are out here and
willing to advise!

 

[Larry Linson]

I don't really understand your description fully, so I may be off-base
here, but I think you may be creating problems for yourself. The fact
that you're asking to do something that Access has no built-in ability to
do is a suggestion that, at the very least, your approach is very unusual
and maybe should be re-examined.

That's one think I've grown to really like about these newsgroups ...
ideas and suggestions! (Well, I guess that's two things <g>)

Nah, I think I'll pass on the API ... I'm not going to be around forever,
and some poor soul is going to have the dubious joy of trying to keep this
thing going. [Have you ever seen an app that the specs didn't evolve over
time? I sure haven't!]

I'll ponder your various pointers ... this app is very much under
development ... it's just that circumstances forced me into developing an
app with live data .... sure glad folk like you are out here and willing
to advise!

Clif,

There are a lot of development approaches to avoid developing with "live
data" or on the "production" system. I can't encourage you strongly enough
to discuss some options so you can revise your methodology so you develop on
your own "development copy" of the system and the database, and then
"release" updated production copies to the actual users.

Larry Linson
Microsoft Office Access MVP

 

[Jason]

How do I upgrade existing data? A database upgrade i did involved copying
the existing files using a batch file, then copy the new files over the
existing files then a long routine in Access to import the old data into the
new tables.

I don't really understand your description fully, so I may be off-base
here, but I think you may be creating problems for yourself. The fact
that you're asking to do something that Access has no built-in ability to
do is a suggestion that, at the very least, your approach is very unusual
and maybe should be re-examined.

That's one think I've grown to really like about these newsgroups ...
ideas and suggestions! (Well, I guess that's two things <g>)

Nah, I think I'll pass on the API ... I'm not going to be around forever,
and some poor soul is going to have the dubious joy of trying to keep this
thing going. [Have you ever seen an app that the specs didn't evolve over
time? I sure haven't!]

I'll ponder your various pointers ... this app is very much under
development ... it's just that circumstances forced me into developing an
app with live data .... sure glad folk like you are out here and willing
to advise!

Clif,

There are a lot of development approaches to avoid developing with "live
data" or on the "production" system. I can't encourage you strongly enough
to discuss some options so you can revise your methodology so you develop on
your own "development copy" of the system and the database, and then
"release" updated production copies to the actual users.

Larry Linson
Microsoft Office Access MVP

 

[Clif McIrvin]

There are a lot of development approaches to avoid developing with
"live data" or on the "production" system. I can't encourage you
strongly enough to discuss some options so you can revise your
methodology so you develop on your own "development copy" of the
system and the database, and then "release" updated production copies
to the actual users.

Larry, I've put a fair amount of thought into this very subject over the
weekend. As this application did not even exist before -- that is, the
database app *and* the live data were 'born' at the same time -- it's a
challenge, but I feel like I've finally learned enough to have a handle
on doing exactly what you propose.

For starters, I will be creating a new development environment which
will contain a snapshot of the live data so I have something to
experiment on; followed by getting a handle on a workable process for
releasing updates; then back to the ongoing development process itself.

Along the way, I anticipate doing some design restructuring to improve
normalization ... since that implies a real-time conversion of live data
that should prove interesting <g>.

Bottom line: I no longer feel like a blind man groping around in a cave
I've never been in before -- thanks in large part to what I've learned
here!

Thanks to all you who help us new-comers!

 

[Larry Linson]

How do I upgrade existing data? A database upgrade
i did involved copying the existing files using a batch
file, then copy the new files over the existing files then
a long routine in Access to import the old data into the
new tables.

Sounds as if you have not split your database into front end (queries,
forms, reports, macros, and modules) and back end (tables, data, and
relationships), with each user having a copy of the FE and the BE on a
shared folder.

-- The most common updates will be to the interface, so each user can just
get
a new copy, because it does not store data (other than, perhaps, some
rarely-
changing lookup tables -- state abbreviation and state name, for
example).

-- When you have to make a change in the data structure, the common
approach
(if you can't go to the location, and open the back end manually) is to
write
VBA code in DAO or ADO to modify the table design (if needed) and data
in the shared back end, and update with any additional data that you
are adding,
and have one user or administrator at the production site execute that
when
they have exclusive access.

There's other information about splitting and about an Auto FE Updater for
use in multiuser environments at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

Good luck with your projects.

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

Best of luck in this new undertaking. Post here if you run into stumbling
blocks; many of us have spent a lot of time backed up to the same walls, and
may be able to help. For a little "overview", see my response in this thread
to Jason. I have, by the way, an "alternative to Auto FE updater" described
in an article on "Versioning" at http://accdevel.tripod.com -- neither
approach is necessarily better, just different, and what to do will depend a
lot on your user audience.

"Designing Effective Database Systems" by Rebecca Riordan (either edition,
either publisher) is a really good book on the subject(s) not only of
structuring relational database but on design for the database application.
Caveat: Rebecca is a personal friend, but I had the same high regard for her
ability and knowledge before we ever met either electronically or in person.

Larry Linson
Microsoft Office Access MVP

 

[Jason]

It is split as you say the tables in the user mde is for temporary data and
data i enter for look-ups etc. Since the data files have changed over time I
have found the process of copying and importing easier. Can you post an
example code where the table is checked and modified (i.e. fields added or
deleted).
Thanks,
J

 

[Jason]

It is split as you say the tables in the user mde is for temporary data and
data i enter for look-ups etc. Since the data files have changed over time I
have found the process of copying and importing easier. Can you post an
example code where the table is checked and modified (i.e. fields added,
modified or deleted).

ALSO tables that are added

 

[Peter Hibbs]

Jason,

You can update the Front-End file easily by sending the client a new
version. However, if you will need to make changes to the Back-End
file you need a different method. Assuming that you cannot visit each
client to make the necessary changes you need to add some code to the
Front-End which will make the relevant changes to the Back-End files
automatically.

There is code at this site :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'
which will do that for you. Just import the form, table and code
module into your database, add one line of code to your 'start up'
form and you are done. To add a new table or field to the Back-End you
just call up the BE Update form, enter the details of the table,
field, relationship, etc and the Back-End file is updated without
affecting the client's existing data.

You will also need to add relinking code to relink the Front-End file
to the tables in the Back-End file. See this site to do that
automatically :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE_ReLink.mdb'

There is full documentation on both sites to explain the procedure
fully.

HTH

Peter Hibbs.

 

[Jason]

I've got relinking sorted but will look at the other later to see if it's
woth doing.

 

[Jason]

I get error "Can't find input table "TableName""

Jason,

You can update the Front-End file easily by sending the client a new
version. However, if you will need to make changes to the Back-End
file you need a different method. Assuming that you cannot visit each
client to make the necessary changes you need to add some code to the
Front-End which will make the relevant changes to the Back-End files
automatically.

There is code at this site :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'
which will do that for you. Just import the form, table and code
module into your database, add one line of code to your 'start up'
form and you are done. To add a new table or field to the Back-End you
just call up the BE Update form, enter the details of the table,
field, relationship, etc and the Back-End file is updated without
affecting the client's existing data.

You will also need to add relinking code to relink the Front-End file
to the tables in the Back-End file. See this site to do that
automatically :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE_ReLink.mdb'

There is full documentation on both sites to explain the procedure
fully.

HTH

Peter Hibbs.