Unbound form - prompt for save on dirty record 1

[byurow]

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), 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(&quot;[CustomerLastName]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerFirstName <> Nz(DLookup(&quot;[CustomerFirstName]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerMiddleInt <> Nz(DLookup(&quot;[CustomerMiddleInt]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerAddress <> Nz(DLookup(&quot;[CustomerAddress]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerAddress2 <> Nz(DLookup(&quot;[CustomerAddress2]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerCity <> Nz(DLookup(&quot;[CustomerCity]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or CboCustomerState <> Nz(DLookup(&quot;[CustomerState]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerZip <> Nz(DLookup(&quot;[CustomerZip]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerPhone <> Nz(DLookup(&quot;[CustomerPhone]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerDOB <> Nz(DLookup(&quot;[CustomerDOB]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or TxtCustomerAge <> Nz(DLookup(&quot;[CustomerAge]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or CboCustomerGender <> Nz(DLookup(&quot;[CustomerGender]&quot;, &quot;TblCustomerInfo&quot;, &quot;[CustomerID] =&quot; & [plngCustomer_No])) _
Or IsNull(Me.NumCustomerID) Then
strMsg = &quot;Changes have not been saved?&quot;
strMsg = strMsg & vbCr & &quot; Click Yes to save changes now.&quot;
strMsg = strMsg & vbCr & &quot; Click No to close the form without saving.&quot;
If MsgBox(strMsg, vbQuestion + vbYesNo, &quot;Save changes now?&quot = 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

 

[rdodge]

Not sure how you have your code after you have added a new record, but let's say you have a table with 5 records (ID # 1 - 5), and it's currently on record 5. Now you use the AddNew method on the recordset. The recordset will have a focus for the new record, but then once the new record has been added via the Update method, the recordset goes back to the record that it previously had the focus prior to adding the new record, which in this case would be record number 5, not 6 (the newly added record) as you may expect.

This is shown in the documentation under the &quot;AddNew Method&quot;, which is under the DAO 3.60 Method Reference.. I'm currently using Access 2002.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[byurow]

Hi Ronald,

Thanks for the quick reply. Does this hold true even after I update?

The following is the code I use when adding a new record.

Dim rsNewCustomer As Recordset
Dim lngNewCustomer_No As Long

lngNewCustomer_No = Nz(DMax(&quot;[CustomerID]&quot;, &quot;TblCustomerInfo&quot;, &quot;CustomerID >= 0&quot) + 1
plngCustomer_No = lngNewCustomer_No
NumCustomerID = plngCustomer_No

Set rsNewCustomer = CurrentDb.OpenRecordset(&quot;TblCustomerInfo&quot;, dbOpenDynaset)

With rsNewCustomer
.AddNew
!CustomerID = Me.NumCustomerID
!CustomerSSN = Me.TxtCustomerSSN
!CustomerLastName = Me.TxtCustomerLastName
!CustomerFirstName = Me.TxtCustomerFirstName
!CustomerMiddleInt = Me.TxtCustomerMiddleInt
!CustomerAddress = Me.TxtCustomerAddress
!CustomerAddress2 = Me.TxtCustomerAddress2
!CustomerCity = Me.TxtCustomerCity
!CustomerState = Me.CboCustomerState.Column(0)
!CustomerZip = Me.TxtCustomerZip
!CustomerPhone = Me.TxtCustomerPhone
!CustomerDOB = Me.TxtCustomerDOB
!CustomerAge = Me.TxtCustomerAge
!CustomerGender = Me.CboCustomerGender
.Update
.Close
End With
DoCmd.Hourglass False
blnAddNew = False
MsgBox &quot;Your new Customer has been saved.&quot;, vbInformation, &quot;Changes Saved&quot;
Set rsNewCustomer = Nothing


After I save a new record, I am able to immediately make changes to that record and save them without any problem (Using .Edit)

Thanks again!

Brenda

 

[rdodge]

After looking over your code, I don't really see anything in particular that sticks out right off hand with regards to how the DLookup Function works, but just as you are using unbound forms for your DB program, I also use unbound forms cause I don't care for how the validation checks in access makes bound forms not so user friendly for those that are predominantly mouse users, in particular to how the events works in Access for bound forms. The one thing different though, I'm using DAO coding, which is still the same Jet Engine as what your code uses.

What I have done to overcome one of the issues, I have a form that is used for either retrieving information or adding new records. Later, I will have several, but right now, I'm still in the early development stages of my DB program. Anyhow, the form starts out on the field that deals with the Primary ID number. The user is allowed to either type in an existing ID number or leave it blank. If it is left blank, the DAO recordset is put into AddNew mode (dbEditAdd). If the user types in a valid ID number, the bookmark is taken to the record with that particular ID number via the Seek method, and then the recordset put into Edit mode (dbEditInProgress), provided the user had that permission. If the user types in an invalid ID number (non existent), the code takes the focus back the the Primary ID textbox and the recordset is left in it's none edit mode (dbEditNone).

I have 4 command buttons, Add/Update, Delete, Reset, and Cancel.

Add/Update uses various rules for adding/editing records.

Delete uses strict rules as far as what can and can't be deleted.

Reset cancels the Adding/Editing of the record provided the EditMode of the recordset does not equal dbEditNone constant.

Cancel is used to back out of the form. (I know, the Esc key should be used for this and you would think it's an easy concept to pick up on, but I literally have users that doesn't seem to even pick this up too good, thus why bound forms don't work for me.)

With regards to the validation check challenge, I created my own general validation events that not only similates the VB6 CausesValidation Property and Validate Event for the specific error checking level, but also for catching errors on the general level as the user types the information in.

Here's a sample of the code that I have used.

--------------------------Begin Code-----------------------

Private Sub Form_Load()
modResize.ReSizeForm Me, 0.8, 0.86
bolRet = False
lngRSL = 0
modValDat.lngEntry = -1
Set drsRSA = ddbPD.OpenRecordset(&quot;tblPRDRSA&quot;, dbOpenTable, dbSeeChanges, dbPessimistic)
Set drsRES = ddbPD.OpenRecordset(&quot;SELECT * FROM tblPRDRES WHERE tblPRDRES.fldENA = -1&quot; & _
&quot; ORDER BY tblPRDRES.fldRID;&quot;, dbOpenSnapshot, dbReadOnly)
Set drsMOD = ddbPD.OpenRecordset(&quot;tblPRDMOD&quot;, dbOpenTable, dbSeeChanges, dbReadOnly)
Set drsCPN = ddbPD.OpenRecordset(&quot;tblPRDRSL&quot;, dbOpenTable, dbSeeChanges, dbReadOnly)
Me.cmdAUD.Default = False
Me.cmdDLT.Default = False
Me.cmdAUD.Enabled = False
Me.cmdDLT.Enabled = False
Me.cbxCVN.Enabled = False
Me.tbxRRC.SetFocus
End Sub

Public Function tbxRRC_Validate()
Dim strSQLSLT As String, strSQLWHR As String, strSQLFRO As String, strSQLSRT As String, lngRCC As Long
tbxRRC_Validate = True
Me.ldsSB.Caption = &quot;&quot;
If Not IsNull(Me.tbxRRC) Then
If Me.tbxRRC.Value <> &quot;&quot; Then
drsRSA.index = &quot;fldRRC&quot;
drsRSA.Seek &quot;=&quot;, Me.tbxRRC.Value
If drsRSA.NoMatch Then
Beep
Me.ldsSB.Caption = &quot;There is currently, no reason assignment with that code. Please either:&quot; & vbCr & _
&quot; 1) Put in a valid code via the drop down list or typing in the code,&quot; & vbCr & vbCr & _
&quot; OR,&quot; & vbCr & vbCr & _
&quot; 2) Leave this field blank to add in a new reason.&quot;
Me.tbxRRC.Value = &quot;&quot;
tbxRRC_Validate = False
Me.cmdAUD.Enabled = False
Me.cmdDLT.Enabled = False
Me.cmdAUD.Default = False
Else
'enable all fields to be set accordingly.
Me.cbxCPN.Locked = False
Me.tbxDRC.Locked = False
Me.cbxMOD.Locked = False
Me.cbxCVN.Locked = False
Me.cbxRID.Locked = False
Me.ckbRSE.Locked = False
'Resource Level
Me.cbxCPN.Value = drsRSA.Fields(&quot;fldCPN&quot.Value
drsCPN.index = &quot;fldRLC&quot;
drsCPN.Seek &quot;=&quot;, Me.cbxCPN.Value
Me.ldsCPN.Caption = drsCPN.Fields(&quot;fldDSC&quot.Value
'Resource
Me.cbxCVN.Value = drsRSA.Fields(&quot;fldCVN&quot.Value
strTBL = drsCPN.Fields(&quot;fldTBL&quot.Value
strFID = drsCPN.Fields(&quot;fldIDF&quot.Value
strFDS = drsCPN.Fields(&quot;fldDNF&quot.Value
strLIB = drsCPN.Fields(&quot;fldLIB&quot.Value
Select Case strLIB
Case &quot;1&quot;
If Not drsCVN Is Nothing Then
drsCVN.Close
End If
Set drsCVN = ddbMD.OpenRecordset(&quot;SELECT * FROM &quot; & strTBL & &quot; WHERE fldENA.&quot; & strTBL & &quot; = -1;&quot;, _
dbOpenSnapshot, dbReadOnly)
drsCVN.index = strFID
drsCVN.Seek &quot;=&quot;, Me.cbxCVN.Value
Me.ldsCCN.Caption = drsCVN.Fields(strFDS).Value
End Select
'Mode
Me.cbxMOD.Value = drsRSA.Fields(&quot;fldMOD&quot.Value
drsMOD.index = &quot;fldMID&quot;
drsMOD.Seek &quot;=&quot;, Me.cbxMOD.Value
Me.ldsMOD.Caption = drsMOD.Fields(&quot;fldDSC&quot.Value
'Reason
Me.cbxRID.Value = drsRSA.Fields(&quot;fldRID&quot.Value
Me.tbxDRC.Value = drsRSA.Fields(&quot;fldDRC&quot.Value
drsRES.index = &quot;fldRID&quot;
drsRES.Seek &quot;=&quot;, Me.cbxRID.Value
Me.ldsRID.Caption = drsRES.Fields(&quot;fldDSC&quot.Value
'Enable
Me.ckbRSE.Value = drsRSA.Fields(&quot;fldENA&quot.Value
'Determine if the reason assignment can be updated or not.
If drsRES.Fields(&quot;fldENA&quot.Value Then
drsRSA.Edit
Me.cmdAUD.Enabled = True
Me.cmdAUD.Default = True
Else
'Record can not be updated due to reason itself has been disabled, so lock all fields except for reason assignment number
' and the reason id number.
Me.cmdAUD.Enabled = False
Me.cmdAUD.Default = False
Me.ckbRSE.Locked = True
Me.cbxCPN.Locked = True
Me.tbxDRC.Locked = True
Me.cbxMOD.Locked = True
Me.cbxCVN.Locked = True
End If
'Determine if reason assignment can be deleted or not
'The drsAPR recordset needs to be modified to include only those conversion centers that meets the resource level code.
strSQLSLT = &quot;SELECT * &quot;
'Check historic recordings
strSQLFRO = &quot;FROM tblPRDAPR&quot;
Select Case CLng(Me.cbxCPN.Value)
Case 1, 4, 5
strSQLWHR = &quot;WHERE tblPRDAPR.fldRRC = &quot; & CStr(Me.tbxRRC.Value) & &quot; &quot;
Case 2, 3
strSQLWHR = &quot;WHERE tblPRDAPR.fldCRC = &quot; & CStr(Me.tbxRRC.Value) & &quot; &quot;
Case Else
End Select
Set drsAPR = modDB.ddbPD.OpenRecordset(strSQLSLT & strSQLFRO & strSQLWHR & &quot;;&quot;, dbOpenDynaset, dbReadOnly)
lngRCC = drsAPR.RecordCount
drsAPR.Close
'Check current recordings
strSQLFRO = &quot;FROM tblPRDCPS&quot;
strSQLWHR = &quot;WHERE tblPRDCPS.fldRRC = &quot; & CStr(Me.tbxRRC.Value) & &quot; &quot;
Set drsAPR = modDB.ddbPD.OpenRecordset(strSQLSLT & strSQLFRO & strSQLWHR & &quot;;&quot;, dbOpenDynaset, dbReadOnly)
lngRCC = drsAPR.RecordCount + lngRCC
If lngRCC = 0 Then
Me.cmdDLT.Enabled = True
Else
Me.cmdDLT.Enabled = False
'Lock all fields except for Enable as that has been determined by if the record can be updated or not.
'the reason assignment number doesn't get locked as that is used to determine if an existing record
' comes up or if the mode is to add a new reason assignment to a resource.
Me.cbxCPN.Locked = True
Me.tbxDRC.Locked = True
Me.cbxMOD.Locked = True
Me.cbxCVN.Locked = True
Me.cbxRID.Locked = True
End If
drsAPR.Close
End If
Else
drsRSA.AddNew
End If
Else
drsRSA.AddNew
End If
If drsRSA.EditMode = dbEditAdd Then
Me.tbxRRC.Value = drsRSA.Fields(&quot;fldRRC&quot.Value
Me.cmdAUD.Enabled = True
Me.cmdDLT.Enabled = False
Me.cmdAUD.Default = False
Me.cbxRID.Value = &quot;&quot;
Me.ldsRID.Caption = &quot;&quot;
Me.ckbRSE.Value = drsRSA.Fields(&quot;fldENA&quot.DefaultValue
If IsNull(Me.cbxCPN.Value) Then
Me.cbxCVN.Locked = True
If Me.cbxCPN.Value = &quot;&quot; Then
Me.cbxCVN.Locked = True
End If
End If
If drsRSA.EditMode <> dbEditNone Then
If IsNull(Me.tbxRSD.Value) Then
Me.cmdAUD.Default = False
ElseIf Me.tbxRSD.Value = &quot;&quot; Or Me.ckbRSE.Locked Then
Me.cmdAUD.Default = False
Else
Me.cmdAUD.Default = True
End If
End If
End Function

-------------------------End Code-------------------------

One thing I must mention, the above code does not show the setting of the Workspace Object or the Database Object via DAO coding. The workspace is created at the time the DB is openned, and the database object is created at the time of going into a switchboard screen. As you may have noticed, I have centralized both, the workspace object and db object in a standard module, which then I prequalify the db object with the standard module name, when setting a recordset based on the database object.

All of my textboxes are setup as String format as I did that on purpose and using my own general validation code checking as the user types in the information.

There's a lot of things that's taking place in this code, so feel free to ask questions, if you have any.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[byurow]

Hey Ronald,

Thanks so much for all of the great code! It is going to take a while for me to digest it and try some of it out. I have a very complicated database that is used as a client intake database for domestic violence. Based on a number of selections made by the user, different things happen (i.e. some selections trigger surveys, some trigger e-mails, some trigger other forms to open etc.) I too have users that are just not computer literate. In addition to the following standard buttons, Save, Delete, Cancel (clears the form of any changes the user made), I have a CLOSE button...I have users that just don't know that the little x at the top of the application means CLOSE!

Anyway thanks again for the great code and advice. I will let you know how things turn out!

Brenda

 

[rdodge]

Glad to know that I'm not the only one that's dealing with these types of users.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000