Answered by:
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.
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