MS Access - Form - Edit selected record from other form

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.

Answer 1

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...

Answer 2

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.

Answer 3

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.

Answer 4

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