Stop Action

[Joanne]

I have one user form
The upper half is for user input.
These values will be put in word docs as bookmarks

The lower half of the form is for various file manipulations (view,
print etc) after the bookmarks have been updated with the input from
upper half of form.

My problem is how to stop the user from trying to manipulate any of
the docs BEFORE the routine to update the bookmarks has been run.

I thought to stop the action with a msgbox requiring a yes/no from the
user (giving me an opportunity to run the routine to set the bookmarks
on a response=yes, or send them back to the input portion of the form
to fill-in the control that is missing data on a response=no) after
the 'after update' event of the last control, but if they don't tab
through the controls and they miss the last control, this approach is
no good.

I also thought to call a routine from each control on the manipulation
portion of the form to check that the controls are all filled in, and
if so, run the routine to update the bookmarks. I think this might be
my best way to go, but I have a real beginners question nagging at me
about this.

I can't use a loop to go thru the controls becuase I only want to
check those controls that are for the user input, not the commands,
combo boxes etc that are for file manipulation. So would it be more
correct to use a select case statement or an if/then/else statement to
check these controls. there are approx 30 input controls and it seems
to me that the if/then/else thing would be unwieldy.
If I use the select case method, once all controls have a value, will
I be able to call the routine to update the bookmarks before giving
control of the app back to the user?

thanks for your time and expertise
Joanne

 

[Nikos Yannacopoulos]

Joanne,

You say that the "lower part" of your form is for the user to manipulate
docs... I suppose this is done through command buttons?

One way to do it would be to have those command buttons disabled in your
form design, and add an extra one which the user has to click once they are
done with their input in the "upper part", which:
* Checks that all required input is provided
* If check doesn't fail, updates the bookmarks and enables the command
buttons for file manipulation
Note: because changing command buttons fromdisabled to enabled constitutes a
change in design, which you do not want saved, you will need to remove the
standard close button form the form's control box, and add a custom button
on the form to close it without saving.

Now, on the check for data entry: the trick that would result in the neatest
code is to make the names of all the controls subject to check start with a
particular prefix, e.g. reqXXXXXX, while none of the other controls on the
form starts with the same prefix. That way you can easily loop through the
controls and subject to check only those starting with it, like:

For Each ctl in Me.Controls
If Left(ctl.Name, 3) = "req" Then
If IsNull(ctl) Then
Msgbox "Please enter all required data", vbCritical,
"Attention!"
Exit Sub
End If
End If
Next

' Code to update bookmarks and enable command buttons

Alternatively, if you don't want to change the names of the controls in your
design, you could store the control names in an array (say, CtlName()) at
the beginning of the sub, and loop through the values like:

For i = 0 to 29 '(assumed 30 controls)
If IsNull(Me.Controls(CtlName(i))) Then
Msgbox "Please enter all required data", vbCritical, "Attention!"
Exit Sub
End If
Next

HTH,
Nikos

 

[Joanne]

Nikos
Thank you so much for your time and expertise.
I used your method, put a command button on 'upper' portion, they must
use it to enable controls on lower portion, It works great!!
Actually, I put the code to disable the lower controls in the form
load event, so that I know each time the app is opened, the controls
will be disabled. This is quite a simple app, used to gather data in
access and then using automation put the data in bookmarks in msword
docs for a one-time only use; after the docs are printed out the
record is dumped until the user needs to input data for another
person.

Now, if I may, I need help with one more thing before I can clean up
the code and ship this thing out.
I have combo boxes listing files in them, maybe 8 or 10 in each combo
box. I want my user to be able to choose a particular file to work
with, say to print or just to view.
I am using Access 2000, automating Word2000.
I have the docs in the same folder as the mdb. I have a table with the
doc name and path as found in the folder, and in the combo box I
display the docs, having given them longer filenames for better
clarity.
In the combo box, there are 4 columns. Column 3 has the long filename
in it that I display in the combobox. Column 4 has the path and
filename as saved in the folder containing the mdb and these docs.
My question is how do I tell the app which file the user chose and
(from col 3 of cbo) then match it to the name of the file to be
printed (from col 4 of cbo)?
I don't have any experience doing this but I suspect that I need to
get the index number and do something like if indexnum=1 then print
"mypath\myfilename". This is what I have been able to gather from
looking at the sample dbs in the office program. But it seems to me
that I would have to hard code this for each file, some 65 in all. I
would, of course, much rather find a way to do this by using a
function and passing the 'filename' to be worked with to the function.

If you have more tricks up your sleeve that could help me with this, I
sure would appreciate your sharing them with me
thanks again for your time and knowledge
Joanne

 

[Nikos Yannacopoulos]

Joanne,

Your code behind the Print button on the form can read the path and filename
of the current selection directly from the combo, like this:

vFileName = Me.YourComboName.Column(3)

First column is index 0, second is index 1 etc. It will work even with
columns you don't show (width = 0), as long as they are in the combo's
RowSource.

HTH,
Nikos

 

[Joanne]

Nikos
Again your prompt answer is very much appreciated, as is your simple
little technique, which works like a champ!! Thank you so much.
Joanne

 

[Joanne]

Thanks Nikos, works like a charm! While looking for help on
commandbars, I found this info you gave to someone in August.

Application.CommandBars("Menu bar").Enabled = True

I tried this line of code in my app, changing enabled = False

and it works great on disabling the menu bar in access, which I will
want to do before shipping.
My question is how do I cause this type of action to work on MSWord
main menu when using access to automate word. I don't want the user to
have any option other than what I will put on my own toolbar for their
use. And of course I do not want to mess with their main menu bar in
any way, just hide it for this app, and when my app closes, their
msword menu bar is the way they set it.

So, I want to disable the standard msword toolbar, then load my own
msword toolbar ("WorkPkg") for their use.

Can I do this thru automation? And since my users will be opening and
closing word for various operations, would it be better if I just open
word when the app loads and then make it visible or not visible as
necessary instead of opening and closing it all the time? And is there
a way that I can check to see if msword is open on the machine already
before opening it again? I'm begging lots of help, please forgive me
if it is too much.
Joanne

 

[Nikos Yannacopoulos]

Joanne,

You got me there. I have no experience with Word automation, I'm afraid I
can't be much help on that, but surely someone will be able to help. I would
suggest you start a new thread, though, as this one is becoming long, and
consequently less likely to attract attention.

Nikos