Update a recordset in a subform - little different

[FranS37]

I want to double-click in the e-mail field on a subform and have all the new_e-mail fields update with the value in the field where the cursor is. For instance, on my subform:

New_e-Mail:______________ E-Mail:___FSmith@aol.com_____
New_e-Mail:______________ E-Mail:___MSmith@aol.com_____

becomes:

New_e-Mail:__FSmith@aol.com E-Mail:___FSmith@aol.com_____
New_e-Mail:__FSmith@aol.com E-Mail:___MSmith@aol.com_____

if I've double-clicked in E-Mail:_____FSmith@aol.com______

I wrote this code below, but it's updating to:

New_e-Mail:__FSmith@aol.com E-Mail:___FSmith@aol.com_____
New_e-Mail:__MSmith@aol.com E-Mail:___MSmith@aol.com_____

Can anybody help?

Private Sub EMailName_DblClick(Cancel As Integer)

Dim db As Database
Dim strsql As String
Dim rs As Recordset

strsql = "SELECT * FROM [Employees2] WHERE"
strsql = strsql & " [id] = " & [Forms]![Employees1]![Id] & ""
Debug.Print

Set rs = CurrentDb.OpenRecordset(strsql)

If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
With rs
.Edit
rs![new_EMailName] = rs![EMailName]
End With
rs.MoveNext
Loop
End If
rs.Close

End Sub

 

[bhoran]

I haven't tested it yet but if you added a strEmail and defined it as

dim StrNewEmail as String
strEmail = Me.ActiveControl.

used rs![new_EMailName] = strNewEmail

That should do the trick, I assume from the way you are building your record set you want to update new_emailname with the active control for everything returned.

 

[bhoran]

here is my tested version
Private Sub email_DblClick(Cancel As Integer)
Dim db As Database
Dim strmail As String
Dim rs As Recordset

strmail = Me.ActiveControl

Set rs = CurrentDb.OpenRecordset("select * FROM table1 where name = 'bbb';&quot

rs.MoveFirst
Do Until rs.EOF
With rs
.Edit
rs![newemail] = strmail
rs.Update
End With
rs.MoveNext
Loop
rs.Close

End Sub

 

[FranS37]

Yes, and that worked. Thank you. I knew where I needed to go, but couldn't get there.