Setting Subform Allowadditions and Dataentry properties

[cybe...@worldnet.att.net]

I have some subforms on a tab control. The main form is unbound and each of
the subforms is on a separate tab. Each subform is bound to an updatable
query and the query criteria is based on an unbound combo box on its
particular subform. The subform is initially set to allowadditions no and
dataentry no. I'm trying to do the old save btn, add btn, cancel btn and
delete btn dance where pressing the add botton changes the form's
allowadditions property to yes and the dataentry property to yes (also making
the save and cancel btn's invisible). Cancel btn reverses the order, first
settting dataentry to no and then allowadditions to no. Using the following
syntax in setvalue macro statements:

[Forms]![space management].[SpacesSubform].[Form].[AllowAdditions], Yes

This syntax is not generated by the expression builder: had to add the
forms!formname statement at the front.

Now here's the problem. It works once and once only. Hit add and it goes into
add mode. Hit cancel and it returns. But, and it's a big but since I have 20
hours or so into these forms: Hit add again, and I get a object invalid or
not set error message and the macro comes to a screaming halt. Sometimes it
appears on the first return from add that the form has not properly
requeried--the navigation button shows no records until you click forward or
back--but I have all kinds of requery and runcomma

The macro's are run from buttons on the subforms themselves.

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Dorman Blackman]

I can't answer your question as to why SetValue macros are working only
once, as I never use macros.

BUT, if you put:

[SpacesSubForm].Form.AllowAdditions = True

in the ENTER event of the SubFormControl on the Main Form, you'll get what
you want, assuming SpacesSubForm is the actual name of the SubFormControl
and not the name of the subform itself.

Do the same in the EXIT event of the SubFormControl except set
Allowadditions = False.

Now, whenever focus goes to the subform, Allowadditions is true and it
returns to false when focus leaves the subform.

Obviously you can set other properties like DataEntry the same way.

If you want these properties to change only on the click events of the
buttons (although I'm not sure why you would) just put code in the buttons'
click events. Because the buttons are on the subforms themselves, the syntax
would change to: Me.AllowAdditions = True, in the buttons click event
procedure.

dorman

cybe...@worldnet.att.net wrote in message
<76pf2m$dv$1...@nnrp1.dejanews.com>...

[cybe...@worldnet.att.net]

Dorman: Thanks for your time in trying to answer the question. I took your
suggestion and converted the macros to code. I was pleasantly surprised by
the new capabilities of Access Basic--much improved (user friendly) over the
Access 2.0 version I played with a while ago. I will definitely take time to
learn it and use it.

But in the process of trying to debug this, I think I found a quirk in the
program. First, though, since you asked, let me explain what I'm trying to
do. I have a runtime Access program in distribution to the general public.
I've found new users have a hard time understanding record selectors and the
fact that they can add a new record at the end. So I'm trying to set up the
data entry forms to allow edits but not additions initially. I put an add
button on the form that forces them to deliberately add a record when they
wish. Clicking the add button makes it turn invisible and two buttons,
'cancel' and 'save' appear in its place. The add button sets the forms
allowadditions property to true and the dataentry property to true. The
cancel button runs the undo command if a record has been started and resets,
first the dataentry property to false and then the alllowadditions property
to false. Anyway, that's how it's supposed to go.

Now here is what I found after 3 hours of work. I have two unbound combo
boxes on the form that the user must select data from. These combo boxes are
then the criteria for the form's query. What I found was that the add -
cancel cycle works and repeats if the form's underlying query has no
criteria, but only cycles once without error if the query has criteria. I
even pulled the form off the tab ctrl and off the main form in the process of
attempting to fix this. All to no avail. I pulled the combo boxes off and put
them on a separate form, just to see if they were being put into limbo
somehow. All to no avail.

So here's the problem. Stripped to its bare essentials, a form can't be
switched from allowadditions true and dataentry true to dataentry false
allowadditions false more than once if the underlying query has criteria
based on the value of a control on the form or even on another form. At least
I can't find it.

Do you have any other suggestions before I escalate this to AN UNSOLVED
MYSTERY? Anyone else want to jump in here? If you don't believe me try it. Or
e-mail me at cybe...@worldnet.att.net and I'll send you the forms and
tables to see for yourself. Personally, I think it is a bug. Thanks in
advance for your help.

In article <76qh7c$2dj$1...@hornet.fibr.net>,

[Dorman Blackman]

I've not run into the problem you're describing, perhaps because of
different approaches. I almost never set DataEntry = True unless I'm setting
up a form for continuous data entry. I don't set DataEntry True for normal
add record situations. Once AllowAdditions is set true, and you put:

DoCmd.RunCommand acCmdRecordsGoToNew

in the Click event of the ADD button, you get a new blank record for the
user to input. They then click the Save or Cancel buttons when done. If
Save, no problems. If Cancel then Undo. In both cases, turn off
AllowAdditions.

Try it without setting DataEntry true and see if this solves your problem.
Otherwise, I can't think through all the ramifications of the query
criteria. Is it possible that the record's primary key is not being
properly saved to the record?

dorman

cybe...@worldnet.att.net wrote in message
<76s31c$6vo$1...@nnrp1.dejanews.com>...

[cybe...@worldnet.att.net]

Again, thanks for your interest. I'll try your suggestion this evening. I
could simulate dataentry mode by hiding the navigation buttons. Since the
form's cycle setting is already just fields, there would be no way to leave
the record without hitting the save or cancel buttons.

One workaround that dawned on me this morning that I may also try is to base
the form's underlying query on another query -- and put the criterion in the
query's underlying query. I'll let you know how it comes out.

In article <76tbl9$u0t$1...@hornet.fibr.net>,

[cybe...@worldnet.att.net]

In article <76tjn9$g1n$1...@nnrp1.dejanews.com>,

cybe...@worldnet.att.net wrote:
> Again, thanks for your interest. I'll try your suggestion this evening. I
> could simulate dataentry mode by hiding the navigation buttons. Since the
> form's cycle setting is already just fields, there would be no way to leave
> the record without hitting the save or cancel buttons.
>
> One workaround that dawned on me this morning that I may also try is to base
> the form's underlying query on another query -- and put the criterion in the
> query's underlying query. I'll let you know how it comes out.
>

Well, the workaround worked. For some reason, basing the form's underlying
query on another query (which has the criteria) allows correct cycling to
occur. My guess is that somewhere during the switch, the form must requery
and somehow something is going awry because in Dataentry mode, it doesn't
expect criteria in the underlying query. But who knows. Anyway, thanks for
your help, and leading me to Access Basic again.