AllowEdit doesn't Allow Edit

  • Thread starter Thread starter Karen Hagerman
  • Start date Start date
K

Karen Hagerman

I have a main form with a subform that is a continuous view. On the subform the 'AllowEdits' property is set to 'Yes' but the 'New' navigation button is never active. Any ideas?

Karen
 
Karen,

I think you should also check that the Allow Additions property is also set
to Yes.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a main form with a subform that is a continuous view. On the subform
the 'AllowEdits' property is set to 'Yes' but the 'New' navigation button is
never active. Any ideas?

Karen
 
I have a main form with a subform that is a continuous view. On the subform the 'AllowEdits' property is set to 'Yes' but the 'New' navigation button is never active. Any ideas?

Karen
Click to expand...

Sounds like the Subform is based on a non-updateable query. Try
opening the Form's Recordsource query in query datasheet view - does
it have a "new record" at the bottom?

If not, check the online help for Updateable, and/or post the SQL view
of the query here.
 
Karen Hagerman said:
I have a main form with a subform that is a continuous view. On the
subform the 'AllowEdits' property is set to 'Yes' but the 'New'
navigation button is never active. Any ideas?
Click to expand...

In addition to the possibility that AllowAdditions is set to No, check
that the subform is based on an updatable query. If you open the
subform's recordsource query directly in datasheet view, and there's no
"new record", then the query is probably not updatable.

Also check that the main form containing the subform allows edits. I
think that if the main form doesn't allow edits, the subform won't
either, no matter what the subform's properties say.
 
John and Dirk,

Thanks, that was it, I based it on a query. I based the form on a query
instead of a table because I can't seem to use filter a form to open only
one record from a table based on the table's primary key for that record. I
have tried

------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TestContactForm"

stLinkCriteria = "[ContactID]=" & 6
DoCmd.OpenForm stDocName, , , stLinkCriteria
-----------------------------------------------------------------AND I'VE
TRIED
setting the form's record source to the table and putting [ContactID]=6 in
the Filter property. Allow Filters is 'Yes'

I have been working for hours and this should not be hard at all, what am
I'm doing wrong?

Should I repost this as a separate question or do you have any advice?

Karen

subform the 'AllowEdits' property is set to 'Yes' but the 'New' navigation
button is never active. Any ideas?
 
Karen said:
John and Dirk,

Thanks, that was it, I based it on a query. I based the form on a
query instead of a table because I can't seem to use filter a form to
open only one record from a table based on the table's primary key
for that record. I have tried

------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TestContactForm"

stLinkCriteria = "[ContactID]=" & 6
DoCmd.OpenForm stDocName, , , stLinkCriteria
-----------------------------------------------------------------AND
I'VE TRIED
setting the form's record source to the table and putting
[ContactID]=6 in the Filter property. Allow Filters is 'Yes'

I have been working for hours and this should not be hard at all,
what am I'm doing wrong?

Should I repost this as a separate question or do you have any advice?
Click to expand...

It's not being based on a query per se, it's that this particular query
is apparently not updatable. I don't see anything wrong with the code
you posted above, provided of course that ContactID is a numeric field
in the form's recordsource -- not a text field. If there is no record
with ContactID = 6, you'll get a blank form, of course.

What happened when you tried the above code? Did you see an error
message? What do you see if you open the form without the criterion?
 
Hi Dirk,

Again, thanks for looking at this. I don't get an error message, the filter is just ignored. If I specify an ID that only has one match, I see all of the items in the table. Any ideas?

Karen
Dirk Goldgar said:
Karen said:
John and Dirk,

Thanks, that was it, I based it on a query. I based the form on a
query instead of a table because I can't seem to use filter a form to
open only one record from a table based on the table's primary key
for that record. I have tried

------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TestContactForm"

stLinkCriteria = "[ContactID]=" & 6
DoCmd.OpenForm stDocName, , , stLinkCriteria
-----------------------------------------------------------------AND
I'VE TRIED
setting the form's record source to the table and putting
[ContactID]=6 in the Filter property. Allow Filters is 'Yes'

I have been working for hours and this should not be hard at all,
what am I'm doing wrong?

Should I repost this as a separate question or do you have any advice?
Click to expand...

It's not being based on a query per se, it's that this particular query
is apparently not updatable. I don't see anything wrong with the code
you posted above, provided of course that ContactID is a numeric field
in the form's recordsource -- not a text field. If there is no record
with ContactID = 6, you'll get a blank form, of course.

What happened when you tried the above code? Did you see an error
message? What do you see if you open the form without the criterion?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Karen said:
Hi Dirk,

Again, thanks for looking at this. I don't get an error message, the
filter is just ignored. If I specify an ID that only has one match,
I see all of the items in the table. Any ideas?
Click to expand...

I'm at a loss to explain it. Please post the recordsource of the form,
the data types (from table design view) of the fields in the
recordsource, and the other properties from the Data tab of the form's
property sheet. Also post the exact code you're using to open the
form -- the code that is attempting to applying the where-condition --
and any code in the form's code module.
 
Karen said:
Dirk,

Here it is. The TestContactTable



The form properties



The result



I have to go earn some money now but if you see anything, don't
hesitate to educate me!
Click to expand...

LOL -- so do I, Karen.

You should not post binaries, such as these images, in a text-only
newsgroup like this one. In fact, you're lucky I saw this message at
all, because my newsreader is set to ignore messages over a certain
size.

Unfortunately, looking at the pretty pictures doesn't show me anything
wrong. You didn't post the code I asked for, the exact code that opens
the form and attempts to apply the where-condition, and the code (if
any) in the form's code module.

If you enter this:

DoCmd.OpenForm "TestContactTable", , ,"ContactID=1"

in the Immediate Window, when the form is currently closed, does it open
to the filtered view?
 
Dirk,

OK, I won't do that again.

In order to troubleshoot this problem I had gone back to the form that I am
using as a subform on another form.

When I type DoCmd.OpenForm "TestContactTable", , ,"ContactID=1" in the
immediate window, it does open and it is filtered. When I open the form by
itself, it does not open filtered even though the filter on the form's
property sheet is [ContactID]=1.

When it is a subform this is the code that opens it (in this case it is
filtered but it is because it is based on a query--back to the original
problem, when it is based on a query I can't get the subform to allow
additions. That is why I wanted to based the subform on the table and
filter it)

SELECT DISTINCT TestCompanyContactLinkTable.ccCompanyID,
TestCompanyContactLinkTable.ccContactID, TestCompanyMain.CompanyName,
TestContactTable.*
FROM TestContactTable INNER JOIN (TestCompanyMain INNER JOIN
TestCompanyContactLinkTable ON TestCompanyMain.CompanyID =
TestCompanyContactLinkTable.ccCompanyID) ON TestContactTable.ContactID =
TestCompanyContactLinkTable.ccContactID
WHERE
(((TestCompanyContactLinkTable.ccCompanyID)=[Forms]![TestTestBusinessTab]![C
ompanyID]));

Basically there is a table that has Companies and a table that has Contacts.
There is also a table that lists CompanyID's with any associated
ContactID's. The reason for the third 'index' table was because contacts
can be associated with more than one company at the same time.

I think this is probably starting to get way too complicated so if you don't
see something obvious, you can let it go.

It is a mystery.

Karen
 
Karen said:
Dirk,

OK, I won't do that again.
Click to expand...

No sweat, Karen.
In order to troubleshoot this problem I had gone back to the form
that I am using as a subform on another form.

When I type DoCmd.OpenForm "TestContactTable", , ,"ContactID=1" in the
immediate window, it does open and it is filtered. When I open the
form by itself, it does not open filtered even though the filter on
the form's property sheet is [ContactID]=1.
Click to expand...

If "when I open the form by itself" means "when I open it from the
database window", then that is as it should be. Access stored the last
filter applied in the form's Filter property, but it doesn't apply it
until you explicitly tell it to, either via the user interface (e.g., by
clicking the Apply Filter button) or by code (e.g.,
Forms!TestContactTable.FilterOn = True).
When it is a subform this is the code that opens it
Click to expand...

Now you are confusing me. You don't use code to open a subform; it
opens automatically as part of the opening of its parent form. Are we
talking about a real subform here? That is, a form displayed in a
subform control on a parent form?
(in this case it
is filtered but it is because it is based on a query--back to the
original problem, when it is based on a query I can't get the subform
to allow additions. That is why I wanted to based the subform on the
table and filter it)

SELECT DISTINCT TestCompanyContactLinkTable.ccCompanyID,
TestCompanyContactLinkTable.ccContactID, TestCompanyMain.CompanyName,
TestContactTable.*
FROM TestContactTable INNER JOIN (TestCompanyMain INNER JOIN
TestCompanyContactLinkTable ON TestCompanyMain.CompanyID =
TestCompanyContactLinkTable.ccCompanyID) ON
TestContactTable.ContactID = TestCompanyContactLinkTable.ccContactID
WHERE
(((TestCompanyContactLinkTable.ccCompanyID)=[Forms]![TestTestBusinessTab
]![C
ompanyID]));
Click to expand...

And the above isn't code that opens a form; it's just the SQL of what I
gather is the recordsource of the form. I don't understand what you
mean when you say, "When it is a subform this is the code that opens
it".
Basically there is a table that has Companies and a table that has
Contacts. There is also a table that lists CompanyID's with any
associated ContactID's. The reason for the third 'index' table was
because contacts can be associated with more than one company at the
same time.
Click to expand...

That makes sense, and I believe I understand the table structure, which
is fairly standard.
I think this is probably starting to get way too complicated so if
you don't see something obvious, you can let it go.
Click to expand...

Never say die, Karen!
It is a mystery.
Click to expand...

Light is beginning to dawn, though. Forgive me if I'm jumping to
conclusions, but I have the impression that you aren't too clear on how
subforms work. Is that correct, or do you think I'm just
misinterpreting your words? Suppose we step back a bit, and you tell me
in broader terms how you want your forms to work. Are you trying to set
up a main form based on TestCompanyMain, with a subform that displays
all the contacts for the company that is current on the main form?
 
Dirk,
If "when I open the form by itself" means "when I open it from the
database window", then that is as it should be. Access stored the last
filter applied in the form's Filter property, but it doesn't apply it
until you explicitly tell it to, either via the user interface (e.g., by
clicking the Apply Filter button) or by code (e.g.,
Forms!TestContactTable.FilterOn = True).
Click to expand...
OK, that I did not know. When I can't get something to work I go back to
the individual components to sort out the problem. Thanks so much for that
bit of info, who would have thunk!
Now you are confusing me. You don't use code to open a subform; it
opens automatically as part of the opening of its parent form.
Click to expand...

I knew that, I just got confused when you asked for the code that opened the
form and when you mentioned the 'where' condition I thought you meant the
recordsource.

Are we
talking about a real subform here? That is, a form displayed in a
subform control on a parent form?
Click to expand...
It really is a subform. It is a tabbed form, the first tab has fields
related directly to the tabbed form's recordsource and I dragged the
'contacts' form to the second tab.
And the above isn't code that opens a form; it's just the SQL of what I
gather is the recordsource of the form. I don't understand what you
mean when you say, "When it is a subform this is the code that opens
it".
Click to expand...
Got it, I really did know that the SQL was the recordsource but my
inexperience certainly shows when I use the wrong terms to describe.
Light is beginning to dawn, though. Forgive me if I'm jumping to
conclusions, but I have the impression that you aren't too clear on how
subforms work. Is that correct, or do you think I'm just
misinterpreting your words? Suppose we step back a bit, and you tell me
in broader terms how you want your forms to work. Are you trying to set
up a main form based on TestCompanyMain, with a subform that displays
all the contacts for the company that is current on the main form?
Click to expand...
Yes, that is exactly it. The contacts do display correctly, i.e. the first
tab is the info related to a company and the contacts tab only has the
contacts associated with that company. I was frustrated because I'd like to
add new contacts from the contacts tab but I can't get the asterisk (*)
'new' navigator button to be active. At this point I think I'll just have
to use a command button to open a separate form to add new contacts.

Again, thanks for the patience. I'm still a very green Access person but
it's so much fun it's hard to stop.
--
Click to expand...
Karen
 
Karen said:
Dirk,

OK, that I did not know. When I can't get something to work I go
back to the individual components to sort out the problem. Thanks so
much for that bit of info, who would have thunk!


I knew that, I just got confused when you asked for the code that
opened the form and when you mentioned the 'where' condition I
thought you meant the recordsource.
Click to expand...

No, I was talking about the WhereCondition argument of the
DoCmd.OpenForm method. "WhereCondition" is the actual name of the
argument where you pass a filter-string such as "ContactID=1" -- where
the stLinkCriteria variable is passed in code written by the Command
Button Wizard.
Are we
It really is a subform. It is a tabbed form, the first tab has fields
related directly to the tabbed form's recordsource and I dragged the
'contacts' form to the second tab.
Click to expand...

Okay, good.
Yes, that is exactly it. The contacts do display correctly, i.e. the
first tab is the info related to a company and the contacts tab only
has the contacts associated with that company. I was frustrated
because I'd like to add new contacts from the contacts tab but I
can't get the asterisk (*) 'new' navigator button to be active. At
this point I think I'll just have to use a command button to open a
separate form to add new contacts.
Click to expand...

Oh, no, not at all. I believe the problem is quite simple, and it's
just as John Vinson and I were both suggesting might be the case before
we went off on this wild goose chase over filtering. Your "new record"
button is disabled, and your contacts are uneditable, because your
subform's recordsource query is not updatable. It is not that
multi-table queries are inherently nonupdatable, it's just that your
query incorrectly includes the TestCompanyMain table, thereby creating a
many-to-one-to-many relationship within the query. You don't need any
fields from TestCompanyMain, because they're already there on the main
form, and adding this table creates a nonupdatable query.

Try this as your recordsource query:

SELECT DISTINCT
TestCompanyContactLinkTable.ccCompanyID,
TestCompanyContactLinkTable.ccContactID,
TestContactTable.*
FROM TestContactTable
INNER JOIN TestCompanyContactLinkTable
ON TestContactTable.ContactID =
TestCompanyContactLinkTable.ccContactID;

On the property sheet of the subform control on the main form, set the
Link Master Fields property to CompanyID -- I assume that's the name of
the primary key field of TestCompanyMain -- and set the Link Child
Fields property to ccCompanyID (*not* the CompanyID field from
TestContactTable). That will automatically take care of filtering the
subform so that it only shows the contacts related to the current
company on the main form. You don't need any WHERE clause in the above
recordsource query, because Access's normal handling of the subform will
take care of that.

I won't go so far as to guarantee that making these changes will make
the form and subform work right off the bat, but I'll guarantee that
doing it will put us within striking distance. Unless there's something
else you haven't told us ... ;-)
Again, thanks for the patience. I'm still a very green Access person
but it's so much fun it's hard to stop.
Click to expand...

Ain't it, though?
 
OK, I take it back. This is not fun. I used your suggested recordsource
and it works like a champ. I set it as the recordsource of the contacts
subform. I set the Link Master Fields property of the subform control to
CompanyID and the Link Child field to ccCompanyID. I doublechecked that the
main form allowed additions and edits as well as the subform.

Everything works, the data looks good BUT the 'new' navigation button is not
active. AARGH!

Karen
 
Karen said:
OK, I take it back. This is not fun. I used your suggested
recordsource and it works like a champ. I set it as the recordsource
of the contacts subform. I set the Link Master Fields property of
the subform control to CompanyID and the Link Child field to
ccCompanyID. I doublechecked that the main form allowed additions
and edits as well as the subform.

Everything works, the data looks good BUT the 'new' navigation button
is not active. AARGH!
Click to expand...

AARGH, indeed. I must have overlooked something, but I'm sure we're
almost there. Unfortunately, it's way past my bedtime, so this will
have to wait until morning. Tell you what: if you'd like to send me a
cut-down copy of your database, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB
in size (preferably much smaller) -- I'll have a look at it tomorrow,
time permitting. You can send it to the address derived by removing NO
SPAM from the reply address of this message.
 
John and Dirk,

Thank you from the bottom of my heart, I can fly again.

John, that was it, I removed the 'Distinct' from the SQL statement and left
everything else as suggested by Dirk and voila!, it works.

Besides books (John, I see you have a few under your belt) and just diving
into projects and, of course, the newsgroups; do either of you have other
suggestions I could use to really learn Access?

Karen
 
John Viescas said:
Ummm. Did I see a DISTINCT keyword in the SQL earlier in the thread?
That would make the query, and therefore the form, not updatable.
Click to expand...

Now *I'll* say AAARRRGHH! I saw that, but I didn't see that! I'm so
embarrassed! Pardon me while I go hide in shame.

Thanks for pointing that out, John.
 
Karen said:
John and Dirk,

Thank you from the bottom of my heart, I can fly again.

John, that was it, I removed the 'Distinct' from the SQL statement
and left everything else as suggested by Dirk and voila!, it works.
Click to expand...

I'm so embarrassed I didn't spot that, and even perpetuated it in my
recommended SQL. said:
Besides books (John, I see you have a few under your belt) and just
diving into projects and, of course, the newsgroups; do either of you
have other suggestions I could use to really learn Access?
Click to expand...

It depends on your background, and how you learn best. FWIW, I learned
Access almost entirely by reading the help file, topic by topic, and by
reading the newsgroups. Unfortunately, the help system's index isn't
much use these days, but fortunately the content is there.
 
Hey, no problem. Sometimes when you get buried in a long thread like this,
you can't see the forest for the trees! I just noticed the long thread and
decided to skim through it to see if you needed some help. It's like trying
to track down a coding bug -- if you have someone look over your shoulder
while you walk through the code and explain it, you'll often find the bug
yourself. Just the presence of the other person does the trick. <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top