Subform fails to act on DataEntry property

[Guest]

Access 2003 subform problem with DataEntry=True property:

I have a form with a two subforms on it.

The first subform shows all records that belong to the main form, from
another table, and does NOT allow record additions. It is sorted in reverse
order so that most recent records are visible at the top and old records
disappear off the bottom of the display area (can scroll down to see them).

The second subform is immediately above it, pointing to the same recordset
and set to AllowAdditions=Yes and DataEntry=Yes. This gives the illusion on
the main form that the one and only data entry field for the related table is
at the TOP of the list, rather than the usual bottom.

All works wonderfully well... until I apply any kind of filter to the main
form. At that time the DataEntry subform (second mentioned subform) seems to
lose its DataEntry property or behaviour, and shows the most recent record
instead of a blank record. If I manually expand the form I can see it shows
all records WITH a blank record available at the bottom.

I've tried programatically resetting the subform's DataEntry property to
True via the ApplyFilter event on the main form, but it doesn't fix the
problem. I know that the filter event is running because I've also applied
the Visible=False method and the subform disappears.

Is there a simple way to make a DataEntry=True subform behave properly when
the main form is filtered? Am I doing something dumb or is this a bug in
Access?

 

[TC]

Ok, that sounds like quite a nice scheme.

- Show me the exact statement that you used to programatically reset
the sunform's DataEntry property.

- Did you also reset AllowAdditions? If that one got zapped & you did
not fix it, this would explain the problem.

- Try displaying both of the two relevant subform props *before and
after* your fixup statements. Then you can see whether (a) your fixup
statements did not change the property values, or (b) they *did*, but
the new values seem to have been ignored.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Thanks for the fast response.

Yes, I set ALL those properties via the main form's ApplyFilter event. Plus
I've now applied the Not Visible attribute because I can't get it to work and
it messes up if the user uses the Add subform while it's misbehaving. Here's
the code:

'MsgBox ("Applying filter values...")
Me![DWDV Donations Add Subform].Form.AllowFilters = True
Me![DWDV Donations Add Subform].Form.AllowEdits = True
Me![DWDV Donations Add Subform].Form.AllowDeletions = True
Me![DWDV Donations Add Subform].Form.AllowAdditions = True
Me![DWDV Donations Add Subform].Form.DataEntry = True
Me![DWDV Donations Add Subform].Form.Repaint
Me![DWDV Donations Add Subform].Visible = False

As for looking at the properties before and after, I've tried doing that.
However, while I can click and get a handle/properties for controls ON the
subform, I can't select the subform itself to see its own properties, which
is where these particular properties reside. Clicking around never selects
it. Any ideas? (The subform is on a tab, but I don't "think" that matters
here.)

By the way, the code above affects only the INSTANCE of the form open at
present. Closing the form and reopening it returns to proper Add subform
behaviour, until another filter is applied.

Hope you can shed some light on this...

Many thanks in advance.

 

[TC]

Thanks for the fast response.

No probs, but I am off for dinner now

Yes, I set ALL those properties via the main form's ApplyFilter event.

You only need two AFAICS: AllowAdditions, and DataEntry.

Plus I've now applied the Not Visible attribute because I can't get it to work and
it messes up if the user uses the Add subform while it's misbehaving.

Don't do that. Focus on why your original code is not working
correctly. There's no reason why it shouldn't. Fiddling with the
Visible property does not help you find the problem.

Here's the code:

'MsgBox ("Applying filter values...")
Me![DWDV Donations Add Subform].Form.AllowFilters = True
Me![DWDV Donations Add Subform].Form.AllowEdits = True
Me![DWDV Donations Add Subform].Form.AllowDeletions = True
Me![DWDV Donations Add Subform].Form.AllowAdditions = True
Me![DWDV Donations Add Subform].Form.DataEntry = True
Me![DWDV Donations Add Subform].Form.Repaint
Me![DWDV Donations Add Subform].Visible = False

That's fine *IF* "DWDV Donations Add Subform" is the name of the
subform *control*. You do realize that it should be the name of the
subform *control* - not the name of the subform *within* that control?
I guess that might be your problem.

As for looking at the properties before and after, I've tried doing that.

So - what was their value afterwards? Had they changed to the new
values you set - or not?

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

The only reason my code exists in the first place is that while the DataEntry
property works just fine when the main form is not filtered, it stuffs up
when it IS filtered. I was using that in an attempt to determine whether
Access had clobbered the properites of the subform while filtering. But it
hadn't.

While the subform CONTROL (on the main form) and the subform itself had the
same name, I changed the control name so they were different; after your
suggestion to refer explicitly. The subform properties still have to be
referred to as a form within the CONTROL as they belong to the subform itself
and NOT to the control. Removing the .Form. segment from the object names
results in an error (property doesn't belong to object).

I just tried a really simple form (as the one I reported is very large and
complex). Same problem. Try this:

1. Set up a new main form with a few controls on it from an existing table.

2. Set up a new subform with a few controls on it from a related table to
above AND set the form's DataEntry property to Yes.

3. Embed the subform in the main form.

4. View the main form and browse through a bunch of main-form records.
Notice that the subform has only one record for every main-form record and
that it's a "new" record.

5. Right-click any field in the main form and Filter By Selection
(preferably on a record you know has a bunch of subform records).
Immediately the subform shows all existing matching records PLUS a blank
new-record. THAT IS NOT RIGHT. Programatically setting the subform's
DataEntry field back to Yes (along with AllowAdditions) does not fix it.

Big problem... what to do about it?

 

[TC]

Neil, if you want me to help you with this, you need to answer my
questions, and go strictly step by step.

(1) Is "DWDV Donations Add Subform" the name of the subform control, or
the name of the actual subform *within* that control?

(2) What were the displayed values of the subform AllowAddition and
Data Entry properties, *before*, and *after*, you executed the code to
reset them?

All I need, is the answer to those two questons. Then I can devise the
next step in the debugging process. But if you go off trying 30 new
things, this does not advance the debugging process - it actually puts
it back to the start.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

(1) After changing the name of the control so it was different from the name
of the form the control refers to, the control name is now "DWDV Donations
and Subform Control", and so the VBA line reads...

Me![DWDV Donations Add Subform Control].Form.DataEntry = True

(The name of the subform iself is still "DWDV Donations Add Subform".)

(2) I had to set up a separate test for this as you need blank subform
Details background to get a clickable Properties handle on it. The short
answer is: BEFORE the filter DataEntry is Yes, AFTER the filter DataEntry is
No.

So, Access is clobbering the value.

Neither VBA code nor a macro will reset the property, though I can easily
set the property of anything ON the subform by either VBA or macro.

(2b) If the Properties dialog is open and I manually set DataEntry to Yes
after Access has clobbered it, the form returns to the way it should be.

(2c) Removing the filter on the main form causes the problem to occur again.

(2d) The problem is with the INSTANCE of the form. Closing the form and
reopening it gives a DataEntry value of Yes as per original saved version.

Hope I didn't go too far ahead that time...

 

[TC]

(1) After changing the name of the control so it was different from the name
of the form the control refers to, the control name is now "DWDV Donations
and Subform Control", and so the VBA line reads...
Me![DWDV Donations Add Subform Control].Form.DataEntry = True
(The name of the subform iself is still "DWDV Donations Add Subform".)

Ok, that looks fine.

One comment: it's good to follow some standard naming conventions. Do
not do that now - this is just a sugestion for later!

For example, you might call the subform control "sfDonations", or
somesuch, and the form within it "frmDonations", or somesuch. Note the
prefix "sf" for a subform control, and "frm" for a form. In my opinion,
it doesn't really matter what convention you use - as long as you use
it consistently, and choose a convention that distinguishes the various
different types of objects. Other common prefixes are "tbl" for tables,
"qry" for queries, "mdl" for modules, "txt" for textboxes, "chk" for
checkbox controls, and so on.

As I say - do not change that now; do it after we've fixed your current
problem.

(2) I had to set up a separate test for this as you need blank subform
Details background to get a clickable Properties handle on it. The short
answer is: BEFORE the filter DataEntry is Yes, AFTER the filter DataEntry is
No. So, Access is clobbering the value.

Ok. Sorry if the next suggestion sounds repetetive, but since we are
both online now, let's get it fixed before we stop!

Please show me the full block of code that you've used to (1) display
the existing values of the DataEntry and AllowAddition propertires of
the subform; (2) change both those properties back to True; then (3)
display the values again.

I'm not trying to twirl you 'round in circles here - I just feel that
if you show me that whole block of code, the problem will be fairly
clear. So far I have not actually seen that whole block of code.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Hi TC,

Yes, I do use conventions... normally, but forgot in the case of subforms.
Will clean up later.

As for code. The only code I've written is that I sent you, in an attempt
to SET the value.

I have no code for reading the value. I do that by eyeball from the
Properties panel while using the form live. That's where I can see the
DataEntry value before and after applying the filter. And it's the ONLY
place I can actually get the value to change back to Yes after Access has
changed it to No -- by manipulating it manually on the Properties panel.

Cheers
Neil

 

[TC]

Neil, your debugging code must do three things - as I have already
said:

(1) display the existing values of the DataEntry and AllowAddition
properties of the subform;

(2) try to change both those properties back to True, then

(3) display those two values again - ie. repeat step (1).

You need to:

(A) show me the whole block of code, verbatim, that you have used to do
that; and,

(B) tell me what values that code displayed, for each of the two
properties, at steps (1) and (3).

That's the only way I can help.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

In step (2), I mean *through code*. It's irrelevant how you've tried to
do that "manually" through the user interface. Ypou nedd to show me
*the code* you've used for the three steps together.

TC

 

[Guest]

OK Tim, guess we're getting used to each other's communication. I thought
you were asking me what I did, rather than telling me what to do.

I penned the following little routine for the main form:

--------------------
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

MsgBox ("AllowAdditions before setting is: " & Me![DWDV Donations Add
Subform Control].Form.AllowAdditions)
MsgBox ("DataEntry before setting is " & Me![DWDV Donations Add Subform
Control].Form.DataEntry)
Me![DWDV Donations Add Subform Control].Form.AllowAdditions = True
Me![DWDV Donations Add Subform Control].Form.DataEntry = True
MsgBox ("AllowAdditions after setting is: " & Me![DWDV Donations Add
Subform Control].Form.AllowAdditions)
MsgBox ("DataEntry after setting is " & Me![DWDV Donations Add Subform
Control].Form.DataEntry)

End Sub
--------------------

Results are:

1. VERY FIRST FILTER USE AFTER INSTANTIATING THE MAIN FORM:
True, True, True, True

2. EVERY OTHER FILTER/CANCEL-FILTER THEREAFER IN CURRENT INSTANTATION:
True, False, True, True

The form misbehaves from the very first filter application and thereafter
until the form is closed and reinstantiated. So, I don't believe the results
of the very first filter (which is confirmed by the Properties panel
immediately saying False).

The results are the same if I use first "Remove filter/sort" instead of
"Filter by selection" or "Filter excluding selection".

Cheers
Neil

 

[Guest]

Hmmm... just been looking at it "more slowly" now. On the second DataEntry
result message box that says "True", the subform is displayed correctly (ie
one blank line for a new record).

But immediately AFTER I click the message OK button, the subform reverts to
a full record list.

So it looks like something is happening AFTER the ApplyFilter event that is
scragging the DataEntry property of the subform. Given that (apart from the
little snippet of code to read and set the values above), this database
project is ENTIRELY Macro- and VBA-free, this is something Access is doing???

Had a look around for Access events that run after ApplyFilter but found no
references.

Cheers
Neil

 

[TC]

Neil, sorry if I wasn't clear before. Communicating via usenet is
*really friggin' difficult*! Your last post is what I needed.

It seems to me, from what you have posted, that Access is not doing
what it should.

For that reason, are you willing to send me a copy of your database? If
so, I will state, putting my neck out, that I will definitely find the
problem for you, by the end of the day after tomorrow - because
tomorrow I have social obligations.

If you were willing to do that, you could put the db on the web,
somewhere where I could get a copy; or you could email it to the
following address: aatcbbtccctc <YOU KNOW WHAT COMES HERE!> yahoo <YOU
KNOW WHAT COMES HERE, TOO!> com

HOWEVER:

(1) It would be preferable if you could simplify the database by
deleting all of the irrelevant objects. The ideal would be, to have
just the form that shows the problem; and just the tables (or other
objects) that are required by that form. This would (a) make my job
simpler, and (b) protect your intellectual property.

(2) You'd need to *zip* the database if you emailed it (instead of
putting it on the web).

And most importantly:

(3) You'd need to include a plain text file, named "INSTRUCTIONS.TXT",
which gave a clear & simple set of instructions, numbered 1. to
whatever, for how to make the problem occur. For example:
1. Download file blah.mdb to your pc;
2. Double-click file blah.mdb;
3. Select the "Blah Form" switchboard option;
(and so on)

Can you do that?

It's 2AM here, so I have to go bye-bye's now! - but I will check for
your answer first thing when I get up tomorrow.

Cheers,
TC (MVP Access)
http://tc2.atspace.com