Suggestion needed, repost

[Fysh]

I posted this yesterday , but didn't get a response. What I am trying to do
is create a form that the user can change whether a category, for a
particular activity, for a particular qual type is required, enabled, and/or
applicable. Then create a query from this table the form edited. Then on
another form using this query have certain check boxes checked, enabled, etc.
Right now there is a lot of coded written on the second form for each
instance of this. I was hoping that I could simplify it for the user for
future use rather than having to go in and edit the form each time there was
a possible change. Is this doable?

I am trying something different here and I was hoping someone might have a
suggestion. What I am attempting to do is create a form from a table related
to other tables as follows.

tblCategory tblActivity tblQualType
CATID ACTIVITYID QUALTYPEID
Category Activity Qual

tblSetup
SetupID
Required yes/no
Enabled yes/no
Applicable Value list
CATID
ACTIVITYID
QUALTYPEID

Now that the tables are set up, I am trying to create a form which will show
all the categories, select an activity and a qual type. Next to each
category have a check box for the required field and one for enabled field
and combo box for applicable.

Once the form is filled in have it create or update the table. Create a
query from this table. On another form when an Activity combo box is
selected have it process the query and update the fields on that form. I
know I have to create the code to accomplish this.

I tried using a subform, but that didn't work. I tried a continuous form
that didn't work.

My question is looking at my tables and my first form is this possible, if
so how? If not how would you suggest doing this? Any support would be
appreciated, thanks in advance.

 

[Tim Ferguson]

I posted this yesterday , but didn't get a response.

I remember this post, and I am afraid I didn't understand the question.

What I am trying
to do is create a form that the user can change whether a category,
for a particular activity, for a particular qual type is required,
enabled, and/or applicable. Then create a query from this table the
form edited. Then on another form using this query have certain check
boxes checked, enabled, etc.

It may be just me, but I cannot make sense of this. Perhaps you could
describe a bit more of _what_ you are trying to do, rather than _how_.

B wishes


Tim F

 

[Fysh]

Thanks Tim for responding. Maybe my post wasn't all that clear. Anyway what
I am trying to do is as follows. I have a form which users use to select
training requirements according to a certain activity (Unit) and
qualification type. It appears each activity and type has different training
requirements (categories). This is hard coded in the form so when a certain
activity is selected along with a certain type then check boxes are checked
and/or enabled.

So what I am attempting to do is create another form with these items so the
user can select an activity, type, category and check the box required and/or
enabled if needed. As mentioned before then I can use a query to update the
first form instead of trying to make changes through code when needed. This
query can be run after they select the activity and type.

This all probably sounds like the same stuff I written, but I don't know how
to make it any clearer. I redid a couple tables many to many activty and
qual type. Then the setup table is a many to many from category and the
combo of the other 2. I think I have the form down, but maybe there could be
a better way. I created a form with all unbound fields and a subform
(continuous) with the categories. These are not linked but rather it pushes a
number into an unbound textbox on the main form.

 

[Tim Ferguson]

Let's take this bit by bit...

Users have

to select
training requirements according to a certain activity (Unit) and
qualification type. It appears each activity and type has different
training requirements (categories).

This is controlled therefore by your SetUp table, although I would have
called it Requirements.. You don't say so but it should have a unique
index on the (Activity, Qualification, Category) fields. This means that
you should be able to query this table for a particular combination of a
candidate's Activity and Qualification and obtain a list of Categories
and whether each of these is Required or not.

I would imagine that this is fairly easy to accomplish in a user
interface, probably with a subform or a listbox. You still have not told
me _what_ you need to do next. I guess that this is something to do with
people applying for accreditation, so you will need another set of tables
to capture the People themselves, the Applications they make to attend
the Categories, and so on.

I don't think I have helped very much so far, but I am still struggling
with your real-life needs.

B Wishes


Tim F

 

[Fysh]

Tim sorry this seems so confusing.
I do have the other tables set up correctly. The question I have is if I
have the ones I posted earlier were set up correctly? The form I am
attempting to design is using a combo box for the activity another for the
qualification type then using a subform with the catgories. Now I got that
part down but should I create another subform for the check boxes required
and enabled? This all should update the setup table.

 

[Tim Ferguson]

Tim sorry this seems so confusing.
I do have the other tables set up correctly.

I remain suspicious about this. Your earlier post talked of "a query
updating a form": queries can only update tables, and when I hear of data
in one table being used to update another table it sounds like a big
design problem. But I don't understand nearly enough of what you are
trying to accomplish to comment further.

The question I have is
if I have the ones I posted earlier were set up correctly?

I just cannot say because I don't know what any of them mean.

The form I
am attempting to design is using a combo box for the activity another
for the qualification type then using a subform with the catgories.
Now I got that part down but should I create another subform for the
check boxes required and enabled?

If what is happening is what I think is happening, then I would probably
use the two combos and a list box to select the tblSetUp record on the
form; and then let Access do the rest.

I think I am going to have to retreat from this because I don't want to
advise you badly. You are probably best off trying to find help locally
who do know what they are doing and can see what you have got so far.
There is only so much that can be done at this sort of distance.

Sorry I can't do more,


Tim F

 

[Fysh]

Tim thanks for your understanding. I realize it is difficult to help
sometimes. Anyway I have got it to work the way I wanted, I hope this clears
it up. What I did was create a form with 2 combo boxes (activity and type) 2
check boxes (required and enabled) and a subform (continuous category
textboxes), on the main form I have 2 hidden text boxes (CATID and
ActQualID). When the activity and type are selected it updates the ActQualID
box with the ID. The same goes for when I select the category from the
subform, it updates the CATID box. Now all of these fields are unbound
including the subform is not related to the main form. This was all tricky.
Anyway If I select an activity or type or category it checks to make sure the
two hidden boxes are not null if not then it calls a function which I created
to do a lookup and updates the 2 check boxes. I also, can check the check
boxes and update the appropriate table using after update event.
Now since this was created and there are at least 400 combinations I created
a query to generate a temp table. On another form which is used to select
training requirements to be evaluated I have several check boxes related to
the categories. After selecting the type of qualification or activity it
checks to make sure the other is not null. If not then it runs a query to
make the temp table. By using some code with a for statement I have it
update the check boxes if they are true and/or enabled.

By doing this I was able to cut down 13 pages of code to 1/2 page. You can
hopefully see now why I wanted to do this. Also, at a later date if the
administrator of the DB wants to change whether a criteria is required or
enabled for a particular activity aand type it is much easier to go to the
form and check the box rather than editing the code.

Anyway I got to work and appreciate your patience. I have been coding for 7
years and sometimes you need a sounding board to bounce off ideas or better
solutions.