Empty Form Fields (1 Viewer)

[Acme]

I know I am beating a dead horse here but I need to get it out there.

I have a form that has validation rules, etc, and I want to let the user close it with custom error handling, so I was building a validation procedure for a form close button. The thing is, whether using Isnull, len()=0, or me.field.value = "", it only works until a value is entered. If the user enters a value, the form passes validation, even if the user then backspaces the data out. The only reasonable way to close the form as I see it is to check whether the record has been saved (via recordset), and if it hasn't, advise the user their changes will be lost (msgbox vbyesno), then use the undo command to clear the form making it safe to close it. I feel this is safe because my form validation will cover the save command.

has anyone else done it this way?

 

[Acme]

Oh great, the above won't work because access writes to the database the second one of the form fields isnot null. I'm telling you, validating that a form is the way it is supposed to be is impossible in access. There always seems to be a way for a user to screw it up without access detecting it.

 

[shadow9449]

I'm not quite following what you are saying about the problem with the form.

The way I usually do data validation is from the table level. That way any form designed will be guaranteed to not permit a user to enter invalid data. I don't need to worry about coding up every form or if the user finds a way to override the coding on the form. It's easy to do and it's guaranteed to work.

I hope this helps.

SHADOW

 

[Acme]

I agree with you. My frustration with defining the rules in the table has been what happens when the user does something unexpected. I like to give users a lot of latitude when working with forms. The app I am developing has some very short forms where I don't mind the system telling users a field is required so they cannot navigate off the record or close the form without losing data. However, I have two forms that are probably bigger than they should be, but as small as they can be given the purpose, and while many fields are necessary for the user to move through the app with the desired result, if they leave certain fields empty, I let them save it anyway instead of bombarding them with error messages. On error resume next can eliminate these messages, but doesn't resolve the underlying issue - incomplete records. So some fields are required for a record to exist, the record will not have to be final for it to be saved - I do this by manually coding saves as Rs.addrecord to capture the mandatory fields, and rs.edit to update the record with additional data. Is this bad form?

 

[spikepl]

It is not clear what you do, but it sounds like you are not using the available tools to ensure data integrity. Your philosophy of allowing incomplete records to be saved could perhaps work if you marked those records as incomplete and thus requiring some further action. Otherwise it does not make sense to allow such saves.

Check this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=248729 and the last attached db

 

[Acme]

It comes down to what the field is "required" for and its been a dilemma for me. Supposed you wanted to provide a sports team with a data on player statistics that would be used to figure out a players bonus. You would need certain info to create a record for the player such as ID, name, team, position, etc. But suppose you didn't have all the statistics yet, you would maybe fill in some of them and leave the rest til later. Meanwhile, you would not really able complete the next record (bonus calculation) without all the stats right? Should you not be allowed to create the player record? The risk is you open the bonus record with incomplete stats, but that would be kind of obvious. I wouldn't want to prevent the user from opening the player stats record in the bonus form, but would need the user to be aware that the bonus calculation is only as good as the player stats record. So if the bonus for Home Runs was 0, they would need to be able to understand that they may have missed something. After all, maybe there were 0 homeruns. Thoughts?

 

[spikepl]

There is nothing "obvious" in data.. Either it is complete or it isn't. If it is acceptable that it is not, then tag it as Incomplete.

If you can mess things up because you are working with incomplete data, but you still wish to proceed, then the result should also be prominently tagged as being based on incomplete data. In fact - the notification should be prominently displayed within the results, or else some optimist will forget it.

You cannot in any way rely on users finding anything "obvious" or remembering to do something.

 

[Acme]

Well said. So on the equivelant of stats, I have various status (draft, proposed, pending, executed, closed) and the user defines these. What you are saying is I need to still do some validating so that if a record "appears" incomplete, say, the quantity = the default quantity, or the homeruns if you will are 0, then there should be a record check field that says so, "Appears Incomplete" highlighted in yellow, for example. That's a good idea. I will do that.

 

[Acme]

Ok, I did what you suggested. I am looking at table record field values and running rules against them to see if there is an issue with the record, and displaying a status with conditional formatting. I am using a recordset to get the field values from the table. My issue is that when I click the button to add a new record, the message says "#Error" and this is bugging me a lot because the rules say if RS.recordcount = 0 then "New Record" message. I think that when a form is at a new record, you really cannot reference any form fields because there isn't anything there. Can someone tell me what I am supposed to do in this situation?

 

[Pat Hartman]

I'm telling you, validating that a form is the way it is supposed to be is impossible in access. There always seems to be a way for a user to screw it up without access detecting it.

It is not a problem once you understand how forms work. As you have already discovered, ZLS (Zero Length String) is not the same as Null. That means you need to account for both in your validation code. There are two common solutions.

1.  If Me.txtSomeField & "" = "" Then
    'field is ZLS or null
Else
    'field has some other value
End If
2.  If Len(Me.txtSomeField) > 0 Then
    'field has some other value
Else
    'field is ZLS or null
End If

The absolute most important thing you must understand about form validation is that the Form's BeforeUpdate event is the last event to fire before a record is saved. That means that any validation that involves checking for null/ZLS or the relationship between two fields should be done here. If you attempt to do it in other events, you will end up with way too much code and may still miss places where Access saves a record and got past you. Think of this event as the funnel through which all data saves must pass. If your code finds a problem, you simply cancel the event to prevent the record from being saved.

 

[Acme]

Thanks Paul,

You have been very helpful during my little summer project. So I am going to keep sharing what I am doing with you for your insights.

So I have two forms that are relatively complicated out of about 40 forms. I have tried several macro based checks but with a long form, unless you have a very long and complex data macro (which may just as well be coded at that point anyway), the user could be bombarded with popup validity messages, and then still may get some of those "Stop all macros?" boxes which I think are a bad user experience. So my next attempt was a record status function, which displayed #error when a new record was created in the form. So now I am using a sub whereby the user clicks "Record Status" which populates a record state text box indicating whether the form appears ok or incomplete - and if incomplete, they cannot use my one-click advance feature which saves the record, closes the form, and opens the next form in the process, but they CAN save the record as a work in progress. The status of the record appears on the next form in the process. Also, when they click on Record Status, they get a pop up with all of the reasons why the form does not Appear OK. Thoughts? I agree with you on the before update, and for most forms that will work. But on a couple of mine, a validation rule might not apply in certain scenarios - so I don't want to train the user to believe that they always need to fill in a field when it is only needed in certain situations. You been there I am sure - would appreciate your feedback.

 

[Pat Hartman]

It's Pat not Paul.

and for most forms that will work

NO -- it works for ALL forms. Occasionally we put validation code in the BeforeUpdate events of individual controls but that is usually because we want to give the user an immediate message and not wait until he has filled in the entire form. But, EVERY rule that deals with null/ZLS or multiple fields MUST be repeated in the BeforeUpdate event to ensure bad data doesn't get saved.

I don't use Data Macros since most of my BE's are SQL Server so I don't know if you can make them conditional. If you can't and the field is optional or not, based on some other field, you will need to do the validation in the FORM's BeforeUpdate event instead. You are correct. You don't want to bombard the user with messages whether they are informational, warning, or errors. If you display too many messages, the users will become desensitized and dismiss them all without understanding what is happening.

 

[Acme]

I didn't mean that it "doesn't work", I meant, it may not be practical in all situations. For example, supposed you are filling out a form that takes a while, like say, a 1040. So you get almost through with it, and realize you are missing something. You don't want to throw away the 20 minutes you spent starting it, but you cannot submit your tax return yet either. So you should be able to save it, and finish it later. It will be saved, even though there are required fields missing. If you prevent records from being saved because they are incomplete as a rule, then the 20 minutes would be lost. There just needs to be a check to ensure that the return is not filed before the form is complete. That's all I was getting at. Of course, even the partial form would still require some data to even create a record (tax id, name, etc).

 

[gemma-the-husky]

I didn't mean that it "doesn't work", I meant, it may not be practical in all situations. For example, supposed you are filling out a form that takes a while, like say, a 1040. So you get almost through with it, and realize you are missing something. You don't want to throw away the 20 minutes you spent starting it, but you cannot submit your tax return yet either. So you should be able to save it, and finish it later. It will be saved, even though there are required fields missing. If you prevent records from being saved because they are incomplete as a rule, then the 20 minutes would be lost. There just needs to be a check to ensure that the return is not filed before the form is complete. That's all I was getting at. Of course, even the partial form would still require some data to even create a record (tax id, name, etc).

so it just moves the problem to a different place.

somehow you are submitting this data. at that point you need to run the validity check, and if it fails, prevent it submitting.

in this case, you cannot make the field required at table level. you have to validate it another way.

function isvalid () as boolean
 
'a series of checks
if nz(somenumber1,0)=0 then goto invalid
if nz(somenumber2,0)=0 then goto invalid
if nz(sometext1,"")="" then goto invalid
if nz(somedata1,0)=0 then goto invalid
 
isvalid = true
exit function
 
invalid:
msgbox "sorry. the data is not complete. All the fields with blue backgrounds must be completed"
isvalid = false
exit function
 
end function

 

[Acme]

That's precisely what I did. There is a subsequent form that must be processes through various stages. It displays the status of the previous form and the status of the current form, and has a record status button that pops up with the noted gaps in the record. And the status cannot be moved forward if there any gaps, and and subsequent forms record sources exclude incomplete status.

 

[Acme]

In doing the check, I included the nz(me.value,0) function in the dimension definitions. For example:
Sub CalcRecordSt
Dim lFlag as Long
Dim sMessage as String
Dim dQty as Double

lFlag = 0
sMessage = "Noted Issues: "
dQty = nz(Forms![Form]![Field].value,0)
If dQty <= 0 Then
lFlag = lFlag + 1
sMessage =s sMessage & " Qty <= 0. "
Else
lFlag = lFlag
sMessage = sMessage
End If

If lFlag > 0 Then
Meform.control.value = "Record Incomplete"
MsgBox & sMessage
Else
MeForm.control.value = "Record Appears OK"
MsgBox "No Issues Noted"
End If

End Sub

It looks like most people would do the Nz function in the if statement instead of the variable definitions.

 

[Pat Hartman]

If you don't want to require records to be complete when they are saved, you need an additional data field in the table to indicate complete/incomplete and a button on your form. Unless the user presses the button, the validation rules don't run. Only if the validation is successful is the complete flag set. Since the complete flag isn't accessible via the interface (if you display it on the form, you MUST lock it), the user can't change the value. The value is only changed by your validation code.