New Record Added to Table, but Doesn't Showup in Query

[URRich57]

I have a database used to track my personal assignments, created about six years ago using Access 2003 on Windows XP. Recently upgraded to Access 2010 on Windows 7. At some point thereafter, I started having the following issue:

When a new record is created, that record gets added to the table, but doesn't show-up in any query, form, or report until after another new record has been added. The most recently added record cannot be located to view or update, except in the table, until after another new record has been added to the table. Queries, forms, & reports now always lag behind by one record.

None of the queries, forms, or reports tested contain filters. I have several multi-user databases that I also support and none of those users have reported having this problem. This is only happening on my personal database.

Does anyone have any idea what might be causing this issue? I've re-created this database once or twice in the past to resolve other issues, but would like to avoid that route this time around, if possible. Hoping that someone can at least point me to why this might be happening, so that it can possibly be fixed and avoided in the future.

Thanks for any help!

 

[bob fitz]

Does this happen with all the tables in the db.

 

[vbaInet]

...I'll add a few more questions:

1. Are these linked tables?
2. How is a new record created? For example, you run an INSERT SQL command?
3. How many tables have you used to form a Record Source?
4. Is there a GROUP BY clause in the Record Source?
5. Have you setup Relationships in the Relationships Designer?

 

[jdraw]

You could try Application.RefreshDatabaseWindow, but you may have some corruption.
It will be interesting to see responses to questions posed by others.

 

[URRich57]

Hi Bob and vbaInet. In pursuit of answers to your questions, I've located and partially corrected the problem. I found that the newest record in my primary table had no matching record in two related one-to-one foreign tables. A few days ago I deleted the newest primary record and now it seems to be functioning as intended.

I have a Primary table with five Foreign tables. Three are one-to-many relationships used for Combo box lookups and two are one-to-one relationships, intended to generate matching record numbers in the two foreign ID fields (AutoNumber, Incremental), with each new record added to the Primary. It worked as intended from about year 2009 until recently. Relationships were setup in Design mode, but during this recent analysis I discovered that I had no Referential Integrity or Cascading. In addition, I had selected or accepted the default Join Type, Option 1 - (i.e., "Only include Rows where the joined fields from both tables are equal"). I believe this explains why the newest record wasn't showing-up in queries, etc. - (i.e., there was no matching record in the the one-to-one foreign tables until the next new record was added).

I have a suspicion, but not certain why the mechanism suddenly stopped working properly and I don't truly know why the relationships were left without Referential Integrity. I normally select the option that includes all records in the Primary Table, although I never fully understood the appropriate reasons to use one or the other or how Referential Integrity and Cascading respond under the various relationship settings.

I still have some table clean up to perform before I can establish Referential Integrity - (i.e., delete or find a way to repatriate unmatched records in the primary and foreign tables) - and properly set relationships. Still have some learning to do with Relationship building.

Thanks for your questions. I'd been struggling with this for several months, evidently clueless that there was a problem at first, shown by the fact that I also found a number of duplicate entries created during the last several months. I was probably re-entering some assignments a second time thinking that they hadn't been saved on the first attempt. Let me know if you need additional details or direct answers to any of your questions. Been re-reading Microsoft Guide to Table Relationships, but could still use some advice in making the best choices in Relationship design.

Thanks again for your help!

 

[vbaInet]

It worked as intended from about year 2009 until recently. Relationships were setup in Design mode, but during this recent analysis I discovered that I had no Referential Integrity or Cascading. In addition, I had selected or accepted the default Join Type, Option 1 - (i.e., "Only include Rows where the joined fields from both tables are equal"). I believe this explains why the newest record wasn't showing-up in queries, etc. - (i.e., there was no matching record in the the one-to-one foreign tables until the next new record was added).

Sounds like you've found the culprit and if it's been working since 2009 it's also possible that some records have slipped through the net which you just haven't noticed. If there wasn't Referential Integrity setup in the first place and the join was an INNER JOIN it won't show the records, which brings me to my next point.

I normally select the option that includes all records in the Primary Table, although I never fully understood the appropriate reasons to use one or the other or how Referential Integrity and Cascading respond under the various relationship settings.

It's best to have it setup as an INNER JOIN (i.e. matching records) in the relationships designer and if there's any need to have all records from one table, change it in the query. Only do this in the query. Always think most restrictive!
However, if you're always going to need all records from one table then it only makes sense to set it up the outter join (i.e. Left or Right join) in the relationships designer.

Cascade Update simply means if the value of a primary key in the parent table changes, it will update the foreign key values in the child table(s). It's rare that primary keys change anyway because most times these PKs and FKs have no real meaning to the user, it's used to relate records between parent and child. You'll have to decide based on your business logic whether this option is needed or not.

 

[URRich57]

Hello vbaInet,

I apologize for the long delay. Finally had time to cleanup and implement your recommendations. Added Referential Integrity to the one-to-one Relationships without Cascading and set to the default join type. I left Cascading in the Lookups though.

After the first cleanup and implementation, the problem recurred after a few days. I'm not sure why, but maybe I accidentally created a record in one of the tables manually, while poking around in the tables. Or maybe while I experimented with join types in the query to see if I could call up orphaned records and caused the tables to unsync again. Maybe forgot to change both query relationships back to the defaults before attempting to create new records.

Yesterday, I was finally able to cleanup again and re-implement and so far, so good. Looks like this one is resolved. Many thanks vbaInet!

Kind Regards

 

[vbaInet]

After the first cleanup and implementation, the problem recurred after a few days.

It might be worth double checking each relationship to ensure everything is setup right.

Glad we all could help.