EXPRESSION PROBLEM IN FORM

[desikan]

Hi, I have an interesting problem regarding the correct code for expression.

I have a form with fields "type","item","order". To ensure that all the fields are filled by the user, there is a small code in the before update as :

If Len(Me!type & "&quot = 0 Or Len(Me!order & "&quot = 0 Or Len(Me!item & "&quot = 0 Then

MsgBox "You Must enter data in all fields", vbInformation + vbOKOnly
Cancel = True
End If

Now my after update has a macro to start an append query.

Now everything works fine. If the conditions for before update are not fulfilled, I get a MsgBox "You Must enter data in all fields", and then the usual message from access that "You can't save this record" and the form closes.

Now is it possible to replace this message from the system by my own message with the same "yes' "no" options. With "yes" the form can close with out running the macro and with "no" the form can comtinue.

Thanks in advance

 

[redwoodly]

to prevent a form closing without all fields complete:

I set a private variable at the top of the form's code (under Option compare database & option explicit) :

Private intNotComplete as integer ' indicates if form is complete or not.
-------------------------
Then in your Form's OnClose event, put:
if intNotComplete = 1 then docmd.CancelEvent
-------------
in Form's OnCurrent Event put:
intNotComplete = 0 ' reset the variable
--------------------
then rather than your statement in the Form's BeforeUpdate put (something like):

dim intResp as integer

intResp = Msgbox("You Must enter data in all fields." & vblf & vblf & "Select OK to quit or Cancel to return to form to complete missing fields.", vbokCancel, "Required Fields Missing&quot

if intresp = 1 then ' user wants to quit
cancel=true
intNotcomplete = 0
elseif intresp = 2 then ' user wants to continue
intNotComplete = 1
Me.Dirty = True
Cancel = True
end if

---------
either way, cancel = true, and it will not run your AfterUpdate event until all fields are complete.

(does that make sense? ha)
regarding msg box:
if you'd rather YEs/No instead of Ok/Cancel use: vbYesNO.
(see HELP - MsgBox Constants for a list of all the options)
and same place for the return values for the buttons in a msg box (the bottom half of the page has the returns - i.e., that clicking ok = 1, cancel = 2, yes = 6,no - 7 etc.) this will be the values the you check intResp for.

I hope this is what you were asking....I wasn't sure.

 

[desikan]

Well, redwoodly thanks for your reply.
I put your code and I found that I get the message "Select OK to quit or Cancel to return to form to complete missing fields." whether all fields are filled or not filled.

Also whether I click ok or cancel I get Microsoft access message that you can't save this record with yes and no options.

What I would like to say is that this usual Microsoft access message is confusing for data entry staff and hence I want this microsoft access message to be suppressed and the form to be closed or continued with previous message ok/cancel only.

I am also trying to find out why we get the message of ok/cancel when all fields are filled. Any clues?

Thanks