Adding a new field to an existing table (long but detailed description of problem)

[munroe47]

I have a database consisting of only 2 tables "Catalog" and "Inventory". Each table contains between 3000 and 4000 records. The database uses multiple forms, queries, macros and reports. All were created using the built-in Access features; I did not write any code myself (don't know how yet). Some forms use the tables as record sources; others use queries as record sources.

I first made a backup then added a field to the "Catalog" table. It is a "currency" field, and at this point, contains no data in any record. I then added the new field to a form whose data source is the edited table. I had no problem. I then added the new field to a query whose data source is this single edited table and had no problem. But, when I tried to add the new field to a second form whose record source is the edited QUERY, I could not even see the field list when using the "Add existing fields" tab in query design. I got the following message instead: "the current record source may be invalid (for example it may contain an invalid join expression) or there was some other problem." It gave me this link: "Click to edit record source", but when I click it I get the following: [Command or action " isn't available now]. The double quote between the words "action" and "isn't" is not a typo, it is part of their message; the brackets are mine.

I tried everything again and noticed that when I brought up the field list in query design (using the "show table" tab), it gave me the choice of Catalog or Inventory in the dialog box, but when I chose Catalog, it brought up the field list under the name of "Catalog_1". Assuming that this was the problem. I checked to see if a new table, "Catalog_1" had been created when I edited "Catalog", but there were only the original 2 tables with their original names, and the "Catalog" table had been successfully edited to contain my newly added field.

I then made a second copy of the backup, and WITHOUT EDITING THE TABLE attempted to add a field to the query. I could, but noticed that the "show table" tab brings up "Catalog_1" even though the table had not been edited. I also found that if I attempt to add a field to the form WITHOUT EDITING THE TABLE OR THE QUERY, there is no problem.

To summarize: The "show table" tab in query design brings up "Catalog_1" when "Catalog" is chosen whether or not the table was edited. If the new field is added to the query, I am unable to even see the field list in form design for the form based on that query.

I was tempted to delete the form and the query and re-create them both, but that specific form is used to view and/or edit data and is opened by more than one macro, each of which is executed by separate command buttons on various other forms. Therefore, I did not do this because I was afraid of creating a whole new set of issues.

I am confused because I have been using and improving this database for years, and I have successfully edited tables, queries, and forms without this problem arising. I always make a backup before making any changes and keep that backup until I have successfully tested everything after making a change, so I am OK at this point. But, I do want to add that new field and use it in the aforementioned form, but I can't figure out why it won't work this time!!

I should add that about two weeks ago, I had some issues when attempting to edit a macro and started getting multiple error messages that I could not seem to fix. This macro was one that only opens a query to select certain records then opens a form to display them. It doesn't manipulate, append, or change any data in the tables. At any rate, I resolved the problem by "retreating" to my most recent backup, and tested it to be sure that all functions worked. The only problem with the backup was that it did not contain the most current records. Rather than try to "merge" the two databases, which would have been a new thing for me and one had a corrupted macro anyway, I copied all the records directly from the tables of the current database and pasted them directly into the tables of the backup. I have been using this version for several weeks now and have thoroughly tested every function; it works just fine, and I have access (no pun) to all my records, including the copied and pasted ones. I don't know if this inability to add a field to a form could be related to my copy and paste, but it doesn't seem so since the problem only arises when I try to edit the table or query.

I just realized that my profile shows Windows XP and Access 2002. I updated it to Windows 8 and Access 2013, But it didn't change on the thread.

[June7]

If you want to provide the modified database for analysis, follow instructions at bottom of my post. However, I can only view with Access 2010 so if there are Access 2013 features, I won't be able to open. But maybe someone else will.