How can I add VB code to refresh form after change?

[jmckeone]

I've been using VB in excel for years but new to it in Access. I'm working with a form someone else created and am adding a delete button. I've managed to add the button and get some code working which will delete a record from a table. The problem I'm having is that after the delete operation occurs all fields in the form, and the corresponding items below the form, show #Deleted in all fields. How can I force a refresh using code?

 

[boerbende]

me.refresh as last code under the delete button?

 

[jmckeone]

I'd found that command via a google search and though I included it in my code, shown below, it did not seem to do the trick.

Private Sub butnRemove_Click()

    Dim strUpdate As String, strDelete As String
    Dim ctl As Control
    Dim strSet As String
    Dim bFirst As Boolean
    Dim iResponse As Integer

'1. delete from the master table
    strDelete = "DELETE * FROM tblMaster"

    bFirst = True

 '2. Ask the question to confirm delete before deleting anything!
    iResponse = MsgBox("Are you sure you want to delete record ID " & ID & "?", vbYesNo)
    If (iResponse = vbNo) Then
        Me.Undo
        Exit Sub
    End If
    
  '3. Adjust the Capacity Field value to 0 so the AuditTrail will acknowledge a change
   For Each ctl In Me.Controls
    With ctl
        If .ControlType = acTextBox Then
          If .Name = "Capacity" Then
            .Value = "0"
          End If
        End If
    End With
    Next
    Set ctl = Nothing

'4. Update the Audit Table
    Call AuditTrail(Me, ID)
    
'5. Delete item from tblMaster
    strDelete = strDelete & " where ID = " & ID.Value
    Debug.Print strDelete
    DoCmd.SetWarnings False
    DoCmd.RunSQL strDelete
    DoCmd.SetWarnings True
        
'6. Update Changes table to remove the same record from the changes table
    strDelete = "DELETE * FROM Changes" & " where ID = " & ID.Value
    Debug.Print strDelete
    DoCmd.SetWarnings False
    DoCmd.RunSQL strDelete
    DoCmd.SetWarnings True
        
    DoCmd.RunCommand acCmdRecordsGoToNext
    
' Refresh the table in the Change Master Record Form, adjust which record is selected, so it appropriate.
    Me.Refresh

    
End Sub

 

[pbaldy]

Try Requery instead of Refresh.

 

[jmckeone]

Thank you very much. That worked beautifully.

 

[gemma-the-husky]

the only issue is that requery resets the cursor to the first record. to avoid this you have to store the current position before the requery, and then return to that position after the requery.

 

[jmckeone]

Thought that was too simple. Advice on how to store record location to return to next after requery?

 

[shadow9449]

Set a variable that will contain the record ID of the record you want to go to, then requery, then advance to the record whose ID you've stored.

Here's a simple example:

Dim remember_me As Long


DoCmd.Echo False
remember_me = Me.ID

Forms!MyForm.Requery


Forms!MyForm.RecordsetClone.FindFirst "[ID] = " & remember_me
Forms!MyForm.Bookmark = Forms!MyForm.RecordsetClone.Bookmark


DoCmd.Echo True

 

[pbaldy]

I've got a sample with similar code here:

http://www.baldyweb.com/Requery.htm

 

[jmckeone]

Forgive me if I'm thick. I've tried applying this procedure and it still goes back to the first record in the form. Here's the code with the last 3 commented sections being where I attempted to apply the process.

Private Sub butnRemove_Click()

    Dim strUpdate As String, strDelete As String
    Dim ctl As Control
    Dim strSet As String
    Dim bFirst As Boolean
    Dim iResponse As Integer
    Dim remember_me As Long

'1. delete from the master table
    strDelete = "DELETE * FROM tblMaster"

    bFirst = True

 '2. Ask the question to confirm delete before deleting anything!
    iResponse = MsgBox("Are you sure you want to delete record ID " & ID & "?", vbYesNo)
    If (iResponse = vbNo) Then
        Me.Undo
        Exit Sub
    End If
    
  '3. Adjust the Capacity Field value to 0 so the AuditTrail will acknowledge a change
   For Each ctl In Me.Controls
    With ctl
        If .ControlType = acTextBox Then
          If .Name = "Capacity" Then
            .Value = "0"
          End If
        End If
    End With
    Next
    Set ctl = Nothing

'4. Update the Audit Table
    Call AuditTrail(Me, ID)
    
'5. Delete item from tblMaster
    strDelete = strDelete & " where ID = " & ID.Value
    Debug.Print strDelete
    DoCmd.SetWarnings False
    DoCmd.RunSQL strDelete
    DoCmd.SetWarnings True
        
'6. Update Changes table to remove the same record from the changes table
    strDelete = "DELETE * FROM Changes" & " where ID = " & ID.Value
    Debug.Print strDelete
    DoCmd.SetWarnings False
    DoCmd.RunSQL strDelete
    DoCmd.SetWarnings True
        
    DoCmd.RunCommand acCmdRecordsGoToNext
    
' Capture next item to resume after requery command
    remember_me = acCmdRecordsGoToNext
    
' Refresh the table in the Change Master Record Form, adjust which record is selected, so it appropriate.
    Me.Requery
    
' Reset ID
    Me.RecordsetClone.FindFirst "[ID] = " & remember_me

End Sub

 

[spikepl]

You did not include the code form the example supplied to you which makes the actual move:

Forms!MyForm.Bookmark = Forms!MyForm.RecordsetClone.Bookmark

You cannot be sloppy with coding like this. Access doesn't read minds and is very literal about how it interprets code.

Further, you neeed to check your own work. What makes you think that acCmdRecordsGoToNext contains the ID of a record? Did you check what it holds? Or even, yes really, did you look it up in the documentation?

debug.print myValueToCheck

prints the value to the immediate window.

Again, follow the example you were given. If you make changes, make sure you know what you are doing.

 

[jmckeone]

So, I took another stab at it and it seems Forms!MyForm was not recognized. When replaced with Me it does not fail but still does not return to the correct record but kicks back to beginning again. Before spikepl gives me a verbal bitch slap again a little reminder this is my first venture into using VB in access.

Here's the code as it currently exists

Private Sub butnRemove_Click()

    Dim strUpdate As String, strDelete As String
    Dim ctl As Control
    Dim strSet As String
    Dim bFirst As Boolean
    Dim iResponse As Integer
    Dim remember_me As Long

'1. delete from the master table
    strDelete = "DELETE * FROM tblMaster"

    bFirst = True

 '2. Ask the question to confirm delete before deleting anything!
    iResponse = MsgBox("Are you sure you want to delete record ID " & ID & "?", vbYesNo)
    If (iResponse = vbNo) Then
        Me.Undo
        Exit Sub
    End If
    
  '3. Adjust the Capacity Field value to 0 so the AuditTrail will acknowledge a change
   For Each ctl In Me.Controls
    With ctl
        If .ControlType = acTextBox Then
          If .Name = "Capacity" Then
            .Value = "0"
          End If
        End If
    End With
    Next
    Set ctl = Nothing

'4. Update the Audit Table
    Call AuditTrail(Me, ID)
    
'5. Delete item from tblMaster
    strDelete = strDelete & " where ID = " & ID.Value
    Debug.Print strDelete
    DoCmd.SetWarnings False
    DoCmd.RunSQL strDelete
    DoCmd.SetWarnings True
        
'6. Update Changes table to remove the same record from the changes table
    strDelete = "DELETE * FROM Changes" & " where ID = " & ID.Value
    Debug.Print strDelete
    DoCmd.SetWarnings False
    DoCmd.RunSQL strDelete
    DoCmd.SetWarnings True
        
    DoCmd.RunCommand acCmdRecordsGoToNext
    
'7. Capture next item to resume after requery command
    DoCmd.Echo False
    remember_me = Me.ID
    
    
' Refresh the table in the Change Master Record Form, adjust which record is selected, so it appropriate.
    
    Me.Requery

    Me.RecordsetClone.FindFirst "[ID] = " & remember_me
    Me.Bookmark = Me.RecordsetClone.Bookmark

    DoCmd.Echo True

'' Reset ID
'    Form_Current.RecordsetClone.FindFirst "[ID] = " & remember_me

End Sub