Help with a subform if possible

[Robbyp2001]

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 1 and so on. Just 1 and 2 alternating. Is this possbile? I'm sure that if it is, someone on here will know the code.

The sequence should be thus:
[Term] 1 [Report] 1
[Term] 1 [Report] 2
[Term] 1 [Report] 3
[Term] 2 [Report] 1
[Term] 2 [Report] 2
[Term] 2 [Report] 3
[Term] 3 [Report] 1
[Term] 3 [Report] 2
[Term] 3 [Report] 3



If anyone could help with the code, I'd be very grateful. Keep up the good work!

Rob

[June7]

Sure it's possible, with VBA coding. We can help but you need to provide something to help with. Suggest you attempt code and post for analysis.

[Robbyp2001]

Many thanks for your prompt reply June. Unfortunately I do not understand VBA code, at least not enough to attempt this.

Perhaps I can clarify. There are three fields in my table, [Student ID], [Term] and [Report]. When a new record is created for any student these boxes will be populated by the default value of 1. So the first record will be T=1-R=1. Each successive record for the same student should increment as follows: T=1-R=2, T=1-R=3 then T=2-R=1, T=2-R=2 T=2-R=3 finally T=3-R=1, T=3, R=2, T=3-R=3.

In other words, Term 1 will have 3 reports, Term 2 will have 3 reports and term 3 will have 3 reports. I need the dbase to look at the previous record and update the two fields [Term] and [Report] for each new record until the records reach T=3 and R=3. After this the next record will revert back to T=1-R=1 because this will be a new school year. Then process should start again.

Actually I'm not sure that I have clarified this, maybe the following would be better.

I think this is the sequence: For each new record the following sequence should happen

If [Term]=0 and [Report]=0 then Update [Term] to 1 and [Report] to 1
If [Term]=1 and [Report]=1 then Update [Term] to 1 and [Report] to 2
If [Term]=1 and [Report]=2 then Update [Term] to 1 and [Report] to 3

If [Term]=1 and [Report]=3 then Update [Term] to 2 and [Report] to 1
If [Term]=2 and [Report]=1 then Update [Term] to 2 and [Report] to 2
If [Term]=2 and [Report]=2 then Update [Term] to 2 and [Report] to 3

If [Term]=2 and [Report]=3 then Update [Term] to 3 and [Report] to 1
If [Term]=3 and [Report]=1 then Update [Term] to 3 and [Report] to 2
If [Term]=3 and [Report]=2 then Update [Term] to 3 and [Report] to 3

After this sequence has been completed, the next new record should be
[Term]=1 and [Report]=1 and the sequence starts again.

Unfortunately there is no way that I could convert this sequence to VBA code so any help you can give me would be gratefully received.

Rob

[June7]

VBA is the only way to automate custom generation of unique identifier. I can give you an example from my project. You will have to learn VBA programming to develop procedure tailored for your circumstances. The risk of using code to generate identifiers is that with multiple simultaneous users, it's possible each could generate the same value. In my procedure the new number is created and the record saved immediately. This reduces chance of duplication. Your situation is complicated because you are sequencing two fields but the technique demonstrated in my procedure is still applicable. Search the table for last values and increment. This can be done when the record is initiated as in my procedure or after all data is entered and then generate the IDs and save record.

Public Function NewSample() As String
Dim strLabNum As String
DoCmd.SetWarnings False
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
    DoCmd.RunSQL "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
    strLabNum = Nz(DMax("LabNum", "Submit"), "")
    If strLabNum = "" Then
        'this accommodates very first generated number of blank database
        strLabNum = Year(Date) & "A-0001"
    Else
        'this accommodates change in year
        If Left(strLabNum, 4) = CStr(Year(Date)) Then
            strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
        Else
            strLabNum = Year(Date) & "A-0001"
        End If
    End If
    DoCmd.RunSQL "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
End If
Form_SampleManagement.ctrSampleList.Requery
NewSample = strLabNum
DoCmd.SetWarnings True
End Function

[Robbyp2001]

Thanks very much for your help June. I'll read through this to see what I can understand. Who knows, maybe I'll get it to work. It is interesting what you say about simultanious users causing a problem. There is a small chance that two or more users could enter records at the same time. I'll have a look anyway.

Thanks again

Rob