Can't add new record to form/query

[LauraB]

I'm using a database created several years ago with Access 2000, and it's
currently being used at the client site with Access 2003 (yes we'll upgrade
someday when client gets new hardware). The database is being regularly
used, and I have made very few design changes recently.

The current version of our database, doesn't allow new records to be added
(via the "Purchases" form; the Add New Record button with the "*" is
disabled. I also verfied that new records can't be added to the query this
form is based on "Purchases Query". I checked the properties for the
"Purchases Query" and the query hasn't been edited since Jan 2004, but we've
obviously been able to add purchases since Jan 2004.

The recent change I made to the database involved creating a copy of an
unrelated query and editing that copy. I also did a "Compact and Repair",
which I do from time to time. Is there a chance there's corruption causing
the problem? I've further tried to "Compact and Repair" but the problem
persists.

If curious here's the SQL code for the "Purchases Query"; it's pretty basic:
SELECT Purchases.PurchaseID, Purchases.StudentID, Purchases.EmployeeID,
Purchases.PurchaseDate, Students.FirstName, Students.LastName,
Students.PartnerFirstName, Students.PartnerLastName, Purchases.Discount,
Purchases.Memo
FROM Students INNER JOIN Purchases ON Students.StudentID=Purchases.StudentID;

Any thoughts on fixing this problem, or narrowing it down?
--Laura

 

[Jeff Boyce]

Laura

Is the database "split" or are the forms, queries, reports AND tables all
stored in a single .mdb file?

Can you go directly to the query that used to work as the source and run it?
Do you get records? Check to see if that query is "updateable". If not,
take a look in Access HELP on "updateable queries".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[gllincoln]

Hi Laura,

Just some thoughts;

Did you check the form properties, make sure that allow edits, allow
additions, etc. are set true/yes?

Did you originally define a relationship between the student table and the
purchases table? You might check and verify that the relationship hasn't
been lost somehow.

You might try adding DISTINCTROW as in SELECT DISTINCTROW blah blah blah to
a copy of the query (unique records = true in the query properties sheet)
and see what happens.

I am assuming you opened the Purchases query? That is pulling up records so
we are getting a join, etc. and these are local database tables, so altered
permissions on a SQL Server preventing writes aren't a possiblity?

I noticed an inner join, but given the table and field names it sounds as if
the relationship should be a one student to many purchases - and the query
might be better formed as a

SELECT (fields list) FROM Students LEFT JOIN Purchases on Students.StudentID
= Purchases.StudentID;

Seems as if you would not be able to pull up a new student until after the
student had purchased something, by using an inner join. I can think of
scenarios where it might be practical to preload a student or list of
students into the system and have the data pre-populate the form based on
studentid, at time of purchase.

If all else fails - might add a dummy record directly to the student table
and a dummy purchase record to the dummy student - just to make sure the
tables themselves weren't locked in a read only state for some reason.

As a last consideration, I wonder about pranks, a bright student tweaking
something for effect.

Hope this helps...
Gordon

 

[MikeL]

Is the query selecting a linked table on another file server folder where
rights have been changed? We have had similar issues after upgrading our
file servers to Active Directory - where the folder rights have not migrated
properly. Also, you have said that it is 2000 on 2003 machine?? make certain
you get a clean compile.