Not Able To Add Data Through Form After Making Table Change

[JoeM]

I currently have a database structure that uses a SQL back-end (SQL Server 2008 R2) and an Access front-end (Access 2007).
We currently have a Providers table, and a Providers Form in the Access front-end to enter/edit Provider data.
We need to add a new field to this table (a bit field, allowing nulls - we already have other fields in this table set up the same way).

So I altered the SQL table to add the new field.


I then went into my Access database, and refreshed the table link so that it would now show this new field.
However, now if I go into the Form and try to edit any existing record, I get the following message:

Write Conflict
The record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back if you decide to make changes.

There are three buttons, "Save Record", "Copy to Clipboad", and "Drop Changes". However, the "Save Record" button is disabled.

Any I haven't even tried to add the new field to my Form yet!

It seems I can add new records without issues, I just can't edit existing ones. I confirmed that I was able to do that before I added this new field yto my table.
Does anyone know what might be going on?

Thanks

[RuralGuy]

Try refreshing the RecordSource of your Form. Usually involves selecting a new RecordSource and then put it back to original value.

[JoeM]

Try refreshing the RecordSource of your Form. Usually involves selecting a new RecordSource and then put it back to original value.

Good thought, but unfortunately that did not do the trick.

[RuralGuy]

When you assign a table/query to a Form, Access actually makes a "static query" of the RecordSource. Can you see your "new" field in the Field List of the form?

[RuralGuy]

If you can't see your "new" field in the Field List then the form still has the old definition.

[JoeM]

Yes, I can see the new field. The first time through, I actually added it to the Form. When I got that error, I restored a back-up and started over to see if I could see when the problem occurred. It seemed to be when I refreshed the table link (as the second time around I did that, but tried editing a record before actually adding the field to the Form, though I can see it in the Field List).

[RuralGuy]

If you can see the new field then the RecordSource is refreshed. You should then just be able to add that field to your form. Is you form RecordSource a table or a query?

[RuralGuy]

Old records will not have the new field so there may be some issue there. Is there something in SQL that will add the new field to all of the existing records? It know nothing about SQL.

[JoeM]

If you can see the new field then the RecordSource is refreshed. You should then just be able to add that field to your form. Is you form RecordSource a table or a query?

The record source is a Table, but I was thinking about changing it to a Query (I usually never base my Forms directly on Tables). It is an old database, built about 12 years ago.

Old records will not have the new field so there may be some issue there. Is there something in SQL that will add the new field to all of the existing records? It know nothing about SQL.

Because I added the field to the SQL table and allow Nulls, I would think it should be OK, but maybe not. You may be on to something. Let me try adding a record, and then editing that record. If that works, then I may need to do something to update all the existing records.

I'll check it out tomorrow when I am back in the office and let you know what I find.

[RuralGuy]

Great! Talk to you later.

[JoeM]

OK. So I was still having issues, even on new records. I ran an update require to physically place zero in this field for all new records. And I was still getting messages about conflicts with other users (which isn't the case).

So, I created a query that returns all the records from the table, and change the form to use this new query as the Record Source. Then all the issues went away, and it is now working as it should.

So problem solved! Not sure what it likes about the query that it didn't about the table, but it works now.

[RuralGuy]

Excellent! Thanks for posting back with your success and solution.

[JoeM]

Thanks for marking this as "Solved". It has been so long since I have asked a question (instead of answering them), I forgot to do that.

[JoeM]

I actually started to get those errors again. So I am thinking that there might be some corruption issues going on (the database is pretty old - it is still a "mdb").
So, I opened a new blank "accdb" database, and imported all the objects from the old one.

The new one seems to be working without issues (at least for now). So I am hoping that was the issue - some corruption.

[RuralGuy]

I've got my fingers crossed too! Good Luck.