A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Value Entered Isn't Valid...Error



 
 
Thread Tools Display Modes
  #1  
Old January 4th, 2010, 05:56 PM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default Value Entered Isn't Valid...Error

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
  #2  
Old January 4th, 2010, 07:07 PM posted to microsoft.public.access.forms
Dorian
external usenet poster
 
Posts: 542
Default Value Entered Isn't Valid...Error

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" wrote:

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

  #3  
Old January 4th, 2010, 07:29 PM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default Value Entered Isn't Valid...Error

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!

"Dorian" wrote:

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" wrote:

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

  #4  
Old January 4th, 2010, 10:37 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Value Entered Isn't Valid...Error

On Mon, 4 Jan 2010 10:29:01 -0800, Pamela
wrote:

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.

--

John W. Vinson [MVP]
  #5  
Old January 5th, 2010, 12:31 AM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default Value Entered Isn't Valid...Error

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" wrote:

On Mon, 4 Jan 2010 10:29:01 -0800, Pamela
wrote:

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.

--

John W. Vinson [MVP]
.

  #6  
Old January 5th, 2010, 01:33 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Value Entered Isn't Valid...Error

On Mon, 4 Jan 2010 15:31:01 -0800, Pamela
wrote:

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...
--

John W. Vinson [MVP]
  #7  
Old January 5th, 2010, 02:46 AM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default Value Entered Isn't Valid...Error

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

"John W. Vinson" wrote:

On Mon, 4 Jan 2010 15:31:01 -0800, Pamela
wrote:

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...
--

John W. Vinson [MVP]
.

  #8  
Old January 5th, 2010, 02:49 AM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default Value Entered Isn't Valid...Error

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" wrote:

On Mon, 4 Jan 2010 15:31:01 -0800, Pamela
wrote:

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...
--

John W. Vinson [MVP]
.

  #9  
Old January 5th, 2010, 04:36 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Value Entered Isn't Valid...Error

On Mon, 4 Jan 2010 17:49:01 -0800, Pamela
wrote:

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").
--

John W. Vinson [MVP]
  #10  
Old January 5th, 2010, 05:47 AM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default Value Entered Isn't Valid...Error

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.

"John W. Vinson" wrote:

On Mon, 4 Jan 2010 17:49:01 -0800, Pamela
wrote:

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").
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.