locked
MS Access - Form - Edit selected record from other form RRS feed

  • Question

  • Hi,

    I have a form that have a subform also. In the subform there are records that have field called "Sou". The user click on this field and new form open up that have related data. User select some Source whose ID is sent to previous form. I want that when user send ID to previous form it also update the selected field "Sou" with this ID.

    Any help would be appreciated.

    Thank you.


    Monday, November 6, 2017 10:20 AM

Answers

  • I solve my problem as

    In new open form:

    Private Sub ButtonCopy_Click()
    
        Dim frm     As Form
        
        Set frm = Forms("YourForm")
        frm!txtbxSou.Value = somevalue    
        frm.UpdateSou
        
    End Sub
    
    Select all

    Call function to update value in previous form

    Public Sub UpdateSou()
    
        Dim rs As DAO.Recordset
    
        Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone
    
        If rs.RecordCount > 0 Then
            rs.FindFirst "SomeField = '" & SomeValue & "'"    ' No single-quotes if value is numeric.
            If rs.NoMatch = False Then
                rs.Edit
                    rs!Sou.Value = Me!txtbxSou.Value
                rs.Update
            End If
        End If
        rs.Close
    
    End Sub
    Thank you.

    • Marked as answer by Wsm93 Tuesday, November 7, 2017 8:36 AM
    Tuesday, November 7, 2017 8:36 AM

All replies

  • Hi,

    The way I would probably do it is to open the other form in Dialog mode, so I can assign the ID to the current record after the user closes the other form.

    Just my 2 cents...

    Monday, November 6, 2017 7:54 PM
  • I solve my problem as

    In new open form:

    Private Sub ButtonCopy_Click()
    
        Dim frm     As Form
        
        Set frm = Forms("YourForm")
        frm!txtbxSou.Value = somevalue    
        frm.UpdateSou
        
    End Sub
    
    Select all

    Call function to update value in previous form

    Public Sub UpdateSou()
    
        Dim rs As DAO.Recordset
    
        Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone
    
        If rs.RecordCount > 0 Then
            rs.FindFirst "SomeField = '" & SomeValue & "'"    ' No single-quotes if value is numeric.
            If rs.NoMatch = False Then
                rs.Edit
                    rs!Sou.Value = Me!txtbxSou.Value
                rs.Update
            End If
        End If
        rs.Close
    
    End Sub
    Thank you.

    • Marked as answer by Wsm93 Tuesday, November 7, 2017 8:36 AM
    Tuesday, November 7, 2017 8:36 AM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. I was afraid you might get an error about the record already being edited, but good thing it didn't.

    Good luck with your project.

    Tuesday, November 7, 2017 4:25 PM
  • Just a suggestion to simplify your code a bit

    Public Sub UpdateSou()
       DoCmd.RunSql "Update tblxxx Set fldFoo = '" & txtSou.Text _
                     & "' Where somefld = " & someValue
    End Sub

    In this sample I am treating txtSou.Text as a string value, so you need to enclose the sql string in single quotes

    .... '" & txtSou.Text & "' ....

    and in the clause ... Where somefld = " & someValue  ...

    I am treating somefld as a numeric field -- which does not  require single quotes or pound # symbols like strings and dates.

    Since Access is a relational database management system  (RDBMS) type application you can take advantage of the Jet or Ace engine and let Sql do the work.  It is more efficient than DAO code.

    Also, in this sample I broke to string into two lines (so can be seen more easily).  To break code line in VBA you have to add Underscore _ at the end of the line followed by &.


    Rich P



    • Edited by Rich P123 Wednesday, November 8, 2017 9:40 PM ........
    Wednesday, November 8, 2017 9:35 PM