Results 1 to 5 of 5
  1. #1
    Robbyp2001 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    25

    Help with a subform if possible

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Robbyp2001 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    25
    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

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    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.
    Code:
    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
    Last edited by June7; 10-09-2011 at 05:43 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Robbyp2001 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    25
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass Subform filter to subform in report
    By camftm in forum Programming
    Replies: 16
    Last Post: 07-19-2011, 07:12 AM
  2. Replies: 7
    Last Post: 07-15-2011, 01:58 PM
  3. Replies: 1
    Last Post: 03-15-2011, 03:53 PM
  4. Write Code to Navigate from Subform to Subform
    By Swilliams987 in forum Programming
    Replies: 22
    Last Post: 02-04-2011, 11:30 AM
  5. Replies: 15
    Last Post: 11-09-2010, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums