Full Version: Verifying Value Entered Is Valid - Which Event, Etc
UtterAccess Forums > Microsoft® Access > Access Forms
edaroc
Most of my work with Access forms has been to create reports, and verifying a value the user entered (a parameter for the report) was, in fact, in the database, was done in the Exit event. I'm working on a bound form and it just occurred to me that:
1. It didn't matter which event I used to verify a value when the object was to be used for a parameter in the report, and
2. I should verify in the BeforeUpdate event when the form (and object) is bound. Is this correct? Here is typical code, which worked for an unbound form and the textbox was used as a parameter in the report.
Am I correct I want this in the BeforeUpdate event, and is there any other suggestions to make this more efficient?
The code checks that the Job# entered is in the database. If not then a message is displayed and the value is cleared. If it is OK, then nothing is done, i.e. the value is accepted.
CODE
Private Sub txtJobNum_BeforeUpdate(Cancel As Integer)
    'Verify Job# is valid
    
    On Error GoTo Error_Handler
    
    
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim rs As DAO.Recordset
    
    Dim strSQL As String
    
    Call UsrMsg("", "G", Me) 'A label on the form displays messages to the user, this blanks the message, "G" is to make the font green (although it's a moot point here)
    
    If IsNull(Me.txtJobNum.Value) Then
        Me.txtCustName.Value = ""
        GoTo Exit_Procedure
    End If
    
    strSQL = "SELECT csname as CustName FROM Orders " _
        & "LEFT JOIN Customer on customer.cscode = orders.cscode " _
        & "WHERE job_number = " & Me.txtJobNum.Value
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
    
    If rs.RecordCount Then
        Me.txtCustName.Value = rs!CustName
    Else
        Me.txtCustName.Value = ""
        Call UsrMsg("Job#  " & Me.txtJobNum.Value & "  Not Found", "R", Me) 'The "R" changes the font color to Red, indicating it's critical
        Me.txtJobNum.Value = ""
    End If
    
Exit_Procedure:
    On Error Resume Next
    
    rs.Close
    Set rs = Nothing
    
    Set db = Nothing
    Exit Sub
    
Error_Handler:
    MsgBox ("Please Report Error:  " & Err.Number & vbCrLf & Err.Description)
    Resume Exit_Procedure
End Sub

Maybe some clarification would help me better understand - What is actually effected in the BeforeUpdate and AfterUpdate of a (bound) object on a bound form? i.e. the table data isn't updated until a save is executed - i.e. the form's data is saved between the form's BeforeUpdate and AfterUpdate events, correct?
MikeLyons
Another commonly used technique is instead of a textbox use a combobox that contains only the valid entries, and set the LimitToList property to Yes.
nce you do this, if the user enters a value that is not in the list of valid entries, Access will fire the NotInList event which lets you intercept this condition if you like, and handle it your own way. If you don't implement the event procedure, Access just throws up its standard error message about having not selected a valid entry.
Mike
missinglinq
Mike's suggestion is great assuming that there are a reasonably small, finite set of correct data.
Validation of Format, such as all alpha or one-alpha + 5 numeric or all numeric, etc. is another ball of wax.

Some rules of thumb for Validation

To Validate that data entered is correct in format, value range, etc, use the ControlName_BeforeUpdate event.
To Validate that one or more controls actually has data, use the Form_BeforeUpdate event.
To do Validation of one control in relation to another control, i.e. StartDate must be before EndDate, etc. use Form_BeforeUpdate.
In a Bound Form, in order to check that the Job# actually existed in the Table you could simply use the DCount() function against the domain (Table or Query)
BTW, using an Octothorp (also known as a pound sign or hash mark or number symbol #) in a field name is a very bad idea. The # is used in Access VBA to delimit literal Dates, and two of them appearing in a line of code can sometimes confuse Access into thinking that everything between them is a Date, when it actually isn't. I'll use JobNo, instead, in the following DCount() examples:
If JobNo is a Numeric Datatype

CODE
If DCount("JobNo", "TableOrQueryName", "JobNo = " & Me.JobNo) > 0 Then  
  'JobNo exists in Table/Query
End If

If JobNo is a Text Datatype
CODE
If DCount("JobNo", "TableOrQueryName", "JobNo = '" & Me.JobNo & "'") > 0 Then  
  'JobNo exists in Table/Query
End If

Linq ;0)>
edaroc
Thank you Ling & Mike for your replies.
here's a lot of good tidbits for me. I appreciate your advice.
What I'm going to do, because I do want to get a 1st version into the user's hands today/ASAP, is finish this using the techniques I'm familiar with. The users saw a demo of the current version. I will go right back into this and make the changes. It's easy to install new versions and it is transparent to them.
edaroc
Which Event Is Recommended When I Want To Enable/Disable Another Object Based On The Object(s) Being Exited?
I'm trying to get my mind around this, get my mind to automatically see the flow and how everything best integrates.
ere's my code, after reading your guidelines I think I want to change this to the AfterUpdate event, not the Exit.
Because: AfterUpdate executes only if data was changed, while Exit always executes when the object is exited, regardless of the data.
- BTW I have the same code in the txtJobNum_Exit, Form_Load and Form_Current events
Rule: cmbFileNum is dependent on the JobNum and FileNum, it's RowSource is a query [The database field for FileNum is Drawing_No]:
SELECT SPECS.DRAWING_NO FROM ORDERS LEFT JOIN SPECS ON ORDERS.SPEC_NO = SPECS.SPEC_NO WHERE (((ORDERS.JOB_NUMBER)=[Forms]![frmPlateRemakesPress]![txtJobNum]) AND ((ORDERS.FORM_NO)=[Forms]![frmPlateRemakesPress]![txtFormNum])) GROUP BY SPECS.DRAWING_NO ORDER BY SPECS.DRAWING_NO;
CODE
Private Sub txtFormNum_Exit(Cancel As Integer)
    'File# - Active/Inactive
    
    On Error GoTo Error_Handler
    
    'File# - Not Available Until Job# & Form# have values
    If IsNull(Me.txtJobNum.Value) Or IsNull(Me.txtFormNum.Value) Then
        Me.cmbFileNum.Enabled = False
        Me.cmbFileNum.Locked = True
    Else
        Me.cmbFileNum.Enabled = True
        Me.cmbFileNum.Locked = False
    End If
    
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
    
Error_Handler:
    MsgBox ("Please Report Error:  " & Err.Number & vbCrLf & Err.Description)
    Resume Exit_Procedure
End Sub

Rats! I told my boss the other day I'm getting pretty good with Access VBA - and now I feel like I have so much to learn and understand to be really proficient at the simple stuff and knock off new proj
edaroc
I found the answer ... use the AfterUpdate event.
ote: Remember to consider placing code in the Form_Load event to initialize whether or not that object is enabled.
BruceM
First, text boxes, combo boxes, labels, and just about anything you can place on a form or report is a control; the form or report is an object. Consistent terminology may help make the questions easier to understand.
ote that this will enable/disable, lock\unlock, etc. controls for all records until the next time you update the control (or until you close the form). The control's After Update event is the way to accomplish enable/disable, etc. immediately, but I expect you will also want to run the same code (or something similar) in the form's Current event so that you aren't prevented from changing or adding data in another record (including a new record).
That may be the case if enable/disable is based on the user name or network login name or something like that, but not if enable/disable is based on field values, in which case you would use the form's Current event. Again, you don't want to disable based on a single record, then have no good way to enable the control for another record.
I'm not clear on whether you are using your original code for anything, but this line caught my attention:
If rs.RecordCount Then ...
RecordCount is a long integer, so this expression doesn't accomplish anything I can see.
edaroc
Bruce - Thank you for taking the time to reply and include all that information.
Thanks for pointing out the terminology.
Question about terminology - so that I can get my head in the right place.
From an OOP perspective the controls are objects, correct? (Since my decades of programming was before OOP, I'm still trying to train my head to think OOP)
Assuming the answer is Yes, then, what I need to get into my thick skull is when discussing Access I want to differentiate controls and objects (from an Access perspective). I can understand the confusion created when I say "object" when it is a control.
Since I last posted I learned some stuff as I worked with handling user entering (and changing) data.
Orealized, while working on the code in the AfterUpdate event that the place to put the initializing code would be in the Current event, not Load. So, I caught that one before I got to it. Thanks for the confirmation - it's good to hear that I'm catching on (sometimes, ha, ha).
That's how I'm checking for a valid Job# being entered AND retrieving the Customer Name which is displayed. A positive value (number of records) will be True, no records (0) is False.
Finally, I've discovered that control.oldvalue does not refer to the previous value in the control (user enters 12345 for Job#, then returns to the field and enters 88888; .oldvalue is the (correct me if I'm wrong) underlying record's value. Is there a property for a control's previous value? i.e. Current Record: value = 111, user enters the control and types in 222, then exits the control, user enters the control again (record has not been saved) and types in 333. Is there a property containing the 222 value?
Learning is so much fun (I only wish I didn't have ASAP deadlines! Then again, that adds excitement to the challenge).
edaroc
Bruce - Thank you for taking the time to reply and include all that information.
Thanks for pointing out the terminology.
Question about terminology - so that I can get my head in the right place.
From an OOP perspective the controls are objects, correct? (Since my decades of programming was before OOP, I'm still trying to train my head to think OOP)
Assuming the answer is Yes, then, what I need to get into my thick skull is when discussing Access I want to differentiate controls and objects (from an Access perspective). I can understand the confusion created when I say "object" when it is a control.
Since I last posted I learned some stuff as I worked with handling user entering (and changing) data.
Orealized, while working on the code in the AfterUpdate event that the place to put the initializing code would be in the Current event, not Load. So, I caught that one before I got to it. Thanks for the confirmation - it's good to hear that I'm catching on (sometimes, ha, ha).
That's how I'm checking for a valid Job# being entered AND retrieving the Customer Name which is displayed. A positive value (number of records) will be True, no records (0) is False.
Finally, I've discovered that control.oldvalue does not refer to the previous value in the control (user enters 12345 for Job#, then returns to the field and enters 88888; .oldvalue is the (correct me if I'm wrong) underlying record's value. Is there a property for a control's previous value? i.e. Current Record: value = 111, user enters the control and types in 222, then exits the control, user enters the control again (record has not been saved) and types in 333. Is there a property containing the 222 value?
Learning is so much fun (I only wish I didn't have ASAP deadlines! Then again, that adds excitement to the challenge).
BruceM
I believe that is correct. "Traditional" Access objects include tables, queries, forms, reports, and modules. In VBA, an object (as I understand it) is something that can be identified and manipulated via code, which includes controls such as text boxes. You didn't really create confusion, but I thought the potential was there, so I mentioned it.
OK, I see now. I check whether the recordcount is 0, but I thought of a quick way to test your approach, and it seems to work.
Correct. It refers to the last saved value (the unedited value as it is described in VBA Help).
No. There are ways you could contrive to store the 222 value in your example. For instance, you could use the control's After Update event to write the control's value to a variable, or to the control's Tag property (you would probably want to set the Tag property to "" in the form's Current event). Use of the Tag property in this way can be useful as a substitute for OldValue in unbound controls, to use one example. A search may be performed using parameters from unbound controls. If the search returns records, set the control's Tag property to its value:
Me.ControlName.Tag = Me.ControlName
If no records:
Me.ControlName = Me.ControlName.Tag
You could create an array, I suppose, but I would think it would be cumbersome to retrieve the desired value from the array.
edaroc
Hi Bruce,
Thanks for all your advise. I appreciate it.
Ed
BruceM
Along with everybody at UA, glad to help. Good luck with the project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.