Close form with code

[CoachPoffy]

Good evening, I have an unlinked form for entering data into a table. Yes, I know I can link it, but I would prefer to have the form filled out and verified before the data is written in the table. I have a save button with coding behind it to transfer the data from the form to the table using .update.

My issue today is that on this form, I have a field that has to be filled in or the coding will prevent the user from saving the record to the table. This field is at the top of the form, so to help the user from wasting time, I want to add a msgbox and coding to allow the user to exit the form if they do not have that information. Here is sample coding:

Dim cdomerr As Integer


If [LLEF_DOM].Value = "" Then
cdomerr = MsgBox("You have not entered a Date of Manufacture. Do you wish to quit?", vbYesNoCancel, "DOM Error")
If cdomerr = vbYes Then 'They wish to exit
'close form
DoCmd.Close , , acSaveNo

Else
'return to field
[LLEF_TIME].SetFocus
[LLEF_DOM].SetFocus
End If

I get an error that the Docmd cannot be performed from a "on lost focus" event procedure. I understand why. I tried putting the code on the "after Update" event procedure, but if the operator just tabs through the field, it doesn't work.

How can I program the form so that if the user merely tabs through the field (leaves it blank) a message box pops up and asks the user if they wish to quit. If answer is yes, the form closes.

Any help would be greatly appreciated

[ssanfu]

I have a save button

Put the code in the save button click event.


To close the form, use:

DoCmd.Close acForm, Me.Name

"acSaveNo" is only for the form DESIGN changes. It is not needed to close the form

[June7]

So user might initiate data entry without knowing the Date of Manufacture info?

What about the control's OnExit event? Same issue as OnLostFocus?

[CoachPoffy]

ssanfu - I have a save button, but it is at the end of the form and about 30 fields away. Looking for an instantaneous event on this field which is the 2nd field on the form.

June7 - Yes, tried it on "on exit". Same issue. According to my research, the field has to update in order for the DoCmd.close function to work. One way to do that is .dirty=true for the field. Hit another wall here because you cannot set an unbound fields dirty, can only be done on bound fields.

I am trying to figure out a loop or something in code that if the user leaves the field "" or null, progmatically I can update the field loop back and use the "after update" event procedure. Any thoughts?

[June7]

You mean unbound control, not field.

Maybe if the control is "" set to Null, if it is Null, set to ""?

[ssanfu]

I see what you mean about trying to use a control event to close the form.
I did come up with a two step work around...... don't know if you will like it....

[Missinglinq]

The bottom line, here, is that you simply cannot reliably test whether or not a Control is populated using an event of that is associated with the Control. The only thing a user has to do, to defeat the test, is to simply not enter the Control.

As for your statement

...I know I can link it, but I would prefer to have the form filled out and verified before the data is written in the table...

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

There's nothing that can be done using Unbound Forms, including data validation, that cannot be accomplished with Bound Forms, and usually accomplished with less work!

The only approach that I can think of, for this, would involve moving your warning, reminding the user that the Date of Manufacture has to be be available, when the Form first loads, and letting them either back out or proceed. You'd still need to check, before saving the Record, to see whether the DOM was present, but if they ignored the warning and filled out all 30 Controls, then lost the Record, that would be on them! My guess is that they wouldn't do it, many times, before they got the message.

Linq ;0)>