Message box if fields are blank 1

[kaiana]

Hi All,

I have been struggling with this one for a while and have tried several different options.

Problem: I have a form that is on continuous form and I have fields that I want entered. If these fields are not entered on moving from that record, I want a message box coming up giving them the option to reenter data or continue without saving.

Option 1: Put "Is not null" in Validation rule and message in validation text. For some reason the validation rule just does not kick in, am I missing something. I have tried it in both the table and the form but the message does not come up. The record, however is not saved.

Option 2: With my single form forms, I use all my own buttons, (not the navigation buttons) and put code on all buttons, this works beautifully, however with continuous form forms, the user can easily move to another record without using buttons, so I need something more general. I have tried putting code in the close button, works beautifully when clicking close, but nothing else. Have tried Before update event but then if you click close it brings up message but closes regardless of options, if I put it in both it brings up the message box twice.

Is there a more efficient way of doing this and ensuring that whatever the user chooses to do that a record cannot be saved without those fields entered.

Thanks

 

[judgehopkins]

On your table and on your form, get rid of all the validation rules. Then try something like the following to force your user to enter something.

This procedure forces a user on my form to enter an order number in a correct format.

(Please make sure, if you copy and paste this code, that you double check all the remarks and line continuations; it's just too hard to try to format this to look right in the little bitty boxes we have to write messages in!)

Private Sub OrderNumber_LostFocus()
'On a new record, if the user moves the focus off
'OrderNumber, this procedure requires the user to enter
'a correct order number but also gives the option to cancel
'and go back to the last order entered.
If IsNull(Me!OrderNumber) Or Len(Me!OrderNumber) < 6 _
Or Not Me!OrderNumber Like &quot;##-####&quot; Then
   'Response = acDataErrContinue
   ' Me.Undo
   If (MsgBox(&quot;Click OK to enter an order number in the format 03-1234&quot; _
    & vbCrLf & &quot;or click CANCEL to return to the last order entered.&quot;, _
    vbOKCancel, &quot;On a new record you must enter an order number first!&quot;) _
    = vbOK) Then
'An Access bug makes you set the focus to the prior
'or next control on a form before you can re-set the
'focus to the control you want it on
        Me!Refinance.SetFocus
        Me!OrderNumber.SetFocus
    Else
        DoCmd.GoToRecord , , acLast
        Me!Refinance.SetFocus
        Me!OrderNumber.SetFocus
    End If
End If

End Sub

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[kaiana]

Thanks for your comments, I have several fields on a form that require data entered into and I have found that a lot of people use the mouse to click on the fields they want instead of tabbing through the fields. If they do this lost focus won't work as the field may never have had the focus??

 

[BillPower]

Hi kaiana,

Try this thread702-556034 seems similar to yours.

Regards

Bill

 

[kaiana]

This still won't work if the field has never had the focus will it??

 

[BillPower]

Yep, you're right. I need to point that out to lpf1836 too.

I think similar code should work in the Before Update of the Form.

Bill

 

[kaiana]

Yes, but if you read my original question, this is what is causing the problem. When the close button is clicked it closes regardless of what is in the before update event. and If I put the code on the close button as well, it runs the message twice.

 

[BillPower]

Hi kaiana,

I've knocked together a demo called kaiana.zip for download at

http://www.wpower.fsbusiness.co.uk/

Rather than post a whole heap of code that might not get used, get back to me with any questions. As far as I can see it should do what you want.

Regards

Bill

 

[BillPower]

Well,

That nearly worked. Unfortunately when adding a New Record, clicking Exit if there was a Null, would cause the WHOLE Record to be Deleted.

There is an Update KaianaVer2.zip which I am quietly confident does the job, by taking another approach.

Bill

 

[kaiana]

Bill,

Thanks for you time and the code. I will try to get to it later today and let you know. Here's a star for you efforts. Much appreciated.