Continues form with "more details" section.

[djkay2637]

Good afternoon all.

Here is my question.

I have a query with 60 fields retrieved. Ideally I would like to see all 60 columns for every person in a continuous form however this is too unmanageable and besides, the access has a width limit of 22inches. I did think about using a tabbed form however my users need to see an overview snapshot of all people so therefor I need a continuous form. So as a work around I would like to see just 15 fields (these are tiny and I only need to see 1 character but this will be enough for the user to see that they may need to click on it for more details) and when the user clicks on one of these 15, I would like to see three other fields that I can’t ‘fit’ next to the others in a ‘more info’ section inside the footer. This additional information will only be related to one individual based on which item / person has been clicked on.

My question is, how could I get the text boxes in the footer to display the corresponding values? Thank you in advance and I hope this explanation of the problem has been surfactant.

Regards
Dan

 

[plog]

This sounded off a lot of alarm bells. I'm very suspect of your table structure and I am questioning why you are using a form to display data, instead of a report?

1. 60 fields, even in a query, is a sign that your data isn't normalized properly. Usually you should have more rows, not more columns.

2. When you say you want to display more fields related to other fields this supports my suspicion that your tables aren't proper. If those fields are indeed related, they most likely need to go in a different table.

3. Forms are generaly for interacting with data--add/edit/delete. When you want to display data, you instead use a report.

4. The best solution, especially if I am wrong about your structure, might be just to ship this data to Excel and let the end user view it there.

 

[djkay2637]

OK I will try and be a bit more specific. There are 15 tasks within a course. For each task I need to see who marked it, the grade and when it was last marked. On the main master form you will just see the task number and it will contain a simple code only. When the user clicks on the task code for a specific learner I want it to show the other information (date, status ect) That doesn't need to be on the main overview.

My reasoning for it being in a continuous form as apposed to a report is the fact I want a 'lead' teacher to be able to allocate markers to a specific learner meaning it needs to be editable. My rational for it being in a database as apposed to a spreadsheet is because spreadsheets quickly become old. When markers mark a learners' piece of work they will click a button on another form that only relates to that learner ( less work for them) Scrolling through another system / spreadsheet will only make the process longer. With it being a totally working system the moment I remove data onto a spreadsheet I need a mechanism for ensuring new learners get added and so on...

Do you get my rationale?

Thanks
Dan

 

[plog]

I sort of get your rationale, but you haven't allayed my improper structure fears. Can you post a screenshot of your relationship view? Or post your database so I can see the structure?

As for forms that do interact with data, you want them based on a table, not a query. One form--one table. Since you are marking at the lowest level (tasks), then you would need a form based on your task table. Now, how you drill down to a specific task can be achieved a few ways--either by chosing student then class then task, or by class then student then task.

 

[djkay2637]

I have a main tabbed form of which the data is selected by the query as i have many different search parameters / criteria avaialbe on screen or inside the relevant tab. The user identifies the learner in question and chooses the tab where the marker can check off what has been marked or passed. It automatically inserts the correct teacher code and date and inputs it into the relevant subform. All this works fine and dandy. There are subforms in different tabs depending on the users intentions.

Whilst in an ideal world forms will directly interact with the table this can create limitations when using relational fields. This is why all my forms derive from a query source as the user does not have to perform a new/different query each time.

In response to your main question, it is learner: task but the master overview form is needed to see all learners and all tasks with the ability to amend the data in real time without having get more windows open.

 

[plog]

You really need to focus on your tables before you work on your forms. Forms are actually last in the process-->Tables, Reports, Forms.

You have a major issue with CAVAIQATbl--you have used numerated field names (e.g. U1_T1, U1_T2, U1_T3...). When you feel the need to do that, it is instead time for a new table. Then instead of a column for each value, you would have a row for each value. This data:

StudentID, Test1, Test2, Test3
1, 91, 88, 74
2, 72, 81, 90

Should be stored like so:

StudentID, TestNum, TestScore
1, 1, 91
1, 2, 88
1, 3, 74
2, 1, 72
2, 2, 81
2, 3, 90

You need to get CAVAIQATbl into that structure.

 

[djkay2637]

I am so nearly there. I just need a bit of code that will replace the value of an unbound text box with a value from a different field of the current record. THis code is then run by a oncurrent event.
Just stuck!....

 

[djkay2637]

I have tried the dlookup feature using the code

dlookup("[TheFieldIWantToFind]", "TheQryItIsIn", "[ID] = me.currentRecord")

I am expect it to return the value in [TheFieldIWantToFind] when the currentrecord number = the Current ID thus only returning one result.

Do you think i should be using a SQL select instead? I am quite a novice at writing code / SQL commands.

 

[plog]

I am so nearly there.

If by 'there' you mean overcoming the current issue, then yes. However with your structure you will be facing more of these issues. You are treating a symptom of a larger problem.

with a value from a different field of the current record

If you are on the current record, then you have access to all the data in that record. There would be no need for a DLookup. Perhaps some sort of hidden field sytem would be better. You load all the data to the form and hide/unhide the appropriate box/area.