Split form Refresh Problem

gheyman

Well-known Member
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!

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

gheyman

Well-known Member
Re: Access Split form Refresh Problem

Tried both, neither worded
Code:
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

Well-known Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top