Need VBA Code for Check Box

[FA]

Hi Freinds,
I have an issue and hoping that anyone can help me. I have two forms 1)

frmSystem and
2)frmDelayReasons. frmSystem has a combo box RSN1 which has four
choice. frmDelayReason has all those same four choices as labels with
the unbound Check Boxes next to each choice . First user will make a
selection on frmSystem on combo Box RSN1 and then click a command
botton on the same form to open the frmDelayReasons. Both forms are
associated with the same table so it will open the frmDelayReason with
the same record.
I want to do something like the following;
When user make a selection from RSN1 Combo Box, and click the command
button to open frmDelayReason, the unbound check boxes next to each
choice(same as Combo Box RSN1 Choices) should be checked according to
the selection he/she made on RSN1 Combo Box on frmSystem.


Please Pals help me out !!!


Moe

 

[Steve Schapel]

Moe,

It is not at all a good idea to have two forms bound to the same table
open at the same time. What is your reason for wanting to do this?

I would assume that the RSN1 combobox is bound to a field in the table
that the form is based on... am I right? It would bew good if you made
this field a Number data type, and set your combobox up so that you can
see the text description of the selection being made in the combobox,
but the bound column in entering a number value between 1 and 4.

Why are the checkboxes on the second form unbound? I can't make sense
of this, since the second form is based on the same table as the first
form, and the data is bound on the first form but unbound on the second?
If you have the data as a number data type, you could use an Option
Group on the second form, with 4 checkboxes within the Option Group, and
then it would automatically show the selection made on the first form.

 

[Ron2005]

Moe,
Since you indicate that in reality all of the info is really ONE
record, then instead of a second form you could simply have all of the
fields on the main form (and maybe boxed for visual clarity) but have
them all NOT visible until the user selects an item from the first
combo. You could then use a case statement to visually set or unset
everything.

I agree with Steve, having the second form is not a particulary good
idea.

If you use this method, you have to make them all not visible as
default, and in the oncurrent for the form set everything off again and
then set based on the case statement (or have the final else of the
case set all as not visible.) Then execute this same logic in the
afterupdate of the combo box.
Ron

 

[FA]

First of all i want to thank you for taking your time to answer my
question.
The comboBox RSN1 on frmSystem is bound to a control RSN1_ID. Since its
a combobox, i made a lookup table tblRSN1 and relate its PK to the main
table called SYS_INFO. The main form is based on SYS_INFO and its
taking only the RSN1_ID. The main table already has too many controls
that its effecting its performance so i can not add any more control.
Here is the more clear picture:
The lookup table tblRSN1 has five choices, user can select one choice
at a time.
RSN1ID RSN1
1 Delays because of System team prior to
testing
2 Delays because of System team during
testing
3 Delays because of Assesment Team
4 Budget not yet received
5 Other

Now each RSN1 has six more detailed choices. User can select one or
many since they are check boxes. I had to make Varchar since i am using
SQL Server DB. If i choose Bit Data type, its doesnt work with MS
Access. All of the following fields are on the same table SYS_INFO.

Out / Sick / Unexpected Events (Resources) Varchar(5)
Schedule conflict (e.g. prior test ran longer or slipped) Varchar(5)
Being reprioritized / Project Cancelled
Varchar(5)
Application/Environment not ready
Varchar(5)
Missing/Incorrect Data
Varchar(5)
Other
Varchar(5)

All the above sixe chocies are on frmDelayReasons as a check boxes.
I want to make five sets of the above six choices for each RSN1 on
frmDelayReasons. Along with sets i want to make a heading of each RSN1
for example (Delays because of System team prior to testing) and beside
this heading a check box and under that heading all the above six
chocies.
When user open the frmSystem and select a choce from ComboRSN1 and
there is a little commandbotton next to the combo, if user click that
button frmDelayReasons popsup and the choice that the user made such as
(Delays because of System team prior to testing) should be prechecked
on frmDelayReason.

I have tried to explain in as detail as possible. You guys have done
alot for me and i request that please help me out on that issue.

I am open to any other segestions.

Thanks

Moe

 

[Ron2005]

OK, It is a little clearer to me. Steve may have another approach, but
here is one:

1) frmSystem shows only RSN1 but the supplying query contains it and
all six of the delayReason fields.

2) frmDelay contains six checkboxes with labels of the six reasons.
a) In the OnCurrent or OnOpen event set the six checkboxes based on
the logic:
if forms![frmsystem]![delayreason1] = "whatever you have
put there to say yes" then
me.chkReason1 = true
else
me.chkReason1 = false
endif
same type of logic for all six
The tests can be made in the other direction with isnull
depending on the default value.

b) in the afterupdate of each check box
if me.chkReason1 then
forms![frmsystem]![delayreason1] = "whatever you
have put there to say yes"
else
forms![frmsystem]![delayreason1] = null
endif
same for chkReason2 etc.


Also a suggestion:
on the frmsystem in the onchange event for the RSN1 then set all
six of the delareasons to null since you really don't know if the sub
reason should remain the same since they are changing the main reason.

Footnote: I have worked on SQL server db and it works fine as long as a
default value of 0 was set up for the field. Otherwise, it tends to get
confusing.

Footnote # 2: This method says that this popup can ONLY be used for
this one form since the form name is part of the logic. If it ends up
that you want to use it for other forms then you may want to use a
permanently hidden form to pass the information back and forth or a
number of other equivalent methods.

Hope this isn't more confusing.

Steve, you may have even a slicker way, but I believe that this will
work.

Ron

 

[Steve Schapel]

Well, my "slicker" suggestion was already made in my earlier post... On
the frmDelayReason form, instead of the unbound checkboxes, put an
Option Group bound to the RSN1_ID field. Should be no need for any code.

I would also say, Moe, that having too many fields is very unlikely to
noticably affect performance, so there must be something else going on
here. Also, having a lot of controls is not a good reason for opening a
second form - maybe you could look into using a Tab Control if the "real
estate" of the form is an issue. And finally, if I understand you
correctly, you have 6 "sub-reason" fields for each main reason, but
there will only ever be one main reason selected, so there are a vast
numbewr of fields that are redundant at any given time, which is not a
good design. I would recommend using a second table for the subreasons,
and manage them via a subform.

 

[Ron2005]

And the advantage of Steve's suggestion is that when you end up going
to 7 sub reasons, there is no need to change anything except adding it
to a table to be selected. However I think you were thinking of
allowing multiple selections for subreason ("User can select one or
many since they are check boxes.") and that would make this other
approach difficult.
Ron

 

[FA]

Thanks Ron It really helped and i implemented your approach and its
working fine now. Thanks millions.
Also i want to Thanks Steve for helping me. I have all you guys
suggestions noted down and i will make appropriate changes accordingly.

Also i have a quick question.
Have any of you guys ran into a message from Microsoft Access saying
"Microsoft Access has encountered a problem, Sorry for the
inconvinience, Repair Database, Send Report or Dont Send Report"

Its been happening to me on one of my for that has a subform. that form
only contgain a combobox that is populating the fields in the subform.
but when i choose a record in the combo box, that message apears. I
dont know whats the problem. i have done my debuging and recheck all
the codes but still doing it. So if you guys ran into problem like this
before in your developed applications, please do let me know what could
be the cause of it.

Thanks Millionss

Moe

 

[Ron2005]

First suggestion: do a compact and repair on the application and if
your tables are linked in, then do the same on the table mdb(s).

If that does not solve the problem, then probably the easiest solution
is to remake subform from scratch. Sounds as is something is corruped.

Ron