Problem with run time error 3164 - field cannot be updated

[BruceUK]

Hi everyone - I'm using Access 365 under Windows 10.



I have a VBA procedure that includes the following code to add a new record to a table:

With rstSolution
.AddNew
!lngProblemNumber = 1
!lngSolutionNumber = lngVBACount0
!lngNumberOfValuesUsed = lngVBASolutionNumberOfProvidedValuesUsed(lngVBACou nt0)
!strConsolidatedComponent = strVBAConsolidatedComponent(lngVBACount0)
.Update
End With

When I run the code, I'm getting a run time 3164 error (Field cannot be updated). When I click the Debug option, the statement !lngSolutionNumber = lngVBACount0 is highlighted.

The explanation given in the help info says that another user or process has locked the relevant table - but I'm the only user! There is no other process running at the time either.

Can someone please explain why this would be happening and suggest a workaround. Is there a way of explicitly unlocking the table within VBA code?

Thanks in advance for any help!

[Minty]

Is rstSolution based on the forms data you are in?
If it is that's your problem.

[BruceUK]

Is rstSolution based on the forms data you are in?
If it is that's your problem.

Thanks but I don't think that applies here. The controls on the form that is used to trigger this procedure are actually all unbound, so the form is not based on an underlying table or query. The rstSolution recordset is sourced by a query based on two related tables, but the VBA procedure derives the data to be written to these tables from the values entered on the form.

[davegri]

Is lngVBACount0 defined as an autonumber in the table? Autonumbers cannot be updated.

[BruceUK]

Is lngVBACount0 defined as an autonumber in the table? Autonumbers cannot be updated.

No, the Solution Number attribute in the Solution table is just defined as a Number. In any case, I'm trying to create a new record in the table, not update an existing one.

The two tables I referred to in my previous answer are tblProblem and tblSolution. These are in a one-to-many relationship (a problem can have more than one solution) and their primary keys are, respectively:

lngProblemNumber
lngProblemNumber, lngSolutionNumber.

The VBA procedure first deletes any existing records in the two tables. Then it writes a new record to tblProblem using a similar .AddNew set of statements, and this is working OK. The error occurs at a later point when it tries to write the first record to tblSolution. As I mentioned, it fails at the assignment statement for the lngSolutionNumber attribute, so it doesn't seem to mind setting the lngProblemNumber attribute. I don't know whether this might be a clue.

Any more ideas?

[Welshgasman]

No, the Solution Number attribute in the Solution table is just defined as a Number. In any case, I'm trying to create a new record in the table, not update an existing one.

Still cannot set an AutoNumber?

[BruceUK]

But it's NOT an Autonumber!

[Micron]

Put a break on that line and when you get there, open the table and look for the lock icon on any record? If you see it, something else or some part of the procedure that we can't see has put a lock on it.
Or compact and zip a db copy and post that here.

[Welshgasman]

But it's NOT an Autonumber!

I never said it was?, I was responding to your 'In any case' logic?

[Gicu]

Just a thought, you could try to change your code and use Currentdb.Execute "Insert Into...." and see if that works.

Cheers,
Vlad

[BruceUK]

Just a thought, you could try to change your code and use Currentdb.Execute "Insert Into...." and see if that works.

Cheers,
Vlad

Hi Vlad, thanks for this idea. I've changed the code as you suggested, but I'm now getting a run-time error 3061: "Too few parameters. Expected 3"

The statement I've used is:

Currentdb.Execute "Insert Into tblSolution (lngProblemNumber, lngSolutionNumber, lngNumberOfValuesUsed, strConsolidatedComponent) VALUES (1, lngVBACount0, lngVBAValues, strVBAComponent)"

Can you see anything wrong with this? I haven't used this Insert Into statement before, so I might have misunderstood something.

I have a nasty feeling that, even if I can get past this error, it might still give the original run time error 3164, but one error at a time!

[davegri]

Try it this way. The debug.print will show the values of the update variables when the syntax is right. Note delimiters around the string variable.

dim sSQL as string
sSQL= "Insert Into tblSolution (lngProblemNumber, lngSolutionNumber, lngNumberOfValuesUsed, strConsolidatedComponent) VALUES (1"  & ", " & lngVBACount0 & ", " &  lngVBAValues & ", '" &  strVBAComponent & "')"
debug.print sSQL
Currentdb.Execute sSQL, dbFailOnError

[BruceUK]

Try it this way. The debug.print will show the values of the update variables when the syntax is right. Note delimiters around the string variable.

dim sSQL as string
sSQL= "Insert Into tblSolution (lngProblemNumber, lngSolutionNumber, lngNumberOfValuesUsed, strConsolidatedComponent) VALUES (1"  & ", " & lngVBACount0 & ", " &  lngVBAValues & ", '" &  strVBAComponent & "')"
debug.print sSQL
Currentdb.Execute sSQL, dbFailOnError

Hi, many thanks - that seems to have worked.

The only thing is that, during execution, a dialog box is appearing very briefly then disappearing before I can see what it is showing!

And I'm still puzzled about why this Insert Into statement works when my original approach based on the recordset and using .AddNew gave the error. Do you have any idea why that would be the case?

[davegri]

And I'm still puzzled about why this Insert Into statement works when my original approach based on the recordset and using .AddNew gave the error. Do you have any idea why that would be the case?

I've run into problems like that when trying to use sql updates behind a BOUND form. I don't often user unbound forms so can't comment on that.

[Gicu]

Could it be the composite primary key? Can you try to add an auto number PK instead and see what happens?
Cheers,