entries updating in tables but disappearing in form

[Guest]

I have a database with a form that updates into my tables. I enter
information into the form and save it it and it updates to the tables. My
problem is that when I open the form to view or add more information the
previous entries disappear from the form. If I try to reenter the
information it tells me that it will not update because I am creating a
duplicate value. When I search for the information in the table it says that
it is not there. I have tried to delete the information from the tables and
then re-enter the information but the same thing happens. Any ideas how to
fix this would be greatly appreciated.

 

[Allen Browne]

Is your form's Data Entry property set to Yes? That places the form in a
mode where no existing entries are shown (i.e. it is set up just for
entering new records.)

If not, is the form based on a query? Filtered? Or is this a subform, where
the LinkChildFields/LinkMasterFields could limit entries, or where the
foreign key could be null?

 

[Guest]

The data entry is set to "NO" and the form was created using the Form Wizard.
No queries were used, it is not filtered and it is not a subform.

 

[Allen Browne]

Post what is in the the RecordSource property of your form.

 

[Guest]

SELECT DISTINCTROW [Documents].[Document Number], [Documents].[Document
Type], [Documents].[Document Name], [Drafts].[Draft Initiated],
[Drafts].[Draft Complete], [Drafts].[Comment Cycle1], [Drafts].[Comment
Cycle2], [Pre-Approvals].[Pre-Approval Init], [Pre-Approvals].[Pre-Approval
Complete], [Pre-Approvals].[Comment Cycle1], [Pre-Approvals].[Comment
Cycle2], [Pre-Approvals].[Comment Cycle3], [Pre-Approvals].[Comment Cycle4],
[Executions].[Execution Initiated], [Executions].[Execution Complete],
[Executions].[Comment Cycle1], [Executions].[Comment Cycle2], [Final
Approvals].[Final Approval Initiated], [Final Approvals].[Final Approval
Completed], [Final Approvals].[Comment Cycle1] FROM (((([Documents] INNER
JOIN [Drafts] ON [Documents].[Document Number] =[Drafts].[Document Number])
INNER JOIN [Pre-Approvals] ON [Drafts].[Document Number]
=[Pre-Approvals].[Document Number]) INNER JOIN [Executions] ON
[Pre-Approvals].[Document Number] =[Executions].[Document Number]) INNER JOIN
[Final Approvals] ON [Executions].[Document Number] =[Final
Approvals].[Document Number]);

 

[Allen Browne]

The SQL statement in the RecordSource of your form is a query that uses
INNER JOINs on 5 tables:
- Documents
- Drafts
- Pre-Approvals
- Executions
- Final Approvals
Unless a record has a match in *every* one of the 5 tables, the INNER JOIN
will prevent it from appearing in your form.

You may be able to work around the problem by dropping tables, and/or using
outer joins instead.

To experiment with the issue:
1. Click the Build button (...) beside the RecordSource property.
Access invokes the query builder.

2. Double-click the lines between the tables
Access offers 3 choices. The first is the inner join, so choose one of the
others.

You will probably run into issues with "ambiguous outer joins" unless you
drop some tables, at least to start with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SELECT DISTINCTROW [Documents].[Document Number], [Documents].[Document
Type], [Documents].[Document Name], [Drafts].[Draft Initiated],
[Drafts].[Draft Complete], [Drafts].[Comment Cycle1], [Drafts].[Comment
Cycle2], [Pre-Approvals].[Pre-Approval Init],
[Pre-Approvals].[Pre-Approval
Complete], [Pre-Approvals].[Comment Cycle1], [Pre-Approvals].[Comment
Cycle2], [Pre-Approvals].[Comment Cycle3], [Pre-Approvals].[Comment
Cycle4],
[Executions].[Execution Initiated], [Executions].[Execution Complete],
[Executions].[Comment Cycle1], [Executions].[Comment Cycle2], [Final
Approvals].[Final Approval Initiated], [Final Approvals].[Final Approval
Completed], [Final Approvals].[Comment Cycle1] FROM (((([Documents] INNER
JOIN [Drafts] ON [Documents].[Document Number] =[Drafts].[Document
Number])
INNER JOIN [Pre-Approvals] ON [Drafts].[Document Number]
=[Pre-Approvals].[Document Number]) INNER JOIN [Executions] ON
[Pre-Approvals].[Document Number] =[Executions].[Document Number]) INNER
JOIN
[Final Approvals] ON [Executions].[Document Number] =[Final
Approvals].[Document Number]);

Post what is in the the RecordSource property of your form.

 

[Guest]

Thank you, Thank you, Thank you. This seems to have worked.

The SQL statement in the RecordSource of your form is a query that uses
INNER JOINs on 5 tables:
- Documents
- Drafts
- Pre-Approvals
- Executions
- Final Approvals
Unless a record has a match in *every* one of the 5 tables, the INNER JOIN
will prevent it from appearing in your form.

You may be able to work around the problem by dropping tables, and/or using
outer joins instead.

To experiment with the issue:
1. Click the Build button (...) beside the RecordSource property.
Access invokes the query builder.

2. Double-click the lines between the tables
Access offers 3 choices. The first is the inner join, so choose one of the
others.

You will probably run into issues with "ambiguous outer joins" unless you
drop some tables, at least to start with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SELECT DISTINCTROW [Documents].[Document Number], [Documents].[Document
Type], [Documents].[Document Name], [Drafts].[Draft Initiated],
[Drafts].[Draft Complete], [Drafts].[Comment Cycle1], [Drafts].[Comment
Cycle2], [Pre-Approvals].[Pre-Approval Init],
[Pre-Approvals].[Pre-Approval
Complete], [Pre-Approvals].[Comment Cycle1], [Pre-Approvals].[Comment
Cycle2], [Pre-Approvals].[Comment Cycle3], [Pre-Approvals].[Comment
Cycle4],
[Executions].[Execution Initiated], [Executions].[Execution Complete],
[Executions].[Comment Cycle1], [Executions].[Comment Cycle2], [Final
Approvals].[Final Approval Initiated], [Final Approvals].[Final Approval
Completed], [Final Approvals].[Comment Cycle1] FROM (((([Documents] INNER
JOIN [Drafts] ON [Documents].[Document Number] =[Drafts].[Document
Number])
INNER JOIN [Pre-Approvals] ON [Drafts].[Document Number]
=[Pre-Approvals].[Document Number]) INNER JOIN [Executions] ON
[Pre-Approvals].[Document Number] =[Executions].[Document Number]) INNER
JOIN
[Final Approvals] ON [Executions].[Document Number] =[Final
Approvals].[Document Number]);

Post what is in the the RecordSource property of your form.

The data entry is set to "NO" and the form was created using the Form
Wizard.
No queries were used, it is not filtered and it is not a subform.

:

Is your form's Data Entry property set to Yes? That places the form in
a
mode where no existing entries are shown (i.e. it is set up just for
entering new records.)

If not, is the form based on a query? Filtered? Or is this a subform,
where
the LinkChildFields/LinkMasterFields could limit entries, or where the
foreign key could be null?

I have a database with a form that updates into my tables. I enter
information into the form and save it it and it updates to the
tables.
My
problem is that when I open the form to view or add more information
the
previous entries disappear from the form. If I try to reenter the
information it tells me that it will not update because I am
creating a
duplicate value. When I search for the information in the table it
says
that
it is not there. I have tried to delete the information from the
tables
and
then re-enter the information but the same thing happens. Any ideas
how
to
fix this would be greatly appreciated.