Split form Refresh Problem

[gheyman]

I have a split form based on Table. The below code runs an append query that updates the aforementioned table. My problem is that after the append query is run, the Split form does not refresh to show the new data that was added to the form. If I hit the Refresh button in the Ribbon the form does update, but I cannot seem to get it to update using VBA code. So my work around as you see below is to close the form and reopen it - ugh!

Any suggestions how to make this better? Please, my VBA for Access is limited and really no SQL knowledge so please keep that in mind. I really appreciate your help!

Private Sub Command29_Click()
On Error GoTo Command29_Click_Err

  DoCmd.SetWarnings False

If IsNull(ComboVendorASL) Then
    DoCmd.GoToRecord , "", acNewRec
    
Else
    DoCmd.OpenQuery "qry_vluVendor_MakeTable", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry_vluVendor_MakeTable"

       
        DoCmd.Close , ""
        DoCmd.OpenForm "frm_Add_Supplier", acNormal, "", "", , acNormal
        DoCmd.GoToRecord , "", acLast
End If

Command29_Click_Exit:
    Exit Sub

Command29_Click_Err:
    MsgBox Error$
    Resume Command29_Click_Exit

End Sub

 

[welshgasman]

Re: Access Split form Refresh Problem

Ever heard of the Refresh event for a form?

https://docs.microsoft.com/en-us/office/vba/api/access.form.refresh

However I think you would need the requery method, not the refresh?

 

[gheyman]

Re: Access Split form Refresh Problem

Tried both, neither worded

Private Sub Command29_Click()
On Error GoTo Command29_Click_Err

  DoCmd.SetWarnings False

If IsNull(ComboVendorASL) Then
    DoCmd.GoToRecord , "", acNewRec
    
Else
    DoCmd.OpenQuery "qry_vluVendor_MakeTable", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry_vluVendor_MakeTable"
    
    Me.Refresh
    Me.Requery
       
   '     DoCmd.Close , ""
   '     DoCmd.OpenForm "frm_Add_Supplier", acNormal, "", "", , acNormal
        DoCmd.GoToRecord , "", acLast
End If

Command29_Click_Exit:
    Exit Sub

Command29_Click_Err:
    MsgBox Error$
    Resume Command29_Click_Exit

End Sub

 

[welshgasman]

Re: Access Split form Refresh Problem

Not sure what else to suggest.
I would have thought that you would execute a MakeTable query and not just open it.?, but that does appear to be one of the options?
https://www.fmsinc.com/MicrosoftAccess/query/action-queries/SuppressWarningMessages.htm
You are setting warnings to false, but not back to True.?
Why is an append query named MakeTable?

Split forms do have some strange quirks, but I do not think this is one of them.