Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012

    Unanswered: How to manage a variable (resizeable) subform in Access

    Hi all,

    I have the followign problem:

    I have parts that can have differents counters and I need to manage a form with a subform where in the form I have general data of a part and in the subform I want to display a kind of table with data related to counters definitions and in the cell the values that the counters have at different takes of values in the time.

    I have defined these tables:
    1) Tab_Parts(p_id, p_nr, p_name, p_location, etc...)
    2) Tab_Part_Counters(pc_id, pc_p_id, pc_name, pc_typ, etc...)
    3) Tab_Read_Parts(rp_id, rp_p_id, rp_date, rp_status, etc...)
    4) Tab_Read_Part_Counters(rpc_id, rpc_rp_id, rpc_p_ic, rpc_pc_id, rpc_value, rpc_comment, etc...)

    Tab_Parts (PK: P_Id) is the link with Tab_Part_Counters (FK: pc_p_id)
    Tab_Parts (PK: P_Id) is also the link with Tab_Read_Parts (FK: rp_p_id)
    Tab_Part_Counter (PK: PC_Id) is the link with Tab_Read_Part_Counters (FK: rpc_pc_id)
    Tab_Read_Parts (PK: rp_id) is the link with Tab_Read_Part_Counters (FK: rpc_rp_id)

    How I have to manage the form and subform setting so that when I move to the next part the subform change and display in a title row the pc_name and pc_typ as label and in the detail all the rows registered with the differents Read_Parts related to the Part_id and in the grid cells the differents rcp_value for each counter?

    If the part has 4 counters and I have made 10 reads I should have a subform with the first two colummns displaying the rp_date and rp_status and the next 4 columns the values of the 4 counters. The grid should display the 10 rows...

    If the next part has 7 counters and I have made 25 reads I should have a subform with the first two colummns displaying the rp_date and rp_status and the next 7 columns the values of the 7 counters. The grid should display 25 rows... or display the first 10 rows and activate a scrollbar to see also the rest of the values.

    To register new data I wanto to have also in the Form as much EntryFields as are necessary to enter the rp_date and rp_status and the n counters values.

    I have added a couples of screenshot to specify what I mean...
    I'm able to display and hide elements in the primary form and I need to understand how to modify the subform so that on changing the part_id then the relatives counters (3, 4, 5 or more counters can be possible) and all Reads made at differents times for the part_id should be displayed...

    To be more explicit I have added 2 other screenshots.
    In the first one you can see sample data available in tables
    In the second one yo can see an Excel-sheet with the result that I want to realize in the subForm. The 2 empty supplementary columns in Excel should demonstrate that if I have more or less that 4 Counters in a Parts, then more or less Columns should be visible in the subForm.

    Is the DB design correct or not? Can I do something differents?

    Any suggestions how to synchronize and display the two forms and manage the data for new Entry and Modify existing one?

    Kind Regards
    Attached Thumbnails Attached Thumbnails Reads-Parts-Detail_Frm.jpg   Reads-Parts-Detail_Part_id_1-Sel_Frm.jpg   Reads-Parts-Detail_Part_id_2-Sel_Frm.jpg   Reads-Parts-Detail_Part_id_3-Sel_Frm.jpg   PartsCountersReads_ER.jpg  

    Reads-Parts-DemoDataInTables.jpg   Reads-Parts-WishDataInForms.jpg  
    Last edited by Gio56; 08-11-12 at 12:24.

  2. #2
    Join Date
    Aug 2012
    After 2 weeks and more than 120 visit still no one answer or suggestion...
    Why? Just because I don't post any concret rows of code or what?

    I have tryed to do this steps to solve my problem and now I have a solution that work, but I would appreciate a suggestion to do it better or to do it in the right best way.

    I also realized that a PivotView should be also a possible solution, but I don't want to see the filters, rows and columns selectors at all.

    I just need to display in the subform in a dynamic grid only the data of the differents reads of one part at time.

    To add new Reads data into the tables I will use the EntryFields in the MainForm and save it with a cmdButton after doing some validations tests.

    Here my code that display the data in the subform using the Exit-Event
    on a EntryField when the PartId value is entered or modified.
    Private Sub TF_PartId_Exit(cancel As Integer)
    Dim db As Database
    Dim rs As Recordset
    Dim SQLstr As String
    Dim strFilter As String
    Dim lngPrtId As Long

    'Check if a PartId is in the TextField, if null set to 0
    If Me.TF_PartId.Value <> "" Then
    strFilter = "rp_p_id = " & TF_PartId.Value
    lngPrtId = TF_PartId.Value
    strFilter = "rp_p_id = 0"
    lngPrtId = 0
    End If

    'Call a sub-Routine that fill a Temp-Table with the CrossTable Data
    Call Me.FU_ReadParts_UF.Form.ShowNewGridData(lngPrtId)

    Me.FU_ReadParts_UF.Form.Filter = strFilter
    Me.FU_ReadParts_UF.Form.FilterOn = True

    End Sub

    Here the subroutine to collect the data from the differents tables
    and put it into a TmpDataView Table that can accept data for 20
    Sub ShowNewGridData(lngPrtId As Long)
    Dim db As Database
    Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset, rs4 As Recordset
    Dim SQLStr1 As String, SQLStr2 As String, SQLStr3 As String, SQLStr4 As String
    Dim strFilter As String
    Dim i As Integer, j As Integer
    'Dim lngPrtId As Long, lngRdPrtId As Long
    Dim lngAnzSpalten As Long, lngAnzZeilen As Long
    Dim frm As Form
    Dim tmpTblZeilen As Long
    'Dim fld As Field

    'lngPrtId = 1
    'lngRdPrtId = 3
    'Set frm = Forms![frm_Rpc_Values_Dataview]

    Set db = CurrentDb()
    SQLStr1 = "SELECT * FROM Tab_Part_Counters " & _
    "WHERE pc_p_id = " & lngPrtId & ";"

    Set rs1 = db.OpenRecordset(SQLStr1, dbOpenDynaset)
    lngAnzSpalten = rs1.RecordCount
    'frm.TF_AnzSpalten.Value = lngAnzSpalten

    SQLStr2 = "SELECT * FROM Tab_Read_Parts " & _
    "WHERE rp_p_id = " & lngPrtId & ";"

    Set rs2 = db.OpenRecordset(SQLStr2, dbOpenDynaset)
    lngAnzZeilen = rs2.RecordCount
    'frm.TF_AnzZeilen.Value = lngAnzZeilen

    SQLStr3 = "SELECT r1.rp_id, r1.rp_p_id, r1.rp_date, r2.rpc_pc_id, " & _
    " left(r3.pc_name, 2) as txt_nr, r3.pc_name, r2.rpc_value" & _
    " FROM Tab_Read_Parts as r1, Tab_Read_Part_Counters as r2, " & _
    " Tab_Part_Counters as r3 " & _
    " WHERE r1.rp_p_id = r2.rpc_p_id" & _
    " AND r2.rpc_pc_id = r3.pc_id" & _
    " AND r1.rp_id = r2.rpc_rp_id" & _
    " AND r1.rp_p_id = " & lngPrtId & _
    " ORDER BY r1.rp_id, r3.pc_id;"

    Set rs3 = db.OpenRecordset(SQLStr3, dbOpenDynaset)

    ReDim gmArray(lngAnzZeilen - 1, lngAnzSpalten - 1, 6)

    SQLStr4 = "SELECT count(*) as AnzZeilen FROM tmpDataView;"
    Set rs4 = db.OpenRecordset(SQLStr4, dbOpenDynaset)
    tmpTblZeilen = rs4!AnzZeilen

    'Tabellenzugriff auf tmpDataView öffnen
    Set rs4 = db.OpenRecordset("tmpDataView", dbOpenDynaset)

    If tmpTblZeilen > 0 Then

    While Not rs4.EOF

    End If

    ' Neue Zeile in TmpDataView Tabelle einfügen
    For j = 0 To lngAnzZeilen - 1

    rs4!rp_id = rs2!rp_id
    rs4!rp_p_id = rs2!rp_p_id
    rs4!rp_date = rs2!rp_date

    For i = 0 To lngAnzSpalten - 1
    gmArray(j, i, 0) = rs3!rp_id
    gmArray(j, i, 1) = rs3!rp_p_id
    gmArray(j, i, 2) = rs3!rp_date
    gmArray(j, i, 3) = rs3!rpc_pc_id
    gmArray(j, i, 5) = rs3!txt_nr
    gmArray(j, i, 5) = rs3!pc_name
    gmArray(j, i, 6) = rs3!rpc_value

    rs4.Fields("TxtPcId_" & (i + 1)) = rs3!rpc_pc_id
    rs4.Fields("TxtPcLbl_" & (i + 1)) = rs3!txt_nr
    rs4.Fields("TxtPcVal_" & (i + 1)) = rs3!rpc_value

    Next i

    Next j

    End Sub

    I have added 3 screenshots with differents data view of 3 Parts with differents counters and reads... This rows header are at the moment not optimal but should look like the Header of the EntryFields in MainForm.
    The Subform should also display only so much columns that reflect the number of Counters in a Part.

    Thanks for any suggestion on PivotView or in a better solution by using
    directly the array as recordset without the necessity to save the data into
    a temp table first and then display it.

    Kind regards
    Attached Thumbnails Attached Thumbnails MainAndSubForm_4r_4c.jpg   MainAndSubForm_3r_3c.jpg   MainAndSubForm_4r_5c.jpg  
    Last edited by Gio56; 08-22-12 at 08:03. Reason: adding screenshots

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    The easiest way I know to manage a variable subform in Access consist in having several subforms and switch to the appropriate one according to the parent's data, using the SourceObject property of the SubForm/SubReport control of the parent. Other solutions imply the use of dynamic and/or "adaptative" forms.

    However, I'm not sure that this is applicable to the situation you describe. As far as I can understand from the screenshots you posted, the number of controls in the parent form seems to vary too. This is confusing towards what you describe.

    More than not providing sample data, I think the problem is that you do not clearly define any rules concerning the change of subforms nor about the structure of the database. If there is none, i.e. if you can have any number of rows/columns for a data unit (whatever the way it's defined), then you probably have a problem with the database structure which lacks of normalization.

    I might be wrong entirely because, as I wrote here above, I don't have the information that would be necessary to understand what's the purpose of the database, nor how it's organized and how it works.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts