none
Open and update a detail record in a form for updating RRS feed

  • Question

  • I have a main form and suborm structure.  The subform is in a datasheet form. The end user would like to be able to open any record in the subform in a pop-up window and modify the record.  How can I go about this.

    What is the best way of doing this.

    N.B - Main form training events

              Sub Form - Related Training Activities.


    olu Solaru

    Monday, May 13, 2019 3:15 PM

All replies

  • I usually use the DblClick event of one (or more) of the controls on the subform to open a detail form based on the same table.  To avoid conflicts, I first make sure that the subform record is not dirty.  So the code for the DblClick even looks sort of like this:

    Private Sub txtID_DblClick(Cancel As Integer)
    
        If Not IsNull(Me.txtID) Then
    
            If Me.Dirty Then Me.Dirty = False
        
            DoCmd.OpenForm "frmTrainingActivitiesDetail", WhereCondition:="ID = " & Me.txtID
        
        End If
    
    End Sub
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 13, 2019 3:33 PM
  • Thanks.  What does Me.TXTID represent. Would that be the Unique ID on the form?

    olu Solaru

    Monday, May 13, 2019 6:48 PM
  • Thanks.  What does Me.TXTID represent. Would that be the Unique ID on the form?

    In my example, that was the unique ID of the subform record.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 13, 2019 6:52 PM
  • Thanks for responding. Will this be the double-click event of the subform?

    olu Solaru

    Tuesday, May 14, 2019 1:56 PM
  • Also in this line - 

    DoCmd.OpenForm "frmTrainingActivitiesDetail", WhereCondition:="ID = " & Me.txtID

    My option after "frmTrainingACtivitiesDetail" is acNormal, AcPreview...etc...I don't see the option to do the whereCondition.


    olu Solaru

    Tuesday, May 14, 2019 1:59 PM
  • This is what my syntax looks like 

    Private Sub Form_DblClick(Cancel As Integer)
        If Not IsNull(Me.[Activity_ID]) Then
        If Me.Dirty Then Me.Dirty = False
        DoCmd.OpenForm "frmTrainingActivity", WhereCondition = "ID" = " & Me.Activity_ID"
    End Sub


    olu Solaru

    Tuesday, May 14, 2019 2:08 PM
  • This is what my syntax looks like 

    Private Sub Form_DblClick(Cancel As Integer)
        If Not IsNull(Me.[Activity_ID]) Then
        If Me.Dirty Then Me.Dirty = False
        DoCmd.OpenForm "frmTrainingActivity", WhereCondition = "ID" = " & Me.Activity_ID"
    End Sub

    You probably wouldn't use the form's (subform's) DblClick event here, because that only fires when the user double-clicks on the record selector.  For this sort of thing, I use the DblClick event of the Activity_ID field, or the associated description field.  I can't see how your form is  laid out, so I can't give more specific advice.  But it's your choice what control(s) DblClick event you want to use to activate this procedure.  For that matter, you could put a small command button on the form, and use that button's Click event instead.

    The OpenForm call in your code is not quite right. Assuming that ID is the primary or unique key in the table to which frmTrainingActivity is bound, then you would do it like this:

    DoCmd.OpenForm "frmTrainingActivity", WhereCondition:="ID=" & Me.Activity_ID

    Notice the use of the ":=" operator in the argument specification.  That is necessary to tell VBA that this is a named argument, not a positional one.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, May 14, 2019 8:33 PM