Update record in table

[wikus]

Can somebody perhaps help with VBA code to update a second table with a record selected if first table.
I have 2 tables, Prospects and Clients tables. I want to select a Prospect and it must be appended in the Client table and marked as moved to Clients in the Prospect table.

 

[Trevor G]

Shown below is how to use an Update using VBA with an SQL statement.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> sqlUWRCode()<br>DoCmd.SetWarnings <SPAN style="color:#00007F">False</SPAN><br>sql = "UPDATE [XIAP Stub dataset New] INNER JOIN [Underwriter Codes] ON [XIAP Stub dataset New].[Uwr ID Code] = [Underwriter Codes].[Initials]"<br>sql = sql & "SET [XIAP Stub dataset New].[Uwr ID Code] = [Underwriter Codes]![XIAP Code];" <SPAN style="color:#007F00">' The table to update 'the field and content to add</SPAN><br>Debug.Print sql<br>DoCmd.RunSQL sql<br>DoCmd.SetWarnings <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Second sample

Function sqlParticipant2()
DoCmd.SetWarnings False
Dim sql As String
sql = "UPDATE [XIAP Stub dataset New] " 'The Table to Update
sql = sql & "SET [XIAP Stub dataset New].[Participant 2]='PRC040'" 'The field and content to add
sql = sql & "WHERE [XIAP Stub dataset New].[Participant 2]<>''" 'The Field and the current content to change
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.SetWarnings False
End Function

One way you can do this, is create a query and convert it to Update, then look at the SQL code and you have the SQL side to then add in a VBA statement like the above.

 

[wikus]

Thanks Trevor, appreciate your help.

 

[Trevor G]

Your welcome I hope it helps find a solution if not post back.