Form not updating. Table is updated. (1 Viewer)

[Zak14]

When I add new records to the form and close the form, the tables are updated with the new records, but the new records don't show in the form (navigation).

 

[Uncle Gizmo]

Is your form property Data Entry set to "Yes" if so try setting it to "No"

 

[HealthyB1]

G'day,
If your record count is increasing at the bottom left of the screen when you enter a new record then as you say the record is being written to the table.

When you interrogate the table to display the records for the form are you sorting the records in the table by some field say alphabetically prior to displaying them? (i.e. Sorting by Customer name) The record probably is there but not where you expected to see it.

 

[Zak14]

Is your form property Data Entry set to "Yes" if so try setting it to "No"

It's already set to no.

G'day,
If your record count is increasing at the bottom left of the screen when you enter a new record then as you say the record is being written to the table.

When you interrogate the table to display the records for the form are you sorting the records in the table by some field say alphabetically prior to displaying them? (i.e. Sorting by Customer name) The record probably is there but not where you expected to see it.

No, the form record count increases as I add records, then once I close the form and re-open it, the record count resets to the number before I added any new records. It's as if the records didn't save, but they did (in the table).

This is a new problem btw. It never happened before.

 

[Sketchin]

Do you have filter criteria in the underlying query? Do you have an incorrect join in your underlying query? Do you have the same data in the new record as you do in past records that are showing?

 

[JHB]

What is the recordsource for the form, table/query?
If query do you have any criteria in it?
Look if you've a setup a filter in the form.

 

[Zak14]

Do you have filter criteria in the underlying query? Do you have an incorrect join in your underlying query? Do you have the same data in the new record as you do in past records that are showing?

There is no filter criteria in the underlying query.
All relationship joins are good. (primary key joined to foreign keys.
There are similarities in the new records against the old one (obviously) but there aren't any duplicates.

What is the recordsource for the form, table/query?
If query do you have any criteria in it?
Look if you've a setup a filter in the form.

The record source is my main table and the form has 3 tables linked to it.

 

[Uncle Gizmo]

Use the wizard to create a new form ... see if that works, if it does, then go through the two and see what's different.

 

[Zak14]

Use the wizard to create a new form ... see if that works, if it does, then go through the two and see what's different.

I used the form wizard to create a new form, with the same fields from the same tables, and this one has the same problem; only old records show; new records in the table are not shown.

 

[Zak14]

I've discovered the reason for this problem but haven't an idea of how to fix it.

In the form, when none of the fields in a certain table have any fields entered, it doesn't get updated in the table.
Only when all 3 tables are updated for a certain primary key, is when the new record is displayed on the form.

For the sake of example, if the table1 fields in the form are updated for a new record and table2/table3 fields are not touched, the table2/table3 records are not added and the entire user record doesn't show on the re-opening of the form.

How do I make sure that table2/table3 foreign keys are automatically added when a new primary key is added to table1.

Btw, Table2 and Table3 each have a one-to-one relationship with Table 1.

 

[Zak14]

Anybuddy ?

 

[Uncle Gizmo]

Post the db here in *.mdb format.

 

[Zak14]

I can't do that; it contains confidential info, but let me quickly draw a similar database with the relevance and upload that.
Thank you.

 

[Zak14]

Here it is! This example db has the same problem.

The first 3 records in tblDetails show in the form because all tables linked to the form have data in them.
The next 3 records in tblDetails don't show in the form because they are missing data from 1 of the other 2 tables.
I want to allow an automatic entry in both the other tables when any new record is added to tblDetails.

(I uploaded in accdb format. Idk how to save it as mdb format - i tried 'save as'. Please tell me how and I'll be happy to do so)

 

[Uncle Gizmo]

Ah! I see...

Your structure is all wrong.

You have a separate table for Email & Telephone numbers, which is good, but you have it as like a spreadsheet, which is bad.

Read this thread here:-
http://www.access-programmers.co.uk/forums/showthread.php?p=671226
And you will see what I'm driving at.

Also there is a similar thread here which explores moving data from a spreadsheet layout like yours into a database format.
http://msaccesshintsandtips.ning.com/profiles/blogs/excel-in-access-videos

 

[JHB]

Use a Left Join, try the attached database.

 

[Zak14]

Ah! I see...

Your structure is all wrong.

You have a separate table for Email & Telephone numbers, which is good, but you have it as like a spreadsheet, which is bad.

Read this thread here:-
http://www.access-programmers.co.uk/forums/showthread.php?p=671226
And you will see what I'm driving at.

Also there is a similar thread here which explores moving data from a spreadsheet layout like yours into a database format.
http://msaccesshintsandtips.ning.com/profiles/blogs/excel-in-access-videos

This isn't my actual database or anything similar to it in terms of design and table fields. It only resembles similarity in the table structures and whatnot. My actual database is a lot bigger and has much more in it.

Use a Left Join, try the attached database.

How did you do that? What's a left join?

 

[Uncle Gizmo]

This isn't my actual database or anything similar to it in terms of design and table fields. It only resembles similarity in the table structures and whatnot. My actual database is a lot bigger and has much more in it.

If it's bigger in the sense that you have reached the table field limit, so you need to put tel numbers in a separate table, then you may need to reconsider your structure.

Really it's a bit pointless making suggestions and the like if your example does not truly represent your db.

 

[Zak14]

If it's bigger in the sense that you have reached the table field limit, so you need to put tel numbers in a separate table, then you may need to reconsider your structure.

Really it's a bit pointless making suggestions and the like if your example does not truly represent your db.

No, the actual database hasn't got a seperate table for the contact details. And the example db does represent my db, only not in terms of design and field numbers (I don't imagine that matters a whole lot).

 

[JHB]

How did you do that? What's a left join?

Look at the picture, then you'll see what a left join do!