locked
Create 12 rows for Subform RRS feed

  • Question

  • Dear all,

    I want to create one record for hourly output. on main form i have some info like shift, crew, production date, machine. When I select shift 01 and press Load button I will have 12 rows, first field of each rows will add instant time like: 6:00 - 7:00; 7:00 - 8:00,...until 17:00 - 18:00. If i select shift 02 the time will change like: 18:00 - 19:00, 19:00 - 20:00,.. till 05:00- 6:00. 

    Field1
    6:00 - 7:00
    7:00 - 8:00
    17:00 - 18:00

    Can you help me how to build VBA code to do that. Thank you in advance

    Saturday, August 15, 2020 5:01 PM

All replies

  • First create a table named ShiftHours with columns ShiftNumber and ShiftHour.  Fill it like this:

    ShiftNumber         ShiftHour
    01                        6:00 - 7:00
    01                        7:00 - 8-00
    etc…
    02                        18:00 - 19:00
    02                        19:00 - 20:00
    etc…

    To insert rows into the subform the code would be like this:

        Dim strSQL AS String

        strSQL = "INSERT INTO NameOfSubFormsTable(ShiftID, ShiftHour) " & _
            "SELECT " & Me.Shift ID & " ShiftHour FROM ShiftHours " & _
            "WHERE ShiftNumber  = """ & Me.ShiftNumber & """"

        CurrentDb.Execute strSQL, dbFailOnError
        Me.[NameOfSubformControl].Requery

    This assumes ShiftID is the primary key of the parent form and the corresponding foreign key in the subform's table, and is a number data type.  It also assumes that ShiftNumber is a text data type to accommodate the leading zero.  NameOfSubformControl should be the name of the subform control in the parent form's Controls collection which houses the subform.  It might or might not be the same as the name of its source form object.

    If the subform's table is already named ShiftHours, you'd need to use a different name for the new table of course.

    Ken Sheridan, Stafford, England

    Saturday, August 15, 2020 5:58 PM
  • Thank Ken

    Here is my database, i try as your instruction but not yet OK. can you help me to more understand your code. 

    https://drive.google.com/file/d/1_iedUduQkQBDhMmvpQUQDuJNtwvLQUEO/view?usp=sharing

    Thanks

    Nghi Trinh

    Sunday, August 16, 2020 7:45 AM
  • And when press "Load" Time field will fill Hour from table and of course each row we also have other value like ProdDate, shift, crew, machine like Header has
    Sunday, August 16, 2020 10:08 AM
  • It is much simpler and faster to use the RecordsetClone of the subform, a shift table is not needed, and the subform will update instantly.

    Run this code from your Load button:

    Private Sub Command5_Click()
    
        Const StartShift1   As Date = #6:00:00 AM#
        Const StartShift2   As Date = #6:00:00 PM#
        Const HourCount     As Integer = 12
    
        Dim Records     As DAO.Recordset
        
        Dim HourStart   As Date
        Dim Index       As Integer
        
        Set Records = Me!FrmSubHourlyOutput.Form.RecordsetClone
        If Records.RecordCount < 2 * HourCount Then
            If Me!Shift.Value = 1 Then
                HourStart = StartShift1
            Else
                HourStart = StartShift2
            End If
            For Index = 1 To HourCount
                Records.AddNew
                    Records!Time.Value = Format(DateAdd("h", Index - 1, HourStart), "hh\:nn") & " - " & Format(DateAdd("h", Index, HourStart), "hh\:nn")
                    Records!ProdDate.Value = DateValue(Me!ProdDate.Value + DateAdd("h", Index - 1, HourStart))
                    Records!Crew.Value = Me!Crew.Value
                    Records!Shit.Value = Me!Shift.Value
                    Records!Machine.Value = Me!Machine.Value
                Records.Update
            Next
        End If
        Records.Close
    
    End Sub


    Gustav Brock

    Monday, August 17, 2020 8:19 AM