Sub Form Help Needed

[Robbyp2001]

Hello folks

I have a form with an embedded subform which is based on a query. What I'd like to do is the following: For each new record I'd like the field [Term] to increment by 1 so that with the default value starting at 1, the next record will show 2 and the next will show 3. After 3 has been reached the value will return to 1 for the next new record and the process starts again.

In addition to this there is another field on the subform [Report] which I would like to populate with 1, the next record 2, the next record 3 and so on. Just 1 then 2 then 3 alternating. Is this possible? I'm sure that if it is, someone on here will know the code.

The sequence should be thus:
“student x” [Term] 1 [Report] 1
"student x” [Term] 1 [Report] 2
“student x” [Term] 1 [Report] 3
“student x” [Term] 2 [Report] 1
“student x” [Term] 2 [Report] 2
“student x” [Term] 2 [Report] 3
“student x” [Term] 3 [Report] 1
“student x” [Term] 3 [Report] 2
“student x” [Term] 3 [Report] 3

If this is possible, I’m sure someone on this forum will know how to do it.

Rob

 

[Robbyp2001]

Thanks for the reply UG. I have looked again at the description and the list and I can't see how they don't match. What am I missing? Apologies if it is not clear.

Anyway, in short: Student x will have 3 terms (Term 1, 2, 3). In each term he will have 3 reports, so 'Term 1' will be...T1-R1, T1-R2, T1-R3. 'Term 2' will be T2-R1, T2-R2, T2-R3 and finally 'Term 3' will be T3-R1, T3-R2, T3-R3.

After this, the sequence should go back to the beginning and start again with each new record.

I hope that this is a bit clearer and please accept my apologies for any confusion.

Rob

 

[Robbyp2001]

Dear Uncle Gizmo, you are quite correct. The main form contains the individual student details including the student ID number (Access generated). Embedded in this form is a subform which contains the student ID, the term number and the report number. The forms are linked by Student ID. The subform and the main form are both based on queries rather than the tables so that in the future unplanned data manipulation should be made easier (sorting, filtering etc). In any case the forms and the underlying queries and tables are working well.

The operator searches for the student ID on the main form and uses the subform to input each report results. This equates to 9 reports over 3 terms per year. The form shows all reports per student as a list (Continuous Form). This part is working well but requires the operator to manually input the Term number and the Report number for each new record. This is not a difficult task in itself, but I am trying to minimise the potential for errors.

The reports are sorted by Term and Report number and there is potential for the wrong report to be generated and printed if there is an error whilst inputting the numbers.


Having the database generate these numbers is now looking far more complicated than I had first envisaged and I am now thinking of making the combination of [Year], [Term] and [Report] unique so that duplication is impossible, with the operator manually inputting the numbers. What do you think?

Regards

Rob

 

[Robbyp2001]

Unfortunately my knowledge of SQL is limited. I do think that adding the records programmatically would be more efficient and safer but given my SQL shortcomings and the complexity of the process it's looking ever more unlikely. I also think that there might be another issue. If the records were added programmatically, what would happen if more than one user were to try to enter a record at the same time? I think it would be rather unlikely that two users would try to add a record to the same Student at the same time, but I suppose it is possible.

I think if I make the combination of [Year], [Term] and [Report] unique and limit the input numbers to drop down lists, thus forcing the user to select either 1 or 2 or 3 with no other numbers possible, this would reduce the risk of error considerably. However, as you correctly pointed out, it will not be 100% risk free.

I have another question. In the table with the term and report field, I have set the new record default to Date() with the format YYYY. This means that every new record created will show the year 2011. The question is this, does the database see this as simply 2011 or does it see the content of this field as the actual date, say 09/10/2011? The reason I ask is that if the database sees the actual full date rather than just the year, this will have serious ramifications for making the combination of fields unique.

Thanks for your comments

Rob