473,322 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Control Validation

4 Nibble
I am having problems validating entry data on my order entry screen. I have an Access front end to a MS SQL Server back end. The entry is simple. I have the following controls on a sub-form:
cboProductID (a combo box for available products), txtProductName (a text box for the name of the selected ID), Quantity (a text box for quantity of the selected product), Price (price of selected product) and LineTotal (quantity times price). I want to validate that a quantity is entered. I tried to use the control validation on the Access form for the Quantity text box stating ">0" as the Validation Rule and "You must enter a quantity" as the Validation Text. It is my understanding that you couldn't tab out of the control until the entry passes the validation rule. If I hit tab or enter, the cursor jumps to the next control, even though it didn't meet the validation criteria. I can't see that I am doing anything wrong.

I have also tried the following code in the LostFocus event:
Expand|Select|Wrap|Line Numbers
  1.     If Me.Quantity = 0 Or IsNull(Me.Quantity) Then
  2.         DoCmd.Beep
  3.         MsgBox "You must enter a quantity.", vbOKOnly
  4.         Me.Quantity.SetFocus
  5.         GoTo Proc_Exit
  6.     Else
  7.         Forms![frmInvoiceOrders]!cmdSaveRec.Enabled = True
  8.     End If
This code generates the MsgBox message, but the cursor jumps to the next control when clicking OK on the message box, even though I have the SetFocus statement in the code.
I would like to get either of these options to work.
Jan 2 '21 #1
7 1344
NeoPa
32,556 Expert Mod 16PB
Validation Rules determine what entries are valid. They only apply when there is an entry to check. Your answer lies elsewhere :-(

The code looks OK to me but you do use the same name for the Control as you use for the Field so that's likely to give you issues. I'd correct that first.

If you were using an Access BE you could use the ValidationRule property of the table itself, which is somewhat under used due to it being so much harder to discover than that for the Fields (& the Controls too of course). I just looked for SQL Server tables and it's there too. You may not like the idea of setting it as a table property but it's there if you do.

Otherwise, the code's just a snippet. It looks fine as far as it goes but if it still doesn't work for you then the whole module might be worth posting (Properly in [Tags] of course ;-) )
Jan 2 '21 #2
isladogs
455 Expert Mod 256MB
Validation tests are best done in the Form_BeforeUpdate event as that always runs before saving changes and the change can be cancelled if it fails the validation test.

You could also use
Expand|Select|Wrap|Line Numbers
  1. If Nz(Quantity,0)=0 Then
Jan 3 '21 #3
NeoPa
32,556 Expert Mod 16PB
Nice one IslaDogs. I should have included that but I went blank at the time :-(
Form_BeforeUpdate() is indeed where you can, and would probably want to, handle such an issue at the Form level.

There is a whole nother discussion about where best to handle such matters within a database but before going there you need to know the available options.
Jan 3 '21 #4
isladogs
455 Expert Mod 256MB
Hi @NeoPa
Its very easy to overlook that event, In fact I developed in Access for several years before using it for the first time.

Failing that the next best choice is the Before_Update event of the actual control ...but there is no real advantage to doing so...unless there is additional code to be run in the same event.
In addition why use validation code in multiple control events when one event can handle it all!
Jan 3 '21 #5
NeoPa
32,556 Expert Mod 16PB
IslaDogs:
In addition why use validation code in multiple control events when one event can handle it all!
That is required sometimes. It comes down to whether you'd like to catch problems immediately, and to disallow any invalid data before being allowed to move on even to the next Control, or whether you, as the developer or client, prefer to let them get all the information in before reporting on any issues. Sometimes both can be required where some validations can be done at a Control level, which can be done in isolation, where others can only be determined by comparison with multiple values within the same Form.

In this case of course, the Form_BeforeUpdate() is definitely required as it's a check that can't be adequately handled at the Control level.
Jan 3 '21 #6
isladogs
455 Expert Mod 256MB
All true but (I'm partly playing devil's advocate here) ...
If there are several fields being updated, the control before update event will of course, only cancel the change in that one control, whereas the form before update event will reverse all change made to that record.

It could easily get very confusing if both types of event are used in the same form.
More for interest than anything else, I've attached a very basic example using before update events in both a control (Input1) and the form. Both have message boxes so its easy to see which event is which. In this case, there is no validation involved.
The control event is triggered first on leaving that field then the form event on leaving the record. Even if the control field change is confirmed and then the user changes another field before moving to another record, both changes can be reversed in the form event

BTW: The Almost Impossible DB name is not an indication that it is complicated. Its the username of someone I was assisting at the time!
Attached Files
File Type: zip NearImpossibleUpdate.zip (37.8 KB, 45 views)
Jan 3 '21 #7
NeoPa
32,556 Expert Mod 16PB
IslaDogs:
If there are several fields being updated, the control before update event will of course, only cancel the change in that one control, whereas the form before update event will reverse all change made to that record.
Yes indeed. This is very much indicative of how the <ESC> key works when in a Form & Control/Field with updates. <ESC> #1 reverses the changes made at the Control/Field level if there are any in the current Control/Field, otherwise (If no changes to undo.) it will affect the Form (Record) instead. <ESC> #2, assuming the #1 reversed the Control/Field, will reverse the whole record of the Form.

I sometimes have fun trying to ensure that <ESC> is only ever used for triggering a CommandButton Control (Property Cancel=Yes.) when there are no changes to be reversed just so it doesn't interfere with expected usage within Access of the <ESC> key.
Jan 3 '21 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Sebastian | last post by:
Hi, I have datagrid. In its footer there is editbox to add new item. All is cool. Datagrids 'Delete' button has CausesValidation set to false and all is great. But the problem is in 'Process'...
5
by: KJ | last post by:
I need help. I have a checkbox and two textboxes on a webform. How can I validation if a person either enters something in the two textboxes OR the checkbox? I tried using a custom validator...
2
by: Mark Sandfox | last post by:
I have a tricky control validation issue. I have probably designed this the wrong way but here is what I have. I have 6 TextBoxes; tbPN, tbA, tbC, tbS, tbZ, tbDOB and there are 20 of each with...
5
by: Richard Brown | last post by:
Ok, I've been looking through the .NET SDK docs and stuff. I'm wondering if you can provide a control extender that does generic validation or functionality just by dropping it on the form. For...
3
by: ilockett | last post by:
The background: I have a web app with a simple master page that contains just one content placeholder. I have created a web form that then uses this master page. Within the content...
0
by: tsw_mik | last post by:
I have created a custom control. It has: -label -button -list of textboxes -list of dropdownlists. I want to use a custom validator to perform some validation, but somehow I can't fo it. The...
3
by: =?Utf-8?B?UGlldGVy?= | last post by:
I have to make a survey and I have te following problem. For each question in a subject (our survey had multiple subjects) I add a radiobuttonlist and a requerdvieldvalidator to te control...
0
by: clintonG | last post by:
OBJECTIVE: A Wizard control often needs two TextBoxes on specific steps. In this instance both Step5 and Step6 each require two TextBoxes in each step, that is, each step requires a TextBox to...
1
by: ticketdirector | last post by:
I have created a user control containing a drop down list box. I want to be able to put this user control onto a page along with a validator control. The problem is, the validator doesn't see the...
0
by: =?Utf-8?B?Q29kZVJhem9y?= | last post by:
I have an accordion AJAXToolkit control. It works okay. However, I want to work it so that, each pane in the accordion will contain some controls. And those controls will have validation...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.