Access DB stopped accepting new records

[Sam]

Hello,
It's first time I've posted here so bear with me

I don't know MS Access at all, but I've been given a simple Access DB (next
to no VB code) that has for some reason stopped saving new entries. It's not
giving me any error message at all, it's just not saving the record and not
telling me that I've not saved it either!

I open the form and hit the Create new Entry icon and input some data, save
and close the form, but when I reopen it, the entry is not there. However, it
I edit an existing entry and the changes are saved.

The save button seems to be inbuilt and not coded specifically for the
application (I've also tried the "Save Record" from the "Record" menu), again
without success. This seems to have happened suddenly and across all the
forms within the Database.

I thought it may be corrupt so have run a "Compact and Repair Database" from
the "Tools" menu. This reduced the database from about 20MB to about 2MB, so
although this has not fixed the problem it's done some good.

I've found the Security area "User Groups and Permissions" under the "Tools"
menu and it say's "Current User" is "Admin" and Admin seems to have access to
everything.

Subdatasheet Expanded : No
Key Preview : No
Timer Interval : 0
Has Module : No

Data Entry is also set to "No", but changing this to "Yes" seems to filter
out all the existing records and then not save any new records anyway.

Otherwise everything else seems to be set to "Yes" or something else
positive, including "Allow Additions" which caught my eye as a potential
cause.

Oh yes, I also found how to turn filters off, which again didn't help.

Any help would be great, thanks

Sam

 

[Allen Browne]

Hi Sam

This doesn't sound like any particular problem I am aware of, so let's try
some fairly generic problem solving.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect

Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact
Explanation of why you want to do this:
http://allenbrowne.com/bug-03.html

2. If the database has a particular form nominated for Startup, temporarily
change it to None under:
Tools | Startup | Display Form

3. If the database has a macro named AutoExec, temporarily rename it to
something else (e.g. AutoExecX). Then restart the database.

4. Make sure all 3 boxes are checked under:
Tools | Options | Edit/Find

5. Open the Immediate window (Ctrl+G) and enter:
DoCmd.SetWarnings True
After pressing Enter, you get no response it if worked okay.

6. Choose a form that exhibits this problem, and open it in design view.
Make sure the Record Selector property is set to Yes.
Switch to Form view.
Start a new record.
You will see that the icon in the Record Selector (extreme left of form
window, the height of the detail section) changes to a pencil.
Press Shift+Enter to save the new record.
Confirm that the Record Selector icon is no longer a pencil.
Check to see if the new record made it into your table.

If it still does not work, make you have an AutoNumber field in your table.
When you move to the new record in your form, you should see:
(AutoNumber)
in this field.
Once you start the new entry, it should change to a number.
When you save the entry, this number should be in your table.

If you are still stuck, please indicate the version of Access you are using,
and which service pack is applied. (See Help | About.)

The only bug I am aware of that could trigger this applies only to
programmatically closing the form. Details in:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

 

[Sam]

Thanks for your help Allen, but still no joy.

Your first point about unchecking the boxes under Tools... Name AutoCorrect
showed something interesting. I tried to enter another record and it said
that I could not create a duplicate Primary Key. I then looked and found a
table with this record and a few others that I had created as tests (ones
that I thought han't been created). But they still didn't show in the form
when looking through each record.
I then compacted the database as you said.

There was no nominated form for startup.

There were no Macros in the database.

All three boxes under Edit/Find were already checked.

I did a ctrl+g and then entered the text and hit enter successfully.

Now I had a little trouble finding the record selector option, but I found
"Record Selectors" with the extra "s", this was already set to "Yes". I
didn't get the pencil icon at any point though, have an arrow pointing right
in the top corner of the form though.

I'm going to try the auto number thing, although I expect that it may take
me a while, so thought I'd send details of the version (Access 2003 SP2).

Because I've found the records being created in a table, I think it may be
some sort of selection problem with the form, not selecting records from the
correct table maybe??? Not found selection criteria for tables or forms yet
though.

I would be really appreciative if you'd be able to chat through this with me
some more.

Thanks

Sam

My email address is (e-mail address removed).

 

[Allen Browne]

There must have been a problem with the primary key if it was attempting to
create a duplicate. If you open the table in design view, what data type is
the primary key field? Number? Text? AutoNumber? (The compact/repair may
have fixed the problem if it was an autonumber.)

RecordSelectors is right. It is significant that when you edit something on
the form, you do not the pencil icon in the RecordSelector. When the form is
in deisgn view, do the text boxes show the field they are bound to? Or do
they read "Unbound"?

 

[Sam]

The primary key is of type text.

I'm really not sure about this bound or unbound thing. I can't see anything
to suggest either. In design mode, each field has it's own anme inside of it
and then a label with the same name as the field. I've looked through the
properties of the fields and nothing suggests that they are either bound or
unbound.

I didn't understand your comment about the pencil icon, I only have the
arrow pointing right.

I'm not sure how this works, but the form must have some type of selection
criteria that tells it what records to show, I can only imagine that this is
either determined by the primary key or the form is bound to a table. I'm
pretty sure it's not determined by the primary key as otherwise I would
expect it to show everything from all tables, so there must be a link to the
associated table, but I'm not sure where to look for this. My guess is that
the form is showing records from one table and saving newly entered records
to another table. BUt I'm just guessing!

Looking at the relationships under the "Tools" menu, it seems as though
every table is linked to every other table using the primary key. I would
normally expect to see some secondary, composite or other keys linked to the
primary, but again I don't know, I only know what I learnt 10years ago at Uni
about relational databases, which wasn't much.

These tables don't look well normalised either...

Cheers for any more help you might be able to give.

Sam

 

[Sam]

I've found the pencil icon, it appears instead of the arrow pointing right
only once you've started typeing in a field, didn't notice it before, unless
it's only just started appearing.

Sam

 

[Allen Browne]

Hmm. A bit hard to pin-point.

If the text boxes contain the field names when you open the form in design
view, then they must be bound.

With the form open in design view, open the Properties box (View menu.)
First item on the Data tab is RecordSource (if you are looking at the
properties of the Form, not those of a text box or label.) That tells you
what table the form is bound to.

The little pencil icon is an important visual cue. You will also see it to
the left of all the fields when you edit/enter a record into the table
directly.

 

[Larry Linson]

"Allen Browne" wrote

If the text boxes contain the field names
when you open the form in design
view, then they must be bound.

With the form open in design view, open
the Properties box (View menu.)
First item on the Data tab is RecordSource
(if you are looking at the properties of the
Form, not those of a text box or label.) That
tells you what table the form is bound to.

While in the Data tab of the Form's Properties, check to make sure that
"Allow Additions" property is "Yes."

And, in the Property sheet for a Control on the Form, the Control Source
shows whether the Control is bound to a Field in the Record Source, or is
calculated, or, if the Control Source Property is empty, the Control is
un-bound (and can be so, even if the Form has a Record Source).

Larry Linson
Microsoft Access MVP

 

[UpRider]

What's the data source for the form? If it's a query it might have a where
clause that excludes recently added records....

UpRider

 

[David M C]

Open the form in design view. Look at the form properties. Find Control
Source and see what is in there. If it is a query, open the query. It will
only show the records being displayed by the form.

Firstly, check for any WHERE clauses (criteria in the query grid). Secondly,
check how many tables and queries the form query is based on. I have found,
if this gets too complicated, Access will only display the first x number of
records. So, when designing and testing, all looks good, then the record
count increases and the query fails. To fix this you have to simplify the
query. You will probably find lots of fields in the query that are not
needed/used on the form; start by removing these.

Dave

 

[Sam]

It seems that if I enter a new record and press ctrl+enter it blanks the
record. When entering the record it shows the pencil icon to the left of the
form.

Wondering if this gives any clues. Like I said, this database was fine and
then all of a sudden became un-editable.

Any further help would be great.

Thanks

Sam

 

[Sam]

Sorry, I take that first comment back, it doesn't blank the field/record it
just shifts to the next line making it seem blank. I was only filling in the
mandatory field. Either way, the record in the form still doesn't save.

 

[Sam]

Okay, by a "Control" I'm assuming you mean a button of some sort, in which
case, I think I'm safe, there are none. There are onlly fields on the form
and users use the standard "Save" toolbar action to save the record or just
close the form.

See below for the property details, the filter one seems interesting,
particularlly the "Is Null", although I've tired leaving that field Null and
not Null with no different effect.

Record Source = "CS Prospect Form Query - DO NOT EDIT"
Filter = "(([CS Prospect Form Query - DO NOT EDIT].[1st Contact Job Title]
Is Null))"

From what people have said here, I think I'm going to need to investigate
the Query listed in the Record Source. Any help finding my way around that
would be appreciated.

From my first look at Access although it seems a little complicated, I think
it's just a case of knowing where everything is. I'm actually warming to it a
little...

Sam

 

[Sam]

I can't find "Control Source" although "Control Box" is set to "Yes".
Ah, Okay, found "Control Source" on the properties for each field, checked a
few of them (including the primary key) and they all seem to be the same as
the "Name" property above them. Suppose that is a way of saying they should
hold there own values.

"Allow Additions" is set to "Yes" too.

In the design of the Query there seems to be three linked Tables that make
up the query, again each Primary Key seems to be linked to the primary key of
the adjacent table.
Then below there is a grid with the row titles Field, Table, Sort, Show,
Criteria, Or. These look like they are listing every field from each of the
three tables, the Sort row is blank in all of them, the Show row is ticked in
all of them and the Criteria and "Or" is blank, again in all of them.

Does this give any clues? I couldn't find a "Where" clause though.

 

[Rick Brandt]

Okay, by a "Control" I'm assuming you mean a button of some sort, in
which case, I think I'm safe, there are none. There are onlly fields
on the form and users use the standard "Save" toolbar action to save
the record or just close the form.

A control on a form or report is nearly everything you see in design view.
Reports and Forms do not have "fields". What you are referring to as fields
are most likely TextBox controls.

Controls have a ControlSource which indicates "where do I get my data from".
This will either be empty (unbound control), the name of a field in the
underlying RecordSource, or an expression beginning with "=".

If your controls have the names of fields in their ControlSource property
then they are bound controls.

 

[Sam]

Okay, working through this some more I think I may have found the problem. I
knew it would be something silly, it always is!!! I think a few suggestions
were around this too.

It seems that if I enter data in 4 specific fields on the form, the form is
displayed. This suggests some sort of filter. I'm not sure though if it's a
form filter or a query filter as the form is associated with a query (as it's
source).

What I'd like to know now is how to view a list of all filters for a form
and all filters for a query. I see the Apply Filter/Sort and Remove
Filter/Sort under the Tools menu, but have clicked these repeatidly, but it
doesn't seem to be removing whatever filter is there, nor does it give me any
indication of the filter or sort that it's removing.

I think I'm nearly there!!

 

[Larry Linson]

Does this give any clues? I couldn't find a "Where" clause though.

The WHERE clause is part of the SQL Statement generated for an Access Query.
In the toolbar that, by default, displays in Query Builder, the leftmost
icon allows you to display SQL View of the Query. I'd say, however, that
learning SQL is a bit much to ask of you, at this point.

Larry Linson
Microsoft Access MVP

 

[David M C]

Lets start again.

Does this form open with the old data displayed (ie can you navigate through
all the old records)? When you create a new record, close the form, then open
the form, is all the old data still displayed, but the new record doesn't
show up?

When you open the table where the new record should be stored, does it show
up there?

If you answered yes to all of those, the problem is with your query. Open
the query "CS Prospect Form Query - DO NOT EDIT". Do all the old records, but
none of the new records show up here? I suspect the answer wil be yes. If so,
you need to redesign the query to make it easier for Access to interpret. I
have found, for complicated, badly designed queries, Access only returns the
first x number of records.

If all of the above is correct, you need to post the SQL here. Open the
query in design view, then choose View -> SQL view. Copy and paste that into
your reply.

Dave