Field not updatable

katade
katade used Ask the Experts™
on
I have a Access database linked to VB.  I am trying to use FIND through ADODC to locate a file , but I keep getting "Cannot update 'Client#'; field not updatable".  I think it is because its a AutoNumber data type in Access, but I don't want to change it.  Could someone tell me how to get round this problem?

Code below: -


Dim ClientNumber As String
   
ClientNumber = AdodcCarePlan.Recordset.Fields("Client#")
               
AdodcClient.Recordset.Find "Client# = 'Client Number'"
   
   
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How about this?

AdodcClient.Recordset.Find "[Client#] = '" & Client Number & "'"
ack, sorry... Forgot to remove the space...

AdodcClient.Recordset.Find "[Client#] = '" & ClientNumber & "'"

Author

Commented:
Sorry. No different.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
Increasing points.  Last ones left
There's no reason for find to try to interpret that as an update.  Can you post the code that appears before that line?

Author

Commented:
I can post the other code, but upon further investigation it appears that problem is directly linked to FIND.  The problem stems from the Clients# field in Access.  To test my theory I created a datagrid linked a ADODC which uses SQL to pull table data from Access.  In run time when I tried to click on a record within the datagrid, it came up with the same error message.  The program I am creating is for a college assignment and I am getting desperate.  I can't post all of the apps. code but below is the code from the form I am having problems with.  Excuse the coding I have only been doing VB for about 6 months.

Any input would be appricated.

Option Explicit
Dim CarePlanNumber As Long
Private Sub Form_Load()
    'Declare variables
    Dim SystemMenu, Res
    Dim CarePlanNumber As Long
    'Disable the form "close" option
    SystemMenu = GetSystemMenu(hwnd, 0)
    Res = RemoveMenu(SystemMenu, 6, MF_BYPOSITION)
    'Removes the Separator Line in the System Menu
    Res = RemoveMenu(SystemMenu, 6, MF_BYPOSITION)
    'Set position of form
    Me.Top = -10
    Me.Left = 1000
    'Shows form
    frmCare_Plan.Show
   
    Call ClearTextBoxes
   
    Refresh
 
End Sub
Private Sub cmdSearch_Click()
    'Open search part of form
    Height = 10410
    'Clear the datagrid using SQL
    AdodcCarePlanQuery.RecordSource = "SELECT [CarePlan].[CarePlan#], [CarePlan].[Client#], [CarePlan].[CareManager#], [CarePlan].[CarePlanExpenditure], [Client].[ClientFirstName], [Client].[ClientLastName], [Client].[ClientPostcode], [CareManager].[CareManagerFirstName], [CareManager].[CareManagerLastName] FROM Client INNER JOIN (CareManager INNER JOIN CarePlan ON [CareManager].[CareManager#] =[CarePlan].[CareManager#]) ON [Client].[Client#] =[CarePlan].[Client#];"
    AdodcCarePlanQuery.Refresh
    'Make Add, Delete, Amend, Search disabled
    cmdAdd.Enabled = False
    cmdDelete.Enabled = False
    cmdAmend.Enabled = False
    cmdSearch.Enabled = False
   
    txtLastNameSearch.SetFocus
   
End Sub
Private Sub cmdAmend_Click()
    'Set form to accept amendment
    If txtCarePlanNumber.Text = "" Then
        response = MsgBox("There currently no records on display to amend. Please select a record to amend.", vbOKOnly + vbInformation, "No care plan details") 'display message box advising no care plan details
        Exit Sub
    End If
    'Make OK and Cancel button visible
    cmdOkAmend.Visible = True
    cmdCancelAmend.Visible = True
    'Make Add, Delete, Amend, Search disabled
    cmdAdd.Enabled = False
    cmdDelete.Enabled = False
    cmdAmend.Enabled = False
    cmdSearch.Enabled = False
    'Make Add and Deleted buttons visible in frames
    cmdChangeCareManager.Visible = True
    cmdAddService.Visible = True
    cmdDeleteService.Visible = True
    'Unlock expenditure box
    txtCarePlanExpenditure.Locked = False
   
End Sub
Private Sub cmdOkAmend_Click()
    'Amends care plan details
    'Checks if users really wants to amend supplier details
    response = MsgBox("You are about to amend a suppliers details. Are you sure?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm supplier amendment") 'display message box to confirm supplier amendment
    If response = 6 Then
        AdodcSupplier.Recordset.Fields("SupplierName") = txtSupplierName.Text
        AdodcSupplier.Recordset.Fields("SupplierAddress") = txtSupplierAddress.Text
        AdodcSupplier.Recordset.Fields("SupplierDistrict") = txtSupplierDistrict.Text
        AdodcSupplier.Recordset.Fields("SupplierTown") = txtSupplierTown.Text
        AdodcSupplier.Recordset.Fields("SupplierCounty") = txtSupplierCounty.Text
        AdodcSupplier.Recordset.Fields("SupplierPostcode") = txtSupplierPostcode.Text
        AdodcSupplier.Recordset.Fields("SupplierTelephone#") = txtSupplierTelephoneNumber.Text
        AdodcSupplier.Recordset.Fields("SupplierContact") = txtSupplierContact.Text
        AdodcSupplier.Recordset.Update
        AdodcSupplier.Refresh
    End If
    'Make OK and Cancel button invisible
    cmdOkAmend.Visible = False
    cmdCancelAmend.Visible = False
    'Make Add, Delete, Amend, Search enabled
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdAmend.Enabled = True
    cmdSearch.Enabled = True
    'Make Add and Deleted buttons invisible in frames
    cmdChangeCareManager.Visible = False
    cmdAddService.Visible = False
    cmdDeleteService.Visible = False
    'Lock expenditure box
    txtCarePlanExpenditure.Locked = True
   
    Call LockTextBoxes
End Sub
Private Sub cmdCancelAmend_Click()
    'Cancels amending care plan details
    'Make OK and Cancel button invisible
    cmdOkAmend.Visible = False
    cmdCancelAmend.Visible = False
    'Make Add, Delete, Amend, Search enabled
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdAmend.Enabled = True
    cmdSearch.Enabled = True
    'Make Add and Deleted buttons invisible in frames
    cmdChangeCareManager.Visible = False
    cmdAddService.Visible = False
    cmdDeleteService.Visible = False
    'Lock expenditure box
    txtCarePlanExpenditure.Locked = True
   
    Call LockTextBoxes
End Sub
Private Sub cmdAdd_Click()
    'Set form to accept input
    'Make OK and Cancel button visible
    cmdOKAdd.Visible = True
    cmdCancelAdd.Visible = True
    'Make Add, Delete, Amend, Search disabled
    cmdAdd.Enabled = False
    cmdDelete.Enabled = False
    cmdAmend.Enabled = False
    cmdSearch.Enabled = False
    'Make Add and Deleted buttons visible in frames
    cmdAddClient.Visible = True
    cmdAddCareManager.Visible = True
    'Unlock expenditure box
    txtCarePlanExpenditure.Locked = False
    Call ClearTextBoxes
End Sub
Private Sub cmdOkAdd_Click()
    'Adds new care plan to DB
    If txtClientNumber.Text = "" Then
        response = MsgBox("You have not entered a client. Please enter a client.", vbOKOnly + vbInformation, "No client") 'display message box advising no client
        cmdAddClient.SetFocus
        Exit Sub
    ElseIf txtCareManagerNumber.Text = "" Then
        response = MsgBox("You have not entered a care manager. Please add a care manager.", vbOKOnly + vbInformation, "No care manager") 'display message box advising no care manager
        cmdAddCareManager.SetFocus
        Exit Sub
    ElseIf dbgrdCarePlanService.ColContaining = Null Then
        response = MsgBox("You have not entered a service. Please add a service.", vbOKOnly + vbInformation, "No service") 'display message box advising no service
        cmdAddService.SetFocus
        Exit Sub
    End If
        'Checks if users really wants to add new care plan
        response = MsgBox("You are about to add a new care plan. Are you sure?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm new care plan") 'display message box to confirm new care plan
        If response = 6 Then
            'Update DB table
            AdodcCarePlan.Refresh
            AdodcCarePlan.Recordset.AddNew
            AdodcCarePlan.Recordset.Fields("Client#") = txtClientNumber.Text
            AdodcCarePlan.Recordset.Fields("CareManager#") = txtCareManagerNumber.Text
            AdodcCarePlan.Recordset.Update
            AdodcCarePlan.Refresh
        End If
    End If
    'Make OK and Cancel button invisible
    cmdOKAdd.Visible = False
    cmdCancelAdd.Visible = False
    'Make Add, Delete, Amend, Search enabled
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdAmend.Enabled = True
    cmdSearch.Enabled = True
    'Make Add and Deleted buttons invisible in frames
    cmdAddClient.Visible = False
    cmdAddCareManager.Visible = False
    'Unlock expenditure box
    txtCarePlanExpenditure.Locked = True

End Sub
Private Sub cmdCancelAdd_Click()
    'Cancels adding new supplier to DB
    Call ClearTextBoxes
    'Make OK and Cancel button invisible
    cmdOKAdd.Visible = False
    cmdCancelAdd.Visible = False
    'Make Add, Delete, Amend, Search enabled
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdAmend.Enabled = True
    cmdSearch.Enabled = True
    'Make Add and Deleted buttons invisible in frames
    cmdAddClient.Visible = False
    cmdAddCareManager.Visible = False
    'Unlock expenditure box
    txtCarePlanExpenditure.Locked = True
End Sub
Private Sub cmdDelete_Click()
    'Deletes supplier from DB if there are no care plans currently linked to them
    If txtSupplierNumber.Text = "" Then
        response = MsgBox("There currently no records on display to delete. Please select a record to delete.", vbOKOnly + vbInformation, "No supplier details") 'display message box advising no supplier details
    Else
        AdodcCarePlanService.Refresh
        AdodcCarePlanService.Recordset.MoveFirst
        Do While AdodcCarePlanService.Recordset.EOF = False
            If AdodcCarePlanService.Recordset.Fields("Supplier#") = txtSupplierNumber.Text Then
                response = MsgBox("You cannot delete this supplier as they currently have care plans associated to them.", vbOKOnly + vbExclamation, "Cannot delete supplier") 'display message box to advise supplier cannot be deleted
                Exit Sub
            Else
                AdodcCarePlanService.Recordset.MoveNext
            End If
        Loop
        response = MsgBox("You are about to delete a supplier. Are you sure?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm delete supplier") 'display message box to confirm delete supplier
        If response = 6 Then
            'Update DB table
            AdodcSupplier.Recordset.Delete
            Call ClearTextBoxes
        End If
    End If
End Sub
Private Sub cmdAddService_Click()
    'Open Add Service form
    Load frmAddService
End Sub
Private Sub ChangeCareManager_Click()
   
End Sub
Private Sub ClearTextBoxes()
    'Clear the all the text boxes in the form
    For Each txt In Me
    If TypeOf txt Is TextBox Then txt.Text = ""
    Next
End Sub
Private Sub txtSupplierNameSearch_Change()
    'Search for supplier based upon input in text box
    If txtSupplierNameSearch.Text = "" Then
        Exit Sub
    End If
    AdodcSupplier.RecordSource = "SELECT * FROM Supplier WHERE SupplierName LIKE '" & txtSupplierNameSearch.Text & "%'"
    AdodcSupplier.Refresh
    txtSupplierNameSearch.SetFocus
End Sub

Private Sub txtLastNameSearch_Gotfocus()
    'Highlights text box
    txtLastNameSearch.BackColor = vbYellow
End Sub
Private Sub txtLastNameSearch_Lostfocus()
    'Highlights text box
    txtLastNameSearch.BackColor = vbWhite
End Sub
Private Sub txtLastNameSearch_Change()
    'Search for care plan based upon input in text box
    If txtLastNameSearch.Text = "" Then
        Exit Sub
    End If
    AdodcCarePlanQuery.RecordSource = "SELECT [CarePlan].[CarePlan#], [CarePlan].[Client#], [CarePlan].[CareManager#], [CarePlan].[CarePlanExpenditure], [Client].[ClientFirstName], [Client].[ClientLastName], [Client].[ClientPostcode], [CareManager].[CareManagerFirstName], [CareManager].[CareManagerLastName] FROM Client INNER JOIN (CareManager INNER JOIN CarePlan ON [CareManager].[CareManager#] =[CarePlan].[CareManager#]) ON [Client].[Client#] =[CarePlan].[Client#] WHERE [Client].[ClientLastName] LIKE '" & txtLastNameSearch.Text & "%'"
    AdodcCarePlanQuery.Refresh
    txtLastNameSearch.SetFocus
End Sub
Private Sub cmdConfirm_Click()
    'Enter selected care details details into care plan form
    Dim ClientNumber As String
   
    ClientNumber = AdodcCarePlan.Recordset.Fields("Client#")
   
   
    'Do While AdodcCarePlan.Recordset.EOF = False
        'If AdodcCarePlan.Recordset.Fields("Client#") = AdodcCarePlanQuery.Recordset.Fields("Client#") Then
               
    'AdodcClient.Recordset.Find "[Client#] = '" & ClientNumber & "'"
   
   
    'AdodcClient.Recordset.MoveFirst
    Do While AdodcClient.Recordset.EOF = False
        If AdodcClient.Recordset.Fields("Client#") = AdodcCarePlanQuery.Recordset.Fields("Client#") Then
            txtClientNumber.Text = NullToValue(AdodcClient.Recordset.Fields("Client#"))
            txtClientTitle.Text = NullToValue(AdodcClient.Recordset.Fields("ClientTitle"))
            txtClientFirstName.Text = NullToValue(AdodcClient.Recordset.Fields("ClientFirstName"))
            txtClientLastName.Text = NullToValue(AdodcClient.Recordset.Fields("ClientLastName"))
            txtClientMiddleNames.Text = NullToValue(AdodcClient.Recordset.Fields("ClientMiddleNames"))
            txtClientDOB.Text = NullToValue(AdodcClient.Recordset.Fields("ClientDOB"))
            txtClientGender.Text = NullToValue(AdodcClient.Recordset.Fields("ClientGender"))
            txtClientAddress.Text = NullToValue(AdodcClient.Recordset.Fields("ClientAddress"))
            txtClientDistrict.Text = NullToValue(AdodcClient.Recordset.Fields("ClientDistrict"))
            txtClientTown.Text = NullToValue(AdodcClient.Recordset.Fields("ClientTown"))
            txtClientCounty.Text = NullToValue(AdodcClient.Recordset.Fields("ClientCounty"))
            txtClientPostcode.Text = NullToValue(AdodcClient.Recordset.Fields("ClientPostcode"))
            txtClientTelephoneNumber.Text = NullToValue(AdodcClient.Recordset.Fields("ClientTelephone#"))
           
            CarePlanNumber = AdodcCarePlan.Recordset.Fields("CarePlan#")
            AdodcCarePlanService.RecordSource = "SELECT [CarePlanService].[CarePlan#], [Service].[ServiceDescription], [Supplier].[SupplierName] FROM Supplier INNER JOIN (Service INNER JOIN CarePlanService ON [Service].[Service#] =[CarePlanService].[Service#]) ON [Supplier].[Supplier#] =[CarePlanService].[Supplier#]; WHERE [CarePlanService].[CarePlan#] = " & CarePlanNumber & ""
            AdodcCarePlanService.Refresh
           
            Exit Sub
        Else
            AdodcClient.Recordset.MoveNext
        End If
    Loop
           
    Height = 6855
   
    'Make Add, Delete, Amend, Search enabled
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdAmend.Enabled = True
    cmdSearch.Enabled = True
    'Clear search data
    txtLastNameSearch.Text = ""
End Sub
Private Sub cmdAbandon_Click()
    'Abandon search and reset buttons
    Height = 6855
    'Make Add, Delete, Amend, Search enabled
    cmdAdd.Enabled = True
    cmdDelete.Enabled = True
    cmdAmend.Enabled = True
    cmdSearch.Enabled = True
    'Clear search data and data grid
    txtLastNameSearch.Text = ""
End Sub
Private Sub cmdClose_Click()
    'Close form
    Unload Me
End Sub
   

Author

Commented:
Correction to previous message...."appears that problem is NOT directly linked to FIND"

Author

Commented:
Increasing points

Author

Commented:
If have identified the problem, but still need a solution.  The problem relates to the SQL in a Access Query.  The SQL is as follows: -

SELECT CarePlan.[CarePlan#], CarePlan.[Client#], CarePlan.[CareManager#], CarePlan.CarePlanExpenditure, Client.ClientFirstName, Client.ClientLastName, Client.ClientPostcode, CareManager.CareManagerFirstName, CareManager.CareManagerLastName
FROM Client INNER JOIN (CareManager INNER JOIN CarePlan ON CareManager.[CareManager#] = CarePlan.[CareManager#]) ON Client.[Client#] = CarePlan.[Client#];

I keep getting "Cannot update 'Client#'; field not updatable" when I want to change the recordet of the Client Table.  How do I amend Access/SQL to get around this problem?  

Author

Commented:
Sorted.  I have fixed the problem using a slightly different SQL.   The problems was that I as trying to update a field within a table which was not updateable.  

SQL used was:-

SELECT CarePlan.[CarePlan#], Client.[Client#], Client.ClientTitle, Client.ClientFirstName, Client.ClientLastName, Client.ClientMiddleNames, Client.ClientAddress, Client.ClientDistrict, Client.ClientTown, Client.ClientCounty, Client.ClientPostcode, Client.[ClientTelephone#], Client.ClientDOB, Client.ClientGender, CareManager.[CareManager#], CareManager.CareManagerFirstName, CareManager.CareManagerLastName, CarePlan.CarePlanExpenditure
FROM Client INNER JOIN (CareManager INNER JOIN CarePlan ON CareManager.[CareManager#] = CarePlan.[CareManager#]) ON Client.[Client#] = CarePlan.[Client#];

As I have fixed it myself could I reduce points to 100 for replier as sign of goodwill.

Commented:
you can request :
PAQ and Point refund

if you really solved this problem yourself...

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ With Point Refund

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

MYLim
EE Cleanup Volunteer
PAQed, with points refunded (190)

Computer101
E-E Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial