Unbound form - prompt for save on dirty record

M

Misty30706

Help!!!!

I have a form that is UNBOUND. I need to have the application ask the user if
he/she wants to save changes before closing, if the user made changes to the
data.

OK, I have a pretty good start. The code below actually works as specified
(for the most part) What happens is, sometimes, the user gets prompted even
if NO changes have been made! This usually happens after the user has added a
new record.

While this is certainly not a critical error (the user either hits yes or no
then the form closes), it is annoying!

Can anyone see anything wrong with my code?

To give a little bit more background....when the form opens, a boolean value:
blnAddNew is set to false. When a user wants to add a new record (as opposed
to updating an existing record) the user clicks a button that clears the form
and sets the blnAddNew to True. Once the user is finished adding the new
record, he/she hits save, the save adds a new record to the table(s) (code is
..AddNew then at the end... .update .close), then sets the blnAddNew back to
false. Then, when closing the form, the following code is run:

Dim strMsg As String
plngCustomer_No = NumCustomerID

If TxtCustomerLastName <> Nz(DLookup("[CustomerLastName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerFirstName <> Nz(DLookup("[CustomerFirstName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerMiddleInt <> Nz(DLookup("[CustomerMiddleInt]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress <> Nz(DLookup("[CustomerAddress]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress2 <> Nz(DLookup("[CustomerAddress2]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerCity <> Nz(DLookup("[CustomerCity]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerState <> Nz(DLookup("[CustomerState]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerZip <> Nz(DLookup("[CustomerZip]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerPhone <> Nz(DLookup("[CustomerPhone]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerDOB <> Nz(DLookup("[CustomerDOB]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAge <> Nz(DLookup("[CustomerAge]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerGender <> Nz(DLookup("[CustomerGender]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or IsNull(Me.NumCustomerID) Then
strMsg = "Changes have not been saved?"
strMsg = strMsg & vbCr & " Click Yes to save changes now."
strMsg = strMsg & vbCr & " Click No to close the form without
saving."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes now?") =
vbYes Then
If blnAddNew = True Then
Call SaveNewCustomer
Else
Call EditExistingCustomer
End If
End If
End If

DoCmd.Close


Obviously my formatting has been all muffed up, but hopefully you get the
picture. It is at this point the my pop-up box comes up asking the user if
he/she wants to save the changes - even if no changes have been made! What is
so aggravating is that this only happens after adding a new record (but this
also makes it not very critical, just annoying).

So does anyone see anything wrong with my code that would cause this problem to
happen? Or does anyone have a simpler way to do this?

Thanks so much for any help you can offer!

Brenda
 
T

Treebeard

Instead of checking every var when the form closes, why don't you create
another var, e.g. called blnHasChanged, which you set to false when the form
opens. On the update event of every object on the form, set the variable to
True. That way you only have to check these two vars when the form closes.

Jack


Misty30706 said:
Help!!!!

I have a form that is UNBOUND. I need to have the application ask the user if
he/she wants to save changes before closing, if the user made changes to the
data.

OK, I have a pretty good start. The code below actually works as specified
(for the most part) What happens is, sometimes, the user gets prompted even
if NO changes have been made! This usually happens after the user has added a
new record.

While this is certainly not a critical error (the user either hits yes or no
then the form closes), it is annoying!

Can anyone see anything wrong with my code?

To give a little bit more background....when the form opens, a boolean value:
blnAddNew is set to false. When a user wants to add a new record (as opposed
to updating an existing record) the user clicks a button that clears the form
and sets the blnAddNew to True. Once the user is finished adding the new
record, he/she hits save, the save adds a new record to the table(s) (code is
.AddNew then at the end... .update .close), then sets the blnAddNew back to
false. Then, when closing the form, the following code is run:

Dim strMsg As String
plngCustomer_No = NumCustomerID

If TxtCustomerLastName <> Nz(DLookup("[CustomerLastName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerFirstName <> Nz(DLookup("[CustomerFirstName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerMiddleInt <> Nz(DLookup("[CustomerMiddleInt]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress <> Nz(DLookup("[CustomerAddress]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress2 <> Nz(DLookup("[CustomerAddress2]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerCity <> Nz(DLookup("[CustomerCity]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerState <> Nz(DLookup("[CustomerState]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerZip <> Nz(DLookup("[CustomerZip]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerPhone <> Nz(DLookup("[CustomerPhone]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerDOB <> Nz(DLookup("[CustomerDOB]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAge <> Nz(DLookup("[CustomerAge]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerGender <> Nz(DLookup("[CustomerGender]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or IsNull(Me.NumCustomerID) Then
strMsg = "Changes have not been saved?"
strMsg = strMsg & vbCr & " Click Yes to save changes now."
strMsg = strMsg & vbCr & " Click No to close the form without
saving."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes now?") =
vbYes Then
If blnAddNew = True Then
Call SaveNewCustomer
Else
Call EditExistingCustomer
End If
End If
End If

DoCmd.Close


Obviously my formatting has been all muffed up, but hopefully you get the
picture. It is at this point the my pop-up box comes up asking the user if
he/she wants to save the changes - even if no changes have been made! What is
so aggravating is that this only happens after adding a new record (but this
also makes it not very critical, just annoying).

So does anyone see anything wrong with my code that would cause this problem to
happen? Or does anyone have a simpler way to do this?

Thanks so much for any help you can offer!

Brenda
Click to expand...
 
J

John S

I use the "change" event, and there is one weakness: even if your user
enters a field, and makes no change, it fires the event. Some of the people
in this newsgroup suggested to me that you can't avoid this problem using
the events.

I also have to deal with subform, (a datasheet) bound to a temporary table,
and for this will probably have to do a vartiable by variable check.

Ah, the joy of unbound forms.

John S
Aylmer, PQ

Treebeard said:
Instead of checking every var when the form closes, why don't you create
another var, e.g. called blnHasChanged, which you set to false when the form
opens. On the update event of every object on the form, set the variable to
True. That way you only have to check these two vars when the form closes.

Jack


Misty30706 said:
Help!!!!

I have a form that is UNBOUND. I need to have the application ask the user if
he/she wants to save changes before closing, if the user made changes to the
data.

OK, I have a pretty good start. The code below actually works as specified
(for the most part) What happens is, sometimes, the user gets prompted even
if NO changes have been made! This usually happens after the user has added a
new record.

While this is certainly not a critical error (the user either hits yes
Click to expand...
or
no
then the form closes), it is annoying!

Can anyone see anything wrong with my code?

To give a little bit more background....when the form opens, a boolean value:
blnAddNew is set to false. When a user wants to add a new record (as opposed
to updating an existing record) the user clicks a button that clears the form
and sets the blnAddNew to True. Once the user is finished adding the new
record, he/she hits save, the save adds a new record to the table(s)
Click to expand...
(code
is
.AddNew then at the end... .update .close), then sets the blnAddNew back to
false. Then, when closing the form, the following code is run:

Dim strMsg As String
plngCustomer_No = NumCustomerID

If TxtCustomerLastName <> Nz(DLookup("[CustomerLastName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerFirstName <> Nz(DLookup("[CustomerFirstName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerMiddleInt <> Nz(DLookup("[CustomerMiddleInt]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress <> Nz(DLookup("[CustomerAddress]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress2 <> Nz(DLookup("[CustomerAddress2]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerCity <> Nz(DLookup("[CustomerCity]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerState <> Nz(DLookup("[CustomerState]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerZip <> Nz(DLookup("[CustomerZip]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerPhone <> Nz(DLookup("[CustomerPhone]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerDOB <> Nz(DLookup("[CustomerDOB]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAge <> Nz(DLookup("[CustomerAge]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerGender <> Nz(DLookup("[CustomerGender]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or IsNull(Me.NumCustomerID) Then
strMsg = "Changes have not been saved?"
strMsg = strMsg & vbCr & " Click Yes to save changes now."
strMsg = strMsg & vbCr & " Click No to close the form without
saving."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes now?") =
vbYes Then
If blnAddNew = True Then
Call SaveNewCustomer
Else
Call EditExistingCustomer
End If
End If
End If

DoCmd.Close


Obviously my formatting has been all muffed up, but hopefully you get the
picture. It is at this point the my pop-up box comes up asking the user if
he/she wants to save the changes - even if no changes have been made! What is
so aggravating is that this only happens after adding a new record (but this
also makes it not very critical, just annoying).

So does anyone see anything wrong with my code that would cause this problem to
happen? Or does anyone have a simpler way to do this?

Thanks so much for any help you can offer!

Brenda
Click to expand...
Click to expand...
 
T

Treebeard

Good point John.

Can't a comparison be done between Me.ControlName.Value and
Me.ControlName.Oldvalue on the change/update event to determine if the value
has really changed?

I've never done it myself because I've always used temporary tables to
handle these types of situations.

Jack



John S said:
I use the "change" event, and there is one weakness: even if your user
enters a field, and makes no change, it fires the event. Some of the people
in this newsgroup suggested to me that you can't avoid this problem using
the events.

I also have to deal with subform, (a datasheet) bound to a temporary table,
and for this will probably have to do a vartiable by variable check.

Ah, the joy of unbound forms.

John S
Aylmer, PQ

Treebeard said:
Instead of checking every var when the form closes, why don't you create
another var, e.g. called blnHasChanged, which you set to false when the form
opens. On the update event of every object on the form, set the variable to
True. That way you only have to check these two vars when the form closes.

Jack


Misty30706 said:
Help!!!!

I have a form that is UNBOUND. I need to have the application ask the user if
he/she wants to save changes before closing, if the user made changes
Click to expand...
to
the
data.

OK, I have a pretty good start. The code below actually works as specified
(for the most part) What happens is, sometimes, the user gets
Click to expand...
prompted
even
if NO changes have been made! This usually happens after the user has added a
new record.

While this is certainly not a critical error (the user either hits yes
Click to expand...
or
no
then the form closes), it is annoying!

Can anyone see anything wrong with my code?

To give a little bit more background....when the form opens, a boolean value:
blnAddNew is set to false. When a user wants to add a new record (as opposed
to updating an existing record) the user clicks a button that clears
Click to expand...
the
form
and sets the blnAddNew to True. Once the user is finished adding the new
record, he/she hits save, the save adds a new record to the table(s)
Click to expand...
(code
is
.AddNew then at the end... .update .close), then sets the blnAddNew
Click to expand...
back
to
false. Then, when closing the form, the following code is run:

Dim strMsg As String
plngCustomer_No = NumCustomerID

If TxtCustomerLastName <> Nz(DLookup("[CustomerLastName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerFirstName <> Nz(DLookup("[CustomerFirstName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerMiddleInt <> Nz(DLookup("[CustomerMiddleInt]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress <> Nz(DLookup("[CustomerAddress]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress2 <> Nz(DLookup("[CustomerAddress2]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerCity <> Nz(DLookup("[CustomerCity]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerState <> Nz(DLookup("[CustomerState]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerZip <> Nz(DLookup("[CustomerZip]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerPhone <> Nz(DLookup("[CustomerPhone]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerDOB <> Nz(DLookup("[CustomerDOB]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAge <> Nz(DLookup("[CustomerAge]", "TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerGender <> Nz(DLookup("[CustomerGender]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or IsNull(Me.NumCustomerID) Then
strMsg = "Changes have not been saved?"
strMsg = strMsg & vbCr & " Click Yes to save changes now."
strMsg = strMsg & vbCr & " Click No to close the form without
saving."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes now?") =
vbYes Then
If blnAddNew = True Then
Call SaveNewCustomer
Else
Call EditExistingCustomer
End If
End If
End If

DoCmd.Close


Obviously my formatting has been all muffed up, but hopefully you get the
picture. It is at this point the my pop-up box comes up asking the
Click to expand...
user
if
he/she wants to save the changes - even if no changes have been made! What is
so aggravating is that this only happens after adding a new record
Click to expand...
(but
this
also makes it not very critical, just annoying).

So does anyone see anything wrong with my code that would cause this problem to
happen? Or does anyone have a simpler way to do this?

Thanks so much for any help you can offer!

Brenda
Click to expand...
Click to expand...
Click to expand...
 
M

Misty30706

Thanks both of you for the tips.

How do you use Temporary tables? There are several places where I use them,
but I don't use them for just a general update.

Love to hear more!

Brenda
 
T

TC

The easiest "general purpose" solution would be to code a loop at the point
in time where you want to save the record if it has changed. The loop would
cycle through the fields in the form's recordset, comparing their current
values in the form, to the values currently stored on disk. If any were
changed, the fporm record must be saved or discarded. If >none were changed,
the form record has not been changed, or existing values have ben retyped,
or values have been changed, not saved & then changed back again..

If the form is based on a query joining several tables, there are properties
that tell you the >actual< field and table that is the source of data for
each field in the form's recordset. (I forget what those props are called.)
So the same method would work fine, for forms that were based on an updatble
query.

HTH,
TC


Treebeard said:
Good point John.

Can't a comparison be done between Me.ControlName.Value and
Me.ControlName.Oldvalue on the change/update event to determine if the value
has really changed?

I've never done it myself because I've always used temporary tables to
handle these types of situations.

Jack



John S said:
I use the "change" event, and there is one weakness: even if your user
enters a field, and makes no change, it fires the event. Some of the people
in this newsgroup suggested to me that you can't avoid this problem using
the events.

I also have to deal with subform, (a datasheet) bound to a temporary table,
and for this will probably have to do a vartiable by variable check.

Ah, the joy of unbound forms.

John S
Aylmer, PQ

the
form variable
to
Click to expand...
changes
to
the
data.

OK, I have a pretty good start. The code below actually works as
specified
(for the most part) What happens is, sometimes, the user gets prompted
even
if NO changes have been made! This usually happens after the user has
added a
new record.

While this is certainly not a critical error (the user either hits
Click to expand...
yes
or
no
then the form closes), it is annoying!

Can anyone see anything wrong with my code?

To give a little bit more background....when the form opens, a boolean
value:
blnAddNew is set to false. When a user wants to add a new record (as
opposed
to updating an existing record) the user clicks a button that clears the
form
and sets the blnAddNew to True. Once the user is finished adding
Click to expand...
the
new
record, he/she hits save, the save adds a new record to the table(s) (code
is
.AddNew then at the end... .update .close), then sets the blnAddNew back
to
false. Then, when closing the form, the following code is run:

Dim strMsg As String
plngCustomer_No = NumCustomerID

If TxtCustomerLastName <> Nz(DLookup("[CustomerLastName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerFirstName <> Nz(DLookup("[CustomerFirstName]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerMiddleInt <> Nz(DLookup("[CustomerMiddleInt]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress <> Nz(DLookup("[CustomerAddress]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAddress2 <> Nz(DLookup("[CustomerAddress2]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerCity <> Nz(DLookup("[CustomerCity]",
"TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerState <> Nz(DLookup("[CustomerState]",
"TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerZip <> Nz(DLookup("[CustomerZip]",
"TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerPhone <> Nz(DLookup("[CustomerPhone]",
"TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerDOB <> Nz(DLookup("[CustomerDOB]",
"TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or TxtCustomerAge <> Nz(DLookup("[CustomerAge]",
"TblCustomerInfo",
"[CustomerID] =" & [plngCustomer_No])) _
Or CboCustomerGender <> Nz(DLookup("[CustomerGender]",
"TblCustomerInfo", "[CustomerID] =" & [plngCustomer_No])) _
Or IsNull(Me.NumCustomerID) Then
strMsg = "Changes have not been saved?"
strMsg = strMsg & vbCr & " Click Yes to save changes now."
strMsg = strMsg & vbCr & " Click No to close the form without
saving."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save changes
now?") =
vbYes Then
If blnAddNew = True Then
Call SaveNewCustomer
Else
Call EditExistingCustomer
End If
End If
End If

DoCmd.Close


Obviously my formatting has been all muffed up, but hopefully you
Click to expand...
get
the
picture. It is at this point the my pop-up box comes up asking the user
if
he/she wants to save the changes - even if no changes have been made!
What is
so aggravating is that this only happens after adding a new record (but
this
also makes it not very critical, just annoying).

So does anyone see anything wrong with my code that would cause this
problem to
happen? Or does anyone have a simpler way to do this?

Thanks so much for any help you can offer!

Brenda
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

is there a Dlookup to get entire record or multiple fields ?? 8
dlookup compare date values 8
Event code apply to all objects 5
Event Fires Twice 2
Problem when no changes made to form 4
Type Mismatch in dLookup 9
Dlookup - new record 5
Control afterupdate - run from module - insert data - set flag 3

Top