Solved Unable to save form record

[db-noob]

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:

' 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:

Option Compare Database
Public GUserName As String

Purchase Order:

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:

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

 

[theDBguy]

Hi. This could be better analyzed and fixed if you could share a sample/demo copy of your db. Just a thought...

 

[db-noob]

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]

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]

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

 

[db-noob]

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?

 

[db-noob]

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]

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?

 

[db-noob]

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:

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:

'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:

'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

 

[db-noob]

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.

 

[db-noob]

Scary. But it's not my company.

For sure! At least I won't be with them forever and the next person can sort that mess, mercy on their soul. Thanks again for the help!

 

[db-noob]

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.

 

[db-noob]

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.

 

[db-noob]

There are lots of much more complicated scripts out there but this one has worked for me for years. It doesn't raise an error in the MD if the directory already exists, nor does it raise an error in the del if the database doesn't exist because in both cases, I would ignore the error anyway. So my position is, don't raise an error you don't want to handle

Notice that the standard and Citrix .bat files are identical in how they work. They just reference the local directory differently.

Standard .bat file

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Citrix .bat file

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb

I'm familiar with Linux commands (not all, but some), so I'm guessing this is done in a command line for a Windows machine? I've messed with batch files using console commands in Skyrim, but never anything else. But this is interesting! I don't have access to the other users' machines because I'm not their IT manager, but I could definitely make & edit copies for every user to include on their desktop.

 

[db-noob]

What I do is create the .bat file and leave it on the server. Then I send the users a shortcut that runs the .bat file and they can place the shortcut on their desktops. Placing the .bat file on the server makes it easy for you to change it if you need to. If you send it to each user, you would need to somehow update it on each user PC if you needed to change it.

Cool! Two questions.
1) Do I have to make a new directory? I figure mine would be:

del C:\Users\<User>\Desktop\database.accdb
copy "\\<ServerName>\...Path...\frontend.accdb" C:\Users\<User>\Desktop\

2) Why is "c:\DwgLog\DrawingLog.accdb" on the last line? Since you already have "c:\DwgLog" on the copy line.

 

[db-noob]

I don't like to save files to the desktop because I believe that the user should decide what should be on his desktop. Also, this method needs to run from a shortcut because you ALWAYS want the user to download a new version of the app each time he opens it. The shortcut should be on the desktop so having both the shortcut and the actual .accdb on the desktop will certainly lead to confusion and mis-use of the app. So, do NOT save the database to the desktop. If you want to save it to a different existing folder, that would be fine.

The last line opens the application.


From your questions, I think you are misunderstanding the purpose of using a batch file. If the user opens the database directly (even if it is his own personal copy), you would need to have code in the app that runs whenever the app opens to determine if the file that was opened is the most recent version. As I said earlier, this gets more complicated. You can do it but since you can't replace a file that is open, you need a trick and the trick is opening a different database that closes the original, deletes it, and downloads a new one. You can certainly write all that code but I gave you a batch file that is only four lines of code. OK, you don't want to make a directory, fine. Now you need three lines of code but the .accdb is lost in a sea of perhaps thousands of files if you use a system folder.

Unfortunately, you are correct. How I used batch files in Skyrim and how they work eludes me. All I ever do is type "bat <filename>" in the console and it does what I coded it to do.

Okay. So I'll have to delete the FE's that are their desktops and give them a shortcut on their desktop, for example:

md C:\Access
del C:\Access\database.accdb
copy "<filepath to FE" C:\Access
C:\Access\database.accdb

Am I getting close?

 

[db-noob]

Created a batch file on the server and sent it as a shortcut to my desktop to test it out. I had to change a few things:

md C:\Access
del "C:\Access\SERCOM Database_FrontEnd.accdb"
copy "\\<path>\SERCOM Database_FrontEnd.accdb" C:\Access
start msaccess "C:\Access\SERCOM Database_FrontEnd.accdb"

The "start msaccess" bit is redundant, but it slightly reminds me of opening a program in Linux so I'll take that comfort. But it seems to work! Will see how it goes.

Thanks @Pat Hartman and sorry for the trouble!