Record Source is being Corrupted that drives Form

[CementCarver]

Seems that if you keep adding fields to the form in question, you assume that the record source for the form keeps pace. But every so often, when you check the record source, the query design screen, after you pick the record source from the form's properties, the design of the query is gone, meaning that there is no tables shown in that design window.

But then I tried to copy and paste from a backup copy that I had, the sql statement paste is flawed and causes an error and some field name is incorrect.
This is really odd.

Are there know issues with Acces 2010 and record source updating properly?

CementCarver

[June7]

Don't understand. Adding a field to a form first requires that the field is in the form's RecordSource. How are you adding fields that aren't in the RecordSource?

[CementCarver]

June7, my record sources have been automatically updated and it actually prompts me if I want to update my record source after I've added new fields. But for some reason, in one of my forms, the resource now displays as the title of the form query (that's really odd) and there are no tables displayed in the query design screen.

CementCarver

[Missinglinq]

I don't understand, either! But a common mistake is to

1. Base a Form on a Query
2. Add a Field to the underlying Table
3. Forget to add the Field to the Query by going into Query Design View

Access won't do #3 automatically! You have to do that!

Linq ;0)>

[CementCarver]

And then after the resource gets corrupted, it dumps it into the "unrelated object section' the same problem we saw before when you looked at my db.

CC

[CementCarver]

Missinglinq, I can't even add any fields to the existing record source query, since it's as if I'm building the query from scratch and I need to show tables, add them and then try to reconstruct my whole query all over again.

CC

[June7]

Think I finally figured out how you were adding new fields - dragging fields from the field list. Not getting the prompt asking if I want to update RecordSource, it just does. RecordSource is an SQL statement, not a query object. Using Access 2007 right now.

However, can't replicate the issue.

Possibly a corrupted form. Try building new.

[CementCarver]

So, in the future, what are the proper steps to take when adding new fields to an existing form?
I've:
- created the new fields in the table design
- opened my form in design view
- added my new fields

So, you're saying that I then need to open the record source and add then new field there was well?

CementCarver

[June7]

Guess it depends if the RecordSource is a reference to query object or is an SQL statement.

[CementCarver]

I've gone back from my backup this morning and will add my new fields from there. Yes, June7, that's exactly how I do it.

Weird.....CC

[CementCarver]

I initially built the form from a query, which now Missinglinq says I shoudl NOT have done. I'm confused when you stated that it's a reference to query object or is an SQL statement.... are they not both the same?

CementCarver

[June7]

A query object is an Access object that can be seen in the navigation pane. If the RecordSource is an SQL statement then that is not an object in the navigation pane. The SQL statement can use either a table or a query object as data source.

I tested a form with an SQL statement as RecordSource and the RecordSource updates when I drag more fields.

I just tested creating a form referencing a query object that has only one field in the query. The field list does not show any fields other than the one I included in the query object until I click 'Show all tables'. Now when I drag a field to the form I get the prompt asking to modify the RecordSource. I said yes and now the RecordSource no longer references the query object, it is now an SQL statement drawing fields from the table. The query object is unchanged.

[CementCarver]

Morning June7,

My record source is indeed a SQL statement because it does not appear in the navigation pane. After I've created three new fields, instead this time I went straight to the recordsource of the form and altered the SQL statement by appending the three fields to the statement. Then I went to the add fields to my form and immediately after I went and checked the recordsource, the SQL statement has been corrupted. Please see attached.

CementCarver

[June7]

Yes, that does seem odd but I can't replicate the issue.

Did you try rebuilding the form from scratch? Still get the same issue?

[CementCarver]

Regardless of how I try to enter in the three new fields, I end with the same result in the picture above. Would you prefer that I post my db so you can have a look at it?

CementCarver