Value Entered Isn't Valid...Error

[Pamela]

I have a cbo ShopName on my subform which is populated by ltblShop. The cbo
displays the Name, but is bound by the ShopID. If the user doesn't enter a
value, I want a msgbox to open confirming this and then entering "None",
which is an item in my ltblShop. Admittedly, the idea of having a tbl for
Shops which then has a "None" entry which isn't a shop feels wrong but seemed
to be the most expedient at the time and easiest for such the novice that I
am. The code for this upon entering and exiting that control seems to work
fine. The box opens upon a Null entry and will put "None" as directed. The
problem comes in when I later refer to that control in code to concatenate it
into text to copy. The copy operation gives me a "The operation has been
canceled" error and then I get "The value entered isn't valid..." error and
the system sets focus back to this control.
Here is my code:
Private Sub ShopName_Exit(Cancel As Integer)
If Len(Me.ShopName & vbNullString) = 0 Then
If MsgBox("Are you sure there's no shop?", vbYesNo, "Enter Shop") =
vbYes Then
Me.ShopName = "None"
Else
Me.ShopName.SetFocus
End If
End If
End Sub
**Note, if a ShopName is entered, the whole form works perfectly - it's only
on this "None" portion that it stops up.
Thanks so much for help!!
Pamela

 

[Dorian]

I would perform all your form-related validations in the forms BeforeUpdate
event.
Don't use the Exit event for the control.
Another alternative would be to set the default for the control to the
'none' entry in the list.
However, it would be preferable to allow no entry for this control to
represent 'none'.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Pamela]

Hi, Dorian,
I little more guidance would be great. I'm very green. I don't understand
what you said about "form-related validations in the form's BeforeUpdate
event." My thinking is if the form runs the code before the user even starts
to fill it in, how will the code find entries that are input after the code
runs...?? I am a little worried about using default values because my users
tend to get very lazy and don't always pay attention to fields they don't
HAVE to update so if they all start blank...they have to fill it in. I do
understand about the No Entry = None, but I'm a little worried about
implementing it. Perhaps a query...?? Thanks for any additional help!

 

[John W. Vinson]

I little more guidance would be great. I'm very green. I don't understand
what you said about "form-related validations in the form's BeforeUpdate
event." My thinking is if the form runs the code before the user even starts
to fill it in, how will the code find entries that are input after the code
runs...??

The "Before Update" event's name is a bit confusing. It fires AFTER the user
has entered all the data that they plan to enter, and taken some action that
will cause the record to be committed to the database - e.g. moving off the
record or closing the form. It does fire "before" the record is updated - but
*right* before that happens, after the user has filled in everything.

 

[Pamela]

Hi John,
Thanks so much for that clarification. How does that work with subforms?
Are all of the subforms handled separately in that I'd need to do separate
BeforeUpdate events for each one and then only pertaining to the controls on
that subform or can they all be done from any of the forms? For all of this
that I've been working on (thanks for all your help on it) I have a command
button at the botton of the last subform that selects an unbound text box
which, on it's GotFocus event, runs all of this code to lookup - based on
the user's data - and concatenate and then copy a nice little paragraph and
then close the form. The user can then paste that paragraph into whatever
outside application needed. With this in mind, could I run the validation
codes for all of these controls on that button and then also go ahead and run
the code for compiling my paragraph? Getting it "right" which seems to be
somewhat subjective, may help with all of these problems I'm running into.
Thanks so much again!
Pamela

 

[John W. Vinson]

Hi John,
Thanks so much for that clarification. How does that work with subforms?

Exactly the same way. When the user takes an action which would cause the
subform data to be saved to disk - e.g. setting focus to the mainform, or
moving to a different record on the subform - the Subform's BeforeUpdate event
fires. The main form's event does not.

Are all of the subforms handled separately in that I'd need to do separate
BeforeUpdate events for each one and then only pertaining to the controls on
that subform or can they all be done from any of the forms?

Each (sub)Form has its own independent BeforeUpdate event.

For all of this
that I've been working on (thanks for all your help on it) I have a command
button at the botton of the last subform that selects an unbound text box
which, on it's GotFocus event, runs all of this code to lookup - based on
the user's data - and concatenate and then copy a nice little paragraph and
then close the form.

The GotFocus event will NOT fire unless the *user* manually sets focus to the
event. Rather than seting focus to the box and letting the GotFocus code fill
it, you should fill it directly in the button's Click event.

The user can then paste that paragraph into whatever
outside application needed. With this in mind, could I run the validation
codes for all of these controls on that button and then also go ahead and run
the code for compiling my paragraph?

I'd suggest validating in the Form's BeforeUpdate event. Having the validation
in the button code might work but it'll be more complex since it has to look
not only at multiple fields, but multiple forms!

Getting it "right" which seems to be
somewhat subjective, may help with all of these problems I'm running into.

It is always subjective. That's why us consultants are all independently
wealthy <cough cough>...

 

[Pamela]

Wow...that's a lot of scary work! I think I'll try to limp it by for the
time being before jumping into something that big - plus I'm sure you and all
the other helpful folks here could use a well deserved break from holding my
hand. LOL But really, thank you, obviously with all the help you've given
you know I couldn't have done any of it without you!

Pamela

 

[Pamela]

One more question about running the compiling code from my final command
button... I was told that in order to run do the RunCommand.AcCmdCopy
function/procedure that Focus must be set to a control from which to copy the
text. Will it work w/o having a text box to which to set focus and from
which to copy it??

Thanks!
Pamela

 

[John W. Vinson]

One more question about running the compiling code from my final command
button... I was told that in order to run do the RunCommand.AcCmdCopy
function/procedure that Focus must be set to a control from which to copy the
text. Will it work w/o having a text box to which to set focus and from
which to copy it??

I've never used acCmdCopy at all, and it's certainly neither necessary nor the
best way to go.

Instead, just copy the data:

Me.targetcontrolname = Me.sourcecontrolname

or

Me.targetcontrolname = SomeVariableYouHaveSet

By using a VBA variable to hold the calculated value, you can - if you wish -
move the calculation from (possibly complicated) expressions on the Form into
your VBA code, where it can use all the power of VBA to validate, properly
handle missing information, use conditional logic (e.g. "if there is a check
in the TaxExempt checkbox use the value as is, otherwise add the tax").

 

[Pamela]

Thanks, I'll give it a shot but I do still need the AcCmdCopy because the
user needs to paste the compiled paragraph into other (non-MS) applications.
It needs to be copied to the Clipboard not just another control in Access.