Option in opt group not selected

D

Donna

I've got a form that is bound to a query. This form is a follow-up for a
work order. I have placed an option group on the form, with 5 options, to
determine the final status of the work order. I want to make sure one of the
options is selected before the user moves to another record. I can't seem to
find the right way to evaluate the option group. It is bound to a number
field.

I have tried using the After Update event but that does not work unless I
actually click on an option. There is only one other stop after leaving the
option group, to a text box that is used for notes, that seldom will have
anything typed in it. I've tried using the BeforeUpdate event on this text
box to see if the option group is empty and I can not get that work either.

I've spent hours on this, reading all I can get my hands on, and still have
not found the right way to assess the option group.

I want to make sure there is a completion date entered and a final status
option checked before leaving the record. Also, is it possible to stay on
this record and move the focus back to a combo box? I have a combo box for
the user to select the work order number. If the user tabs through the
controls, it goes to the next record. The next record may not be the same
thing listed in the combo box because it is not bound to the form. Once a
selection is made from the combo box, the record matches with the selection,
but it sure could cause some problems. It would be great if it would not go
to the next record when it gets to the end of the form. I don't know how to
handle that situation.

I'm really pressed for time also. I've spent 2 full days trying to figure it
out myself, but I'm not having any luck. I sure could use some help.

Another dilema I have with that combo box.... I have placed a really useful
filtering combo box in the form's header. I used the ValueList for the Row
Source Type in this combo box and have placed the text for All, Today,
Yesterday, Past 7 days and past 14 days as the values and have a macro set
up for them. Afterwards, I used the menu item to convert the macro to VBA
and now it's in the VBA code for the form instead of the macro. That's
really handy. As useful as the filter is, it still leaves the form
vulnerable to getting on the wrong record. As a precaution, I have included
the work order number next to the combo box, with a label that says You are
on record: work order #. If they don't match, the user gets a message. I'd
still like the combo list that is showing the work order numbers to only
show the filtered ones. Again, I don't know how to do that.

I'm primarily concerned with the option group have a selection as long as
there is a completion date. Again, I am revisiting an existing record, so
setting the validation at the table level would create an error message.

I've tried using:
if me.optgrpFinalStatus = 0 then msgbox "check one" (condensed version)

How do I look at the date? Its easier if its null, how do I refer to the
value thats in it?

If there is a completion date and the final status option has not been
selected, then tell the user to check an option or cancel.

And also, when the user gets to the end of the record, don't go to the next
record, but return to the combo box.

Sorry for the lengthy message... my brain is really tired and confused

Donna
 
W

Wayne Morgan

One possibility is that once you select an option, you can't unselect all of
them (at least if you're using radio buttons). So, if you give the option
group a Default Value, then one of the options will already be selected when
you go to a new record.

Another possibility is in the form's BeforeUpdate event. This will run
before the record is stored. Check the value of the option group and Cancel
the update if it's not what you want.

Example:
If IsNull(Me.optgrpFinalStatus) Then
MsgBox "You must select an option!", vbExclamation + vbOkOnly
Cancel = True
Exit Sub
End If
 
D

Donna

Thanks for the quick reply. I tried the form's BeforeUpdate code using the If
IsNull(optgrp) then msg. It just doesn't work. If a selection were made, it
will look at that, but if the user completly skips the option group, it does
not trigger the event. However, its a new day, I got some sleep, and I have
found a way to get this to work. This is what I have tested:

At the Table level, I will set the default value of the FinalStatus field to
6. (I will no longer be able to check this field for null values, so will
have to modify my queries.)

On the form, I will add the field FinalStatus as a textbox, visible set to
No.
In the form's BeforeUpdate I will look to see if txtFinalStatus = 6 and if
so, do the msgbox.

I like your suggestion to use the Form's BeforeUpdate event. This will
prevent the user from going to another record in this instance.

After the required fields are acceptable, is it possible to stay on that
record and NOT advance to the next record? I would rather select the record
to go to from the combo box.

Thanks so very much! Donna
 
W

Wayne Morgan

You're right, skipping the Option Group won't trigger the event. The event
will be triggered when the user tries to save the record, then it won't let
them until a selection has been made in the option group. Now, if the user
never does anything to make the record "dirty", then there will be nothing
to save and the BeforeUpdate won't run, but as I said, there isn't anything
being saved either. If you went to an old record that the option group
hadn't been filled in for and them moved to the next record without doing
anything, then you're right, nothing happened. This won't fix the old
records that already exist. For that you need to run an update query or have
someone go through with the form filtered to only show records where the
option group is Null and manually fix them. I see you've set a default
value, that was one of the suggestions. Of course, with a default value set,
as you've noted, the option group won't ever be Null except, again, for
records that already exist where it is Null.

If the required fields are acceptable but you want to stay on the current
record, it is possible. If the only way you want the user to go to a new
record is to use the combo box, you'll need to do a few things. 1) Set the
Record Selectors property of the form to No. 2) Set the form's Key Preview
to Yes and trap the Page Up and Page Down keys. I haven't tried this, so I'm
assuming this would work. If not, using the Autokeys macro to reassign those
keys may work. 3) Get Stephen Lebans's mouse wheel program
(http://www.lebans.com/mousewheelonoff.htm). 4) The record should still get
saved when you close the form or use the combo box to move to another
record; however, if you want to add a Save button, the code would be (If
Me.Dirty Then Me.Dirty = False). 5) Remove the Records menu and any other
item that would let them search for or filter the records.

I'll admit, I may not have fully understood what you're asking for here. If
all you want is a way to save the record without moving to the next record,
then #4 by itself will do it. You could enable the button in the form's
Dirty event and disable the button in the form's Undo event and Current
event. Also disable it again in the button's Click event. This way the
button will only be available if the record is dirty.
 
D

Donna

I will try to give a brief example of what I mean about not moving to the
next record automatically. There are 200 work orders, numbered 1-200. Some
of the jobs have been completed and the paperwork turned in. The user gets
the stack of paper, not in any paticular order. The user has record number
10, 15, 56, 89, 23, 118. When the user opens up the form, they select record
10 and go thru the form filling it out.On the last field, they tab, and it
goes to record 11. The user needs to go to record 15. The only way the user
knows he is on record 11 at this point is because I've put the record number
on the form with the message "you are on record:". It would be real easy to
enter data on the wrong record. I want to eliminate this potential problem,
but unsure how. This is why I would like to remain on the current record
until I make another selection from the combolist for the work orders. The
user would stay on record 10, focus would go back to the combo box and he
would select record 15.

One of the things I've done on the form, is set a Requery on the forms after
update so that it refreshes the list and form before going to the next
record.

Question: The option group, why won't it return a value of null when it has
not been dirtied? It has a default value? I can not get any action to work.
I focused all my attention on looking at the option group itself. It it
weren't bound to an underlying field, would the action (msgbox) trigger if
it was skipped over? It doesn't appear to notice. I tried looking at the
values of each optionbox to see if they were null and I got a big error
message. ha It finally dawned on me that I needed to evaluate the condition
of the underlying field value. I don't know why it took me so long to figure
that out.... must have been the lack of sleep.

I saw the mousewheel program but did not look in to it. I will take a look
at it now. Thanks for your suggestions. Have a good day, Donna
 
W

Wayne Morgan

Open the form in design mode. Open the form's Properties dialog. On the
Other tab set Cycle to Current Record. However, if you Requery the form, it
will go back to record 1 after the requery. To return to the current record
after the requery, you'll first need to get the value from the unique ID
field for the record, requery, then move to that record again using the
unique ID value as the search value.

There is no way to know that the option box has been "skipped over" except
by checking its value before saving. If you tab to the option box and leave
it without making a selection, you can check for that, but "skipped over"
(i.e. bypassed) won't trigger anything. Yes, if the option box has a default
value, then it won't be Null. Also, be sure to check the value of the option
box, not the value of the objects in the option box. Checking the value of
the field the option box is bound to will do this also.
 
D

Donna

Hello Wayne,
I have not had a chance to work with the database much since I last wrote. I
tried the Current Record setting and I got an error, because I have the
requery set to go in the form's after update. I have some remodeling to do,
and error trapping. I would like to elaborate more, show the error message I
get, but I am pressed for time and will have to get back to you Friday. I
wanted to post this so you would know I have another question. :) Thanks so
much! I also posted another question in regard to another db problem I've
been having. Thanks Wayne, hope you don't mind following this thread.
Donna
 
Top