Assigning values to check-boxes

[Bill Stanton]

I have a form that contains, amongst other controls,
two check-boxes both of which are bound to record
fields. In the table definitions, one default is set to
yes/true while the other's default value is unspecified.
Thus, when the form opens, one is checked and the
other is not... all as expected.

There is a condition that is reflected in the OpenArgs
(strDParms below) where I want to change the values
of the check-boxes in the OnOpen code. Every
attempt I've made to assign a new value, e.g.,
Me.MyChkBox = or Me.MyChkBox.Value =
produces an error message to the effect that a value
cannot be assigned. I've tried using both the name
of the check-boxes and the field names in the
assignments without success.

DoCmd.OpenForm frm, , , , acAdd, , strDParms

Is something special required when in "acAdd" mode?

Thanks,
Bill

 

[Albert D. Kallal]

The on-open event of a form is used to run verifying code, and check if some
condition is incorrect. If those conditions fail, (say, not having passed
openargs for example), the you can set Cancel = True, and the FORM WILL NOT
LOAD.

So, the on-open event is not a place where you can modify fields. It is too
soon, and take note of the cancel option when you write on-open event code.


You want to use the on-load event. In this event, the form has now loaded,
and you can modify the values of controls on the screen. In the on-open
event, you can examine the values...not modify.

Thus, the on-open event is real nice place to put any code that can test
things, and prevent the form from loading.

The on-load event is a real ncie place to put set-up code, additional
defaults, and initialise any of the variables and code used for the form.

 

[Andrew Smith]

Use the load event rather than the open event. The open event occurs before
the form's records are displayed, so I assume this means that you cannot use
it to set the values of any fields or any bound controls. The help text for
the load event says:

"By running a macro or an event procedure when a form's Load event occurs,
you can specify default settings for controls, or display calculated data
that depends on the data in the form's records."

 

[Bill Stanton]

Thanks Albert and Andrew,
Your replies helped beyond my expectations.
Bill

 

[Bill Stanton]

Oh oh!

I moved all the initialization code to the OnLoad
event section of the code-sheet, but continue to
get an error message to the effect that a value
cannot be assigned to the check-box.

I tried:
Me.Check138.Value = True
Me.Check109.Value = False

as well as:

Me.MyFieldName1.Value = True
Me.MyFieldName2.Value = False

to which the check-boxes are bound.

I also tried the assignments without the ".Value",
in the event that there's some quirk there.

Have I missed some of what you were trying to
teach me?

Bill

 

[Andrew Smith]

It should work for any of the variations you have tried.

Have you deleted the code from the OnOpen event?
What is the error message - the same as before or different?
Have you looked at the line of code where you get the error, or tried
stepping through the code?
Have you made sure that the OnLoad property of the form says "Event
Procedure".
Is there any other code running that may have to go in the OnOpen event?

 

[Bill Stanton]

I single stepped the code and it does in fact raise the
error on the statement: Me.Check138.Value = True

 

[Albert D. Kallal]

Hum, that is certainly strange.

I assume your code compiles fine when you do a compile from the code menu?

Perhaps the enabled settings were somehow changed. Take a quick look at the
property sheet for those two check box controls. Perhaps the Enabled
property, or even the locked property is set wrong.

 

[Albert D. Kallal]

Hum, if you remove those two lines of code (just comment them out), can you
click and use the check boxes on the form without trouble?

We are not seeing the bad tree here, but we can still see the forest!

I would also delete the check boxes, and then re-add them to the form (that
likely means the control names will change - so check your code after you do
this).

You could try viewing the field list, and use drag and drop from the field
list to create the check boxes. Thus, just try dragging the fields from the
field list on the form in design mode.

I would also check the record source of the form.

After that, I trying think of what else we are missing here....

 

[Bill Stanton]

Re-created from field list via drag. Names given to the
new check-boxes same as field names (no suprise here).
Code adjusted accordingly.

RecordSource query is well established and works
perfectly.

Exact same error!

Still clueless!

Bill

 

[Albert D. Kallal]

In the first part of my last post, I suggested to comment out the two lines
of code. Then, when viewing the form, I suggested to test/try the check
boxes to see if they
function normally?

Can you check/use the checkboxes normally if you comment out that code that
fails?

 

[Bill Stanton]

Sorry Albert, I forgot to report on those suggestions.

The code works fine with the two assignment statements
commented out. Also, the form and code PRIOR to the
addition of the "Donations Only" OpenArgs is old and
well established. So, whenever the form is opened with
any OpenArgs other than "Donations Only", everything
works perfectly. And, as I've already mentioned, it
works fine if I disable the two assignment statements.

I have to go out for a couple of hours, but I'll be back
about 8:30PM California time.

Bill

 

[Bill Stanton]

Okay, I understand the questions you were trying to get answered
and I am confident that we're okay with all of that. I executed the
RecordSource query and verified that fields are as expected and
have values set throughout the recordset.

I just tried to modify one of the text-boxes that is also bound to
a field in the RecordSource and get the same thing:

Run-time error '-2147352567 (80020009)'
"You can't assign a value to this object"

So, it isn't something necessarily related to check-boxes.

The only thing I can think of at this point is that it has something
to do with the fact that the form is opened "acAdd". What I don't
is whether Access builds a new record in memory and waits until
the form closes before a new record is created in the DB. Could
it be an anomaly when "acAdd" mode is used?

Bill

 

[PC Datasheet]

Bill,

If the record that the form is at has values for the fields that the checkboxes
are bound to, you are always going to get an error if you try to change those
values using the Me!CheckBoxName = method or the Me("CheckBoxName") method.
That's the way it is when a control is bound to a field in a table and you
aren't going to change that!

If you want to make the change you are trying to make, you need to
programatically change the value in the table and then requery. The
Rst.Edit/Rst.Update or an update query are your options followed by a requery.


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

 

[Dirk Goldgar]

Bill,

If the record that the form is at has values for the fields that the
checkboxes are bound to, you are always going to get an error if you
try to change those values using the Me!CheckBoxName = method or the
Me("CheckBoxName") method. That's the way it is when a control is
bound to a field in a table and you aren't going to change that!

If you want to make the change you are trying to make, you need to
programatically change the value in the table and then requery. The
Rst.Edit/Rst.Update or an update query are your options followed by a
requery.

I don't follow what you're saying here. Can you clarify? I'd certainly
expect to be able to change the value of a field by assigning to the
control bound to it, so long as the recordset and field are updatable
and the control is enabled.

 

[Albert D. Kallal]

Okay, I understand the questions you were trying to get answered
and I am confident that we're okay with all of that. I executed the
RecordSource query and verified that fields are as expected and
have values set throughout the recordset.

That was not my question. And I am not confident we are ok here. My question
was when you comment out the two lines of code, and then have the code/form
load in the add mode (and the form now loads ok without error). Then right
at this POINT, can you click on the two checkboxes on the form with the
mouse? Hence, do those two check boxes function correctly without any errors
occurring? THAT WAS my question, and I still have not got a clear answer. I
mean, are those fields updateable at this point at all? It is rather simple
question. So, you want to run the code and keep all else the same, but only
leave out those two lines of code. Now, try clinking on the check boxes. Do
they work?

If the fields are not updateable, then going on a wild goose chase to fix
code is a complete waste of time here. I mean, if those fields are not
updateable with the mouse, then why even bother looking at code? The problem
would thus not be our code, but the simple fact that the query or those
fields are NOT updateable. We want to determine this fact. So, no, I not
one bit confident you have followed my instructions, nor answered my
question. Give the above a try, what happens?

 

[Bill Stanton]

Dirk wrote:

"I'd certainly expect to be able to change the value of a field by
assigning to the control bound to it, so long as the recordset and
field are updatable and the control is enabled."

So would I, and that's exactly what is making this caper so mysterious!
Other than my having erroneously inserted the assignment statements
in the OnOpen code rather than in the OnLoad section, this should be
about as "vanilla as it comes". Having said that, I don't understand
what PC Datasheet is saying about one not being able to assign values
to bound controls from code. To me, that's the same thing as saying that
code
can't act as proxy for the user in assigning values to a field. However, I'm
new enough at all of this to not challenge the experts, so I remain
clueless.

So, to clarify, I open a form in "acFormAdd" mode and attempt to toggle
two of the check-box controls from the OnLoad section of the form's
code-sheet. With the repeated failures there, I modified the code to
attempt to assign text to one of the many text-boxes and encountered
the same error. With that, I've concluded the problem has nothing to
do with the check-boxes themselves, rather there is a problem in general
while trying to perform assignments to controls from code.

Be back in a few hours. (10:48AM PST)
Bill

 

[Albert D. Kallal]

Albert, sorry I didn't reply completely. With the two assignment
statements commented out, the form works perfectly. That is,
all check boxes respond normally, data can be entered and
records created accordingly.
Bill

Ok, that does surprise me, but ok. As long as right after the forms loads,
the first thing you try is clicking on a that check box, and if it works ok,
then it most surprising why trying to modify the box in code would fail. I
suggest you try placing a simple button on the form, and the code behind the
button can go:

me.FieldCheckBox = True

If the above code works when you press the button, then something along the
way in the event code is being missed here. Perhaps you have (or have been)
doing something else after the form loads, and NOT telling me about it. If
the above button code works, then the field is updateable after the form
loads.

No doubt if the query is a multi-table query (and that is a BIG if), then
you generally must write out the parent table FIRST, since then the key id
is created and written to disk. Then, and only then can child records be
added. It is possible that you have some other code that when the forms
finally loads you run. Perhaps you have been by habit editing other things
on the form first BEFORE you click on the check box, and that is causing the
key id to be created . However, based on my above questions, I have been
clearly stating to try the check box before you do anything else on the
form. It has been clearly stated that first thing you try is the check box
when the form loads.

Try placing two buttons on the form right beside that check box. One button
can set the check box to true, and the other button to false. When you try
the button code, does the check box toggle ok? Now trying actually clicking
on the check box, does is again toggle ok?

However, if the query is in fact a multi-table join, then you DO have to
force the parent record to disk before adding (or in your case editing) the
child record fields.

 

[Bill Stanton]

Below is the OnOpen, OnLoad and, just added, the OnClick
for a command button that assigns values to the check-box
controls as you suggested. The OnClick code executes without
error, so there's something strange going on at the time the OnLoad
executes.

Bill


Private Sub Command142_Click()
Me.FamilyDonOnly = True
Me.FamilyNewsLetter = False
End Sub


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize 'Open window maximized
End Sub


Private Sub Form_Load()
DoCmd.MoveSize 1, 1
Me.AddFamilyMembers.Visible = True

If IsNull(Me.OpenArgs) Then 'Not being invoked subordinate
to table form.
Me.NavigationButtons = False
Else 'Being invoked with parameters.
If Me.OpenArgs = "NewEntry" Then Exit Sub

If Me.OpenArgs = "RegisterDonor" Then
Me.NavigationButtons = False
Me.AddFamilyMembers.Visible = False
'Me.FamilyDonOnly = True
'Me.FamilyNewsLetter = False
Exit Sub
End If

'Move to the record specified in open parameter
Me.RecordsetClone.FindFirst "[FamilyID] = " & Me.OpenArgs
Me.Bookmark = Me.RecordsetClone.Bookmark
FamilyName.SetFocus
Me.AllowAdditions = False
Me.AddNewMember.Visible = True

End If

SetImagePath
End Sub

 

[Bill Stanton]

Albert, I forgot to confirm for you that the query is a
simple single table query.
Bill