I have a very useful functionwhich works excellent for all the tables except two, namely for the tables order details and the table customers.There i receive the mesage order details.orderid not updatable and customers.customerid not updatable.How can i solve this problem? I cannot explain myself why this happens with 2 tables and not with the others.i will be very grateful if i receive some advice.Otherwise the is very effective and easy to handle
I will be grateful for any advice
Public Function UpdateTables()
UpdateTable "orders1", "orders", "orderid"
UpdateTable "orderdetails1", "orderdetails", "orderid"
UpdateTable "customers1", "customers", "customerid"
UpdateTable "TblClients1", "TblClients", "ClientID"
UpdateTable "CallsClients1", "CallsClients", "CallID"
UpdateTable "CallsCustomers1", "CallsCustomers", "CallID"
End Function
Public Function UpdateTable(SourceTable As String, TargetTable
As String, LinkField As String)
' Call this function in the following manner:
'UpdateTable SourceTable:="CallClients1",
'TargetTable:="CallClients",
'LinkField:="CallID"
'or simply
'UpdateTable "CallsClients1", "CallsClients", "CallID"
Dim strSQL As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(SourceTable)
strSQL = "UPDATE [" & TargetTable & "] INNER JOIN [" &
SourceTable & "] " & _
"ON [" & TargetTable & "].[" & LinkField & " ]=[" & SourceTable
& "].[" & LinkField & "] " & _
"SET "
For Each fld In tdf.Fields
If Not (fld.Name = LinkField) Then
strSQL = strSQL & "[" & TargetTable & "].[" & fld.Name & "]=" & _
"[" & SourceTable & "].[" & fld.Name & "], "
End If
Next fld
' Get rid of last ", "
strSQL = Left(strSQL, Len(strSQL) - 2)
' Execute update query
dbs.Execute strSQL, dbFailOnError
ExitHandler:
' Clean up
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Function
ErrHandler:
' Report error, then go to cleaning up section
MsgBox Err.description, vbExclamation
Resume ExitHandler
End Function
