Record update issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have encounter a problem I have never come across before.

I added a new table to an existing database. The new table is in a
one-to-one relationship with an existing table.

The existing table has a primary key and I set the Index property on my key
field in the new table to Yes (no duplicates). All other settings are
identical to the primary key field in the existing table.

I had an existing query built on the existing table, so I modified the query
by showing the new table and dragging its fields down onto the grid.

When I open my form all is well. I can edit records (except for the newly
added fields from the new table) and add new records (including the the new
fields added to the source query).

The problem arises when I attempt to edit one of the four new fields added
to the form. Each of them are checkboxes. If I attempt any edit of the four
checkboxes I receive an error message which reads "Could not update;
currently locked."

I could understand this if this database were shared on a network, but I am
the only one who has ever used it.

If I modify the Record Locks property of the form to "no locks" then I am
prompted to manually save each record as though someone else is currently
editing the record also.

I am just baffled. Any help is greatly appreciated.
 
I have encounter a problem I have never come across before.

I added a new table to an existing database. The new table is in a
one-to-one relationship with an existing table.
Click to expand...

That's a red flag right there. One to one relationships are VERY rare.
Are you Subclassing, or using this table to implement field level
security? If not, why do you need a one to one, rather than just
adding the new table's fields to the existing table?
The existing table has a primary key and I set the Index property on my key
field in the new table to Yes (no duplicates). All other settings are
identical to the primary key field in the existing table.

I had an existing query built on the existing table, so I modified the query
by showing the new table and dragging its fields down onto the grid.
Click to expand...

Did you put in a Join line linking the primary keys of the two tables?
That's essential...
When I open my form all is well. I can edit records (except for the newly
added fields from the new table) and add new records (including the the new
fields added to the source query).
Click to expand...

If you don't have a join line, I'm guessing that you'll see each
record in the original table being displayed repeatedly, as many times
as there are records in the new table... right?
The problem arises when I attempt to edit one of the four new fields added
to the form. Each of them are checkboxes. If I attempt any edit of the four
checkboxes I receive an error message which reads "Could not update;
currently locked."
Click to expand...

What's the SQL of the Form's Recordsource property?


John W. Vinson[MVP]
 
John:

Thanks for the response.

Yes I joined the tables and confirmed the relationship type as one to one.

I wouldn't typically use a one to one (as I agree, the purpose if typically
for security), but the four fields are for storing marketing settings and I
felt that did not fit into a Clients table. However, I am approaching a
change as my frustration grows.

My query result is definitely not a cross product.

One thing I forgot to mention in the email is that I can make the
modifications in query view, but when I attempt the same change in the form I
get the message mentioned.


SELECT tblClients.Lname, tblClients.Clientno, [Title] & " " & [Fname] & " "
& [Spouse] & " " & [Lname] & " " & [Business] AS cFullClient,
tblClients.Business, tblClients.BusinessAddress, tblClients.Address1,
tblClients.Address2, tblClients.City, tblClients.State, tblClients.Zip,
tblClients.Fname, tblClients.Title, tblClients.Phone1, tblClients.Ext1,
tblClients.Phone2, tblClients.Ext2, tblClients.Fax, tblClients.Hphone,
tblClients.Pager, tblClients.Cellular, tblClients.Bemail, tblClients.Hemail,
tblClients.Notes, tblClients.Date, tblClients.ShortList, tblClients.Holiday,
tblClients.Bday, tblClients.Spouse, tblMarketingSettings.MarketToMe,
tblMarketingSettings.MarketToMaybe, tblMarketingSettings.FixAddress,
tblMarketingSettings.UseSpouseName, tblMarketingSettings.Clientno
FROM tblClients INNER JOIN tblMarketingSettings ON tblClients.Clientno =
tblMarketingSettings.Clientno
ORDER BY tblClients.Lname, tblClients.Clientno;

Thanks again,

Seth
 
One thing I forgot to mention in the email is that I can make the
modifications in query view, but when I attempt the same change in the form I
get the message mentioned.
Click to expand...

VERY odd! The query you posted was indeed the Recordsource of the
form? There are textboxes or other controls bound to these new fields,
and the fields are updateable in datasheet view but not on the form?

I would suggest (if you haven't done so) unchecking Name Autocorrect
in Tools... Options... General (it's buggy and causes many strange
errors); if need be, delete all these controls, and recreate them on
the form after Compacting the database.

John W. Vinson[MVP]
 
John:

I had the AutoCorrect feature deselected from the get go. I agree, it is a
nasty devil inside Access.

I tried the other things to no end. I have succome to defeat and added the
fields to my Client table and now they work flawlessly.

Thanks for your effort.

Seth
 
Back
Top