Form Filter and RecordSource Don't Query New Record

[maxwhipw]

Hi all I have something that I've been working at for 2 days straight and intermittently for 2 weeks now.
None of the solutions online have worked, I think I saw one other person who had my problem but it wasn't the same fix.

Explanation: When setting my single record form's .Filter or .RecordSource to a new record I immediately made prior, .Requery does not populate the form with the new data unless one waits 5 or so seconds after the new record was added with Recordset.Add and Recordset.Update. If it don't wait requery leaves a blank form if I apply a filter, and doesn't change a thing if I change the RecordSource to a SQL query targeting the new record. Manually refreshing with F5 will bring the record up after waiting.

Details & Considerations:
I have a split database, the back-end is on my network drive and the front-end is in my documents folder on my local drive.

I am trying to update a single record form that has one of my tables Parts as its RecordSource.

With VBA code I open the Parts recordset in the backend db, in a new workspace, add the record and commit the transaction.

The front end form is fairly complex and has a lot of CBOs and has a subform in a subform, but if the .Update is synchronus it should just be slow and still eventually update.

I don't have a lot of indexes or records in my database.

Code:

Private Function CreatePart() As Boolean
   
    Dim objFSO As New FileSystemObject
   
    If objFSO.FolderExists(obj_PartPaths.FullPath) = False Then
        If MsgBox("New part number detected. Select OK to create a new part folder structure for " & str_PartNumber, vbOKCancel, "New Part Number") _
            = vbOK Then
            obj_PartPaths.CreatePartFolder
            obj_PartPaths.CreateInternalFolders
           
        Else
            CreatePart = False
            Exit Function
        End If
    End If
   
    Dim wsp As DAO.Workspace: Set wsp = CreateWorkspace("CreatePart", "admin", "")
    Dim db As Database: Set db = OpenDatabase(BE_DB)
    Dim rstParts As Recordset: Set rstParts = db.OpenRecordset("Parts")
   
    Debug.Print "Record count immediately before transaction: "; DCount("*", "Parts")
   
   
    wsp.BeginTrans

    rstParts.AddNew
    rstParts!stxPartNumber = str_PartNumber       'Private module level variables
    rstParts!stxCustomer = str_CustomerName
    rstParts.Update
   
    wsp.CommitTrans
   
    'This prints the same as the debug line above showing the record is added, yet the following requery does nothing to update the form unless a 5 second timer delay is added to the form. It can take longer than 5 seconds
    Debug.Print "Record count immediately after transaction: " & DCount("*", "Parts") & vbNewLine & vbNewLine
   
   [Form_Engineer Toolkit].Requery
   
    DoCmd.OpenForm "Engineer Toolkit", , , "stxPartNumber = '" & str_PartNumber & "'"
   
   
    rstParts.Close
    Set rstParts = Nothing
    Set db = Nothing
    Set wsp = Nothing
   
   
   
    CreatePart = True

End Function

I have tried:

• Adding DoEvents after .Update
• .Refresh after .Update
• .Requery and .Refresh after .Update
• Commiting transactions and then .Requery
• .RecordSource instead of .Filter
• I unsuccessfully tried the Me.Bookmark = rst.Bookmark method, as for some reason when I do Set rst = Me.RecordsetClone it throws an error saying my front-end database is already in use? This happens regardless of whether I have shared or exclusive mode enabled

The only thing that "works" is setting a timer and checking to see if the form has updated correctly and running Me.Requery every second until it does.
However this isn't ideal and causes the form to flash every time it requeries and I would hope it to be much simpler

Does anyone know why this is happening or how to fix it?

Thank you I greatly appreciate any help given




Below is the backend relationship if that matters

 

[theDBguy]

Are you working with a bound or unbound form? Just curious...

 

[maxwhipw]

Are you working with a bound or unbound form? Just curious...

It's bound to the Parts table that I open and add a record to in the code which is what really confuses me.

 

[arnelgp]

also why are you opening the BE, can't you create a linked table on Parts table?

It's bound to the Parts table that I open and add a record to in the code which is what really confuses me.

that is the problem you have the table open in the Form and yet you use VBA to update your table.
my opinion is you abandon opening the BE and again opening the table (which is already open in the form),
and use RecordsetClone instead.

 

[arnelgp]

not tested:

Private Function CreatePart() As Boolean
 
    Dim objFSO As New FileSystemObject
 
    If objFSO.FolderExists(obj_PartPaths.FullPath) = False Then
        If MsgBox("New part number detected. Select OK to create a new part folder structure for " & str_PartNumber, vbOKCancel, "New Part Number") _
            = vbOK Then
            obj_PartPaths.CreatePartFolder
            obj_PartPaths.CreateInternalFolders
         
        Else
            CreatePart = False
            Exit Function
        End If
    End If
 
    Dim wsp As DAO.Workspace: Set wsp = CreateWorkspace("CreatePart", "admin", "")
    'Dim db As Database : Set db = OpenDatabase(BE_DB)
    Dim rstParts As Recordset                                    ': Set rstParts = db.OpenRecordset("Parts")
 
    Set rstParts = Me.RecordsetClone
  
    Debug.Print "Record count immediately before transaction: "; DCount("*", "Parts")
 
 
    wsp.BeginTrans

    rstParts.AddNew
    rstParts!stxPartNumber = str_PartNumber       'Private module level variables
    rstParts!stxCustomer = str_CustomerName
    rstParts.Update
 
    wsp.CommitTrans
 
  
    'This prints the same as the debug line above showing the record is added, yet the following requery does nothing to update the form unless a 5 second timer delay is added to the form. It can take longer than 5 seconds
    Debug.Print "Record count immediately after transaction: " & DCount("*", "Parts") & vbNewLine & vbNewLine
 
   'no need to requery since we are using the Form's recordset
   '[Form_Engineer Toolkit].Requery
 
    'arnelgp
    'why open a instance of the form?
    'DoCmd.OpenForm "Engineer Toolkit", , , "stxPartNumber = '" & str_PartNumber & "'"
 
 
    'rstParts.Close
    Set rstParts = Nothing
    Set db = Nothing
    Set wsp = Nothing
 
 
 
    CreatePart = True

End Function

 

[MarkK]

I would refactor the code more like...

Private fso_ As New Scripting.FileSystemObject

Private Function CreatePart() As Boolean
    Dim rsp As VbMsgBoxResult
  
    If fso_.FolderExists(obj_PartPaths.FullPath) = False Then
        rsp = MsgBox("New part number detected. Select OK to create a new part folder structure for " & str_PartNumber, vbOKCancel, "New Part Number")
        If rsp = vbOK Then
            DoCreatePartFolders
            DoCreatePart
            CreatePart = True
        End If
    End If
End Function

Private Sub DoCreatePartFolders()
    obj_PartPaths.CreatePartFolder
    obj_PartPaths.CreateInternalFolders
End Sub

Private Sub DoCreatePart()
    With CurrentDb.OpenRecordset("Parts")
        .AddNew
        !stxPartNumber = str_PartNumber       'Private module level variables
        !stxCustomer = str_CustomerName
        .Update
        .Close
    End With
End Sub

This makes it much clearer, to me, what is going on. Each routine does one thing. Each unary task is clearly named.

You don't need a workspace, you don't need a transaction, you don't need to open a database reference to the BE. If DCount("*", "Parts") works, then there must be a local Parts table, so use it. I wouldn't add rows to the RecordsetClone.

This notation, with the prefixed "Form_"...

[Form_Engineer Toolkit].Requery

... creates a new non-default instance of the form, requeries that instance, which then immediately goes out of scope again and closes, so it does nothing.

The method that calls CreatePart in the first place should handle user interface updates like requerying open forms. If Engineer Toolkit is open, look for it at...

Forms("Engineer Toolkit")

hth

 

[maxwhipw]

I would refactor the code more like...

This makes it much clearer, to me, what is going on. Each routine does one thing. Each unary task is clearly named.

You don't need a workspace, you don't need a transaction, you don't need to open a database reference to the BE. If DCount("*", "Parts") works, then there must be a local Parts table, so use it. I wouldn't add rows to the RecordsetClone.

This notation, with the prefixed "Form_"...

[Form_Engineer Toolkit].Requery

... creates a new non-default instance of the form, requeries that instance, which then immediately goes out of scope again and closes, so it does nothing.

The method that calls CreatePart in the first place should handle user interface updates like requerying open forms. If Engineer Toolkit is open, look for it at...

Forms("Engineer Toolkit")

hth

Thank you! Unfortunately whenever I try to do Set rstParts = CurrentDb.OpenRecordset("Parts") it says Run-time error: '3045' Could not use '{db-file.accdb''; file already in use

No idea why it's doing that. No one else is using the file and I don't have locks enabled.

The file being set to shared or exclusive doesn't change this, will try again to confirm that.

 

[maxwhipw]

not tested:

Same as I mentioned above. Doing Me.RecordsetClone says throw an error saying the database is already in use.

 

[MarkK]

Make sure you don't have a hidden instance of MSAccess running. Open task manager and look in applications, but also in processes. Kill any instance you see of MSACCESS.EXE.

 

[maxwhipw]

Huh wait doing it this way works

Private Sub DoCreatePart() With CurrentDb.OpenRecordset("Parts") .AddNew !stxPartNumber = str_PartNumber 'Private module level variables !stxCustomer = str_CustomerName .Update .Close End With End Sub

 

[maxwhipw]

Make sure you don't have a hidden instance of MSAccess running. Open task manager and look in applications, but also in processes. Kill any instance you see of MSACCESS.EXE.

The with statement fixed it thank you!
Did some of the other edits too, much appreciated.

 

[Pat Hartman]

With VBA code I open the Parts recordset in the backend db, in a new workspace, add the record and commit the transaction.

Why are you not using a bound form?

 

[maxwhipw]

Why are you not using a bound form?

I use a separate unbound form to select and add Parts and one bound one to edit it for data integrity purposes. The record ID should never be changed ideally

 

[Pat Hartman]

There is no reason to use a separate form for adds and updates. The ID, if it is an autonumber, is not changable under any conditions. If it is generated, then you lock the ID field in the current event of the form when you scroll to an existing record, AND you have code that doesn't allow the ID to be changed.

When you use two separate forms, you have two separate validation procedures and that means that you or your successor always have to remember that you have two separate procedures that must be modified if you change one of them.