Multiple Forms, Error Checking

[Gary Dolliver]

Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[strive4peace]

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Gary Dolliver]

Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[strive4peace]

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[Gary Dolliver]

Hi Crystal!
Wow, you are opening up whole new worlds to me!!
Sorry to say that I am not quite sure what everything means.
1. Where would I put the function code, you mention either in a module or
behind the form - but, should this be in a sub command or what about global
versus private?? I would like this function to be global so I can call it
from different forms.
2. I am not entirely sure about what to change in the code provided. I have
done the following:

function CheckItem(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("Creation_ID","items",pCriteria),0)
if mRecordID = 0 then
CheckItem = false
else
CheckItem = true
end if
end function

if not CheckCheckItem() then
msgbox "An Item does not exist in this creation. Please add an
item.",,"Cannot save record yet"
Cancel = true
end if

I was not sure what pCriteria was, but am assuming it is something I need to
change. Help?

Thanks!
-gary

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[strive4peace]

Hi Gary,

Hello world!

Function CheckItem would go into a general module since it does not use
a reference to a control or a form or report. What it needs is
passed... hence pCriteria

'p' is my notation for a passed variable (as opposed to a global variabl
or a variable created in the procedure)

pCriteria is a string that will be used to limit the records for looking
up... chances are, you do not want to know if there is ANY Creation_ID
in the Items table because if you have records, it will, of course, find
one. You are wanting to know if there is a Creation_ID for certain
conditions... pCriteria is the string for those conditions.

For example:

"SomeOtherID=9"

~~~

the code snippet (if not CheckCheckItem() then...) would go in the
BeforeUpdate code behind your form

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. Be sure to put "Access Basics" in the subject line so that
I see your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal!
Wow, you are opening up whole new worlds to me!!
Sorry to say that I am not quite sure what everything means.
1. Where would I put the function code, you mention either in a module or
behind the form - but, should this be in a sub command or what about global
versus private?? I would like this function to be global so I can call it
from different forms.
2. I am not entirely sure about what to change in the code provided. I have
done the following:

function CheckItem(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("Creation_ID","items",pCriteria),0)
if mRecordID = 0 then
CheckItem = false
else
CheckItem = true
end if
end function

if not CheckCheckItem() then
msgbox "An Item does not exist in this creation. Please add an
item.",,"Cannot save record yet"
Cancel = true
end if

I was not sure what pCriteria was, but am assuming it is something I need to
change. Help?

Thanks!
-gary

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

:

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[Gary Dolliver]

Hello again,
I am so sorry, I am not sure if it is just too early, but I am still having
troubles
I placed the function in a module and the code behind the BeforeUpdate event
of the Creations form. When I try to test, I get a Compile Error: Argument
Not Optional message, and it is stopping on my code in the form, highlighting
"CheckItem" - I understand that pCriteria is a passed variable, but where is
it defined as to what I am passing to this? I am not sure where I am
supposed to put this, or if it is even needed?
Within the function, I have a variable being delcared:
mRecordID = Nz(DLookup("Creation_ID", "items", pCriteria), 0)
and from my assumption this means that mRecordID is looking up "Creation_ID"
in table "items" where pCriteria??. Would I set up pCriteria as a variable
in the code or the function? both, neither? Something like
Dim pCriteria as String
pCriteria = [Creation_ID].value??
I understand what you are saying (well, maybe I am not) but feel I am
missing just one little thing.
Sorry.
-gary
PS, I emailed you requesting the Access document, I have been struggling
with this project for a couple months. I appreciate the help!

Hi Gary,

Hello world!

Function CheckItem would go into a general module since it does not use
a reference to a control or a form or report. What it needs is
passed... hence pCriteria

'p' is my notation for a passed variable (as opposed to a global variabl
or a variable created in the procedure)

pCriteria is a string that will be used to limit the records for looking
up... chances are, you do not want to know if there is ANY Creation_ID
in the Items table because if you have records, it will, of course, find
one. You are wanting to know if there is a Creation_ID for certain
conditions... pCriteria is the string for those conditions.

For example:

"SomeOtherID=9"

~~~

the code snippet (if not CheckCheckItem() then...) would go in the
BeforeUpdate code behind your form

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. Be sure to put "Access Basics" in the subject line so that
I see your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal!
Wow, you are opening up whole new worlds to me!!
Sorry to say that I am not quite sure what everything means.
1. Where would I put the function code, you mention either in a module or
behind the form - but, should this be in a sub command or what about global
versus private?? I would like this function to be global so I can call it
from different forms.
2. I am not entirely sure about what to change in the code provided. I have
done the following:

function CheckItem(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("Creation_ID","items",pCriteria),0)
if mRecordID = 0 then
CheckItem = false
else
CheckItem = true
end if
end function

if not CheckCheckItem() then
msgbox "An Item does not exist in this creation. Please add an
item.",,"Cannot save record yet"
Cancel = true
end if

I was not sure what pCriteria was, but am assuming it is something I need to
change. Help?

Thanks!
-gary

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

:

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[strive4peace]

Passing Parameter, Line Continuation, Compile
---


Hi Gary,

your criteria string would be something like this:

"[Creation_ID]=" & me.[Creation_ID]

.... and I realized my example useage was not complete, sorry... in the
form BeforeUpdate code:

'~~~~
dim mCriteria as string

'assuming Creation_ID is numeric
mCriteria = "[Creation_ID]=" & me.[Creation_ID]

If not CheckCheckItem(mCriteria) then
msgbox "An Item does not exist in this creation." _
& " Please add an item." _
,,"Cannot save record yet"
Cancel = true
End if
'~~~

GIVEN
the procedure declaration is:
Function CheckItem(pCriteria as string) as Boolean
'~~~

Space Underscore at the end of a line is the Line Continuation symbol
and means that the statement is continued on the next line -- often
makes code easier to read

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)
~~~~~~~~~~~

I just sent you the Access Basics document


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello again,
I am so sorry, I am not sure if it is just too early, but I am still having
troubles
I placed the function in a module and the code behind the BeforeUpdate event
of the Creations form. When I try to test, I get a Compile Error: Argument
Not Optional message, and it is stopping on my code in the form, highlighting
"CheckItem" - I understand that pCriteria is a passed variable, but where is
it defined as to what I am passing to this? I am not sure where I am
supposed to put this, or if it is even needed?
Within the function, I have a variable being delcared:
mRecordID = Nz(DLookup("Creation_ID", "items", pCriteria), 0)
and from my assumption this means that mRecordID is looking up "Creation_ID"
in table "items" where pCriteria??. Would I set up pCriteria as a variable
in the code or the function? both, neither? Something like
Dim pCriteria as String
pCriteria = [Creation_ID].value??
I understand what you are saying (well, maybe I am not) but feel I am
missing just one little thing.
Sorry.
-gary
PS, I emailed you requesting the Access document, I have been struggling
with this project for a couple months. I appreciate the help!

Hi Gary,

Hello world!

Function CheckItem would go into a general module since it does not use
a reference to a control or a form or report. What it needs is
passed... hence pCriteria

'p' is my notation for a passed variable (as opposed to a global variabl
or a variable created in the procedure)

pCriteria is a string that will be used to limit the records for looking
up... chances are, you do not want to know if there is ANY Creation_ID
in the Items table because if you have records, it will, of course, find
one. You are wanting to know if there is a Creation_ID for certain
conditions... pCriteria is the string for those conditions.

For example:

"SomeOtherID=9"

~~~

the code snippet (if not CheckCheckItem() then...) would go in the
BeforeUpdate code behind your form

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. Be sure to put "Access Basics" in the subject line so that
I see your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal!
Wow, you are opening up whole new worlds to me!!
Sorry to say that I am not quite sure what everything means.
1. Where would I put the function code, you mention either in a module or
behind the form - but, should this be in a sub command or what about global
versus private?? I would like this function to be global so I can call it
from different forms.
2. I am not entirely sure about what to change in the code provided. I have
done the following:

function CheckItem(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("Creation_ID","items",pCriteria),0)
if mRecordID = 0 then
CheckItem = false
else
CheckItem = true
end if
end function

if not CheckCheckItem() then
msgbox "An Item does not exist in this creation. Please add an
item.",,"Cannot save record yet"
Cancel = true
end if

I was not sure what pCriteria was, but am assuming it is something I need to
change. Help?

Thanks!
-gary

:

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

:

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary

 

[Gary Dolliver]

Thank you!! That did it!
-gary

Passing Parameter, Line Continuation, Compile
---


Hi Gary,

your criteria string would be something like this:

"[Creation_ID]=" & me.[Creation_ID]

.... and I realized my example useage was not complete, sorry... in the
form BeforeUpdate code:

'~~~~
dim mCriteria as string

'assuming Creation_ID is numeric
mCriteria = "[Creation_ID]=" & me.[Creation_ID]

If not CheckCheckItem(mCriteria) then
msgbox "An Item does not exist in this creation." _
& " Please add an item." _
,,"Cannot save record yet"
Cancel = true
End if
'~~~

GIVEN
the procedure declaration is:
Function CheckItem(pCriteria as string) as Boolean
'~~~

Space Underscore at the end of a line is the Line Continuation symbol
and means that the statement is continued on the next line -- often
makes code easier to read

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)
~~~~~~~~~~~

I just sent you the Access Basics document


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello again,
I am so sorry, I am not sure if it is just too early, but I am still having
troubles
I placed the function in a module and the code behind the BeforeUpdate event
of the Creations form. When I try to test, I get a Compile Error: Argument
Not Optional message, and it is stopping on my code in the form, highlighting
"CheckItem" - I understand that pCriteria is a passed variable, but where is
it defined as to what I am passing to this? I am not sure where I am
supposed to put this, or if it is even needed?
Within the function, I have a variable being delcared:
mRecordID = Nz(DLookup("Creation_ID", "items", pCriteria), 0)
and from my assumption this means that mRecordID is looking up "Creation_ID"
in table "items" where pCriteria??. Would I set up pCriteria as a variable
in the code or the function? both, neither? Something like
Dim pCriteria as String
pCriteria = [Creation_ID].value??
I understand what you are saying (well, maybe I am not) but feel I am
missing just one little thing.
Sorry.
-gary
PS, I emailed you requesting the Access document, I have been struggling
with this project for a couple months. I appreciate the help!

Hi Gary,

Hello world!

Function CheckItem would go into a general module since it does not use
a reference to a control or a form or report. What it needs is
passed... hence pCriteria

'p' is my notation for a passed variable (as opposed to a global variabl
or a variable created in the procedure)

pCriteria is a string that will be used to limit the records for looking
up... chances are, you do not want to know if there is ANY Creation_ID
in the Items table because if you have records, it will, of course, find
one. You are wanting to know if there is a Creation_ID for certain
conditions... pCriteria is the string for those conditions.

For example:

"SomeOtherID=9"

~~~

the code snippet (if not CheckCheckItem() then...) would go in the
BeforeUpdate code behind your form

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. Be sure to put "Access Basics" in the subject line so that
I see your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal!
Wow, you are opening up whole new worlds to me!!
Sorry to say that I am not quite sure what everything means.
1. Where would I put the function code, you mention either in a module or
behind the form - but, should this be in a sub command or what about global
versus private?? I would like this function to be global so I can call it
from different forms.
2. I am not entirely sure about what to change in the code provided. I have
done the following:

function CheckItem(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("Creation_ID","items",pCriteria),0)
if mRecordID = 0 then
CheckItem = false
else
CheckItem = true
end if
end function

if not CheckCheckItem() then
msgbox "An Item does not exist in this creation. Please add an
item.",,"Cannot save record yet"
Cancel = true
end if

I was not sure what pCriteria was, but am assuming it is something I need to
change. Help?

Thanks!
-gary

:

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

:

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

 

[strive4peace]

you're welcome, Gary happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you!! That did it!
-gary

Passing Parameter, Line Continuation, Compile
---


Hi Gary,

your criteria string would be something like this:

"[Creation_ID]=" & me.[Creation_ID]

.... and I realized my example useage was not complete, sorry... in the
form BeforeUpdate code:

'~~~~
dim mCriteria as string

'assuming Creation_ID is numeric
mCriteria = "[Creation_ID]=" & me.[Creation_ID]

If not CheckCheckItem(mCriteria) then
msgbox "An Item does not exist in this creation." _
& " Please add an item." _
,,"Cannot save record yet"
Cancel = true
End if
'~~~

GIVEN
the procedure declaration is:
Function CheckItem(pCriteria as string) as Boolean
'~~~

Space Underscore at the end of a line is the Line Continuation symbol
and means that the statement is continued on the next line -- often
makes code easier to read

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)
~~~~~~~~~~~

I just sent you the Access Basics document


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello again,
I am so sorry, I am not sure if it is just too early, but I am still having
troubles
I placed the function in a module and the code behind the BeforeUpdate event
of the Creations form. When I try to test, I get a Compile Error: Argument
Not Optional message, and it is stopping on my code in the form, highlighting
"CheckItem" - I understand that pCriteria is a passed variable, but where is
it defined as to what I am passing to this? I am not sure where I am
supposed to put this, or if it is even needed?
Within the function, I have a variable being delcared:
mRecordID = Nz(DLookup("Creation_ID", "items", pCriteria), 0)
and from my assumption this means that mRecordID is looking up "Creation_ID"
in table "items" where pCriteria??. Would I set up pCriteria as a variable
in the code or the function? both, neither? Something like
Dim pCriteria as String
pCriteria = [Creation_ID].value??
I understand what you are saying (well, maybe I am not) but feel I am
missing just one little thing.
Sorry.
-gary
PS, I emailed you requesting the Access document, I have been struggling
with this project for a couple months. I appreciate the help!

:

Hi Gary,

Hello world!

Function CheckItem would go into a general module since it does not use
a reference to a control or a form or report. What it needs is
passed... hence pCriteria

'p' is my notation for a passed variable (as opposed to a global variabl
or a variable created in the procedure)

pCriteria is a string that will be used to limit the records for looking
up... chances are, you do not want to know if there is ANY Creation_ID
in the Items table because if you have records, it will, of course, find
one. You are wanting to know if there is a Creation_ID for certain
conditions... pCriteria is the string for those conditions.

For example:

"SomeOtherID=9"

~~~

the code snippet (if not CheckCheckItem() then...) would go in the
BeforeUpdate code behind your form

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. Be sure to put "Access Basics" in the subject line so that
I see your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal!
Wow, you are opening up whole new worlds to me!!
Sorry to say that I am not quite sure what everything means.
1. Where would I put the function code, you mention either in a module or
behind the form - but, should this be in a sub command or what about global
versus private?? I would like this function to be global so I can call it
from different forms.
2. I am not entirely sure about what to change in the code provided. I have
done the following:

function CheckItem(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("Creation_ID","items",pCriteria),0)
if mRecordID = 0 then
CheckItem = false
else
CheckItem = true
end if
end function

if not CheckCheckItem() then
msgbox "An Item does not exist in this creation. Please add an
item.",,"Cannot save record yet"
Cancel = true
end if

I was not sure what pCriteria was, but am assuming it is something I need to
change. Help?

Thanks!
-gary

:

check data in another table before record save
---


Hi Gary,

sure, make an independent function to check this in the code behind your
form or in a general module

'~~~~~~~~~~~~~
function CheckWhatever(pCriteria as string) as boolean
dim mRecordID as long
mRecordID = nz(dLookup("IDfield","Tablename",pCriteria),0)
if mRecordID = 0 then
CheckWhatever = false
else
CheckWhatever = true
end if
end function
'~~~~~~~~~~~~~

then, in your validation routine:

'~~~~~~~~~~~~~
if not CheckWhatever() then
msgbox "Whatever data is missing",,"Cannot save record yet"
Cancel = true
end if
'~~~~~~~~~~~~~

nz (null-to-zero) is to return a 0 instead of an error if the
information was not found...

hopefully, you can see the logic of this -- if not, post back with your
specific tablenames and fieldnames and criteria...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Thank you so much Crystal! This pretty much takes care of most of my many
questions, but I do have one more before I can close this out...
Is there any way to have a stop (similar to what you have already shown me)
to occur if there is a missing table record in another table? For example, I
would like the user to not be able to close the creation form if there is not
an item added to it and also not be able to close the order if there is not a
creation added to it. In my tables, items does have a creation_id field, and
creations does have an order_id field if that helps??
Thank you so much!!
-gary

:

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

 

[cati]

BeforeUpdate to validate data, Unload to cancel close form
---


Hi Gary,

you can use the form BeforeUpdate event to validate data and CANCEL the
update if the validation does not pass

for instance, on the form BeforeUpdate event:

'----------------- make sure all required data is filled out

'make sure Agreement Type is filled out
If IsNull(me.[Agreement Type]) then

'if it is not filled out, then move the focus to that control
me.[Agreement Type].setFocus

'give the user a message
msgbox "You must enter an Agreement Type",,"Missing Data"

'since this is a combobox, drop the list for them
me.[Agreement Type].dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus
msgbox "The second date must be >= " & me.[Date1],,"Invalid Data"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~`
you can use the form Unload event to stop it from closing -- in your
code for this event:

'~~~~~~~~~~~~~~~`
'try to save the record
if me.dirty then me.dirty = false

'if the record is still dirty, validation did not pass
if me.dirty then CANCEL = true
'~~~~~~~~~~~~~~~`

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi all,
I have 3 forms, the main form holds the order information and has a button
on it to create a "creation" on the order. After selecting creation options
on this form, there is now a button to enter an "item" to the creation. Each
form is controlled by a separate table (orders, creations, items).
What I am wanting to know how to do is if there is a way to set up a stop
and error message to the user if any data is incomplete on the form when the
user either tries to close the form, or add another creation or item. Also,
I would like to know how to have an error/stop if the user tries to close the
creation form without adding an item, or has an incomplete item. Is it
possible to then provide the option to delete the creation if this happens?
Can this be done in the message box?
Hope that makes sense, thank you so much!
-gary