Solved Unable to save form record (1 Viewer)

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
Hello! I'm not sure if this is the correct forum to post this, but here it goes:

I have a database that uses (mainly) two forms: Work Orders, Purchase Orders. I modified their tables, only to reverse all of them, because in testing some new VBA code (After_Update) that updates a record with the user's username and current date, I was getting this error:

You can't save this record at this time.
<Database name> may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?

I'm unable to go to another record. To be safe, I save the record as a PDF. Open the PDF and those change(s) are there. I click the Yes for the error message. Go back to the record I just changed and the fields show the newest values. I have a mix of macros and VBA. I'm unsure of what else to add here. What is it I'm missing, that I'm getting this error? Thanks for reading and hope to hear from you!

EDIT: I've included a sample copy of the database. There are two users to choose from. 1) Username: Access. Password: 123. 2) Username: Han Solo. Password: Han.

Here's some of the code:
Login form:
Code:
' This is theLogin form
Option Compare Database

Private Sub cmd_Cancel_Click()
    DoCmd.Quit acQuitSaveAll
End Sub

Private Sub cmd_login_Click()
Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String

  If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, Title:="Username Required"
    Me.txt_username.SetFocus
    Exit Sub
  End If

  If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, Title:="Password Required"
    Me.txt_password.SetFocus
    Exit Sub
  End If

  'query to check if login details are correct
  'updated query to include UserName 10/13/2020
  strSQL = "SELECT FirstName, LastName, UserName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"

  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    'MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error" --- commented out 10/13/2020
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error" & "strSQL = " & strSQL 'added 10/13/2020
    Me.txt_username.SetFocus
  Else
    'MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, Title:="Login Successful" --- commented out 10/13/2020
    GUserName = rst.Fields("UserName").Value 'added 10/13/2020
    MsgBox prompt:="Hello, " & GUserName & ". .CurrentUser=." & CurrentUser(), buttons:=vbOKOnly, Title:="Login Successful"  'added 10/13/2020
    DoCmd.Close acForm, "frm_login", acSaveYes
  End If

Set db = Nothing
Set rst = Nothing
DoCmd.OpenForm "Main Menu"
End Sub

Then this is where the GUserName comes from, as a module:
Code:
Option Compare Database
Public GUserName As String

Purchase Order:
Code:
Option Compare Database

Private Sub btnReplicateB_Click()
'On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
   
'Save any edits first
    'If Me.Dirty Then
    '    Me.Dirty = False
    'End If
   
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
            Else
                'Duplicate the main record: add to form's clone.
            With Me.RecordsetClone
                .AddNew
                   !AccountNumber = Me.AccountNumber
                   !Client = Me.Client
                   !CAddress = Me.CAddress
                   !CCity = Me.CCity
                   !CState = Me.CState
                   !CZIP = Me.CZIP
                   !CContact = Me.CContact
                   !CPhone = Me.CPhone
                   !CEmail = Me.CEmail
       
                'etc for other fields.
                .Update
            End With
    End If
End Sub

Private Sub cboAccountNumber_Change()
Me.txtClient.Value = Me.cboAccountNumber.Column(1)
Me.txtCAddress.Value = Me.cboAccountNumber.Column(2)
Me.txtCCity.Value = Me.cboAccountNumber.Column(3)
Me.txtCState.Value = Me.cboAccountNumber.Column(4)
Me.txtCZIP.Value = Me.cboAccountNumber.Column(5)
Me.txtCContact.Value = Me.cboAccountNumber.Column(6)
Me.txtCEmail.Value = Me.cboAccountNumber.Column(7)
Me.txtCPhone.Value = Me.cboAccountNumber.Column(8)
'Me.txtAccountNumber.Value = Me.cboAccountNumber.Column(9)
End Sub

Private Sub cboAccountNumber4_Change()
Me.txtCAddress2.Value = Me.cboAccountNumber4.Column(1)
Me.txtCCity2.Value = Me.cboAccountNumber4.Column(2)
Me.txtCState2.Value = Me.cboAccountNumber4.Column(3)
Me.txtCZIP2.Value = Me.cboAccountNumber4.Column(4)
End Sub

Private Sub cmdReplicate_Click()
Dim AccoutNumber As Long

    'TO DO: change all instances of 'BookID' with the actual name of your table's ID or primary key

     If IsNull(POrderNumber) Then
        MsgBox prompt:="Please select the record to copy first.", buttons:=vbExclamation
        Exit Sub
    End If

    currentID = AccountNumber
    DoCmd.GoToRecord record:=acNewRec

    'TO DO: set the fields to be copied (those that most likely will have the same values)
    'FORMAT: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField=" & currentID)
    'FORMAT WHERE CRITERION IS NUMERIC: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField=" & currentID)
    'FORMAT WHERE CRITERION IS A STRING: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField='" & currentID & "'")
    'FORMAT WHERE CRITERION IS A DATE: fieldName = Dlookup("fieldname", "tableName", "DateField=" & Format(varDate, "\#yyyy\-mm\-dd hh:nn:ss\#"))

    'POrderNumber = DLookup("POrderNumber", "PurchaseOrders", "POrderNumber=" & currentID)
    AccountNumber = DLookup("AccountNumber", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    Client = DLookup("Client", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CAddress = DLookup("CAddress", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CCity = DLookup("CCity", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CState = DLookup("CState", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CZIP = DLookup("CZIP", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CContact = DLookup("CContact", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CPhone = DLookup("CPhone", "PurchaseOrders", "POrderNumber='" & currentID & "'")
    CEmail = DLookup("CEmail", "PurchaseOrders", "POrderNumber='" & currentID & "'")
   
     POrderStatus.SetFocus '‘TO DO: change 'Title' with name of field that is going to be edited by the user

End Sub

'Private Sub cmdPDF_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  '  MsBox "Save record before exporting it as a PDF"
'End Sub

Private Sub Form_AfterUpdate() 'added 10/13/2020
Me.EditedBy.Value = GUserName
Me.EditDate = Date
End Sub

Work Order Form:
Code:
Private Sub cmdCreateReportForEachRecordAndExport_Click()
    Dim sReportName As String
    Dim sCriteria As String
    sReportName = "WorkOrderRpt" '' name of the predefined report
   
    Dim rs As Recordset2
    Set rs = Me.Recordset
    rs.MoveFirst
   
    Do While Not rs.EOF
    sCriteria = "[WorkOrder#]=" & rs.Fields("WorkOrder#").Value
   
    DoCmd.OpenReport sReportName, acViewNormal, , sCriteria
    DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, "C:\New Folder\" & rs.Fields("WorkOrder#") & ".PDF", False
    DoCmd.Close acReport, sReportName
   
    rs.MoveNext
    Loop
     
End Sub

Private Sub btnReplicateC_Click()
'On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
   
'Save any edits first
    'If Me.Dirty Then
    '    Me.Dirty = False
    'End If
   
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
            Else
                'Duplicate the main record: add to form's clone.
            With Me.RecordsetClone
                .AddNew
                   !AccountNumber = Me.AccountNumber
                   !Client = Me.Client
                   !CAddress = Me.CAddress
                   !CCity = Me.CCity
                   !CState = Me.CState
                   !CZIP = Me.CZIP
                   !CContact = Me.CContact
                   !CPhone = Me.CPhone
                   !CEmail = Me.CEmail
                   !Equipment = Me.Equipment
                   !MFR = Me.MFR
                   !Model = Me.Model
                   !SN = Me.SN
       
                'etc for other fields.
                .Update
            End With
    End If
End Sub

Private Sub cboWOCust_Change()
Me.txtClient.Value = Me.cboWOCust.Column(1)
Me.txtCAddress.Value = Me.cboWOCust.Column(2)
Me.txtCCity.Value = Me.cboWOCust.Column(3)
Me.txtCState.Value = Me.cboWOCust.Column(4)
Me.txtCZIP.Value = Me.cboWOCust.Column(5)
Me.txtCContact.Value = Me.cboWOCust.Column(6)
Me.txtCEmail.Value = Me.cboWOCust.Column(7)
Me.txtCPhone.Value = Me.cboWOCust.Column(8)
End Sub

Private Sub cmdOpenWorkOrders_Click()
Me.Filter = "Status = 'Pending'"
Me.FilterOn = True
End Sub

Private Sub Form_AfterUpdate() 'added 10/13/2020
Me.EditedBy.Value = GUserName
Me.Edited.Value = Date
End Sub
 

Attachments

  • Database1.zip
    322.4 KB · Views: 10
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
13,132
Hi. This could be better analyzed and fixed if you could share a sample/demo copy of your db. Just a thought...
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
Hi. This could be better analyzed and fixed if you could share a sample/demo copy of your db. Just a thought...
Thanks for replying. It's a split database. Would it be all right if I created a copy of the front-end with no data?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:23
Joined
Oct 29, 2018
Messages
13,132
Thanks for replying. It's a split database. Would it be all right if I created a copy of the front-end with no data?
As long as it can demonstrate the issue or we can perform the steps to duplicate it, then I guess it would be okay. If, however, we can't even open the form because it needs the data, then I suggest you replace the linked table with local ones and simply put a couple of test records in them.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
As long as it can demonstrate the issue or we can perform the steps to duplicate it, then I guess it would be okay. If, however, we can't even open the form because it needs the data, then I suggest you replace the linked table with local ones and simply put a couple of test records in them.
Here you go. I hope I did that correctly.

You can login as Username: Access. Password: 123. Or... Username: Han Solo. Password: Han
 

Attachments

  • Database1.zip
    322.4 KB · Views: 9

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 19, 2002
Messages
30,050
The AfterUpdate event of a form runs AFTER the record is saved so when you modify the record in this event, you force Access into an infinite loop. You dirty the record in the AfterUpdate event forcing Access to save the record again and that runs the BeforeUpdate event and then the AfterUpdate event in which you dirty the record again sending it back to save and through BeforeUpdate and AfterUpdate again - see - infinite loop. However, newer versions of Access are on to this mistake and instead of freezing Access as older versions did, the loop exits gracefully after a few iterations.

That means that the proper place for the code that adds the person's name and date is the Form's BeforeUpdate event.

PS
.Value is the default property and so it can be omitted.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
The AfterUpdate event of a form runs AFTER the record is saved so when you modify the record in this event, you force Access into an infinite loop. You dirty the record in the AfterUpdate event forcing Access to save the record again and that runs the BeforeUpdate event and then the AfterUpdate event in which you dirty the record again sending it back to save and through BeforeUpdate and AfterUpdate again - see - infinite loop. However, newer versions of Access are on to this mistake and instead of freezing Access as older versions did, the loop exits gracefully after a few iterations.

That means that the proper place for the code that adds the person's name and date is the Form's BeforeUpdate event.

PS
.Value is the default property and so it can be omitted.
Thank you for replying! So let me sure I understand: I should remove the AfterUpdate (and leave it blank) and move it to BeforeUpdate? Then in your post-script, could you clarify?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 19, 2002
Messages
30,050
Correct. Code in the AfterUpdate event of a FORM should NEVER, EVER dirty the current record unless you want to create an infinite loop. You can modify the value of a control in the control's AfterUpdate event though.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
Correct. Code in the AfterUpdate event of a FORM should NEVER, EVER dirty the current record unless you want to create an infinite loop. You can modify the value of a control in the control's AfterUpdate event though.
Okay. Let me test this out.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
Correct. Code in the AfterUpdate event of a FORM should NEVER, EVER dirty the current record unless you want to create an infinite loop. You can modify the value of a control in the control's AfterUpdate event though.
That seemed to fix it! Thank you! Two things. 1) I didn't modify the control value for the AfterUpdate, is that okay? 2) There's Cancel As Integer within parentheses for AfterUpdate as a default. Is that important to leave like that?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 19, 2002
Messages
30,050
You cannot remove parameters from events. The code will not compile. I thought you moved the code to the BeforeUpdate event. The purpose of the Cancel argument is to allow you to PREVENT Access from saving the record. That's why most of your validation code belongs in the FORM's BeforeUpdate event. Other events will work in some situations but the FORM's BeforeUpdate event is the only event that will always catch errors if your code is there since that event ALWAYS runs if the record is dirty and it is the last event to run before a record actually gets saved. It simply cannot be bypassed regardless of what prompted the record save to happen.

Validation that cancels the save looks like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)   
    If CheckAuthorization(Me.Parent.JobPrefix) = True Then
    Else
        Cancel = True
        Me.Undo
        Exit Sub
    End If

    If Me.cboDrawingSfx = "" Then
        Me.cboDrawingSfx = Null
    End If
    If IsNull(Me.cboDrawingTypeID) Then
        MsgBox "Drawing Type is required.", vbOKOnly
        Me.cboDrawingTypeID.SetFocus
        Cancel = True
        Exit Sub
    End If
    Me.FullDwgName = (Me.txtDrawingPfx + " ") & Me.txtDrawingNum & (" " + Me.cboDrawingSfx)
    Me.UpdateDT = Now()
    Me.UpdateBy = Environ("UserName")
End Sub

Notice the "Cancel = True" lines. They are telling Access to NOT save the record. The first If also backs out all changes since the user doesn't have authorization to update the record at all. The other If's do not back up the update. It is always better to allow the user to see the value you are complaining about rather than remove it. Removing it just leaves the user in the dark.

The final two lines of code are in all of my BeforeUpdate procedures. They log who modified the record and when.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
You cannot remove parameters from events. The code will not compile. I thought you moved the code to the BeforeUpdate event. The purpose of the Cancel argument is to allow you to PREVENT Access from saving the record. That's why most of your validation code belongs in the FORM's BeforeUpdate event. Other events will work in some situations but the FORM's BeforeUpdate event is the only event that will always catch errors if your code is there since that event ALWAYS runs if the record is dirty and it is the last event to run before a record actually gets saved. It simply cannot be bypassed regardless of what prompted the record save to happen.

Validation that cancels the save looks like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)  
    If CheckAuthorization(Me.Parent.JobPrefix) = True Then
    Else
        Cancel = True
        Me.Undo
        Exit Sub
    End If

    If Me.cboDrawingSfx = "" Then
        Me.cboDrawingSfx = Null
    End If
    If IsNull(Me.cboDrawingTypeID) Then
        MsgBox "Drawing Type is required.", vbOKOnly
        Me.cboDrawingTypeID.SetFocus
        Cancel = True
        Exit Sub
    End If
    Me.FullDwgName = (Me.txtDrawingPfx + " ") & Me.txtDrawingNum & (" " + Me.cboDrawingSfx)
    Me.UpdateDT = Now()
    Me.UpdateBy = Environ("UserName")
End Sub

Notice the "Cancel = True" lines. They are telling Access to NOT save the record. The first If also backs out all changes since the user doesn't have authorization to update the record at all. The other If's do not back up the update. It is always better to allow the user to see the value you are complaining about rather than remove it. Removing it just leaves the user in the dark.

The final two lines of code are in all of my BeforeUpdate procedures. They log who modified the record and when.
Oh crap. As it stands, I have nothing for AfterUpdate. Here's what I have:

Purchase Orders Form:
Code:
'Private Sub Form_AfterUpdate() 'added 10/13/2020, removed 10/19/2020
'Me.EditedBy.Value = GUserName
'Me.EditDate = Date
'End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer) 'added 10/19/2020
Me.EditedBy.Value = GUserName
Me.EditDate = Date
End Sub

Work Orders Form:
Code:
'Private Sub Form_AfterUpdate() 'added 10/13/2020, removed 10/19/2020
'Me.EditedBy.Value = GUserName
'Me.Edited.Value = Date
'End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer) 'added 10/19/2020
Me.EditedBy.Value = GUserName
Me.Edited.Value = Date
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 19, 2002
Messages
30,050
OK. That should be working. But do you not validate any of the data? You just let the user enter whatever he wants or just leave important data blank?
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
OK. That should be working. But do you not validate any of the data? You just let the user enter whatever he wants or just leave important data blank?
I tried to make many fields require a value (notnull), no zero length strings, validation rules/texts, but they didn't want any. Tried to delete fields that are rarely or never used. They already have a system that generates "official" orders, another for invoice/billing, and something called Meraware (MERA). They don't care that MERA orders have missing data. To them, all that matters if an order is cancelled, estimate, pending, or complete. I'm beginning to question why they even wanted me to build this database in the first place.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 19, 2002
Messages
30,050
I sometimes make notes in code where I disagree with the management decision. At least my successor won't think I made the stupid decision to not validate.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
I sometimes make notes in code where I disagree with the management decision. At least my successor won't think I made the stupid decision to not validate.
That's a great idea! I'm building a case for why I think certain fields should have requirements, validations, etc. and see what they'll say. Unfortunately, I'd have to take down the database to make those changes and they're never happy when I do that.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:23
Joined
Feb 19, 2002
Messages
30,050
Unfortunately, I'd have to take down the database to make those changes
That sounds like a problem in how the app is distributed.

Access applications should be split into two files the BE which has ONLY tables and the FE which has all the other objects and linked tables. The BE is placed in a shared folder on the network and the FE is linked to it. The FE is then distributed to all users so that each has his own personal copy of the FE. The FE should NEVER, EVER be shared.

To simplify the distribution, people normally use one of two methods
1. A shortcut that the users link to downloads a new copy of the FE each time they open the app.
2. A shortcut opens a version control database and that database decides whether or not to download a new copy of the FE.

An option to #2 which is more difficult is to have the shortcut point to the local copy of the FE and when the FE opens, it checks the version on the server and decides if it needs to replace itself. I'm not going in to how to do this. It is unnecessarily complex and there are two easier methods.

So, the bottom line is that the user never actually opens the FE. He always opens a shortcot. This allows you to control when a new version is distributed. That means that replacing the FE never means a system wide shutdown. If you have an emergency fix because you found a dangerous bug, you have to have a way to shut down the app but that's another thread.
 

db-noob

New member
Local time
Yesterday, 17:23
Joined
Oct 16, 2020
Messages
18
That sounds like a problem in how the app is distributed.

Access applications should be split into two files the BE which has ONLY tables and the FE which has all the other objects and linked tables. The BE is placed in a shared folder on the network and the FE is linked to it. The FE is then distributed to all users so that each has his own personal copy of the FE. The FE should NEVER, EVER be shared.

To simplify the distribution, people normally use one of two methods
1. A shortcut that the users link to downloads a new copy of the FE each time they open the app.
2. A shortcut opens a version control database and that database decides whether or not to download a new copy of the FE.

An option to #2 which is more difficult is to have the shortcut point to the local copy of the FE and when the FE opens, it checks the version on the server and decides if it needs to replace itself. I'm not going in to how to do this. It is unnecessarily complex and there are two easier methods.

So, the bottom line is that the user never actually opens the FE. He always opens a shortcot. This allows you to control when a new version is distributed. That means that replacing the FE never means a system wide shutdown. If you have an emergency fix because you found a dangerous bug, you have to have a way to shut down the app but that's another thread.
Yeah. The BE is currently on the shared server and each user, including myself, has a copy of the FE on their own desktop. There's a "master copy" of the FE on the server where I import changes made from my desktop FE. Then I copy&replace their desktop FE with the updated "master copy" FE. If you have any resources on how to do Option #1, let me know.

*Sigh* Despite learning so much about Access in various classes, very little was taught on VBA, development/testing and administering an Access db.
 

Users who are viewing this thread

Top Bottom