Full Version: Grouping Yes/no Fields
UtterAccess Forums > Microsoft® Access > Access Forms
Brendan Adelaide
I am starting on the huge task of rewriting an existing company database. The original version was written over 10 years ago. So I am basing the new version on the orignal and recreating everything from scratch. I have 2 questions.
1. The forms in the original are somehow hidden and locked down. I'm assuming it was written in Access 97, the company who made it are long gone. I've tried everything to find them. Any hints on finding them is appreciated.
2. One of the forms has 6 x Yes/No text boxes. The way it currently works is if the user enters Y in one of the boxes, the other 5 automatically record change to N. Does anone have any clues on how to make this work please?
Many thanks.
Hi Brendan:
. As a start, in the Navigation pane, right click and choose Navigation Options... Then click the box by Show Hidden Objects. See if that shows the objects.
2. One way is to, behind each check box, in the On Click event add an Event Procedure something like:
If Me!CheckBox01 = -1 Then
Me!CheckBox02 = 0
Me!CheckBox03 = 0
End if

You'll have to work this around, using your own check-box names and adding the other boxes, of course - plus doing this for each check-box.
Perhaps others can suggest other things to try, but this might get you started.
Re 2: This should be replaced with an option group with radio buttons.
Hi Brendan,
If you tick mark one and other boxes notes N (no). Suggest that this is option group, aloowing one choice as yes
. That means out of six only one choice could be
I agree about using an option group. If for some reason it is necessary to continue with six separate yes/no fields, you could set the Tag property of each check box to "Z" or whatever you like. Then a sub something like this (untested air code):
Private Sub JustOne()
  Dim ctl As Control
  If Me.ActiveControl.Value = True Then
    For Each ctl in Me.Controls
      If ctl.Tag = "Z" And ctl.Name <> Me.ActiveControl.Name Then
        ctl.Value = False
      End If
    Next ctl
  End If
End Sub

Then in the After Update event of each check box:
Call JustOne()
Again, an option group is better, but if you need to preserve the existing data it will take some updating to translate the six check box values into the single Number field used by the option group. It shouldn't be difficult to do, provided the existing data follow the rule of one "Yes" per record, so if you can do it that way it will make things much tidier.
You can test the data by means of a calculated query field:
TestIt: Abs(Check1 + Check2...etc.)
If the result is other than 1, something may be wrong.
Brendan Adelaide
Thanks for all the suggestions. I do agree that an Option Group is a much better way of handling issue 2.
Oopened the hidden objects but still found nothing. It's a mystery as all the reports are also nowhere to be found. I'm recreating everything anyway from scratch, but it would have been nice to look behind the originals
I'm a little unclear about something. Are you saying you can see forms and/or reports in the original runtime version, but you can't find those objects? How are they being opened in the original? If by something like a command button, what is the underlying code or macro?
Brendan Adelaide
Actually the reports are there but are stored in a folder not inside the database, they were created using Crystal Reports. There is a front end application exe which is only 1.5MB. And there's the .mdb database file with tables and queries. I think it was written in Access 97 as it has that look about it. There is some VBA code (in the attached) that may give a clue
When you post in this forum people are likely to assume Access reports when you mention reports, which may account for some of the puzzlement in this thread <
It sounds as if you have the runtime version of the program, in which case the database is locked down so that you can't see the design view. Is this what is happening. If so, I'm afraid I don't really have a suggestion. This is not to say definitively that there are no options, but only that I don't know one way or the other.
Brendan Adelaide
Further to the issue of the Forms (and Reports) unable to be located. I have had a good look through the VBA and there are 2 lots of VBA objects, modules and class modules in 2 trees: ACWZMAIN and ACWZTOOLS. Judging by the names and icons, all this VBA is what actually creates and makes the Forms function. But, they all come up as "Project Unviewable".
've done some searching and it appears they have something to do with the Wizards and there is a lot of forum posts about how to unlock them, none of which have provided any concrete help.
The database was created in Access 2000 and I am using Access 2007. I have converted the original but still have the 2000 version. Any ideas please on how to unlock the VBA?
If this is truly an exe file it's possible that the Front End was written in something else altogether, like Visual Basic 6, with an Access Back End. And having the Reports done in Crystal Reports kind of suggests that, as well.
Linq ;0)>
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.