locked
Two subforms in a form RRS feed

  • Question

  • Hi,

    I have a form with two subforms (sbf1, and sbf2). The parent table is Schedule, which has child table ScheduleItem. On the form I display the Schedule information. On subform sbf1, i display all the children records (ScheduleItem) for Schedule record on a datasheet. When I move cursor on the datasheet with the SchedueItem records, I would like to view details of the ScheduleItem on the 2nd subform sbf2. The datasheet portion works fine, however how do I setup 1-1 relationship between the two subforms. How do i set up the second subform so that it will display the details of the record the cursor is on, on the datasheet on the first subform.

    Any help is appreciated.

    Thanks.

    sh


    Wednesday, April 9, 2014 5:44 AM

Answers

All replies

  • All you have to do is, set the second form to be exactly the same as the first (except for the Form view instead of Datasheet view). Then using one of the events like Double click or Single click. you can have something like.

        Me.Parent.subFrm_2.SetFocus
        With Me.Parent.subFrm_2.Form.Recordset
            .FindFirst "someIDField = " & Me.theCommonID
        End With


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Wednesday, April 9, 2014 8:52 AM
  • See

    UtterAccess thread: Referencing a subform field from a 2nd subform (especially post #3)

    UtterAccess thread: Link Two Subforms (espcially post #18)

    on using an intermediate (TextBox) Control on the MainForm to synchronize 2 SubForms.

     


    Van Dinh

    • Marked as answer by borg13 Friday, April 11, 2014 1:24 AM
    Wednesday, April 9, 2014 9:49 AM
  • Thanks! That worked just the way i wanted to.
    Thursday, April 10, 2014 2:17 AM
  • Now, when I update the record on the second subform that is displaying the details of the record, I get error “Write Conflict” and a window to either save the record, copy to clipboard, or drop changes when I try to Requery the first subform with datasheet view.

    I have already updated the recordset with update command. I tried to “DoCmd.RunCommand acSaveRecord” before the Requery, this did not work.  I also tried to change the Record Lock property in the second subform from “No Locks” to “Edited Record”, and this did not work either.

    Any idea how I can resolve this.  Thanks.

    sh

    Friday, April 11, 2014 4:37 AM
  • >>
    I tried to “DoCmd.RunCommand acSaveRecord” before the Requery, this did not work.
    <<

    1.  I am not aware of the intrinsic constant acSaveRecord??? Are you sure you had the correct statement? 

    The one I use is acCmdSaveRecord 

    2.  If you meant "DoCmd.RunCommand acCmdSaveRecord", the statement applies to the "Active Data Object" but do you know which one this applies to since "Active data Object" can be either the MainForm, SubForm1 or SubForm2.

    Please post the set-up/context/process that leads to the above saving and the full Procedure code that has the statement "DoCmd.RunCommand acCmdSaveRecord".

     

    3. Do you update the record on the first SubForm?

         


    Van Dinh

    Friday, April 11, 2014 6:20 AM
  • Hi Van, thank you for taking the time for your response.

    1. I have corrected to use acCmdSaveRecord. I still get same error (write conflict).

    2. The DoCmd applies to SubForm2, which displays the detail scheduleitem. On the SubForm1, no updates occur, it just displays the records in the datasheet form.  The update and add occurs on the SubForm2 only. All the fields on the MainForm are disabled also as they display the schedule (header) record.

    3. The record is updated on SubForm2.

    4. Now, when i try to insert a record, I get another error - "You are about to append 1 row(s). Click yes to add or no to undo".

    I have posted the code below. Thank you for your help.

    Option Compare Database

    '

    Private contacts_vol As DAO.Recordset

    Private rs_duty As DAO.Recordset

    Private schedule_detail As DAO.Recordset

    '

    Private blnReturnCode As Boolean

    Private dosave As Boolean

    '

    Private Const ADD = 1

    Private Const CANCEL = 2

    Private Const REPLACE = 3

    Private Const CHANGE_DATE = 4

    Private Const CHANGE_DUTY = 5

    Private Const UNCANCEL = 6

    '

    Private Sub Form_Current()

        Dim intRecordCount As Integer

        Dim i1 As Integer

        ' requery the detail screen when cursor moves on the datasheet of schedule detail

        Forms!ScheduleDetailMaint!sbfScheduleItem.Requery

        '

        ' set the combo for name to keep focus on the current volunteer name

        intRecordCount = Me.cboName.ListCount - 1

        For i1 = 0 To intRecordCount

            If Me.cboName.Column(0, i1) = Me.ContactID Then

                Me.txtName = Me.cboName.Column(1, i1)

                ' Me.cboName.Visible = False

                Exit For

            End If

        Next

       

    End Sub

    Private Sub Form_Load()

    On Error GoTo E_Handler

        Dim strSQL As String

        Dim strDomain As String

        Dim strCriteria As String

        Dim strFields As String

        Dim strName As String

        Dim strOrderBy As String

        Dim intID As Integer

        Dim intVID As Integer

        Dim i1 As Integer

       

        '

        ' set up master->child relationship between the main form and the detail view form

        Forms!ScheduleDetailMaint!sbfScheduleItem.LinkMasterFields = "[txtID]"

        Forms!ScheduleDetailMaint!sbfScheduleItem.LinkChildFields = "[ScheduleDetail].[ID]"

        '

        ' load all volunteers and their names into the combo list.

        strDomain = "Contacts INNER JOIN Volunteer ON Contacts.ID = Volunteer.ContactID "

        If Forms!ScheduleDetailMaint.JKID > 0 Then

            strCriteria = "Contacts.JKID = " & Forms!ScheduleDetailMaint.JKID

        Else

            strCriteria = ""

        End If

        '

        ' get the contact and volunteer tabl fields, to load the name & ids onto the combobox

        strFields = " Contacts.ID as cID, Volunteer.ID as vID, LastName & '_' & FirstName & '_' & MiddleName as sName"

        strOrderBy = " LastName, FirstName, MiddleName "

        Set contacts_vol = GetRecordSet(strDomain, strFields, strCriteria, strOrderBy)

        If contacts_vol.EOF = False Then

            i1 = 0

            ' load the combobox

            Do While contacts_vol.EOF = False

                intID = contacts_vol("cID")

                intVID = contacts_vol("vID")

                strName = contacts_vol("sName")

                Me.cboName.AddItem intID & ";" & strName & ";" & intVID, i1

                contacts_vol.MoveNext

                i1 = i1 + 1

            Loop

        End If

       

    Exit_E_Handler:

        Exit Sub

       

    E_Handler:

        MsgBox Err.Description, vbInformation, conTittle

        Resume Exit_E_Handler

    End Sub

    Private Sub cmdSave_Click()

    On Error GoTo E_Handler

        Dim intCID As Integer

        Dim intVID As Integer

        Dim i1 As Integer

        Dim intIndex As Integer

        '

        Dim strDomain As String

        Dim strCriteria As String

        Dim strFields As String

        Dim strMessage As String

        Dim strValues As String

        Dim strStatus As String

        Dim strSource As String

       

        blnsave = False

        dosave = False          ' true= ready to save record

        '

        ' perform edits

        If Form_Edits() = False Then

            GoTo Exit_E_Handler

        End If

        '

        strDomain = "ScheduleDetail"

        strCriteria = "ID = " & Me.ID

        Set schedule_detail = GetRecordSet(strDomain, strFields, strCriteria)

        If schedule_detail.EOF = True Then

            MsgBox "System Error, could not find Schedule Detail record, or click Close and report the error", vbInformation, conTittle

            Me.fraOption.SetFocus

            GoTo Exit_E_Handler

        End If

        '

        Select Case Me.fraOption.Value

            Case CANCEL:

                schedule_detail.Edit

                schedule_detail!Status = "C"

                dosave = True

                strMessage = "Duty cancelled successfully"

            Case UNCANCEL:

                schedule_detail.Edit

                schedule_detail!Status = ""

                dosave = True

                strMessage = "Duty uncancelled successfully"

            Case CHANGE_DATE:

                schedule_detail.Edit

                schedule_detail!DutyDate = Me.DutyDate

                dosave = True

                strMessage = "Duty date change successfully"

            Case REPLACE:

                schedule_detail.Edit

                schedule_detail!Status = "R"

                schedule_detail!LogUser = Forms("Dashboard").txtUserID

                schedule_detail!LogDateTime = Now

                schedule_detail.Update      ' update then create new record

                '

                ' create a new record for the volunteer replacing the original one.

                intIndex = Me.cboName.ListIndex

                intCID = Me.cboName.Column(0, intIndex)

                intVID = Me.cboName.Column(2, intVID)

                strDomain = "ScheduleDetail"

                strStatus = "G"

                strSource = "M"

                strFields = "ScheduleID, VolunteerID, DutyDate, DutyID, PrintSeq, Status, Source, Remarks, LogDateTime, LogUser"

                strValues = Me.ScheduleID & ", " & intVID & ", " & "#" & Me.DutyDate & "#" & ", " _

                            & Me.DutyID & ", " & 0 & ", '" & strStatus & "', '" & strSource & "', '" & Me.Remarks & "', " _

                            & "#" & Now() & "#,'" & Forms("Dashboard").txtUserID & "'"

                strSQL = "INSERT INTO " & strDomain & "  (" & strFields & ") VALUES (" & strValues & ")"

                '

                ' insert

                DoCmd.RunSQL strSQL

                DoCmd.RunCommand acCmdSaveRecord

                If IsNull(strMessage) = False And strMessage <> "" Then

                    GoTo Exit_E_Handler

                End If

                dosave = True

                strMessage = "Duty date change successfully"

        End Select

        '

        If dosave = True Then

            ' if replace, we have aready saved original, and created new record. for other actions, save the record now

            If Me.fraOption.Value <> REPLACE Then

                schedule_detail!LogUser = Forms("Dashboard").txtUserID

                schedule_detail!LogDateTime = Now

                schedule_detail.Update

                DoCmd.RunCommand acCmdSaveRecord

            End If

            '

            ' requery the other subform wih datasheet so we can display the changes

            Forms!ScheduleDetailMaint!sbfScheduleDetail.Requery

            Forms!ScheduleDetailMaint.txtStatus = strMessage

        Else

            Me.Undo

        End If

        '

        ' disable all the controls, until option box clicked again

        DisableFields

        '

        Me.Dirty = False

    Exit_E_Handler:

        Exit Sub

       

    E_Handler:

        dosave = False

        MsgBox Err.Description, vbInformation, conTittle

        Resume Exit_E_Handler

    End Sub

    Private Sub Form_BeforeUpdate(CANCEL As Integer)

    On Error GoTo E_Handler

        If dosave = False Then

            CANCEL = True

            Me.Undo

        End If

           

    Exit_E_Handler:

        Exit Sub

       

    E_Handler:

        dosave = False

        MsgBox Err.Description, vbInformation, conTittle

        Resume Exit_E_Handler

    End Sub

    Saturday, April 12, 2014 5:12 AM
  • I don't have time to look at the code carefully but this part looks incorrect to me:

     [quote]

              strValues = Me.ScheduleID & ", " & intVID & ", " & "#" & Me.DutyDate & "#" & ", " _

                            & Me.DutyID & ", " & 0 & ", '" & strStatus & "', '" & strSource & "', '" & Me.Remarks & "', " _

                            & "#" & Now() & "#,'" & Forms("Dashboard").txtUserID & "'"

                strSQL = "INSERT INTO " & strDomain & "  (" & strFields & ") VALUES (" & strValues & ")"

                '

                ' insert

                DoCmd.RunSQL strSQL

                DoCmd.RunCommand acCmdSaveRecord

    [quote]

    DoCmd.RunSQL adds the record independently of the (bound) Form then you have DoCmd.RunCommand acCmdSaveRecord which tries to save the Form's CurrentRecord buffer also.  It seems the saving of the record is being done twice to me.


    Van Dinh

    Thursday, April 17, 2014 3:11 AM
  • Thank you for taking time.

    I will look at that.

    Monday, April 21, 2014 3:46 AM