Use an entry from a different table in a form 1

[ChrisTheAncient]

I've been going round in circles until I'm not sure what I've done and tried. My brain cell has just imploded.

And I know the answer must be simple!!! That just makes it worse. Somehow I can't seem to find a similar problem in the FAQ's or search.

Anyway, the problem...

I'm making a 'progress through pre-defined tasks' type of database in Access XP - but will need to be able to convert it down to A97.

I have a very simple pair of tables...

tblProgressTask... that offers a series of pre-defined tasks (up to a set maximum of 25) applicable to all people in the database. Depending on the individual user of the database, not all tasks may be required.

tblProgress... that gives a 'completed' check and 'score' option for each Task on a person-by-person basis. This table, in turn, is linked to the main tblCustomers to provide the scores associated to each individual. If there is no pre-defined task because the full 25 are not needed, the task should not be displayed in the associated frmProgress.

In the frmProgress, I just cannot seem to find a way of displaying the Tasks from the tblTasks (or associated frmTasks) and make them as a label (that can be hidden if not used) for the 'complete' and 'score' fields.

I have been working on the basis that I imagine that the sub Form_Open() checks to see if there is indeed a need to display the label or not by using xxx.visible = false in the code. Unsuccessfully!

I did try a system of making a single line list box giving only one task as a label and that displayed beautifully. The only trouble is, I couldn't find a way of making that label invisible if there was no data in that table row.

My brain cell has now gone into total implosion and formed a black hole in my cranium and I'm admitting defeat on a simple question.

Can anyone save the sanity of me, and thus the wife and cat?

TIA

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

Trying again!

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[willir]

Chris

cannot seem to find a way of displaying the Tasks from the tblTasks (or associated frmTasks) and make them as a label (that can be hidden if not used) for the 'complete' and 'score' fields.

I guess this is how you preceive your problem. But I am trying to follow the "data flow" of your table design. The reason an understanding the data flow is required is because if the design is wrong, then having the information displayed properly would be like pulling teeth.

I am guessing that you are trying to explain a many-to-many (M:M) relationship. One task can have many people, and one person can comple many tasks. This M:M relationship is captured by your tblProgress table which also captures status (complete / incomplete) and score.

Your tblProgressTask describes the task.

offers a series of pre-defined tasks (up to a set maximum of 25 ..... Depending on the individual user of the database

These descriptions confuses me somewhat. How do you pre-define the tasks, and determine who does what? Does a task include a bunch of sub-tasks?

What I would consider doing would be to have profiles for the tasks. In other words, rules.

...Moving on

checks to see if there is indeed a need to display the label or not by using xxx.visible = false in the code. Unsuccessfully!

I think your problem is that you can not perform the proper test; the lack of a proper test, may prevent you from controlling the form.

Next, are you generating the tblProgress records before trying to controll the visible factor, or after?

My approach, if I understand your requirements correctly would be to...

tblTaskMaster
The "header" or "control" record.
TaskMasterID - primary key

tblTask
The specific tasks
TaskID - primary key
TaskMasterID - foreign key to TaskMaster, all tasks associated with the "master" record would have the same ID.

tblTaskProfile
PersonID - foreign key to your person table
TaskMasterID - foreign key to TaskMaster

This table would be used to generate your tblProgress records.

Now for one last problem.

need to display the label or not by using xxx.visible = false

Are you sayig your form is hard coded for each task? And you are trying to make the control visible if the score is present?

You could do this, but you already have the tasks, progress and contact info to display this information in a contineous form.

If you dont want to use the contineous form approach, and use what seems to be a hard-coded form.

Assumptions
PersonID is the field name on the Progress table that is linked to the Person table, and is numeric.

TaskID is also a field on the Progress table that links a task, a person and the respective score. I am assuming TaskID is numeric.

Me.YourControl.Visible = DLookUp("[AProgressFld]", "tblProgress", "[ContactID] = " & Me.ContactID & " and [TaskID] = " & Me.TaskID)

Basically, if the DLookUp finds a progress record, it displays the control. If DLookUp fails, the value of DLookUp is 0 or false.

If you want to peform a series of steps as well as control the visible property of the form, you could use...

If (DLookUp("[AProgressFld]", "tblProgress", "[ContactID] = " & Me.ContactID & " and [TaskID] = " & Me.TaskID)) Then

     Me.YourControl.Visible = True
     ' more code
Else
     Me.YourControl.Visible = False
End if

I took a lot of guesses here. Hopefully some of this convaluted explanation helped.

Richard

 

[ChrisTheAncient]

Hi Willir

Thanks for coming back.

Your reply looks very detailed and needs time for me to think about - and I'm short of time until later today (UK time). I'm very appreciative that you have given the problem so much time and thought and it needs an appropriate amount of time to read and digest (especially with limited brain power like mine!). I shall have a really good study later today.

You seem to have given me a lot of food for thought!

It may (probably) looks as though a serious redesign would be a sensible answer - both of the database and my question! - and I shall feed back later... however things work out.

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

Back again...

willir... Now had a chance to really read and inwardly digest all your wisdom. It's great and now reinforced my thoughts of today (that you started) that I'm going around this in a most 'cack-handed' way. Yup, you're right! I was trying to do a M-M type of thing - but in an even more clumsy method than I was realising!

I did once use a similar thing in another database where all the information of the required tasks was included in the main tasks/progress table (rather than splitting it) along with completion checks and score options.

As all people in the database are on the same set of tasks, I shall include the tasks in the main tasks/progress table again and use a form that allows the tasks (only) to be set up in that table. Then when the progress form is called up I shall make it that the tasks cannot be changed edited there - either by accident or design!

However, I would not have got to this stage without your help in clarifying my mind as to what I was trying, wanting and needing to do - so a star! Your efforts were not wasted and I thoroughly appreciate them.

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************