asked on

rECORD SET NOT UPDATEABLE

HI

I continue to run into this issue where I am unable to add new records via the FE version of my database

1.. I have tried directly via the query and no luck.
2. I have created new versions of the FE and BE versions and no luck.
3. Something is causing this issue and I can not find out what Is the problem.
4, This database has worked fine and then the last few days I keep running into this problem. I am thinking one of the users is doing something that is causing this problem.
5. I do see the following in MsysCompact error, But not sure what this even means,

I have zipped the Fe and Be if someone could look into this.

I see ErrorCode ErrorDescription ErrorRecid ErrorTable
-1504 You tried to assign the Null value to a variable that is not a Variant data type. tblClaims
DMC_Backup_Backup_be.zip

Michael Vasilevsky

What do you mean you cannot add records?

I was able to connect to the backend, compact and repair without error, and add records directly in the Calls and Employees table. I'm not familiar with how you use your frontend of course.

A couple things come to mind immediately:
- make sure you still have read/write access to the backend location
- delete "Name AutoCorrect Save Failures" and "MS Compact and report errors" tables in the frontend and back

If you have more specifics I can look further.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"You tried to assign the Null value to a variable that is not a Variant data type. "
If ... this was in VBA ... this usually happens if you

Dim X as String

then later try to set X=Null '(or something that results in Null

Is this error happening in code ?

John Tsioumpris

I think we need a mini tutorial of how you end up to this error...

Michael Dean

ASKER

OPEN THE FRONT END DB and the CASES FORM appears, Select to"NEW CASE" command button. Or select one of the records under FileNo and you will see the message I am referring

John Tsioumpris

New case is blank but selecting one of the cases runs just fine...

John Tsioumpris

The LEFT Join is trying to join empty values

tblClaims.Reviewer = Provider.ProviderID

Open in new window

Michael Dean

ASKER

John I have not changed that query, why would it stop working. Also if you select the one of the existing cases, it still says "recordse is not updated

very weird>

John Tsioumpris

Well in further investigation i see a lot of null Values on fields that you use to perform joins and also some bizzare fields conflicts...e.g. when i insert fake values to cover the null cases still it had issues...for example the Reviewer field from Providers was causing recordset locking...removing it all were fine....it seems it requires some more work...
I am under the impressions that there was some kind of small bulk insert that went wrong...if i recall correctly you had around 30 nulls on each column that you join...
If you drop the query and rewrite it from scratch i am sure it will work fine...i did it as a test (minus the infamous Reviewer) and the recordset was updateable.

Michael Dean

ASKER

so you rewrote the query without the (infamous Reviewer) Not sure what you mean.

Can you paste the test Sql you wrote or send the FE back to me so I can copy it?

Michael Dean

ASKER

any help here?

John Tsioumpris

sorry i forgot you...hopefuly tomorrow i will take a look and sort this out...as i said take a small subset of your data with all the values filled properly and your query should work fine (the form's recordsource)...if not remove fields until it works and you will find the problem

Michael Vasilevsky

I still don't get any error on new case or on opening about two dozen random existing cases using the hyperlink under FileNo.

I don't think nulls are your issue. It appears as though some lookups are null but there is no table with null primary keys as far as I can tell.

I do however see some records that appear to be corrupted. See tblClaims, InvoiceIDs 3222, 3255, and 8352:

Deleting and recreating these records might help.

Michael Dean

ASKER

YEs I deleted those corrupted records and still Recordset not updatable!! UUUUUGH!!!!!!!!!!!!!!!!!!!!!!

Michael Dean

ASKER

in fact I have deleted all records and tried to start fresh
but still the same error

Michael Vasilevsky

Did you try connecting to a copy of the backend on your local harddrive?

Michael Dean

ASKER

Yes I am working with my local harddrive

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael Dean

ASKER

hi so i unzipped what yu sent , I still get the same error tho.

Did you want me to do anything after i unzipped?

Michael Vasilevsky

What specific steps do you perform to get the error? Can we get a screenshot?

Both the version you uploaded and the version John uploaded seem to be working fine for me - I don't get any error.

Have you tried on another machine?

John Tsioumpris

Maybe you are failing on another part...if you check before/after your "Cases" forms is not updateable at first ( you can't add a record) and then is updateable ...i will check it tomorrow...

Michael Dean

ASKER

are you on the Cases Screen and selecting one of the rows (hyperlink) and does the Case Details screen pop up and allow updates?