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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|