Making Field Entry Mandatory Before Moving on to the Next Field

[nicolaasjan2005]

I tried to run a search, but, I'm likely not entering the correct question - so I'm hoping someone can provide an answer or steer me in the right direction.

I have a subform in which the user enters a date in the medium date format (using the input mask).

Is there some way that I can make this date field mandatory so the user cannot proceed to the next field until a date is entered? I don't need a specific date - it can be any date entered to allow for the entry of historical data. I'm not sure if this can be done in the validation rule property of the field.

 

[boblarson]

I wouldn't make them stay in it until they put in a valid date. Perhaps they choose to vacate the record and not do so, but you've locked them in.

I would use the form's BEFORE UPDATE event to make sure that all is filled out for a record before saving and that way, they can have more freedom when filling out the record but you validate before saving and if it fails validation it can be asked for at that time or the update canceled.

 

[mcmichael13]

another solution is simple vba code... have this attached to a cmd button or such:

If IsNull(Me!Date) Then
MsgBox "There is no date entered"
Exit Sub
End If

That way if the "Date" field is empty.... then this message box pops up and cancels the sub too


hope that helps

 

[boblarson]

another solution is simple vba code... have this attached to a cmd button or such:

If IsNull(Me!Date) Then
MsgBox "There is no date entered"
Exit Sub
End If

That way if the "Date" field is empty.... then this message box pops up and cancels the sub too


hope that helps

Just an FYI - but "attached to a cmd button or such" will not keep it from updating the record if the field is null. It would need to be on the Before Update event of the form, as mentioned by me in the previous post, and if you wanted to cancel the update you would issue a
Cancel = True

instead of the Exit Sub.

 

[Banana]

An option that requires no code at all is to set the field in table design to required. IIRC, you still have freedom of moving around but trying to saving the record without that required filed fill in will result in an error. The only problem is that it's quite inflexible (A either/or proposition) and may not be appropriate for cases where you need it to be conditionally blank for whatever.

I also would second Bob's suggestion of using BeforeUpdate to handle all validation. Any save button's On Click event should only have one line of code: Save the record. It'll then trigger the BeforeUpdate and a good programmer keeps his/her code in one place.

 

[mcmichael13]

really? I'm curious as to why my submit button doesn't submit anything unless i have all my "required" fields entered. I'm not trying to sound sarcastic, I'm just wondering why my code wont submit it unless they are all entered. Would it be because i have the Field checking before the submission code? and being that its before, it cancels the command unless it passes the validation?

not attempting to thread jack, but i think its pertinent.

 

[boblarson]

really? I'm curious as to why my submit button doesn't submit anything unless i have all my "required" fields entered. I'm not trying to sound sarcastic, I'm just wondering why my code wont submit it unless they are all entered. Would it be because i have the Field checking before the submission code? and being that its before, it cancels the command unless it passes the validation?

not attempting to thread jack, but i think its pertinent.

Clicking a button will not do anything for the Before Update event. That is where the "rubber meets the road." That is the last chance, so to speak, where you can cancel an update. And, if you have your click event of your button, it will do as it showed. It will give a message and that is it. If the field is still null and someone navigates to another record, your button will not do anything but sit there. However, the Before Update event will fire because the form is dirty (some change has occured on the form). Also, if the user closes the form, the data would save without the code being in the Before Update event because it will attempt to write the data and fire the Before Update event as the form is closed. The only way to stop the form from being closed and to cancel the update is to issue a Cancel = True from the Before Update event.

So, hopefully that helps. As they say, "the right tool for the right job." Well the same thing could be said for events - "the right event for the right job."

 

[mcmichael13]

hmm i think you have misinterpreted me. maybe i misunderstood you. my code works fine. i have nothing in the "before update". everything is enclosed in the Private Sub Submit_click. You said that this wouldn't keep my submission from canceling if the fields are null, but mine does. If that message window pops up saying a field is missing, the submission to my record list is cancelled. I have a final message window that says "New RMA #_____ has been submitted" when the cmd is finished and the submission is completed, but if i have a null field, the entire sub is canceled.

does that make sense? I'm still new to all this, mechanical engineer (to graduate next year) not a programmer haha. But this is fascinating to me and i enjoy it so I'm always wanting to learn, and I have used your advice Bob on many other things for learning purposes.

 

[Banana]

Few questions i want to clarify-

Is the form bound?
Do you provide navigation buttons or otherwise allow your user to scroll through the records?
Did you set any fields' Required property to Yes?
Did you use Validation Text/Rule property?
If you said yes to either last two questions, is that true for *all* of the fields?
What happens if you fill a record completely, accordingly to your rules, then don't click the button but leave the form (closing it or moving to another record?)

 

[boblarson]

hmm i think you have misinterpreted me. maybe i misunderstood you. my code works fine. i have nothing in the "before update". everything is enclosed in the Private Sub Submit_click. You said that this wouldn't keep my submission from canceling if the fields are null, but mine does. If that message window pops up saying a field is missing, the submission to my record list is cancelled. I have a final message window that says "New RMA #_____ has been submitted" when the cmd is finished and the submission is completed, but if i have a null field, the entire sub is canceled.

does that make sense? I'm still new to all this, mechanical engineer (to graduate next year) not a programmer haha. But this is fascinating to me and i enjoy it so I'm always wanting to learn, and I have used your advice Bob on many other things for learning purposes.

So, let me ask you this. If you start to fill in a record and you close the form without populating that one field, does it save? Unless you have the Required property set to YES in the table, it should save and not give you any warning.

 

[mcmichael13]

Few questions i want to clarify-

Is the form bound?
Do you provide navigation buttons or otherwise allow your user to scroll through the records?
Did you set any fields' Required property to Yes?
Did you use Validation Text/Rule property?
If you said yes to either last two questions, is that true for *all* of the fields?
What happens if you fill a record completely, accordingly to your rules, then don't click the button but leave the form (closing it or moving to another record?)

1. the form is unbound

2. i have a "submit" button, a "report generation", and a "RMA List"... the "RMA List" button displays the table that the form is saving all the information to.

3. Not that i know of

4. again i dont know haha

5. nothing happens, if i close the form without hitting the "submit" button, the information will not be submitted to the table.

 

[Banana]

#1 is precisely why.

If it's unbound, then it's fully DIY and you couldn't use BeforeUpdate anyway, and it wouldn't be saved to the table as it would be if it was bound.

Though, I'd like to ask you- why unbound?

 

[boblarson]

1. the form is unbound
.

That right there shows why things work differently for you. Most Access users (not all) use BOUND forms and they need to use the Before Update event to handle things. Your advice was off, simply because it didn't fit the situation then.

 

[mcmichael13]

oh my apologies, thank you for that. and i'm sorry to the OP

as to why unbound... i dont actually know, its what i learned, havent learned how to use bound forms yet...

 

[boblarson]

havent learned how to use bound forms yet...

Actually, that is one of the benefits of Access and the Rapid Application Development. Not using bound forms - you might as well just have thrown 90% of the value of the program out the window. I find it interesting that people will do that. There are TIMES when you need to use unbound forms, but to just not use them - you might as well just write the program in VB, VB.NET or something like that.

 

[mcmichael13]

i'm sure my database is a horror to real programmers haha its not much code at all really, but it performs what i need it to do very well.


again my apologies to the OP, i hope you got your answer from all this...

 

[nicolaasjan2005]

Thanks a million for all the input everyone! It's most appreciated. Both setting the Required property to YES and adding the code to the BEFORE UPDATE work. I really like the discussion - a lot of good points from a lot of experience!

Thanks again!