Why are records disappearing when I close after saving?

[Guest]

When I enter a record into Access, save, and then close, it doesn't save the
entry. For example, I currently have 192 records. I enter 3 more records,
saving each one. I close down Access and reopen it, and it only shows 192
Records.

Does anyone know how to fix this issue?

 

[Guest]

Are you receiving a Read Only message at any time?

If you want, send a copy of this database to (e-mail address removed)

I'd love to see what's going on.

 

[Guest]

No, I am not receiving a read only message at any time. Although I would love
to send you the database, it has sensitive information (SSN, test scores),
therefore I cannot.

I didn't realize anything was wrong for quite a while. I opened it as
normal, entered the information, saved, and then continued the process. This
was yesterday. Today, I opened the database and nothing is showing. However,
under tables, it is showing the records. It is only not showing the records
where I enter them.

Any help would be greatly appreciated.

 

[Guest]

No, I did not receive a read only message at any time. Although I would love
to send you the database, unfortunately it contains sensititve information.

If it helps, I have no determined that the records do show up in my tables,
they just do not show up where I enter documents, and where we grade the
information.

Any help would be appreciated.

 

[John Vinson]

When I enter a record into Access, save, and then close, it doesn't save the
entry. For example, I currently have 192 records. I enter 3 more records,
saving each one. I close down Access and reopen it, and it only shows 192
Records.

Does anyone know how to fix this issue?

You'll need to give us quite a bit more information. Access doesn't
ordinarily do this, obviously!

How are you "entering" and "saving" the record? Table datasheet? What
table? or a Form? What action are you taking to save the recrord
(normally no action is needed, just move off the record in the table
datasheet or form, or close the form). Do you have Tools... Options
set to suppress warning messages? Maybe the records aren't being saved
at all and you're just not seeing the error message.

John W. Vinson[MVP]

 

[Ken Snell \(MVP\)]

Sounds as if the form's Data Entry property is set to Yes. This means that
the form is to be used for entering new data only; already entered data will
not be displayed on the form when you open it. Change the Data Entry
property (on Data tab in properties window for the form in design view of
the form) to No.

 

[John Vinson]

I didn't realize anything was wrong for quite a while. I opened it as
normal, entered the information, saved, and then continued the process. This
was yesterday. Today, I opened the database and nothing is showing. However,
under tables, it is showing the records. It is only not showing the records
where I enter them.

Ok... the data is NOT being lost, it's just not visible using your
Form or Query. You say it's not showing "where I enter them". Where is
that? A Form? a Query? If it's a Form, what's that Form's
Recordsource? If a query, could you post the SQL view of the query?

John W. Vinson[MVP]

 

[Guest]

Sorry for not providing enough information. I am still learning Access, and I
am unfamiliar with the language.

I am entering the data into a form. The options are not set to suppress
warning messages and I have already double checked that the Data Entry is set
to No. To save, I am just moving off of the form. I know that it is
partially saving because the information does show up on tables. However, the
committee uses the form view to grade the information.

If anyone has other suggestions, I would appreciate it.

Thanks!

 

[John Vinson]

I am entering the data into a form. The options are not set to suppress
warning messages and I have already double checked that the Data Entry is set
to No. To save, I am just moving off of the form. I know that it is
partially saving because the information does show up on tables. However, the
committee uses the form view to grade the information.

The data exists in the tables - and ONLY IN THE TABLES. Data is not
stored in forms; the form is just a window, a tool for seeing and
editing the data stored in the table.

I gather that your data is in fact being correctly saved, and simply
is not *visible* on the form. The solution is not to fix the saving of
the data - that's not broken! - but the Form.

Please open the Form in design view; view its Properties; and see what
is in the Recordsource property. If it is a Query, or if there is a
SQL statement, please post the SQL here. If the form's Filter property
is anything but blank, that might be the problem - you might be
filtering the form to show only certain records, and the new records
aren't included.

John W. Vinson[MVP]

 

[Guest]

Please open the Form in design view; view its Properties; and see what
is in the Recordsource property. If it is a Query, or if there is a
SQL statement, please post the SQL here.

Hi, John. I'm having the same problem that rs83_bac described. There is no
filter applied to my form, the "Data Entry" option is set to "no," and yet
some records that are definitely stored in the table don't show through the
form. I can't find any pattern to the missing records (i.e., not all the same
client name or same due date). I'm very new to Access and afraid I must have
made some sort of embarrassing newbie mistake.

Here is the SQL statement for my form:
SELECT [Project Tracking].[Project #], [Project Tracking].[Date Project
Received], [Project Tracking].[Received By], [Project Tracking].[Date Project
Due], [Project Tracking].[CCG Drop Date], [Project
Tracking].[Client/Prospect/Department], [Project Tracking].[Name of
Client/Prospect], [Project Tracking].[Project Contact], Contacts.Phone,
[Project Tracking].[Project Lead], [Project Tracking].[CCG Project Owner],
[Project Tracking].[Date Project Assigned], [Project Tracking].[Assigned By],
[Project Tracking].[Project Type], [Client Names].Code, [project
codes].[Project Number], [Project Tracking].[Project File Name], [Project
Tracking].[Connect to], [Project Tracking].[Hard Copies?], [Project
Tracking].[# of Hard Copies], [Project Tracking].[Format of Hard Copies],
[Project Tracking].[CDs?], [Project Tracking].[# of CDs], [Project
Tracking].[Label for CD], [Project Tracking].[Cover for CD], [Project
Tracking].[Electronic PowerPoint File?], [Project Tracking].PDF, [Project
Tracking].[Template?], [Project Tracking].[Special Instructions], [Project
Tracking].[Notes:], [Project Tracking].[Date Project Completed], [Project
Tracking].[CCG Owner Time], [Project Tracking].[Print Sheet]
FROM [project codes] INNER JOIN (Contacts INNER JOIN ([Client Names] INNER
JOIN [Project Tracking] ON [Client Names].[Client Name] = [Project
Tracking].[Name of Client/Prospect]) ON Contacts.Name = [Project
Tracking].[Project Contact]) ON [project codes].Project = [Project
Tracking].[Project Type];

Any suggestions you can offer would be appreciated. Thanks for your time!

 

[John Vinson]

FROM [project codes] INNER JOIN (Contacts INNER JOIN ([Client Names] INNER
JOIN [Project Tracking] ON [Client Names].[Client Name] = [Project
Tracking].[Name of Client/Prospect]) ON Contacts.Name = [Project
Tracking].[Project Contact]) ON [project codes].Project = [Project
Tracking].[Project Type];

With the INNER JOINS you will only see those records with matching
values in ALL FOUR tables. If a record is missing in Contacts, or
Client Names, Project Codes, or (of course) Project Tracking you'll
see *nothing*.

It's rather rare to need a Form based on three joined tables in this
way; often you can get the client name (say) from a Combo Box, or the
like.

One worry: using the field with a Name property of Name is going to
eventually get you into trouble, with documentation if nothing else.
It's a reserved word. And joining a Project table to a Client Name
table *by Client Name* is very risky: names are neither unique nor
stable! What if your client Jane Robertson comes in to the office one
day with a nice new ring and introduces herself as Mrs. Jane Wallace?
Or, what if you have three clients all named Fred Brown? (I know three
gentlemen of that name here in little Parma).

John W. Vinson[MVP]

 

[Guest]

Thanks for taking the time to look over my post, John. I appreciate it.
Though, I'm afraid it's only left me with more questions.

With the INNER JOINS you will only see those records with matching
values in ALL FOUR tables. If a record is missing in Contacts, or
Client Names, Project Codes, or (of course) Project Tracking you'll
see *nothing*.

Unfortunately, I don't know anything about INNER JOINS (I am very new to
Access). I didn't set up the form through the SQL and never came across the
term INNER JOINS until I looked up the SQL to post last night.

That said, I believe the form is set up in a way that you cannot have
missing records in any of the tables. The form in question is for adding to
the Project Tracking table, and all fields connected to other tables are
accessed through combo boxes (or pulled in automatically, like when you
select a contact name from the combo box, the phone field fills in from the
Contacts table). None of these linked fields can be left blank -- for
example, if you're adding a project into the database via the Project
Tracking form for a client whose name isn't already in the system, the name
has to be added to the Client Names table before you can continue. So if the
INNER JOINS are causing the records to not show through the form, it's for
some other reason than records being missing from tables. Unless I've
misunderstood your meaning.

joining a Project table to a Client Name
table *by Client Name* is very risky: names are neither unique nor
stable!

In this particular case, the client names are unique, though I do understand
your meaning. I'm not sure how I'd set up the form if the client names
weren't unique, but I'll cross that bridge if I ever have to set up another
database.

 

[John Vinson]

Thanks for taking the time to look over my post, John. I appreciate it.
Though, I'm afraid it's only left me with more questions.



Unfortunately, I don't know anything about INNER JOINS (I am very new to
Access). I didn't set up the form through the SQL and never came across the
term INNER JOINS until I looked up the SQL to post last night.

The inner join is the default. It means you must have a record in both
tables to see the data in either table. If you use the query grid you
can click on the join line between the tables and view its properties
- you get the default option "show all data with records in both
tables", "show all records in <TableA> and matching records in
<TableB>", or "show all records in <TableB> and matching records in

That said, I believe the form is set up in a way that you cannot have
missing records in any of the tables. The form in question is for adding to
the Project Tracking table, and all fields connected to other tables are
accessed through combo boxes (or pulled in automatically, like when you
select a contact name from the combo box, the phone field fills in from the
Contacts table).

If they are displayed on the form with combo boxes, it is NOT
NECESSARY to include them in the form's Query. That's the nice thing
about a combo - it can store a value from one table while displaying a
value from another table.

None of these linked fields can be left blank -- for
example, if you're adding a project into the database via the Project
Tracking form for a client whose name isn't already in the system, the name
has to be added to the Client Names table before you can continue. So if the
INNER JOINS are causing the records to not show through the form, it's for
some other reason than records being missing from tables. Unless I've
misunderstood your meaning.

I'll have to look that over. How do you require that they be added?
Required property on the field, code in the Form's BeforeUpdate event,
or what?

In this particular case, the client names are unique, though I do understand
your meaning. I'm not sure how I'd set up the form if the client names
weren't unique, but I'll cross that bridge if I ever have to set up another
database.

See the Northwind sample database. It's *very* routine to store a
numeric, concealed ID in a table, and use a combo box to display a
name.

Doublecheck the Control Source properties of all your combo boxes: if
you have a Client Name field in both the Client Names table and the
Project Tracking table, it's all too easy to bind the combo to the
wrong one, leaving the field blank or (worse) overwriting an existing
name with an incorrect one.

John W. Vinson[MVP]

 

[Guest]

If they are displayed on the form with combo boxes, it is NOT
NECESSARY to include them in the form's Query. That's the nice thing
about a combo - it can store a value from one table while displaying a
value from another table.

I don't have a query for the form, exactly. I pulled that SQL info from the
Record Source field of the form's properties (which looks like an
automatically generated query). Could having the form not be based on a query
be causing the problem? This is the first database I've ever set up, so I'm
afraid I may have made some dreadful mistakes along the line.

I'll have to look that over. How do you require that they be added?
Required property on the field, code in the Form's BeforeUpdate event,
or what?

I really don't know. The form is set up so you use a combo list generated
from the Contacts table to get a Project Contact's name entered in the
Project Tracking table. If you try to exit the record with the Project
Contact blank or with something that doesn't match an existing item in the
Contacts table, you get this error message, "The Microsoft Jet database
engine cannot find a record in the table 'Contacts' with key matching
field(s) 'Project Contact'." The only way to move on from this is to select
one of the names in the combo box, which is what I need the users to do
anyway, so I wasn't bothered by the error message coming up.

 

[John Vinson]

I don't have a query for the form, exactly. I pulled that SQL info from the
Record Source field of the form's properties (which looks like an
automatically generated query).

That SQL *IS* the Form's Query. It's not stored as a separate query in
the Queries window, but it doesn't need to be.

Could having the form not be based on a query
be causing the problem?

No. As I say, it's based on a query - but it's just a local query, not
a stored one. This is a perfectly acceptable and reasonable method.


Try this: create a new Query in the Queries window; don't pick any
tables. Copy and paste the SQL from the Recordsource (what you posted
to the newsgroup) into the SQL window of this query (replacing the
Select; which should be the only contents). Open this query in
datasheet view (with the datasheet icon on the left of the toolbar).
Do you see any records? Do you see ALL the records you expect?

Now try removing the dependent tables one by one. Do you suddenly see
more records when one of the tables is removed?

I'm not sure why the wizard joined all these tables on creating the
form. I'm GUESSING - not certain, since I cannot see the form - that
the joined tables are not necessary, and that all you need is the
single "master" table, with Combo Boxes to show a single field from
the other tables. But again... I'm not familiar with the inner
workings of your database and I may be wrong; make a backup before you
try deleting tables from the query!

John W. Vinson[MVP]

 

[Guest]

Try this: create a new Query in the Queries window; don't pick any

tables. Copy and paste the SQL from the Recordsource (what you posted
to the newsgroup) into the SQL window of this query (replacing the
Select; which should be the only contents). Open this query in
datasheet view (with the datasheet icon on the left of the toolbar).
Do you see any records? Do you see ALL the records you expect?

Now try removing the dependent tables one by one. Do you suddenly see
more records when one of the tables is removed?

Deleting the dependent tables did cause all the records to appear. However,
the fields that were looked up according to my combo boxes based on the
dependent tables now just say "#Name?" Before, if you selected "John Doe" as
a contact, his phone number would show up in the "Phone" field rather than
"#Name?" being there. I take it there's a way to get this accomplished
without the joined tables. I've been flying by the seat of my pants to set up
this database, I'm sure it's riddled with Bad Things.

I really appreciate all the help you've offered on this.

 

[Guest]

Try this: create a new Query in the Queries window; don't pick any

tables. Copy and paste the SQL from the Recordsource (what you posted
to the newsgroup) into the SQL window of this query (replacing the
Select; which should be the only contents). Open this query in
datasheet view (with the datasheet icon on the left of the toolbar).
Do you see any records? Do you see ALL the records you expect?

Now try removing the dependent tables one by one. Do you suddenly see
more records when one of the tables is removed?

Deleting the dependent tables did cause all the records to appear. However,
the fields that were looked up according to my combo boxes that are based on
the dependent tables now just say "#Name?" Before, if you selected "John
Doe" as a contact, his phone number would show up in the "Phone" field rather
than "#Name?" being there. I take it there's a way to get this accomplished
without the joined tables. I've been flying by the seat of my pants to set up
this database, I'm sure it's riddled with Bad Things.

I really appreciate all the help you've offered on this.

 

[John Vinson]

Deleting the dependent tables did cause all the records to appear. However,
the fields that were looked up according to my combo boxes that are based on
the dependent tables now just say "#Name?" Before, if you selected "John
Doe" as a contact, his phone number would show up in the "Phone" field rather
than "#Name?" being there. I take it there's a way to get this accomplished
without the joined tables.

The simplest way is to include all the fields that you want to see in
the Combo Box's RowSource query, and set the Combo's ColumnCount to
the number of fields. E.g. if you have a table Contacts with fields
ContactID, LastName, FirstName, Phone, Address and City, you could use
a query

SELECT ContactID, LastName & ", " & FirstName AS ContactName, Phone,
Address, City
FROM Contacts
ORDER BY LastName, FirstName;

as the RowSource of the combo (cboContact I'll call it). Set the
Combo's ColumnCount to 6, and its ColumnWidths property to

0;1.25;.75;0;0;0

to display the name when the combo's not dropped down, and the name
and phone number when it is.

Then put three textboxes on the form with Control Sources

=cboContact.Column(3)
=cboContact.Column(4)
=cboContact.Column(5)

to display the phone, address and city respectively (the Column
property is zero based).

John W. Vinson[MVP]

 

[Guest]

Try this: create a new Query in the Queries window; don't pick any

tables. Copy and paste the SQL from the Recordsource (what you posted
to the newsgroup) into the SQL window of this query (replacing the
Select; which should be the only contents). Open this query in
datasheet view (with the datasheet icon on the left of the toolbar).
Do you see any records? Do you see ALL the records you expect?

Now try removing the dependent tables one by one. Do you suddenly see
more records when one of the tables is removed?

Deleting the dependent tables did cause all the records to appear. However,
the fields that were looked up according to my combo boxes based on the
dependent tables now just say "#Name?" Before, if you selected "John Doe" as
a contact, his phone number would show up in the "Phone" field rather than
"#Name?" being there. I take it there's a way to get this accomplished
without the joined tables. I've been flying by the seat of my pants to set up
this database, I'm sure it's riddled with Bad Things.

I really appreciate all the help you've offered on this.

 

[Guest]

John, thank you so much for your help. That fixed the problem. You are a
prince among men, a knight in shining armor, or any other cliche you'd prefer.

And sorry about the multiple posts earlier. Long story not worth going into
really -- though, I suppose it could be summed up as "I am an idiot."

Thanks again for your patience, time, and good advice.