Split Database and Form Update issue

[cunger28]

Good afternoon,

I have a database that was recently split. The front end contains my form
and code, while the back end has the tables obviously. The form has a series
of what should be updateable fields. Things like "YES/NO" combo boxes and
comments fields. When the form is open and I try to make changes to what
should be an updateable field, no action occurs. The form locks and no
selections can be made. What's weird to me, is when I click on the linked
table within the front end, I can update records in the linked table itself
and then refresh my form. The changes then appear. Why will my form not
allow me to make those changes or updates?

Thanks in advance!

 

[Mr B]

Chris,

Is your form bound to the table only or to an sql statement that is pulling
data from more that one table? If the latter is true, you may have a
situation where the recordset from the query is not updatable due to
relational constraints.

By the way, I am located just south of you in Whitehouse, If you need to
talk, feel free to contact me.

 

[Tony Toews [MVP]]

Is your form bound to the table only or to an sql statement that is pulling
data from more that one table? If the latter is true, you may have a
situation where the recordset from the query is not updatable due to
relational constraints.

Relational constraint?

There are many reasons for non updatable queries but I'm not familiar
with relational constraints as a reason. And I'm not even sure what
that term means in this context.

Some of these reasons include a query with totals logic or union
query.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I have a database that was recently split. The front end contains my form
and code, while the back end has the tables obviously. The form has a series
of what should be updateable fields. Things like "YES/NO" combo boxes and
comments fields. When the form is open and I try to make changes to what
should be an updateable field, no action occurs. The form locks and no
selections can be made. What's weird to me, is when I click on the linked
table within the front end, I can update records in the linked table itself
and then refresh my form. The changes then appear. Why will my form not
allow me to make those changes or updates?

Please post the contents of the forms record source. If it's a query
then post the query. Also if the record source is a query try
changing the query Record Set property to "Dynaset (Inconsistent
Updates)" Sometimes that'll work. If it doesn't then change it back.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n _ k e m p f]

In other words-- when Jet throws a tissy fit-- about a query being too
complex-- Tony claims that this is for a reason.

Personally-- I claim that Jet is a piece of shit because it's not
reliable, it's not dependable and it's been obsolete for a decade.

People using MS Access are why the real $$ jobs go to India-- Because
Indian people listen to companies when they look for Enterprise Level
Database developers.
Do you think that Amazon.com runs on Jet?
Do you think that Boeing.com runs on Jet?
Do you think that anything.Gov runs on Jet?

NOT LIKELY, EVERYONE KNOWS THAT JET HAS BEEN OBSOLTE FOR A DECADE~!

 

[cunger28]

Mr. B,

It is actually a sql statement off of the linked split table... I unbound
that and set it to the table itself and the fields would update. I could go
this route, but I have search and filter capabilities that need to be in
place. I guess i could unbind the fields that need to be updated and and
create an update button with an update sql command couldn't I?

I actually lived in Whitehouse for a couple of years.. small world...

Thanks!

 

[cunger28]

Hey Tony,

I forgot to mention previously on the sql statement used, there are no
relational joins, it's just a query off of my linked table.. The only reason
why I used the query is for the search and filter capabilities which I need
to keep.

Here's the query used:

SELECT
System, Company, Division, Franchise, WO_Stat, WO_Num, WO_Num2,
Installer, Type, Time_Slot, Sched_Dte, Account, Customer, ADDR1,
ADDR2, City, State, Zip, Phone, Finding, Solution, Problem, Action,
ArriveOnTime, BootCovers, DustbusterUsed, Professional_Courteous,
Customer_Satisfied, TechRelease, WouldRecommend, InitComment,
[2ndComments], [2ndResolution], FollowUp, CallDte, Assigned,
RunDte, Key, CabellaFactor, [Worked By], RunDte

FROM NET_PROMOTER_DETAIL

GROUP BY
System, Company, Division, Franchise, WO_Stat, WO_Num, WO_Num2,
Installer, Type, Time_Slot, Sched_Dte, Account, Customer, ADDR1,
ADDR2, City, State, Zip, Phone, Finding, Solution, Problem, Action,
ArriveOnTime, BootCovers, DustbusterUsed, Professional_Courteous,
Customer_Satisfied, TechRelease, WouldRecommend, InitComment,
[2ndComments], [2ndResolution], FollowUp, CallDte, Assigned,
RunDte, Key, CabellaFactor, [Worked By], RunDte

HAVING (((WO_Stat) Like "*" & Trim([FORMS]![NetPromoter].[srch_WOStat]) &
"*")
AND ((WO_Num) Like "*" & [FORMS]![NetPromoter].[srch_WO] & "*")
AND ((Installer) Like "*" & [FORMS]![NetPromoter].[srch_Tech] & "*")
AND ((FollowUp) Like "*" & Nz([FORMS]![NetPromoter].[srch_FollowUP]) & "*")
AND ((RunDte) Like "*" & [FORMS]![NetPromoter].[srch_RunDte] & "*"
AND (RunDte)>=CDate(Format(Date(),"mm") & '/01/' & Format(Date(),"yyyy"))));

Thanks,

 

[John W. Vinson]

Hey Tony,

I forgot to mention previously on the sql statement used, there are no
relational joins, it's just a query off of my linked table.. The only reason
why I used the query is for the search and filter capabilities which I need
to keep.

No TOTALS query is ever updateable -that's why you're having the problem!

Remove the entire GROUP BY clause and change the HAVING clause to WHERE.

 

[cunger28]

That actually did the trick! Thanks John and to everyone else attempting to
help!