Update table on Form Exit (1 Viewer)

[kbushon2006]

I have a form based on one table and I need it to update a different table on exit.

I have tried some existing code in the data which I inherited from another programmer and am unable to make it work mostly because I don't really understand how the to write the SQL statement.

What I need to do is have all the fields on the form update my table, however, the Key field of the other table SRID (Shipping Release ID) doubles (563563) instead of just updating the existing.

I have used the following:

Dim sSQL As String
Dim iCnt As Integer
Dim rsTemp As DAO.Recordset
Dim lSRID As Long

If gErrHOn Then On Error GoTo CheckError
Refresh

'Create a copy of this forms Recordset

Set rsTemp = Me.RecordsetClone

rsTemp.MoveFirst

'Loop through all records

Do Until rsTemp.EOF
If rsTemp!ShipYesNo = True Then
'bInsertInXrefTable = True
'SZI - SR Item ID that was passed as a parameter to this form from frmLogisticsMain form
If Len(OpenArgs & "") <> 0 Then
'use the argument for the sql below
lSRID = Forms!frmMatlTrans!SRID
sSQL = "INSERT INTO tblMTMain (SRID, [SR Number]) VALUES (" & lSRID & ", " & rsTemp![SR Number] & ");"
CurrentDb.Execute sSQL, dbSeeChanges
End If

There are more fields and I am wondering if there is anyway to just indicate that I need all fields inserted.

I hope this is clear and I look forward to you help.

:banghead::banghead::banghead::banghead:

 

[Cronk]

Firstly, "INSERT INTO..." in your sql line, adds records, not updates them.

You could simply just create a query which updates your linked table, and run that query in the Exit event.

BTW, to see the sql which has been generated in creating the query, click on the SQL view option when you are in query design.