Updating selected filtered records with new value from unbound form field in VBA (1 Viewer)

[zyborg33]

I am opening a form to allow the user to change names contained in a child table (multiple records)
and having the user enter the old name and the new name

Code below fails. NamePhone in the table projects - says its not defined in context - and yet that is the name of the field in the table projects. I am assuming its just something I have missed.

1.

Private Sub cmdNewName_Click()
2.	Dim rsProjects As ADODB.Recordset
3.	Dim strSQL As String

4.	Set conDatabase = CurrentProject.Connection
5.	strSQL = "SELECT * FROM Projects WHERE NamePhone =" & oldname

6.	Set rsProjects = New ADODB.Recordset
7.	rsProjects.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

8.	With rsProjects
9.	Do While Not .EOF
10.	!NamePhone = Newname
11.	.Update
12.	.MoveNext
13.	Loop
14.	End With
    
      
15.	rsProjects.Close
16.	conDatabase.Close
17.	Set rsProjects = Nothing

Line 7 fails, NamePhone has no value. This is the Projects table - I am trying to update records field NAmePhone with NewName when they match oldname

Projectid projectname cost NamePhone
1 Spinning $3.34 JustKeys
2 Dancing $4.35 SamBird
3 Running $2.3 JustKeys
4 Jogging $3.99 AnsKay

 

[CJ_London]

Not sure why you are using ADODB (it is allowed) but two things I note is

• you are not using the conDatabase assigned in line 4 in the open statement in line 7
• Assuming NamePhone is text line 5 should be strSQL = "SELECT * FROM Projects WHERE NamePhone ='" & oldname & "'"

I presume that OldName and NewName are defined elsewhere as public variables and neither contain single quotation marks

If you were to use DAO then I would suggest the following:

2. Dim strSQL As String
3. strSQL="UPDATE Projects SET Namephone= '" & NewName & "' WHERE NamePhone='" & oldname & "'"
4. currentdb.execute(strSQL)

 

[zyborg33]

Thank u so much and it worked beautifully
Second question if at all possible - How would I update another table, lets say Employees with the new namephone variable. Namephone is the primary key in the employees table.

This doesnt seem to be working . Thank you again for your help

Private Sub cmdNewName_Click()
    Dim conDatabase As DAO.Database
    Dim strSQL As String

' your code which works beautifully
    [I]Set conDatabase = CurrentDb
    strSQL = "UPDATE Projects SET Namephone= '" & Newname & "' WHERE NamePhone='" & oldname & "'"
    conDatabase.Execute (strSQL)[/I]

     
[B]   ' and then I fail on line conDatabase.Execute (strSQL) !!!
     strSQL = "UPDATE Employees SET Nampephone='" & Newname & "' Where NamePhone='" & oldname & "'"
     conDatabase.Execute (strSQL)[/B]
    
    MsgBox "All employees have received a raise of " & txtGeneralRaise
    
    conDatabase.Close
    Set conDatabase = Nothing
End Sub

 

[zyborg33]

Thank you so much. I could not get the DAO to work so I used ADODB and of course couldnt get it to work either. Thank you so much -
Your code is concise, robust, simple and works perfectly. Thank you

 

[CJ_London]

Second question if at all possible - How would I update another table, lets say Employees with the new namephone variable. Namephone is the primary key in the employees table.

The simple answer is that you have a typo highlight in red below

strSQL = "UPDATE Employees SET [COLOR=red]Nampe[/COLOR][COLOR=red]phone[/COLOR]='" & Newname & "' Where NamePhone='" & oldname & "'"

However since this is your primary key, it sounds like your tables are not set up correctly. Primary keys should not be changed because one of the child tables has been changed.

However if you have to go this way then I would look at your relationships and set the one between your employee table and projects table to enforce referential integrity and cascade updates and deletion - see attached as an example.

Then your code just needs to update the employee table and the projects table will be updated automatically.

 

[zyborg33]

omigod! thankyou -
so now I realize I'm blind and dumb -- thank you
getting glasses tomorrow :banghead: