Streamlining Add/Edit functionality (1 Viewer)

[CanuckBuck]

I need some direction regarding form interactions.

I have a view/search form that users use to either find and edit an existing record by clicking an Edit This Record button OR add a new record by clicking an Add New record. When they click either button I open a form in either Edit or Add mode;

Edit;

DoCmd.OpenForm FrmNameEdit(frm), acNormal, "", "[DB_Key]=" & frm.txtDBKey, acEdit

Add;

DoCmd.OpenForm FrmNameEdit(frm), acNormal, "", "", acAdd

I've attached screenshots of the view/browse and edit forms so you can see what I'm working with.

Where I'm struggling is how to efficiently/reliably update the view/search form when the user is finished editing an exiting record or adding a new one.

There are a number of scenarios I need to handle;

1. User doesn't make any changes. This is easy enough. Just close the edit form. Nothing to see here...
2. User makes changes to the main form. I need to refresh the view/search form to display changes.
3. User makes changes to either or both the main from and records in subforms. I may need to refresh the view/search form to display changes to the main form and also to the records in the subforms.
4. User addes a new record. I need to navigate to the newly created record.

I do know that if I were to include the acDialog keyword to the DoCmd.OpenForm method that flow control would be paused while the edit form was open then I'd be able to requery the view/search form when it closes, however, the users have asked for a resizable edit dialog box and the acDialog keyword does not permit that.

I should also note that for reasons I have not been able to determine that the refresh method does not work for the recordsource of my view/search form. I have resorted to saving the primary key of the record, requerying the form and then navigating back to the record.

I'm really hoping that there's a simple way to do what I need to do.

Thanks in advance for any assistance you can provide.

 

[theDBguy]

Hi. I think you may be doing already what can be done (pending viewing your code) if you can pause the code when opening the popup form. Either way, the usual way is to store the ID you're editing, requery the search form, and then navigate to the stored ID using the bookmark property. In the case of adding a new record, then the ID is passed from the popup form. Without using acDialog, you would do the requery and navigation in the Close event of the popup form. So, it sounds like you're already doing all that. If you post your code, we can try to see if there's anything else you could try.

 

[CanuckBuck]

theDBGuy;

Thanks for confirming for me that I was headed in the right direction and that there wasn't some other trick that I didn't know about.

After I refactored my code a bit to get it into the reusable modules (I moved all of the code from the form modules to separate modules - one for the view/search form and one for the Add/Edit form, so that I can call the same routines from multiple forms) this is what I came up with;

In the View/Search form module

Private Sub btnEdit_Click()

'Open the edit form in edit mode
'displaying the currently selected
'record.
'Requery the datasoruce when the
'edit form is closed in order to
'display any changes

    EditButton Me

End Sub

Private Sub btnAdd_Click()

'Open the edit form in add new record
'mode - displaying a blank form.
'If the user saves a new record, when
'the edit form is closed, navigate to
'the newly added record in the view
'form.  If the user doesn't add a new
'record focus remains on the record
'displayed when the user clicked the
'btnAdd button

    AddButton Me

End Sub

In the centralized View/Search module;

Sub EditButton(frm As Form)

'open the data entry form in edit mode, showing
'the currently selected record from the browse form
'The FrmNameEdit(frm) function returns the name of the
'Edit form which is related to the view/search form
    
    DoCmd.OpenForm FrmNameEdit(frm), acNormal, "", "[DB_Key]=" & frm.txtDBKey, acEdit
            
End Sub

Sub AddButton(frm As Form)

'Open the data entry form in add record mode, showing a empty/blank data entry form
        
    DoCmd.OpenForm FrmNameEdit(frm), acNormal, "", "", acAdd
    
End Sub

In the Add/Edit form module

Private Sub btnSaveClose_Click()

'Execute actions when the user clicks
'the Save and Close button

    SaveAndClose Me
    
End Sub

Private Sub btnCancel_Click()

'Execute actions when the user clicks
'the close button

    CancelAndClose Me
    
End Sub

In the centralized Add/Edit module

Sub SaveAndClose(frm As Form)

'Save any unsaved changes
    
    If frm.Dirty Then
        If ValidDecisionDateAndDecision(frm) Then
            frm.Dirty = False
        Else
            Exit Sub
        End If
    End If
    
'Navigate to the edited record in the browse form

    NavigateToEditedRecord frm
    
'Close the edit form

    DoCmd.Close acForm, frm.Name

End Sub

Sub CancelAndClose(frm As Form)

'Undo any unsaved changes
    If frm.Dirty Then
        frm.Undo
    End If
    
'If it was a new record then there's nothing to navigate to.  Just close the form.
'Otherwise navigate to the edited record in the browse form

    If frm.NewRecord Then
        DoCmd.Close acForm, frm.Name
    Else
        NavigateToEditedRecord frm
    End If
    
'Close the edit form

    DoCmd.Close acForm, frm.Name
    
End Sub

Sub NavigateToEditedRecord(frm As Form)
Dim rst As DAO.Recordset
Dim frmName As String

frmName = FrmNameBrowse(frm)

'Get the saved changes into the recordset of the browse form.
'Note that we use a boolean flag to bypass the Form_Current event handler because it get's
'called superfluously multiple times as we requery the recordset and navigate to the
'record being edited
    
    boolBypassFormCurrent = True
    Forms(frmName).Requery

'Navigate the browse form to the newly saved record
    
    Set rst = Forms(frmName).RecordsetClone
    With rst
        .FindFirst "[DB_Key]  = " & frm.txtDBKey
        If Not .NoMatch Then
            boolBypassFormCurrent = False
            Forms(frmName).Bookmark = .Bookmark
        End If
    End With
    Set rst = Nothing
    
    If Forms(frmName).CurrentRecord = 1 Then SetForm Forms(frmName)

End Sub

 

[theDBguy]

Hi. Looks good. Great job! Good luck with your project.